usingSystem;usingSystem.Collections;usingSystem.Collections.Specialized;usingSystem.Data;usingMySql.Data.MySqlClient;usingSystem.Configuration;usingSystem.Data.Common;usingSystem.Collections.Generic;usingSystem.Data.SqlClient;usingSystem.Text;namespacePluginManager.PluginManagerCore
{///
///数据访问抽象基础类///
public abstract classDbHelperMySQL
{//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
public static string connectionString = "Server=172.18.131.52;Database=zabbix; User=root;Password=123456;Use Procedure Bodies=false;Charset=utf8;Allow Zero Datetime=True; Pooling=True; Max Pool Size=50;";publicDbHelperMySQL()
{
}public enumEffentNextType
{/// ///对其他语句无任何影响///
None,/// ///当前语句必须为"select count(1) from .."格式,如果存在则继续执行,不存在回滚事务///
WhenHaveContine,/// ///当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务///
WhenNoHaveContine,/// ///当前语句影响到的行数必须大于0,否则回滚事务///
ExcuteEffectRows,/// ///引发事件-当前语句必须为"select count(1) from .."格式,如果不存在则继续执行,存在回滚事务///
SolicitationEvent
}public classCommandInfo
{public object ShareObject = null;public object OriginalData = null;eventEventHandler _solicitationEvent;public eventEventHandler SolicitationEvent
{
add
{
_solicitationEvent+=value;
}
remove
{
_solicitationEvent-=value;
}
}public voidOnSolicitationEvent()
{if (_solicitationEvent != null)
{
_solicitationEvent(this, newEventArgs());
}
}public stringCommandText;publicSystem.Data.Common.DbParameter[] Parameters;public EffentNextType EffentNextType =EffentNextType.None;publicCommandInfo()
{
}public CommandInfo(stringsqlText, SqlParameter[] para)
{this.CommandText =sqlText;this.Parameters =para;
}public CommandInfo(stringsqlText, SqlParameter[] para, EffentNextType type)
{this.CommandText =sqlText;this.Parameters =para;this.EffentNextType =type;
}
}///
///得到最大值///
///
///
///
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;
}
}///
///执行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, List oracleCmdSqlList)//{//using (MySqlConnection conn = new MySqlConnection(connectionString))//{//conn.Open();//MySqlCommand cmd = new MySqlCommand();//cmd.Connection = conn;//MySqlTransaction tx = conn.BeginTransaction();//cmd.Transaction = tx;//try//{//foreach (CommandInfo myDE in list)//{//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();//throw e;//}//catch (Exception e)//{//tx.Rollback();//throw e;//}//}//}
///
///执行多条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,"dataset");
}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;
}
}///
///执行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);
}
}
}///
///table转json///
///
///
public static stringDataTableToJson(DataTable dt)
{
StringBuilder jsonBuilder= newStringBuilder();
jsonBuilder.Append("{\"Name\":\"" + dt.TableName + "\",\"Rows");
jsonBuilder.Append("\":[");for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{");for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Columns[j].ColumnName);
jsonBuilder.Append("\":\"");
jsonBuilder.Append(dt.Rows[i][j].ToString().Replace("\"", "\\\""));
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length- 1, 1);
jsonBuilder.Append("},");
}
jsonBuilder.Remove(jsonBuilder.Length- 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");returnjsonBuilder.ToString();
}///
///dataset转Json///
///
///
public static stringDatasetToJson(DataSet ds)
{
StringBuilder json= newStringBuilder();
json.Append("{\"Tables\":");
json.Append("[");foreach (DataTable dt inds.Tables)
{
json.Append(DataTableToJson(dt));
json.Append(",");
}
json.Remove(json.Length- 1, 1);
json.Append("]");
json.Append("}");returnjson.ToString();
}
}
}