usingSystem;usingSystem.Collections;usingSystem.Collections.Specialized;usingSystem.Data;usingMySql.Data.MySqlClient;usingSystem.Configuration;usingSystem.Data.Common;usingSystem.Collections.Generic;namespaceMaticsoft.DBUtility
{///
///数据访问抽象基础类///Copyright (C) Maticsoft///
public abstract classDbHelperMySQL
{//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
public static string connectionString =ConfigurationManager.AppSettings["ConnectionString"];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;
}
}
}
}///
///执行MySql和Oracle滴混合事务///
/// SQL命令行列表
/// Oracle命令行列表
/// 执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功
public static int ExecuteSqlTran(List list, ListoracleCmdSqlList)
{using (MySqlConnection conn = newMySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd= newMySqlCommand();
cmd.Connection=conn;
MySqlTransaction tx=conn.BeginTransaction();
cmd.Transaction=tx;try{foreach (CommandInfo myDE inlist)
{string cmdText =myDE.CommandText;
MySqlParameter[] cmdParms=(MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);if (myDE.EffentNextType ==EffentNextType.SolicitationEvent)
{if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");//return 0;
}object obj =cmd.ExecuteScalar();bool isHave = false;if (obj == null && obj ==DBNull.Value)
{
isHave= false;
}
isHave= Convert.ToInt32(obj) > 0;if(isHave)
{//引发事件
myDE.OnSolicitationEvent();
}
}if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType ==EffentNextType.WhenNoHaveContine)
{if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");//return 0;
}object obj =cmd.ExecuteScalar();bool isHave = false;if (obj == null && obj ==DBNull.Value)
{
isHave= false;
}
isHave= Convert.ToInt32(obj) > 0;if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");//return 0;
}if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine &&isHave)
{
tx.Rollback();throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");//return 0;
}continue;
}int val =cmd.ExecuteNonQuery();if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");//return 0;
}
cmd.Parameters.Clear();
}string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");bool res =OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);if (!res)
{
tx.Rollback();throw new Exception("执行失败");//return -1;
}
tx.Commit();return 1;
}catch(MySql.Data.MySqlClient.MySqlException e)
{
tx.Rollback();throwe;
}catch(Exception e)
{
tx.Rollback();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 int ExecuteSqlTran(System.Collections.Generic.ListcmdList)
{using (MySqlConnection conn = newMySqlConnection(connectionString))
{
conn.Open();using (MySqlTransaction trans =conn.BeginTransaction())
{
MySqlCommand cmd= newMySqlCommand();try{int count = 0;//循环
foreach (CommandInfo myDE incmdList)
{string cmdText =myDE.CommandText;
MySqlParameter[] cmdParms=(MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType ==EffentNextType.WhenNoHaveContine)
{if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();return 0;
}object obj =cmd.ExecuteScalar();bool isHave = false;if (obj == null && obj ==DBNull.Value)
{
isHave= false;
}
isHave= Convert.ToInt32(obj) > 0;if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();return 0;
}if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine &&isHave)
{
trans.Rollback();return 0;
}continue;
}int val =cmd.ExecuteNonQuery();
count+=val;if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();return 0;
}
cmd.Parameters.Clear();
}
trans.Commit();returncount;
}catch{
trans.Rollback();throw;
}
}
}
}///
///执行多条SQL语句，实现数据库事务。///
/// SQL语句的哈希表(key为sql语句，value是该语句的MySqlParameter[])
public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.ListSQLStringList)
{using (MySqlConnection conn = newMySqlConnection(connectionString))
{
conn.Open();using (MySqlTransaction trans =conn.BeginTransaction())
{
MySqlCommand cmd= newMySqlCommand();try{int indentity = 0;//循环
foreach (CommandInfo myDE inSQLStringList)
{string cmdText =myDE.CommandText;
MySqlParameter[] cmdParms=(MySqlParameter[])myDE.Parameters;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;
}
}
}
}///
///执行多条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}
}
335
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
