SQLHelper用的不爽，试试CmdRunner吧
为了方便的在SQLConnection上执行sql，微软官方提供了开源数据库操作类：SQLHelper。
这玩意，有两个问题弄的很不爽：
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
{
/// <summary>
/// 提供在同一连接上下文里多次执行SQL的服务
/// 可使用连接上下文临时表
/// 区别于SqlHelper执行时对链接不可控
/// </summary>
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;
}
/// <summary>
/// 以SqlBulkCopy的方式，将在sourceRunner中的sourceSql查询结果，按照desColumns的顺序插入到当前Runner的desTable表中
/// </summary>
/// <param name="sourceRunner">源数据库连接</param>
/// <param name="sourceSql">取数据的源SQL</param>
/// <param name="desTable">要插入的表</param>
/// <param name="desColumns">要插入表的列，格式： "column1, column2, ……"</param>
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<string, string> _columnToDbColumn = new Dictionary<string, string>();
/// <summary>
/// 获取给定表给定列的数据库精确名称，返回的列和数据库定义保持大小写一致
/// </summary>
/// <param name="tableName">表名，可以是dbName..tableName、dbName.dbo.tableName、tableName三种形式之一</param>
/// <param name="writeColumnName">列名，同通常SQL语句写法，不计大小写</param>
/// <returns>返回对应的精确列名；如果未找到，会抛掷异常</returns>
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<string> dbColumns = new List<string>();
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，批量数据插入，提高数据插入效率
欢迎发表看法，交流经验
