C# SQLHelper
using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace CBL.V12.Helper { public class SQLHelper { //数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库. private static string _connectionString = ""; public string ConnectionString { get { return _connectionString; } set { _connectionString = value; } } static SQLHelper() { _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); } /// <summary> /// 表是否存在 /// </summary> /// <param name="TableName"></param> /// <returns></returns> public static bool TabExists(string TableName) { bool cmdresult = true; try { string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; object obj = GetSingle(strsql); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { cmdresult = false; } else { if (int.Parse(obj.ToString()) <= 0) { cmdresult = false; } } } catch { cmdresult = false; } return cmdresult; } public static bool ColumnExists(string tableName, string columnName) { bool bresult = false; try { string sql = string.Concat(new string[] { "select count(1) from syscolumns where [id]=object_id('",tableName,"') and [name]='",columnName,"'"}); object res = GetSingle(sql); if (object.Equals(res, null) || object.Equals(res, DBNull.Value)) { bresult = false; } else { if (int.Parse(res.ToString()) <= 0) { bresult = false; } } } catch { bresult = false; } return bresult; } /// <summary> /// 返回执行结果 /// </summary> /// <param name="cmdText"></param> /// <param name="spts"></param> /// <returns></returns> public static bool Excute(string cmdText, SqlParameter[] spts) { bool result = false; SqlConnection sqlConnection = null; try { sqlConnection = new SqlConnection(_connectionString); sqlConnection.Open(); SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection); for (int i = 0; i < spts.Length; i++) { sqlCommand.Parameters.Add(spts[i]); } sqlCommand.ExecuteNonQuery(); result = true; } catch (Exception ex) { Lgr.Log.Error(ex.Message, ex); } finally { if (sqlConnection != null) { sqlConnection.Close(); } } return result; } public static bool Exists(string strSql) { bool cmdresult = true; try { object obj = GetSingle(strSql); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { cmdresult = false; } else { if (int.Parse(obj.ToString()) <= 0) { cmdresult = false; } } } catch { cmdresult = false; } return cmdresult; } public static bool Exists(string strSql, params SqlParameter[] cmdParms) { bool cmdresult = true; try { object obj = GetSingle(strSql, cmdParms); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { cmdresult = false; } else { if (int.Parse(obj.ToString()) <= 0) { cmdresult = false; } } } catch { cmdresult = false; } return cmdresult; } public static int GetMaxID(string FieldName, string TableName) { int iresult = -1; try { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = GetSingle(strsql); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { iresult = -1; } else { iresult = int.Parse(obj.ToString()); } } catch { iresult = -1; } return iresult; } public static void ExecuteInsertCommand(DataTable dt, bool IsDelete = false) { if (dt != null && dt.Rows.Count > 0) { if (IsDelete) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlTransaction sqlbulkTransaction = conn.BeginTransaction(); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction); for (int i = 0; i < dt.Columns.Count; i++) { bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } bulkCopy.DestinationTableName = dt.TableName; bulkCopy.BatchSize = dt.Rows.Count; try { new SqlCommand { Connection = conn, CommandText = string.Format("TRUNCATE TABLE {0}", dt.TableName), Transaction = sqlbulkTransaction }.ExecuteNonQuery(); bulkCopy.WriteToServer(dt); sqlbulkTransaction.Commit(); } catch { sqlbulkTransaction.Rollback(); } finally { conn.Close(); if (bulkCopy != null) { bulkCopy.Close(); } } } } else { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn)) { for (int i = 0; i < dt.Columns.Count; i++) { bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } bulkCopy.DestinationTableName = dt.TableName; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.WriteToServer(dt); bulkCopy.Close(); } conn.Close(); } } } } public static int ExecuteSql(string SQLString) { int result; using (SqlConnection connection = new SqlConnection(_connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); result = rows; } catch (SqlException e) { throw e; } finally { connection.Close(); } } } return result; } public static int ExecuteSqlByTime(string SQLString, int Times) { int result; using (SqlConnection connection = new SqlConnection(_connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); result = rows; } catch (SqlException e) { throw e; } finally { connection.Close(); } } } return result; } public static int ExecuteSql(List<string> SQLStringList) { int count = 0; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand { Connection = conn }; try { for (int i = 0; i < SQLStringList.Count; i++) { string strsql = SQLStringList[i]; if (strsql.Trim().Length > 1) { try { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } catch { } } } } catch { count = 0; } finally { conn.Close(); } } return count; } public static int ExecuteSqlTran(List<string> SQLStringList) { int result; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand { Connection = conn }; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int i = 0; i < SQLStringList.Count; i++) { string strsql = SQLStringList[i]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); result = count; } catch { tx.Rollback(); result = 0; } finally { conn.Close(); } } return result; } public static int ExecuteSql(string SQLString, string content) { int result; using (SqlConnection connection = new SqlConnection(_connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); SqlParameter myParameter = new SqlParameter("@content", SqlDbType.NText) { Value = content }; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); result = rows; } catch (SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } return result; } public static object ExecuteSqlGet(string SQLString, string content) { object result; using (SqlConnection connection = new SqlConnection(_connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); SqlParameter myParameter = new SqlParameter("@content", SqlDbType.NText) { Value = content }; cmd.Parameters.Add(myParameter); try { connection.Open(); object obj = cmd.ExecuteScalar(); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { result = null; } else { result = obj; } } catch (SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } return result; } public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { int result; using (SqlConnection connection = new SqlConnection(_connectionString)) { SqlCommand cmd = new SqlCommand(strSQL, connection); SqlParameter myParameter = new SqlParameter("@fs", SqlDbType.Image) { Value = fs }; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); result = rows; } catch (SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } return result; } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="SQLString"></param> /// <returns></returns> public static object GetSingle(string SQLString) { object obj = null; using (SqlConnection connection = new SqlConnection(_connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); obj = cmd.ExecuteScalar(); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { obj = null; } } catch (SqlException ex) { throw new Exception(ex.Message); } finally { cmd.Dispose(); connection.Close(); } } } return obj; } /// <summary> /// 返回第一行第一列 /// </summary> /// <param name="SQLString"></param> /// <param name="Times"></param> /// <returns></returns> public static object GetSingle(string SQLString, int Times) { object obj = null; using (SqlConnection connection = new SqlConnection(_connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; obj = cmd.ExecuteScalar(); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { obj = null; } } catch (SqlException ex) { throw new Exception(ex.Message); } finally { cmd.Dispose(); connection.Close(); } } } return obj; } public static SqlDataReader ExecuteReader(string SQLString) { SqlDataReader myReader = null; using (SqlConnection connection = new SqlConnection(_connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); myReader = cmd.ExecuteReader(); } catch (SqlException ex) { throw new Exception(ex.Message); } finally { cmd.Dispose(); connection.Close(); } } } return myReader; } public static DataSet Query(string SQLString) { DataSet ds = new DataSet(); using (SqlConnection connection = new SqlConnection(_connectionString)) { try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (SqlException ex) { throw new Exception(ex.Message); } finally { connection.Close(); } } return ds; } public static DataSet Query(string SQLString, int Times) { DataSet result; using (SqlConnection connection = new SqlConnection(_connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); new SqlDataAdapter(SQLString, connection) { SelectCommand = { CommandTimeout = Times } }.Fill(ds, "ds"); } catch (SqlException ex) { throw new Exception(ex.Message); } finally { connection.Close(); } result = ds; } return result; } public static bool TestConnection() { bool resut = false; try { using (SqlConnection con = new SqlConnection(_connectionString)) { con.Open(); resut = true; } } catch { } return resut; } public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) { int result; using (SqlConnection connection = new SqlConnection(_connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); result = rows; } catch (SqlException ex) { throw new Exception(ex.Message); } finally { connection.Close(); } } } return result; } public static void ExecuteSqlTran(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { foreach (DictionaryEntry myDE in SQLStringList) { PrepareCommand(cmd, conn, trans, myDE.Key.ToString(), (SqlParameter[])myDE.Value); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } conn.Close(); } } public static int ExecuteSqlTran(List<CommandInfo> cmdList) { int result; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int count = 0; foreach (CommandInfo myDE in cmdList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])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(); result = 0; return result; } object obj = cmd.ExecuteScalar(); if (obj == null && obj == DBNull.Value) { } bool isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { trans.Rollback(); result = 0; return result; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { trans.Rollback(); result = 0; return result; } } else { int val = cmd.ExecuteNonQuery(); count += val; if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { trans.Rollback(); result = 0; return result; } cmd.Parameters.Clear(); } } trans.Commit(); result = count; } catch { trans.Rollback(); throw; } finally { conn.Close(); } } } return result; } public static void ExecuteSqlTranWithIndentity(List<CommandInfo> SQLStringList) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; foreach (CommandInfo myDE in SQLStringList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; SqlParameter[] array = cmdParms; for (int i = 0; i < array.Length; i++) { SqlParameter q = array[i]; if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); array = cmdParms; for (int i = 0; i < array.Length; i++) { SqlParameter q = array[i]; if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } finally { conn.Close(); } } } } public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; SqlParameter[] array = cmdParms; for (int i = 0; i < array.Length; i++) { SqlParameter q = array[i]; if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); array = cmdParms; for (int i = 0; i < array.Length; i++) { SqlParameter q = array[i]; if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } conn.Close(); } } public static object GetSingle(string SQLString, params SqlParameter[] cmdParms) { object result; using (SqlConnection connection = new SqlConnection(_connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { result = null; } else { result = obj; } } catch (SqlException e) { throw e; } } } return result; } public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand(); SqlDataReader result; try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); result = myReader; } catch (SqlException e) { throw e; } finally { //cmd.Dispose(); //connection.Close(); } return result; } public static DataSet Query(string SQLString, params SqlParameter[] cmdParms) { DataSet result; using (SqlConnection connection = new SqlConnection(_connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (SqlException ex) { Lgr.Log.Info(ex.Message); throw new Exception(ex.Message); } finally { connection.Close(); } result = ds; } } return result; } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] 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) { for (int i = 0; i < cmdParms.Length; i++) { SqlParameter parameter = cmdParms[i]; if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlDataReader result; using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { connection.Open(); SqlCommand command = this.BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; SqlDataReader returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); result = returnReader; } return result; } public DataSet RunProcedure(string storedProcName, string tableName) { using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { DataSet dataSet = new DataSet(); connection.Open(); new SqlDataAdapter { SelectCommand = this.BuildQueryCommand(connection, storedProcName, null) }.Fill(dataSet, tableName); connection.Close(); return dataSet; } } public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { DataSet dataSet = new DataSet(); connection.Open(); new SqlDataAdapter { SelectCommand = this.BuildQueryCommand(connection, storedProcName, parameters) }.Fill(dataSet, tableName); connection.Close(); return dataSet; } } public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) { using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter adapter = new SqlDataAdapter { SelectCommand = this.BuildQueryCommand(connection, storedProcName, parameters) }; adapter.SelectCommand.CommandTimeout = Times; adapter.Fill(dataSet, tableName); connection.Close(); return dataSet; } } private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection) { CommandType = CommandType.StoredProcedure }; if (parameters != null) { for (int i = 0; i < parameters.Length; i++) { SqlParameter parameter = (SqlParameter)parameters[i]; if (parameter != null) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } } return command; } public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { connection.Open(); SqlCommand command = this.BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); int num = (int)command.Parameters["ReturnValue"].Value; connection.Close(); return num; } } public void RunProcedureNone(string storedProcName, IDataParameter[] parameters) { using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { connection.Open(); SqlCommand command = this.BuildIntCommand(connection, storedProcName, parameters); command.ExecuteNonQuery(); connection.Close(); } } public DataSet RunProcedure(string storedProcName) { DataSet result; using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString)) { DataSet dataSet = new DataSet(); try { sqlConnection.Open(); new SqlDataAdapter { SelectCommand = new SqlCommand(storedProcName, sqlConnection) }.Fill(dataSet); } catch (SqlException ex) { throw new Exception(ex.Message); } finally { sqlConnection.Close(); } result = dataSet; } return result; } private SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = this.BuildQueryCommand(connection, storedProcName, parameters); command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } /// <summary> /// 分页查询 /// </summary> /// <param name="tblName"></param> /// <param name="strGetFields"></param> /// <param name="fldName"></param> /// <param name="PageSize"></param> /// <param name="PageIndex"></param> /// <param name="strWhere"></param> /// <param name="count"></param> /// <returns></returns> public static DataSet GetRecordByPage(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, string strWhere, out int count) { DataSet ds = new DataSet(); try { using (SqlConnection conn = new SqlConnection(_connectionString)) { SqlCommand cmd = new SqlCommand { Connection = conn }; SetParams(cmd); cmd.Parameters[0].Value = tblName; cmd.Parameters[1].Value = strGetFields; cmd.Parameters[2].Value = fldName; cmd.Parameters[3].Value = strWhere; cmd.Parameters[4].Value = PageSize; cmd.Parameters[5].Value = PageIndex; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "PROCE_PAGECHANGE"; cmd.CommandTimeout = 180; SqlDataAdapter adapter = new SqlDataAdapter { SelectCommand = cmd }; DataSet source = new DataSet(); adapter.Fill(ds); //ds.Tables.RemoveAt(0); object o = cmd.Parameters["@TotalRecord"].Value; count = (o == null || o == DBNull.Value) ? 0 : System.Convert.ToInt32(o); } } catch (SqlException e) { throw e; } return ds; } private static void SetParams(SqlCommand cmd) { cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar, 255)); cmd.Parameters.Add(new SqlParameter("@ReFieldsStr", SqlDbType.VarChar, 1000)); cmd.Parameters.Add(new SqlParameter("@OrderString", SqlDbType.VarChar, 255)); cmd.Parameters.Add(new SqlParameter("@WhereString", SqlDbType.VarChar, 1500)); cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int)); SqlParameter param = new SqlParameter("@TotalRecord", SqlDbType.Int) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(param); } /// <summary> /// 异步执行 /// </summary> /// <param name="sql"></param> public static void ExecAsync(string sql) { //注意不能写 using, 也不能在 finally 中关闭连接 //连接的关闭只能在 AsyncCallback 异步调用方法中完成 //但在 catch 中必须有关闭操作,防止万一 SqlConnection conn = new SqlConnection(_connectionString); try { conn.Open(); SqlCommand command = new SqlCommand(sql, conn); command.Parameters.AddWithValue("@isAsync", true); AsyncCallback callback = new AsyncCallback(HandleCallback); command.BeginExecuteNonQuery(callback, command); } catch (Exception ex) { Console.WriteLine(ex.Message); if (conn != null) conn.Close(); } } /// <summary> /// 异步回调方法 /// </summary> /// <param name="result"></param> private static void HandleCallback(IAsyncResult result) { SqlCommand command = (SqlCommand)result.AsyncState; try { int rowCount = command.EndExecuteNonQuery(result); Console.WriteLine("{0:HH:mm:ss} 异步方法完成", DateTime.Now); //PrintInto(); //return rowCount; //this.Invoke(myDataDelegate, dataTable); } catch (Exception ex) { Console.WriteLine("Error :{0}", ex.Message); //return 0; } finally { if (command.Connection != null) command.Connection.Close(); } } /// <summary> /// 输出表内容 /// </summary> private static void PrintInto() { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand("select isAsync,cnt from dbo.TestAsync", conn); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { //Lgr.Log.Info("isAsync:{0}\t cnt:{1}", sdr["isAsync"], sdr["cnt"]); } } } public static bool UpdateTable(DataTable dt, string SQLString, string key, SqlConnection conn, SqlTransaction trans, SqlParameter[] cmdParms) { bool result = false; using (SqlCommand cmd = new SqlCommand(SQLString, conn)) { cmd.Transaction = trans; cmd.Parameters.Clear(); if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } DataTable tempDT = new DataTable(); DataTable dsTable = null; //DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); SqlCommandBuilder cb = new SqlCommandBuilder(adapter) { QuotePrefix = "[", QuoteSuffix = "]" }; try { if (conn.State != ConnectionState.Open) conn.Open(); adapter.Fill(tempDT); //DataTable tempDT = new DataTable(); //DataTable dsTable = null; //设置DataTable主键字段 tempDT.PrimaryKey = new DataColumn[] { tempDT.Columns[key] }; //取DataSet中表1的数据,此处的dsTable是用于盛放从Excel表中读出的数据 dsTable = dt; dsTable.PrimaryKey = new DataColumn[] { dsTable.Columns[key] }; //遍历dsTable中的每一行数据 foreach (DataRow dr in dsTable.Rows) { //Lgr.Log.Info(dr["F_FullName"].ToString()); //在tempDT中查找数据库中是否已经存在该条数据 DataRow modyRow = tempDT.Rows.Find(dr[key]); //如果数据库已经存在该行,则修改记录,不进行插入 if (modyRow != null) { //设置该行记录为“已修改”状态,次操作必须。 tempDT.Rows.Find(dr[key]).SetModified(); //重新给该记录赋值 tempDT.Rows.Find(dr[key]).ItemArray = dr.ItemArray; } else { //新增 //dr["F_ModuleId"] = keyValue; tempDT.Rows.Add(dr.ItemArray); } } //删除不在表内的数据 foreach (DataRow row in tempDT.Rows) { DataRow modyRow = dsTable.Rows.Find(row[key]); if (modyRow == null) { tempDT.Rows.Find(row[key]).Delete(); } } adapter.Update(tempDT);//对表的更新提交到数据库 result = true; } catch (SqlException ex) { Lgr.Log.Error("", ex); trans.Rollback(); throw ex; } finally { //conn.Close(); } } return result; } public static bool UpdateTable(DataSet ds, string SQLString, string key, SqlConnection conn, SqlTransaction trans, string keyValue, SqlParameter[] cmdParms) { bool result = false; using (SqlCommand cmd = new SqlCommand(SQLString, conn)) { cmd.Transaction = trans; cmd.Parameters.Clear(); if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } DataTable tempDT = new DataTable(); DataTable dsTable = null; //DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); SqlCommandBuilder cb = new SqlCommandBuilder(adapter) { QuotePrefix = "[", QuoteSuffix = "]" }; try { if (conn.State != ConnectionState.Open) conn.Open(); adapter.Fill(tempDT); //DataTable tempDT = new DataTable(); //DataTable dsTable = null; //设置DataTable主键字段 tempDT.PrimaryKey = new DataColumn[] { tempDT.Columns[key] }; //取DataSet中表1的数据,此处的dsTable是用于盛放从Excel表中读出的数据 dsTable = ds.Tables[0]; dsTable.PrimaryKey = new DataColumn[] { dsTable.Columns[key] }; //遍历dsTable中的每一行数据 foreach (DataRow dr in dsTable.Rows) { //Lgr.Log.Info(dr["F_FullName"].ToString()); //在tempDT中查找数据库中是否已经存在该条数据 DataRow modyRow = tempDT.Rows.Find(dr[key]); //如果数据库已经存在该行,则修改记录,不进行插入 if (modyRow != null) { //设置该行记录为“已修改”状态,次操作必须。 tempDT.Rows.Find(dr[key]).SetModified(); //重新给该记录赋值 tempDT.Rows.Find(dr[key]).ItemArray = dr.ItemArray; } else { //新增 //dr["F_ModuleId"] = keyValue; tempDT.Rows.Add(dr.ItemArray); } } //删除不在表内的数据 foreach (DataRow row in tempDT.Rows) { DataRow modyRow = dsTable.Rows.Find(row[key]); if (modyRow == null) { tempDT.Rows.Find(row[key]).Delete(); } } adapter.Update(tempDT);//对表的更新提交到数据库 result = true; } catch (SqlException ex) { Lgr.Log.Error("", ex); trans.Rollback(); throw ex; } finally { //conn.Close(); } } return result; } } }