usingSystem;usingSystem.Collections;usingSystem.Collections.Specialized;usingSystem.Data;usingMySql.Data.MySqlClient;usingSystem.Configuration;usingSystem.Data.Common;usingSystem.Collections.Generic;namespaceStudent_Client
{///
///数据访问抽象基础类///Copyright (C) 2004-2008 By LiTianPing///
public abstract classDbHelperMySQL
{//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
public static string connectionString = "server=127.0.0.1;user id=root;pwd=root;database=06a_exam";publicDbHelperMySQL()
{
}#region 公用方法
///
///得到最大值///
///
///
///
public static int GetMaxID(string FieldName, stringTableName)
{string strsql = "select max(" + FieldName + ")+1 from" +TableName;object obj =GetSingle(strsql);if (obj == null)
{return 1;
}else{return int.Parse(obj.ToString());
}
}///
///是否存在///
///
///
public static bool Exists(stringstrSql)
{object obj =GetSingle(strSql);intcmdresult;if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))
{
cmdresult= 0;
}else{
cmdresult= int.Parse(obj.ToString());
}if (cmdresult == 0)
{return false;
}else{return true;
}
}///
///是否存在(基于MySqlParameter)///
///
///
///
public static bool Exists(string strSql, paramsMySqlParameter[] cmdParms)
{object obj =GetSingle(strSql, cmdParms);intcmdresult;if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))
{
cmdresult= 0;
}else{
cmdresult= int.Parse(obj.ToString());
}if (cmdresult == 0)
{return false;
}else{return true;
}
}#endregion
#region 执行简单SQL语句
///
///执行SQL语句,返回影响的记录数///
/// SQL语句
/// 影响的记录数
public static int ExecuteSql(stringSQLString)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))
{try{
connection.Open();int rows =cmd.ExecuteNonQuery();returnrows;
}catch(MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();throwe;
}
}
}
}public static int ExecuteSqlByTime(string SQLString, intTimes)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))
{try{
connection.Open();
cmd.CommandTimeout=Times;int rows =cmd.ExecuteNonQuery();returnrows;
}catch(MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();throwe;
}
}
}
}///
///执行多条SQL语句,实现数据库事务。///
/// 多条SQL语句
public static int ExecuteSqlTran(ListSQLStringList)
{using (MySqlConnection conn = newMySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd= newMySqlCommand();
cmd.Connection=conn;
MySqlTransaction tx=conn.BeginTransaction();
cmd.Transaction=tx;try{int count = 0;for (int n = 0; n < SQLStringList.Count; n++)
{string strsql =SQLStringList[n];if (strsql.Trim().Length > 1)
{
cmd.CommandText=strsql;
count+=cmd.ExecuteNonQuery();
}
}
tx.Commit();returncount;
}catch{
tx.Rollback();return 0;
}
}
}///
///执行带一个存储过程参数的的SQL语句。///
/// SQL语句
/// 参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加
/// 影响的记录数
public static int ExecuteSql(string SQLString, stringcontent)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{
MySqlCommand cmd= newMySqlCommand(SQLString, connection);
MySql.Data.MySqlClient.MySqlParameter myParameter= new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
myParameter.Value=content;
cmd.Parameters.Add(myParameter);try{
connection.Open();int rows =cmd.ExecuteNonQuery();returnrows;
}catch(MySql.Data.MySqlClient.MySqlException e)
{throwe;
}finally{
cmd.Dispose();
connection.Close();
}
}
}///
///执行带一个存储过程参数的的SQL语句。///
/// SQL语句
/// 参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加
/// 影响的记录数
public static object ExecuteSqlGet(string SQLString, stringcontent)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{
MySqlCommand cmd= newMySqlCommand(SQLString, connection);
MySql.Data.MySqlClient.MySqlParameter myParameter= new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
myParameter.Value=content;
cmd.Parameters.Add(myParameter);try{
connection.Open();object obj =cmd.ExecuteScalar();if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))
{return null;
}else{returnobj;
}
}catch(MySql.Data.MySqlClient.MySqlException e)
{throwe;
}finally{
cmd.Dispose();
connection.Close();
}
}
}///
///向数据库里插入图像格式的字段(和上面情况类似的另一种实例)///
/// SQL语句
/// 图像字节,数据库的字段类型为image的情况
/// 影响的记录数
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{
MySqlCommand cmd= newMySqlCommand(strSQL, connection);
MySql.Data.MySqlClient.MySqlParameter myParameter= new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
myParameter.Value=fs;
cmd.Parameters.Add(myParameter);try{
connection.Open();int rows =cmd.ExecuteNonQuery();returnrows;
}catch(MySql.Data.MySqlClient.MySqlException e)
{throwe;
}finally{
cmd.Dispose();
connection.Close();
}
}
}///
///执行一条计算查询结果语句,返回查询结果(object)。///
/// 计算查询结果语句
/// 查询结果(object)
public static object GetSingle(stringSQLString)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))
{try{
connection.Open();object obj =cmd.ExecuteScalar();if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))
{return null;
}else{returnobj;
}
}catch(MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();throwe;
}
}
}
}public static object GetSingle(string SQLString, intTimes)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))
{try{
connection.Open();
cmd.CommandTimeout=Times;object obj =cmd.ExecuteScalar();if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))
{return null;
}else{returnobj;
}
}catch(MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();throwe;
}
}
}
}///
///执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )///
/// 查询语句
/// MySqlDataReader
public static MySqlDataReader ExecuteReader(stringstrSQL)
{
MySqlConnection connection= newMySqlConnection(connectionString);
MySqlCommand cmd= newMySqlCommand(strSQL, connection);try{
connection.Open();
MySqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);returnmyReader;
}catch(MySql.Data.MySqlClient.MySqlException e)
{throwe;
}
}///
///执行查询语句,返回DataSet///
/// 查询语句
/// DataSet
public static DataSet Query(stringSQLString)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{
DataSet ds= newDataSet();try{
connection.Open();
MySqlDataAdapter command= newMySqlDataAdapter(SQLString, connection);
command.Fill(ds,"ds");
}catch(MySql.Data.MySqlClient.MySqlException ex)
{throw newException(ex.Message);
}returnds;
}
}public static DataSet Query(string SQLString, intTimes)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{
DataSet ds= newDataSet();try{
connection.Open();
MySqlDataAdapter command= newMySqlDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout=Times;
command.Fill(ds,"ds");
}catch(MySql.Data.MySqlClient.MySqlException ex)
{throw newException(ex.Message);
}returnds;
}
}#endregion
#region 执行带参数的SQL语句
///
///执行SQL语句,返回影响的记录数///
/// SQL语句
/// 影响的记录数
public static int ExecuteSql(string SQLString, paramsMySqlParameter[] cmdParms)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand())
{try{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);int rows =cmd.ExecuteNonQuery();
cmd.Parameters.Clear();returnrows;
}catch(MySql.Data.MySqlClient.MySqlException e)
{throwe;
}
}
}
}///
///执行多条SQL语句,实现数据库事务。///
/// SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])
public static voidExecuteSqlTran(Hashtable SQLStringList)
{using (MySqlConnection conn = newMySqlConnection(connectionString))
{
conn.Open();using (MySqlTransaction trans =conn.BeginTransaction())
{
MySqlCommand cmd= newMySqlCommand();try{//循环
foreach (DictionaryEntry myDE inSQLStringList)
{string cmdText =myDE.Key.ToString();
MySqlParameter[] cmdParms=(MySqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val =cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}catch{
trans.Rollback();throw;
}
}
}
}///
///执行多条SQL语句,实现数据库事务。///
/// SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])
public static voidExecuteSqlTranWithIndentity(Hashtable SQLStringList)
{using (MySqlConnection conn = newMySqlConnection(connectionString))
{
conn.Open();using (MySqlTransaction trans =conn.BeginTransaction())
{
MySqlCommand cmd= newMySqlCommand();try{int indentity = 0;//循环
foreach (DictionaryEntry myDE inSQLStringList)
{string cmdText =myDE.Key.ToString();
MySqlParameter[] cmdParms=(MySqlParameter[])myDE.Value;foreach (MySqlParameter q incmdParms)
{if (q.Direction ==ParameterDirection.InputOutput)
{
q.Value=indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val =cmd.ExecuteNonQuery();foreach (MySqlParameter q incmdParms)
{if (q.Direction ==ParameterDirection.Output)
{
indentity=Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}catch{
trans.Rollback();throw;
}
}
}
}///
///执行一条计算查询结果语句,返回查询结果(object)。///
/// 计算查询结果语句
/// 查询结果(object)
public static object GetSingle(string SQLString, paramsMySqlParameter[] cmdParms)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand())
{try{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);object obj =cmd.ExecuteScalar();
cmd.Parameters.Clear();if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))
{return null;
}else{returnobj;
}
}catch(MySql.Data.MySqlClient.MySqlException e)
{throwe;
}
}
}
}///
///执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )///
/// 查询语句
/// MySqlDataReader
public static MySqlDataReader ExecuteReader(string SQLString, paramsMySqlParameter[] cmdParms)
{
MySqlConnection connection= newMySqlConnection(connectionString);
MySqlCommand cmd= newMySqlCommand();try{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);
MySqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();returnmyReader;
}catch(MySql.Data.MySqlClient.MySqlException e)
{throwe;
}//finally//{//cmd.Dispose();//connection.Close();//}
}///
///执行查询语句,返回DataSet///
/// 查询语句
/// DataSet
public static DataSet Query(string SQLString, paramsMySqlParameter[] cmdParms)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{
MySqlCommand cmd= newMySqlCommand();
PrepareCommand(cmd, connection,null, SQLString, cmdParms);using (MySqlDataAdapter da = newMySqlDataAdapter(cmd))
{
DataSet ds= newDataSet();try{
da.Fill(ds,"ds");
cmd.Parameters.Clear();
}catch(MySql.Data.MySqlClient.MySqlException ex)
{throw newException(ex.Message);
}returnds;
}
}
}private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, stringcmdText, MySqlParameter[] cmdParms)
{if (conn.State !=ConnectionState.Open)
conn.Open();
cmd.Connection=conn;
cmd.CommandText=cmdText;if (trans != null)
cmd.Transaction=trans;
cmd.CommandType= CommandType.Text;//cmdType;
if (cmdParms != null)
{foreach (MySqlParameter parameter incmdParms)
{if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&(parameter.Value== null))
{
parameter.Value=DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}#endregion
#region 执行带参数的存储过程
///
///执行带参数的存储过程///
///
///
///
public static object ExecuteProc(string SQLString, paramsMySqlParameter[] cmdParms)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand())
{try{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);
cmd.CommandType=CommandType.StoredProcedure;int rows =cmd.ExecuteNonQuery();
Console.WriteLine("返回值为" + cmdParms[1].Value);
cmd.Parameters.Clear();returnrows;
}catch(MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();throwe;
}
}
}
}///
///存储过程查询DataTable///
///
///
///
public static DataTable QueryProc(string SQLString, paramsMySqlParameter[] cmdParms)
{using (MySqlConnection connection = newMySqlConnection(connectionString))
{using (MySqlCommand cmd = newMySqlCommand())
{try{
PrepareCommand(cmd, connection,null, SQLString, cmdParms);
cmd.CommandType=CommandType.StoredProcedure;
MySqlDataAdapter adapter= newMySqlDataAdapter(cmd);
DataTable dt= newDataTable();
adapter.Fill(dt);
cmd.Parameters.Clear();returndt;
}catch(MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();throwe;
}
}
}
}#endregion}
}