MySql工具类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Collections; using MySql.Data.MySqlClient; namespace skylark.SqlTool { public class MySqlDBHelper { public static string connectionString = ConnectConfig.DBConnectionString; public MySqlDBHelper() { } private static void init(DataTable dtStru, MySql.Data.MySqlClient.MySqlDataAdapter adapter, MySqlConnection connection) { string strTableName = dtStru.TableName; DataColumn[] colPkName = dtStru.PrimaryKey; string strInsertField = ""; string strInsertValue = ""; string strUpdate = ""; //插入语句 string strInsertSql = ""; //修改语句 string strUpdateSql = ""; //删除语句 string strDeleteSql = ""; string strWhereSql = ""; foreach (DataColumn dc in colPkName) { if (strWhereSql == "") strWhereSql = " where " + dc.ColumnName + @" = ?Original_" + dc.ColumnName; else strWhereSql += " and " + dc.ColumnName + @" = ?Original_" + dc.ColumnName; } MySql.Data.MySqlClient.MySqlCommand sqlInsertComm = new MySql.Data.MySqlClient.MySqlCommand(); MySql.Data.MySqlClient.MySqlCommand sqlUpdateComm = new MySql.Data.MySqlClient.MySqlCommand(); MySql.Data.MySqlClient.MySqlCommand sqlDeleteComm = new MySql.Data.MySqlClient.MySqlCommand(); //动态生成映射列 System.Data.Common.DataTableMapping dTableMap = new System.Data.Common.DataTableMapping("Table", strTableName); foreach (DataColumn dcField in dtStru.Columns) { dTableMap.ColumnMappings.Add(dcField.ColumnName, dcField.ColumnName); //如果不是自动增长的字段 if (!dcField.AutoIncrement) { if (strInsertField == "") { strInsertField = dcField.ColumnName; strInsertValue = "?" + dcField.ColumnName; } else { strInsertField += "," + dcField.ColumnName; strInsertValue += ",?" + dcField.ColumnName; } if (!dcField.Unique) { if (strUpdate == "") { strUpdate = dcField.ColumnName + "=?" + dcField.ColumnName; } else { strUpdate += "," + dcField.ColumnName + "=?" + dcField.ColumnName; } } } } strInsertSql = @"INSERT INTO " + strTableName + " (" + strInsertField + ") values(" + strInsertValue + ")"; strUpdateSql = @"update " + strTableName + " set " + strUpdate + strWhereSql; strDeleteSql = @"delete from " + strTableName + strWhereSql; adapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { dTableMap }); // // Adapter // adapter.DeleteCommand = sqlDeleteComm; adapter.InsertCommand = sqlInsertComm; adapter.UpdateCommand = sqlUpdateComm; // // sqlInsertComm // //动态生成插入语句 sqlInsertComm.CommandText = strInsertSql; sqlInsertComm.Connection = connection; BuildPara(sqlInsertComm, dtStru); // // sqlUpdateComm // sqlUpdateComm.CommandText = strUpdateSql; sqlUpdateComm.Connection = connection; BuildPara(sqlUpdateComm, dtStru); BuildWherePara(sqlUpdateComm, dtStru); // // sqlDeleteComm // sqlDeleteComm.CommandText = strDeleteSql; sqlDeleteComm.Connection = connection; BuildWherePara(sqlDeleteComm, dtStru); } /// <summary> /// 验证表结构 /// </summary> /// <param name="dtStru">DataTable</param> /// <param name="adapter"></param> /// <param name="arlCol">在SQlServer表中的列名称</param> private static void init(DataTable dtStru, MySqlDataAdapter adapter, MySqlConnection connection, ArrayList arlCol) { string strTableName = dtStru.TableName; DataColumn[] colPkName = dtStru.PrimaryKey; string strInsertField = ""; string strInsertValue = ""; string strUpdate = ""; //插入语句 string strInsertSql = ""; //修改语句 string strUpdateSql = ""; //删除语句 string strDeleteSql = ""; string strWhereSql = ""; foreach (DataColumn dc in colPkName) { if (strWhereSql == "") strWhereSql = " where " + dc.ColumnName + @" = ?Original_" + dc.ColumnName; else strWhereSql += " and " + dc.ColumnName + @" = ?Original_" + dc.ColumnName; } MySqlCommand sqlInsertComm = new MySqlCommand(); MySqlCommand sqlUpdateComm = new MySqlCommand(); MySqlCommand sqlDeleteComm = new MySqlCommand(); //动态生成映射列 System.Data.Common.DataTableMapping dTableMap = new System.Data.Common.DataTableMapping("Table", strTableName); foreach (DataColumn dcField in dtStru.Columns) { bool bnlIsExistCol = false; dTableMap.ColumnMappings.Add(dcField.ColumnName, dcField.ColumnName); //检查在表结构中是否存在 foreach (string aStr in arlCol) { if (aStr.Equals(dcField.ColumnName)) { bnlIsExistCol = true; } } //如果不是自动增长的字段 if (!dcField.AutoIncrement && bnlIsExistCol) { if (strInsertField == "") { strInsertField = dcField.ColumnName; strInsertValue = "?" + dcField.ColumnName; strUpdate = dcField.ColumnName + "=?" + dcField.ColumnName; } else { strInsertField += "," + dcField.ColumnName; strInsertValue += ",?" + dcField.ColumnName; strUpdate += "," + dcField.ColumnName + "=?" + dcField.ColumnName; } } } strInsertSql = @"INSERT INTO " + strTableName + " (" + strInsertField + ") values(" + strInsertValue + ")"; strUpdateSql = @"update " + strTableName + " set " + strUpdate + strWhereSql; strDeleteSql = @"delete from " + strTableName + strWhereSql; adapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { dTableMap }); // // Adapter // adapter.DeleteCommand = sqlDeleteComm; adapter.InsertCommand = sqlInsertComm; adapter.UpdateCommand = sqlUpdateComm; // // sqlInsertComm // //动态生成插入语句 sqlInsertComm.CommandText = strInsertSql; sqlInsertComm.Connection = connection; BuildPara(sqlInsertComm, dtStru); // // sqlUpdateComm // sqlUpdateComm.CommandText = strUpdateSql; sqlUpdateComm.Connection = connection; BuildPara(sqlUpdateComm, dtStru); BuildWherePara(sqlUpdateComm, dtStru); // // sqlDeleteComm // sqlDeleteComm.CommandText = strDeleteSql; sqlDeleteComm.Connection = connection; BuildWherePara(sqlDeleteComm, dtStru); } private static void BuildPara(MySql.Data.MySqlClient.MySqlCommand mSqlommand, DataTable dt) { foreach (DataColumn dc in dt.Columns) { //如果不是自动增长的字段, 创建参数 if (!dc.AutoIncrement) { switch (dc.DataType.Name.ToString()) { case "String": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.VarChar, 0, dc.ColumnName)); break; case "Int16": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Int16, 0, dc.ColumnName)); break; case "Int32": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Int32, 0, dc.ColumnName)); break; case "Int64": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Int64, 0, dc.ColumnName)); break; case "Decimal": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Decimal, 0, dc.ColumnName)); break; case "Boolean": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Bit, 0, dc.ColumnName)); break; case "DateTime": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.DateTime, 0, dc.ColumnName)); break; case "VarBinary": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.VarBinary, 2147483647, dc.ColumnName)); break; case "Byte[]": mSqlommand.Parameters.Add(new MySqlParameter("?" + dc.ColumnName, MySqlDbType.VarBinary, 2147483647, dc.ColumnName)); break; } } } } private static void BuildWherePara(MySql.Data.MySqlClient.MySqlCommand mSqlommand, DataTable dt) { foreach (DataColumn dc in dt.PrimaryKey) { switch (dc.DataType.Name.ToString()) { case "String": mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.VarChar, 0, dc.ColumnName)); break; case "Int16": mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Int16, 0, dc.ColumnName)); break; case "Int32": mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Int32, 0, dc.ColumnName)); break; case "Int64": mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Int64, 0, dc.ColumnName)); break; case "Decimal": mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Decimal, 0, dc.ColumnName)); break; case "Boolean": mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.Bit, 0, dc.ColumnName)); break; case "DateTime": mSqlommand.Parameters.Add(new MySqlParameter("?Original_" + dc.ColumnName, MySqlDbType.DateTime, 0, dc.ColumnName)); break; } } } /// <summary> /// 根据表名返回所有列名称 /// </summary> /// <param name="strTablename">表名称</param> /// <returns></returns> private static ArrayList GetColumn(string strTablename, MySqlTransaction myTrans) { try { MySqlParameter[] paramss = { new MySqlParameter{ParameterName="?table_name",Direction = ParameterDirection.Input, Value = strTablename} }; // 执行存储过程,返回指定的结果集行数,同时返回总行数 DataSet ds = Query("select column_name from information_schema.COLUMNS where table_name= ?table_name",paramss); if (ds != null && ds.Tables.Count > 0) { ArrayList arlCol = new ArrayList(); foreach (DataRow dr in ds.Tables[0].Rows) { arlCol.Add(dr["column_name"].ToString()); } return arlCol; }; return null; } catch (System.Exception e) { throw e; } } private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, 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; if (cmdParms != null) { foreach (MySqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #region 查询 /// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } return ds; } } } public static DataSet Query(string SQLString,string strTablename, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.MissingSchemaAction = MissingSchemaAction.AddWithKey; if (strTablename != "") da.Fill(ds, strTablename); else da.Fill(ds); cmd.Parameters.Clear(); } catch (MySql.Data.MySqlClient.MySqlException ex) { throw new Exception(ex.Message); } return ds; } } } /// <summary> /// 根据SQLString 获取记录集 /// </summary> /// <param name="commandText"></param> /// <param name="strTabname"></param> /// <returns></returns> public static DataSet Query(string commandText, string strTabname) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, null, commandText, null); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.MissingSchemaAction = MissingSchemaAction.AddWithKey; if (strTabname != "") da.Fill(ds, strTabname); else da.Fill(ds); } catch (System.Exception e) { throw e; } return ds; } } } /// <summary> /// 执行多条SQL /// 2011-04-11 解决排序问题 /// </summary> /// <param name="hstSQlTabel">Key: TableName Value: SQLs</param> /// <returns></returns> public static DataSet Query(ArrayList hstSQlTabel) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); using (MySqlDataAdapter da = new MySqlDataAdapter()) { DataSet data = new DataSet(); MySqlTransaction myTrans = connection.BeginTransaction(); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; try { for (int i = 0; i < hstSQlTabel.Count; i++) { Hashtable hst = (Hashtable)hstSQlTabel[i]; foreach (DictionaryEntry hstSql in hst) { DataSet ds = new DataSet(); MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection,myTrans, hstSql.Value.ToString(), null); da.SelectCommand = cmd; if (hstSql.Key.ToString() != "") da.Fill(ds, hstSql.Key.ToString()); else da.Fill(ds); data.Merge(ds); } } myTrans.Commit(); } catch (System.Exception e) { myTrans.Rollback(); throw e; } return data; } } } /// <summary> /// 执行多条SQL /// 2011-04-11 解决多表问题 /// </summary> /// <param name="hstSQlTabel">Key: TableName Value: SQLs</param> /// <returns></returns> public static DataSet Query(Hashtable hstSqlTable) { ArrayList arList = new ArrayList(); arList.Add(hstSqlTable); return Query(arList); } #endregion #region 更新数据 /// <summary> /// 根据记录集更新数据 /// </summary> /// <param name="data">用户进行增加删除修改操作的记录集</param> /// <returns></returns> public static string UpdateData(DataSet data) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); string blnResult = ""; int intTableNum = data.Tables.Count; MySqlDataAdapter[] adapter = new MySqlDataAdapter[intTableNum]; MySqlTransaction myTrans = connection.BeginTransaction(); try { for (int i = 0; i < intTableNum; i++) { MySqlDataAdapter adapterTmp = new MySqlDataAdapter(); adapter[i] = adapterTmp; init(data.Tables[i], adapter[i],connection); adapter[i].UpdateCommand.Transaction = myTrans; adapter[i].DeleteCommand.Transaction = myTrans; adapter[i].InsertCommand.Transaction = myTrans; } for (int i = 0; i < intTableNum; i++) { if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted)); if (data.GetChanges(DataRowState.Added) != null) adapter[i].Update(data.GetChanges(DataRowState.Added)); if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified)); } myTrans.Commit(); } catch (Exception e) { myTrans.Rollback(); blnResult = e.Message; throw e; } return blnResult; } } /// <summary> /// 根据记录集更新数据 /// 参数记录集data, 与SQlServer表结构不一致时使用(--待更新表的字段关联了一些其他表的字段) /// *** /// </summary> /// <param name="data">用户进行增加删除修改操作的记录集</param> /// <returns></returns> public static string UpdateDataCheckField(DataSet data) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { string blnResult = ""; int intTableNum = data.Tables.Count; connection.Open(); MySqlDataAdapter[] adapter = new MySqlDataAdapter[intTableNum]; MySqlTransaction myTrans = connection.BeginTransaction(); try { for (int i = 0; i < intTableNum; i++) { ArrayList arlCol = GetColumn(data.Tables[i].TableName, myTrans); MySqlDataAdapter adapterTmp = new MySqlDataAdapter(); adapter[i] = adapterTmp; init(data.Tables[i], adapter[i],connection, arlCol); adapter[i].UpdateCommand.Transaction = myTrans; adapter[i].DeleteCommand.Transaction = myTrans; adapter[i].InsertCommand.Transaction = myTrans; } for (int i = 0; i < intTableNum; i++) { if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted)); if (data.GetChanges(DataRowState.Added) != null) adapter[i].Update(data.GetChanges(DataRowState.Added)); if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified)); } myTrans.Commit(); } catch (Exception e) { myTrans.Rollback(); blnResult = e.Message; throw e; } return blnResult; } } #endregion #region 获取主键,更新主键表 /// <summary> /// 更新当前主键编号,获取主键编号 /// </summary> /// <param name="strFieldKey">主键标识--两位</param> /// <returns>Return[0]:成功与否;Return[1]:返回主键</returns> public static string[] getOrderNO(string strFieldKey) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { string[] blnResult = new string[2]; blnResult[0] = ""; blnResult[1] = ""; connection.Open(); string scommandtext = "SELECT KeyGroup,KeyNumber FROM PriOrders where KeyGroup = '" + strFieldKey + "'"; MySqlCommand cmd = new MySqlCommand(); MySqlTransaction myTrans = connection.BeginTransaction(); PrepareCommand(cmd, connection,myTrans, scommandtext, null); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { try { DataSet dsOrder = new DataSet(); da.Fill(dsOrder, basOrderData.BASORDER_TABLE); string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString(); if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "") { strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001"; } else { if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8)) { int intNum = int.Parse(strKeyNum.Substring(10, 6)); intNum = 1000001 + intNum; strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6); } else { strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001"; } } //更新主键值 MySqlCommand updatecommand = new MySqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where KeyGroup = '" + strFieldKey + "'", connection); updatecommand.Transaction = myTrans; updatecommand.ExecuteNonQuery(); blnResult[1] = strKeyNum; myTrans.Commit(); } catch (Exception e) { myTrans.Rollback(); blnResult[0] = e.Message; throw e; } return blnResult; } } } public static string[] getOrderNO(string strFieldKey, int OrderNum) { string[] blnResult = new string[2]; blnResult[0] = ""; blnResult[1] = ""; using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); MySqlTransaction myTrans = connection.BeginTransaction(); try { string scommandtext = "SELECT KeyGroup,KeyNumber FROM PriOrders where KeyGroup = '" + strFieldKey + "'"; MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, myTrans, scommandtext, null); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet dsOrder = new DataSet(); da.Fill(dsOrder, basOrderData.BASORDER_TABLE); string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString(); string strKeyNumB = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString(); if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "") { int intNum = 1000001 + OrderNum; strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + intNum.ToString().Substring(1, 6); strKeyNumB = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001"; } else { if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8)) { int intNum = int.Parse(strKeyNum.Substring(10, 6)); intNum = 1000001 + intNum + OrderNum; int intNumB = 1000001 + intNum; strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6); strKeyNumB = strKeyNum.Substring(0, 10) + intNumB.ToString().Substring(1, 6); } else { int intNum = 1000001 + OrderNum; strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + intNum.ToString().Substring(1, 6); strKeyNumB = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001"; } } //更新主键值 MySqlCommand updatecommand = new MySqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where KeyGroup = '" + strFieldKey + "'", connection); updatecommand.Transaction = myTrans; updatecommand.ExecuteNonQuery(); blnResult[1] = strKeyNumB; myTrans.Commit(); } } catch (Exception e) { myTrans.Rollback(); blnResult[0] = e.Message; throw e; } return blnResult; } } #endregion /// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } } } /// <summary> /// 是否存在 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public static bool Exists(string strSql) { object obj = GetSingle(strSql); int cmdresult; 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; } } /// <summary> /// 是否存在(基于MySqlParameter) /// </summary> /// <param name="strSql"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static bool Exists(string strSql, params MySqlParameter[] cmdParms) { object obj = GetSingle(strSql, cmdParms); int cmdresult; 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; } } /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object GetSingle(string SQLString) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } public static object GetSingle(string SQLString, int Times) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand(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 { return obj; } } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { 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 { return obj; } } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } } } /// <summary> /// 执行存储过程,返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { int result; connection.Open(); MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } /// <summary> /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand 对象实例</returns> private static MySqlCommand BuildIntCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters) { MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.Parameters.Add(new MySqlParameter("ReturnValue", MySqlDbType.Int16, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters) { MySqlCommand command = new MySqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (MySqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } } return command; } private static DataSet RunProcedureNoConn(string storedProcName, IDataParameter[] parameters, MySqlTransaction myTrans) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlDataAdapter adapter = new MySqlDataAdapter(); DataSet data = new DataSet(); try { adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); adapter.SelectCommand.Transaction = myTrans; adapter.Fill(data, storedProcName); } catch (System.Exception e) { throw e; } return data; } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { DataSet dataSet = new DataSet(); MySqlDataAdapter sqlDA = new MySqlDataAdapter(); try { sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); } catch (Exception) { throw; } return dataSet; } } /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTran(List<String> SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); MySqlCommand cmd = new MySqlCommand(); 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(); return count; } catch { tx.Rollback(); return 0; } } } /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { 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; } } } } /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param> public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { MySqlCommand cmd = new MySqlCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value; foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (MySqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } } }