为了方便的在SQLConnection上执行sql，微软官方提供了开源数据库操作类：。
这玩意，有两个问题弄的很不爽：
1. 每次执行的时候要传递ConnectionString，参数传一大堆；如果一个处理过程要在同一连接上多次执行SQL，代码看着都不舒服
2. 每次用SQLHelper执行SQL时，都使用单独的连接，带连接上下文(#开头的)临时表不能用
SQLHelper不罗嗦了，微软的大牛们推出的，自然有好处，至少不用担心连接释放的问题。
此外，稍大点的项目估计大家也都封装个操作类，很少用SQLHelper吧。
在这里，贴出我实现的CmdRunner类：
View Code using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; namespace Yzj.CSharp.DBInterface.Connection { /// /// 提供在同一连接上下文里多次执行SQL的服务 /// 可使用连接上下文临时表 /// 区别于SqlHelper执行时对链接不可控 /// public class CmdRunner : System.IDisposable { int _sqlBulkCopySize = 10000; string _connString = string.Empty; SqlConnection _conn = null; public CmdRunner(string connectionString) { if (string.IsNullOrEmpty(connectionString)) throw new System.ArgumentNullException(); _connString = connectionString; _conn = new SqlConnection(connectionString); _conn.Open(); } public SqlConnection Connection { get { return _conn; } } public int SqlBulkCopySize { get { return _sqlBulkCopySize; } set { _sqlBulkCopySize = value > 1 ? value : 10000; } } public int ExcuteNoQuery(string sql) { return ExcuteNoQuery(sql, 0, null); } public int ExcuteNoQuery(string sql, params SqlParameter[] array) { return ExcuteNoQuery(sql, 0, array); } public int ExcuteNoQuery(string sql, int iTimeOut, params SqlParameter[] array) { System.Diagnostics.Debug.Assert(_conn != null && _conn.State == System.Data.ConnectionState.Open); int nCount = 0; using (SqlCommand cmd = _conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandTimeout = iTimeOut; if (array != null && array.Length > 0) { cmd.Parameters.AddRange(array); } nCount = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } return nCount; } public object ExecuteScalar(string sql) { return ExecuteScalar(sql, null); } public object ExecuteScalar(string sql, params SqlParameter[] array) { System.Diagnostics.Debug.Assert(_conn != null && _conn.State == System.Data.ConnectionState.Open); object value = null; using (SqlCommand cmd = _conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandTimeout = 0; if (array != null && array.Length > 0) { cmd.Parameters.AddRange(array); } value = cmd.ExecuteScalar(); cmd.Parameters.Clear(); } return value; } public System.Data.DataTable ExcuteDataTable(string sql) { return ExcuteDataTable(sql, null); } public System.Data.DataTable ExcuteDataTable(string sql, params SqlParameter[] array) { System.Diagnostics.Debug.Assert(_conn != null && _conn.State == System.Data.ConnectionState.Open); System.Data.DataTable dt = new System.Data.DataTable(); using (SqlCommand cmd = _conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandTimeout = 0; if (array != null && array.Length > 0) { cmd.Parameters.AddRange(array); } using (SqlDataAdapter ad = new SqlDataAdapter(cmd)) { ad.Fill(dt); } cmd.Parameters.Clear(); } return dt; } /// /// 以SqlBulkCopy的方式，将在sourceRunner中的sourceSql查询结果，按照desColumns的顺序插入到当前Runner的desTable表中 /// /// 源数据库连接 /// 取数据的源SQL /// 要插入的表 /// 要插入表的列，格式： "column1, column2, ……" public void ExcuteSqlBulkCopy(SqlConnection sourceConn, string sourceSql, string desTable, string desColumns) { if (string.IsNullOrEmpty(desTable)) throw new System.ArgumentNullException("desTable"); if (string.IsNullOrEmpty(sourceSql)) throw new System.ArgumentNullException("sourceSql"); if (string.IsNullOrEmpty(desColumns)) throw new System.ArgumentNullException("desColumns"); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(this._conn)) { bulkCopy.BulkCopyTimeout = 1200; bulkCopy.BatchSize = SqlBulkCopySize; bulkCopy.DestinationTableName = desTable; string[] columns = desColumns.Split(','); for (int i = 0; i < columns.Length; i++) { string dbColumn = this.GetDbColumn(desTable, columns[i].Trim()); bulkCopy.ColumnMappings.Add(i, dbColumn); } using (SqlCommand sourceCmd = sourceConn.CreateCommand()) { sourceCmd.CommandText = sourceSql; sourceCmd.CommandTimeout = 90000; using (SqlDataReader sourceReader = sourceCmd.ExecuteReader()) { bulkCopy.WriteToServer(sourceReader); } } } } public void ExcuteSqlBulkCopy(string sourceSql, string desTable, string desColumns) { using (CmdRunner runner = new CmdRunner(this._connString)) { ExcuteSqlBulkCopy(runner.Connection, sourceSql, desTable, desColumns); } } #region sqlbulkcopy-column-manage Dictionary _columnToDbColumn = new Dictionary (); /// /// 获取给定表给定列的数据库精确名称，返回的列和数据库定义保持大小写一致 /// /// 表名，可以是dbName..tableName、dbName.dbo.tableName、tableName三种形式之一 /// 列名，同通常SQL语句写法，不计大小写 /// 返回对应的精确列名；如果未找到，会抛掷异常 string GetDbColumn(string tableName, string writeColumnName) { if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(writeColumnName)) { throw new System.InvalidOperationException(); } // 查看缓存是否存在 string key = tableName + "." + writeColumnName.ToLower(); if (_columnToDbColumn.ContainsKey(key)) return _columnToDbColumn[key]; // 从数据库创建缓存 // tableName 可以是：olap..tableName、olap.dbo.tableName、tableName三种形式 string dbTable = tableName; string dbName = string.Empty; int nIndex = tableName.IndexOf('.'); if (nIndex > 0) { dbName = tableName.Substring(0, nIndex) + ".."; nIndex = tableName.LastIndexOf('.'); dbTable = tableName.Substring(nIndex + 1); } string sql = string.Format("select name from {0}syscolumns where id=object_id('{1}')", dbName, dbTable); System.Data.DataTable dt = this.ExcuteDataTable(sql); if (dt.Rows.Count == 0) throw new System.Exception("不存在表'" + tableName + "'！"); string dbColumn = string.Empty; List dbColumns = new List (); foreach (System.Data.DataRow row in dt.Rows) { string column = Convert.ToString(row[0]); _columnToDbColumn[tableName + "." + column.ToLower()] = column; if (dbColumn.Length == 0 && column.Equals(writeColumnName, StringComparison.InvariantCultureIgnoreCase)) dbColumn = column; } if (dbColumn.Length == 0) throw new System.Exception("表'" + tableName + "'不存在列'" + writeColumnName + "'！"); return dbColumn; } #endregion #region IDisposable 成员 public void Dispose() { if (_conn != null) { _conn.Dispose(); _conn = null; } } #endregion } } 和SQLHelper的主要区别点：
1. CmdRunner对象组合SQLConnection，用完需要Disppose
2. 带SqlParameter[] 参数的，都给params化，调用的时候会少了new SqlParameter[]的代码
3. 封装SQLBulkCopy，批量数据插入，提高数据插入效率
欢迎发表看法，交流经验