在项目中我是这样处理数据的:
/**//// <summary>
/// 外包业务访问类
/// </summary>
public class OutSourcingDAO
{
/**//// <summary>
/// 增加
/// </summary>
/// <param name="bt"></param>
/// <returns></returns>
public int InsertGetIdentity(OutSourcing bt)
{
return new DAHelper(DataAccess.Create()).InsertGetIdentity(bt);
}
/**//// <summary>
/// 更新
/// </summary>
/// <param name="bt"></param>
/// <returns></returns>
public int Update(OutSourcing bt)
{
return new DAHelper(DataAccess.Create()).Update(bt);
}
/**//// <summary>
/// 删除
/// </summary>
/// <param name="bt"></param>
/// <returns></returns>
public int Delete(OutSourcing bt)
{
return new DAHelper(DataAccess.Create()).Delete(bt);
}
}
/// 外包业务访问类
/// </summary>
public class OutSourcingDAO
{
/**//// <summary>
/// 增加
/// </summary>
/// <param name="bt"></param>
/// <returns></returns>
public int InsertGetIdentity(OutSourcing bt)
{
return new DAHelper(DataAccess.Create()).InsertGetIdentity(bt);
}
/**//// <summary>
/// 更新
/// </summary>
/// <param name="bt"></param>
/// <returns></returns>
public int Update(OutSourcing bt)
{
return new DAHelper(DataAccess.Create()).Update(bt);
}
/**//// <summary>
/// 删除
/// </summary>
/// <param name="bt"></param>
/// <returns></returns>
public int Delete(OutSourcing bt)
{
return new DAHelper(DataAccess.Create()).Delete(bt);
}
}
上面 OutSourcing 为与表相对应的实体类 ;DAHelper 为作者自己实现的一个包装类,可以对任意实体进行增加,修改,删除 查询等功能。
再贴一段代码:
public int ExecSql(string strSql)
{
try
{
this.OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
return cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
throw this.CatchException(e);
}
finally
{
this.CloseConnection();
}
}
{
try
{
this.OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
return cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
throw this.CatchException(e);
}
finally
{
this.CloseConnection();
}
}
我最终通过调用 ExecSql 方法来与数据库交互,而该方法会自己打开数据库连接,执行语句,然后关闭连接。
在操作同一个数据库的时候,如果要高效的使用 TransactionScope,必须保证 SqlConnection 不改变,即用同一个 SqlConnection 来完成所需要的增加删除,或修改。我想写这样的代码进行事务控制:
public int UpdateTest(OutSourcing outSourcing, BusinessAccept businessAccept)
{
IDataAccess dac = DataAccess.Create();
DAHelper myHelper = new DAHelper(dac);
using (TransactionScope ts = new TransactionScope())
{
myHelper.Update(outSourcing);
myHelper.Update(businessAccept);
ts.Complete();
}
}
{
IDataAccess dac = DataAccess.Create();
DAHelper myHelper = new DAHelper(dac);
using (TransactionScope ts = new TransactionScope())
{
myHelper.Update(outSourcing);
myHelper.Update(businessAccept);
ts.Complete();
}
}
这样就需要,执行第一个操作时候打开数据库连接,执行,不关闭连接,然后执行第二个操作,执行完关闭。显然,我想让 TransactionScope 在 实行 Dispose() 方法的时候关闭数据库连接。using 代码块的本质上等同于 try{}finally{} 语句代码块。为什么不封装一下 TransactionScope 让它满足自己的要求呢?
/**//// <summary>
/// TransactionScope 包装类
/// </summary>
public sealed class Scope : IDisposable
{
private TransactionScope m_TransactionScope = null;
/**//// <summary>
/// 测试访问类
/// </summary>
private DataAccessTest m_DataAccessTest = null;
/**//// <summary>
/// 实例化一个新的 TransactionScope
/// </summary>
/// <param name="dac"></param>
public Scope(DataAccessTest dac)
{
this.m_DataAccessTest = dac;
//告诉访问类 你已经使用了事务
dac.SetScope(this);
this.m_TransactionScope = new TransactionScope();
}
/**//// <summary>
/// 发出事务结束命令
/// </summary>
public void Complete()
{
this.m_TransactionScope.Complete();
}
IDisposable 成员#region IDisposable 成员
/**//// <summary>
/// 当执行该方法的时候完成两件任务
/// 1 关闭数据库栀湩???? ??魀?(连接
/// 2 调用 TransactionScope 的 Dispose()方法
/// </summary>
void IDisposable.Dispose()
{
try
{
m_DataAccessTest.Close();
}
finally
{
m_TransactionScope.Dispose();
}
}
#endregion
}
/// TransactionScope 包装类
/// </summary>
public sealed class Scope : IDisposable
{
private TransactionScope m_TransactionScope = null;
/**//// <summary>
/// 测试访问类
/// </summary>
private DataAccessTest m_DataAccessTest = null;
/**//// <summary>
/// 实例化一个新的 TransactionScope
/// </summary>
/// <param name="dac"></param>
public Scope(DataAccessTest dac)
{
this.m_DataAccessTest = dac;
//告诉访问类 你已经使用了事务
dac.SetScope(this);
this.m_TransactionScope = new TransactionScope();
}
/**//// <summary>
/// 发出事务结束命令
/// </summary>
public void Complete()
{
this.m_TransactionScope.Complete();
}
IDisposable 成员#region IDisposable 成员
/**//// <summary>
/// 当执行该方法的时候完成两件任务
/// 1 关闭数据库栀湩???? ??魀?(连接
/// 2 调用 TransactionScope 的 Dispose()方法
/// </summary>
void IDisposable.Dispose()
{
try
{
m_DataAccessTest.Close();
}
finally
{
m_TransactionScope.Dispose();
}
}
#endregion
}
数据库访问类代码如下:
/**//// <summary>
/// 模拟数据库访问类
/// </summary>
public class DataAccessTest
{
SqlConnection con = null;
SqlCommand cmd = new SqlCommand();
Scope scope = null;
string strCon = "这里是数据库连接字符串。。。。。";
public void SetScope(Scope scope)
{
this.scope = scope;
}
private void OpenConnection()
{
if (con == null || scope == null)
{
con = new SqlConnection(strCon);
cmd.Connection = con;
con.Open();
Console.WriteLine(" 打开数据库连接;");
}
}
private void CloseConnection()
{
this.cmd.Parameters.Clear();
if (scope == null)
{
//
con.Close();
con.Dispose();
Console.WriteLine(" 未使用事务 关闭数据库连接;");
}
}
public int ExecuteSql(string strSql)
{
try
{
this.OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
Console.WriteLine("执行 Sql 语句。。。");
return cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
throw e;
}
finally
{
this.CloseConnection();
}
}
public void Close()
{
con.Close();
con.Dispose();
Console.WriteLine(" 关闭数据库连接->该方法由 Scope中的Dispose()方法调用 ");
}
}
/// 模拟数据库访问类
/// </summary>
public class DataAccessTest
{
SqlConnection con = null;
SqlCommand cmd = new SqlCommand();
Scope scope = null;
string strCon = "这里是数据库连接字符串。。。。。";
public void SetScope(Scope scope)
{
this.scope = scope;
}
private void OpenConnection()
{
if (con == null || scope == null)
{
con = new SqlConnection(strCon);
cmd.Connection = con;
con.Open();
Console.WriteLine(" 打开数据库连接;");
}
}
private void CloseConnection()
{
this.cmd.Parameters.Clear();
if (scope == null)
{
//
con.Close();
con.Dispose();
Console.WriteLine(" 未使用事务 关闭数据库连接;");
}
}
public int ExecuteSql(string strSql)
{
try
{
this.OpenConnection();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
Console.WriteLine("执行 Sql 语句。。。");
return cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
throw e;
}
finally
{
this.CloseConnection();
}
}
public void Close()
{
con.Close();
con.Dispose();
Console.WriteLine(" 关闭数据库连接->该方法由 Scope中的Dispose()方法调用 ");
}
}