.Net对数据库的操作，少了不DbHelperSQL类，我们前端所有的GetModel，ModifyToDb，AppendToDb等方法，最终都是转化为SQL命令，然后提交给数据库执行的。
该类定义了DbHelperSQL类要实现的方法，以后面的代码中，我们可以只使用IDbHelper类，不使用具体实现类，后期大家可以扩展不同数据的DbHelper类，让QDORM支持更多类型的数据库(哎呀，IDbHelper中出现了SqlTransaction,和SqlParameter，还怎么支持其它类型数据库呢？ 嗯……因为本人现在项目使用的是SQL Server，所以就偷懒了，大家可以自己修改。)
该类就很简单了，返回一个IDbHelper实例，这里我又偷懒了，因为只有一个IDbHelper实现类嘛，所以就直接返回DbHelperSQL实例了。大家可以结合Web.Config文件，使用反射方式实始化不同的IDbHelper实例，参考代码如下(以下代码只是展示了一个思路，和QDORM无关系)：
public class DbHelperSQL : IDbHelper
{
public DbHelperSQL(string connectionString)
{
this.ConnectionString = connectionString;
}
public string ConnectionString { get; set; }
public string DatabaseName
{
get
{
using (SqlConnection conn = GetConnection())
{
return conn.Database;
}
}
}
private int _DbCommandTimeOut;
public int DbCommandTimeOut
{
set { _DbCommandTimeOut = value; }
private get { return _DbCommandTimeOut; }
}
private SqlTransaction _SqlTransaction;
public SqlTransaction SqlTransaction
{
get
{
return _SqlTransaction;
}
}
/// <summary >
/// 是否有开启事务，如果开启了事务，则需手动关闭数据库
/// </summary >
private bool IsBeginTransaction = false;
public void BeginTransaction()
{
SqlConnection conn = GetConnection();
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
_SqlTransaction = conn.BeginTransaction();
IsBeginTransaction = true;
}
public void CloseTransaction()
{
_SqlTransaction = null;
IsBeginTransaction = false;
}
public void Commit()
{
if (_SqlTransaction != null)
{
_SqlTransaction.Commit();
CloseConnection();
}
}
public void Rollback()
{
if (_SqlTransaction != null)
{
_SqlTransaction.Rollback();
CloseConnection();
}
}
private SqlConnection _SqlConnection;
/// <summary >
/// 返回连接
/// </summary >
/// <returns ></returns >
private SqlConnection GetConnection()
{
if (_SqlConnection == null)
{
_SqlConnection = new SqlConnection(ConnectionString);
}
else if (string.IsNullOrEmpty(_SqlConnection.ConnectionString))
{
_SqlConnection.ConnectionString = ConnectionString;
}
return _SqlConnection;
}
/// <summary >
/// 尝试关闭数据库
/// </summary >
private void TryCloseConnection()
{
if (!IsBeginTransaction && _SqlConnection != null)
{
CloseConnection();
}
}
//关闭数据库
private void CloseConnection()
{
if (_SqlConnection != null)
{
if (_SqlConnection.State != ConnectionState.Closed)
{
_SqlConnection.Close();
}
_SqlConnection.Dispose();
}
}
public object ExecuteScalar(string sql, params SqlParameter[] paramter)
{
SqlConnection conn = GetConnection();
try
{
SqlCommand cmd = PrepareCommand(conn, SqlTransaction, sql, CommandType.Text, paramter);
object result = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
TryCloseConnection();
}
}
/// <summary >
/// 执行SQL语句，返回影响的记录数
/// </summary >
/// <param name="SQLString" >SQL语句</param >
/// <returns >影响的记录数</returns >
public int ExecuteSql(string sql, params SqlParameter[] paramter)
{
SqlConnection conn = GetConnection();
try
{
SqlCommand cmd = PrepareCommand(conn, SqlTransaction, sql, CommandType.Text, paramter);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (Exception ex)
{
throw ex;
}
finally
{
TryCloseConnection();
}
}
/// <summary >
/// 执行查询语句，返回DataSet
/// </summary >
/// <param name="SQLString" >查询语句</param >
/// <returns >DataSet</returns >
public DataSet Query(string sql, params SqlParameter[] paramter)
{
SqlConnection conn = GetConnection();
try
{
SqlCommand cmd = PrepareCommand(conn, SqlTransaction, sql, CommandType.Text, paramter);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
TryCloseConnection();
}
}
public void ExecuteSql(IList<DictionaryEntry > CommandList)
{
SqlConnection conn = GetConnection();
SqlTransaction trans;
if (SqlTransaction == null)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
trans = conn.BeginTransaction();
}
else
{
trans = SqlTransaction;
}
SqlCommand cmd;
try
{
foreach (DictionaryEntry dic in CommandList)
{
if (dic.Key != null && !string.IsNullOrEmpty(dic.Key.ToString()))
{
string cmdText = dic.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])dic.Value;
cmd = PrepareCommand(conn, trans, cmdText, CommandType.Text, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
if (!IsBeginTransaction)
{
trans.Commit();
}
}
catch (SqlException ex)
{
if (!IsBeginTransaction)
{
trans.Rollback();
}
throw ex;
}
finally
{
TryCloseConnection();
}
}
/// <summary >
/// 执行存储过程
/// </summary >
/// <param name="storedProcName" >存储过程名</param >
/// <param name="parameters" >存储过程参数</param >
/// <returns >DataSet</returns >
public DataSet RunProcedure(string storedProcName, SqlParameter[] parameters)
{
SqlConnection connection = GetConnection();
try
{
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = PrepareCommand(connection, SqlTransaction, storedProcName, CommandType.StoredProcedure, parameters);
sqlDA.Fill(dataSet);
connection.Close();
return dataSet;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
TryCloseConnection();
}
}
private SqlCommand PrepareCommand(SqlConnection conn, SqlTransaction trans, string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand(cmdText, conn);
if (DbCommandTimeOut > 0)
{
cmd.CommandTimeout = DbCommandTimeOut;
}
cmd.CommandType = cmdType;
if (trans != null)
cmd.Transaction = trans; ;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.Input
OutPut || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
return cmd;
}
}
该类的大部分代码都可以在网上搜索的到，都是对数据库的操作嘛，变不到那里去，但相对网上代码，我增加了一些和事务相关的属性及方法，如BeginTransaction()，CloseTransaction()，Commit()，Rollback()方法等。
，以实现多个逻辑段在一个事务中处理，这和第一种方式有什么区别呢？ 比如说，我有两段逻辑需要处理，且第二段逻辑中需要第一段逻辑的处理结果，那么我们就不能将所有命令一次提交给数据库了，要经过