代码改变世界

【转载】DbHelper数据操作类,DbProviderFactories，添加了实验代码和TransactionScope事务

2010-02-25 10:56  Virus-BeautyCode  阅读(2702)  评论(2)  编辑  收藏  举报

TransactionScope需要数据库服务器开启DTC服务

原文地址：DbHelper数据操作类,DbProviderFactories

原文作者：http://www.cnblogs.com/kinms

 

DbHelper数据操作类,DbProviderFactories

微软的企业库中有一个非常不错的数据操作类了.但是,不少公司(起码我遇到的几个...),对一些"封装"了些什么的东西不太敢用,虽然我推荐过微软的企业库框架了...但是还是要"评估"...一评就是几个月...而且,一些公司有的根本就是裸ado.net开发,或者自己封装的数据库操作类非常别扭,很不好用.
      这里我给大家共享一个我参照企业库中的数据操作组件编码风格写的数据库操作类,对使用它的程序员来说,编码是很舒服滴(起码我觉得很好撒).以下是代码,很简单的,没有做任何多余的封装,只是改变了ADO.NET的编码步骤,方便了具体开发数据库操作代码的程序员.

 


using System;
    using System.Data;
    using System.Data.Common;
    using System.Configuration;

    public class DbHelper
    {
        private static string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"];
        private static string dbConnectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"];

        private DbConnection connection;
        public DbHelper()
        {
            this.connection = CreateConnection(DbHelper.dbConnectionString);
        }
        public DbHelper(string connectionString)
        {
            this.connection = CreateConnection(connectionString);
        }
        public static DbConnection CreateConnection()
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = DbHelper.dbConnectionString;
            return dbconn;
        }
        public static DbConnection CreateConnection(string connectionString)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = connectionString;
            return dbconn;
        }

        public DbCommand GetStoredProcCommond(string storedProcedure)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = CommandType.StoredProcedure;
            return dbCommand;
        }
        public DbCommand GetSqlStringCommond(string sqlQuery)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = sqlQuery;
            dbCommand.CommandType = CommandType.Text;
            return dbCommand;
        }

        增加参数#region 增加参数
        public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
        {
            foreach (DbParameter dbParameter in dbParameterCollection)
            {
                cmd.Parameters.Add(dbParameter);
            }
        }
        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }
        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }
        public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }
        public DbParameter GetParameter(DbCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }

        #endregion

        执行#region 执行
        public DataSet ExecuteDataSet(DbCommand cmd)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        public DataTable ExecuteDataTable(DbCommand cmd)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            cmd.Connection.Open();
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);            
            return reader;
        }
        public int ExecuteNonQuery(DbCommand cmd)
        {
            cmd.Connection.Open();
            int ret = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            return ret;
        }

        public object ExecuteScalar(DbCommand cmd)
        {
            cmd.Connection.Open();
            object ret = cmd.ExecuteScalar();
            cmd.Connection.Close();
            return ret;
        }
        #endregion        

        执行事务#region 执行事务
        public DataSet ExecuteDataSet(DbCommand cmd,Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }

        public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;            
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();            
            return reader;
        }
        public int ExecuteNonQuery(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;  
            int ret = cmd.ExecuteNonQuery();            
            return ret;
        }

        public object ExecuteScalar(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;  
            object ret = cmd.ExecuteScalar();            
            return ret;
        }
        #endregion
    }

    public class Trans : IDisposable
    {
        private DbConnection conn;
        private DbTransaction dbTrans;
        public DbConnection DbConnection
        {
            get { return this.conn; }
        }
        public DbTransaction DbTrans
        {
            get { return this.dbTrans; }
        }

        public Trans()
        {
            conn = DbHelper.CreateConnection();
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }
        public Trans(string connectionString)
        {
            conn = DbHelper.CreateConnection(connectionString);
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }
        public void Commit()
        {
            dbTrans.Commit();
            this.Colse();
        }

        public void RollBack()
        {
            dbTrans.Rollback();
            this.Colse();
        }

        public void Dispose()
        {
            this.Colse();
        }

        public void Colse()
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }

那么如何使用它呢?下面我给出一些基本的使用示例,基本能满足你大部分的数据库操作需要了.
1)直接执行sql语句

        DbHelper db = new DbHelper();
        DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values('haha')");
        db.ExecuteNonQuery(cmd);

2)执行存储过程

        DbHelper db = new DbHelper();
        DbCommand cmd = db.GetStoredProcCommond("t1_insert");
        db.AddInParameter(cmd, "@id", DbType.String, "heihei");
        db.ExecuteNonQuery(cmd);

3)返回DataSet

        DbHelper db = new DbHelper();
        DbCommand cmd = db.GetSqlStringCommond("select * from t1");
        DataSet ds = db.ExecuteDataSet(cmd);

4)返回DataTable
 

        DbHelper db = new DbHelper();
        DbCommand cmd = db.GetSqlStringCommond("t1_findall");
        DataTable dt = db.ExecuteDataTable(cmd);

5)输入参数/输出参数/返回值的使用(比较重要哦)

        DbHelper db = new DbHelper();
        DbCommand cmd = db.GetStoredProcCommond("t2_insert");
        db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
        db.AddOutParameter(cmd, "@outString", DbType.String, 20);
        db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);

        db.ExecuteNonQuery(cmd);

        string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
        int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value

6)DataReader使用

      DbHelper db = new DbHelper();
        DbCommand cmd = db.GetStoredProcCommond("t2_insert");
        db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
        db.AddOutParameter(cmd, "@outString", DbType.String, 20);
        db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);

        using (DbDataReader reader = db.ExecuteReader(cmd))
        {
            dt.Load(reader);
        }        
        string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
        int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value


7)事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦)

以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:
 

    pubic void DoBusiness()
    {
        using (Trans t = new Trans())
        {
            try
            {
                D1(t);
                throw new Exception();//如果有异常,会回滚滴
                D2(t);
                t.Commit();
            }
            catch
            {
                t.RollBack();
            }
        }
    }
    public void D1(Trans t)
    {
        DbHelper db = new DbHelper();
        DbCommand cmd = db.GetStoredProcCommond("t2_insert");
        db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
        db.AddOutParameter(cmd, "@outString", DbType.String, 20);
        db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);

        if (t == null) db.ExecuteNonQuery(cmd);
        else db.ExecuteNonQuery(cmd,t);

        string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
        int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value
    }
    public void D2(Trans t)
    {
        DbHelper db = new DbHelper();
        DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values('..')");        
        if (t == null) db.ExecuteNonQuery(cmd);
        else db.ExecuteNonQuery(cmd, t);
    }

 

    <appSettings>
        <add key="DbHelperProvider" value="System.Data.SqlClient"/>
        <add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa"/>
    appSettings>

其实,DbHelper需要的仅仅是两个字符串,你可以自己修改,作成加密什么的...

好了,就这样,DbHelper的代码是非常简单和透明的,只是在ado.net上做了一点小包装,改变了一下使用它的程序员的编码方式,去除掉一些比较"物理级"的编程概念,如connection的open和close之类的,使程序员更专注于业务逻辑代码的编写,少死掉点脑细胞,另外,统一了数据操作层的数据操作代码的风格和格式,维护起来很方便!

 

 

下面是我的dbfacroty

 

代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.Common;

namespace Kimbanx.Tradiz.Utility.Server
{
    /// <summary>
    /// 
    /// </summary>
    public class DBUtility
    {
        #region member

        public static string _connString = ConfigurationManager.ConnectionStrings["sltest"].ConnectionString;
        public static string _providerName = ConfigurationManager.ConnectionStrings["sltest"].ProviderName;
        private  static DbProviderFactory _factory;
        private DbConnection _conn; 
        private DbCommand _comm;
        #endregion

        public DBUtility()
            : this(_connString)
        {

        }
        public DBUtility(string connectionString)
        {   CreateFactory();
            this._conn = CreateConnection(connectionString);
         
        }

        public static DbConnection CreateConnection()
        {
            //DbProviderFactory factory = DbProviderFactories.GetFactory(_providerName);
            DbConnection conn = _factory.CreateConnection();
            conn.ConnectionString = _connString;
            return conn;
        }
        public static DbConnection CreateConnection(string connectionString)
        {
            //DbProviderFactory factory = DbProviderFactories.GetFactory(_providerName);
            DbConnection conn = _factory.CreateConnection();
            conn.ConnectionString = connectionString;
            return conn;
        }
        public static void  CreateFactory()
        {
            _factory = DbProviderFactories.GetFactory(_providerName);
        }

        public DbCommand GetStoredProcCommand(string storedProcedure)
        {
            _comm = _conn.CreateCommand();
            _comm.CommandType = CommandType.StoredProcedure;
            _comm.CommandText = storedProcedure;
            return _comm;
        }
        public DbCommand GetSqlStringCommand(string sqlString)
        {
            _comm = _conn.CreateCommand();
            _comm.CommandType = CommandType.Text;
            _comm.CommandText = sqlString;
            return _comm;
        }
        #region Add DbParameters

        public void AddParameterCollection(DbCommand comm, DbParameterCollection dbParameterCollection)
        {
            foreach (DbParameter dbParameter in dbParameterCollection)
            {
                comm.Parameters.Add(dbParameter);
            }
        }
        public void AddOutParameter(DbCommand comm, string parameterName, DbType dbType, int size)
        {
            DbParameter parameter = comm.CreateParameter();
            parameter.DbType = dbType;
            parameter.ParameterName = parameterName;
            parameter.Size = size;
            parameter.Direction = ParameterDirection.Output;
            comm.Parameters.Add(parameter);
        }
        public void AddInParameter(DbCommand comm, string parameterName, DbType dbType, object value)
        {
            DbParameter parameter = comm.CreateParameter();
            parameter.DbType = dbType;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            parameter.ParameterName = parameterName;
            comm.Parameters.Add(parameter);
        }
        public void AddReturnParameter(DbCommand comm, string parameterName, DbType dbType)
        {
            DbParameter parameter = comm.CreateParameter();
            parameter.DbType = dbType;
            parameter.Direction = ParameterDirection.ReturnValue;
            parameter.ParameterName = parameterName;
            comm.Parameters.Add(parameter);
        }
        #endregion
        public DbParameter GetParameter(DbCommand comm, string parameterName)
        {
            return comm.Parameters[parameterName];
        }
        public DataSet ExecuteDataSet(DbCommand comm)
        {
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            adapter.SelectCommand = comm;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }
        public DataTable ExecuteDataTable(DbCommand comm)
        {
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            adapter.SelectCommand = comm;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
        public DbDataReader ExecuteReader(DbCommand comm)
        {
            DbDataReader reader = null;
            comm.Connection.Open();
            reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        public int ExecuteNonQuery(DbCommand comm)
        {
            comm.Connection.Open();
            int result = comm.ExecuteNonQuery();
            comm.Connection.Close();
            return result;
        }
        public object ExecuteScalar(DbCommand comm)
        {
            comm.Connection.Open();
            object obj = comm.ExecuteScalar();
            comm.Connection.Close();
            return obj;
        }

        public DataSet ExecuteDataSet(DbCommand comm, Trans t)
        {
            comm.Connection = t.DbConnection;
            comm.Transaction = t.DbTransaction;
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            adapter.SelectCommand = comm;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }
        public DataTable ExecuteDataTable(DbCommand comm, Trans t)
        {
            comm.Connection = t.DbConnection;
            comm.Transaction = t.DbTransaction;
            DbDataAdapter adapter = _factory.CreateDataAdapter();
            adapter.SelectCommand = comm;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
        public DbDataReader ExecuteReader(DbCommand comm, Trans t)
        {
            comm.Connection.Close();
            DbDataReader reader = null;
            comm.Connection = t.DbConnection;
            comm.Transaction = t.DbTransaction;
            reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        public int ExecuteNonQuery(DbCommand comm, Trans t)
        {
            comm.Connection.Close();
            comm.Connection = t.DbConnection;
            comm.Transaction = t.DbTransaction;
            int result = comm.ExecuteNonQuery();
            return result;
        }
        public object ExecuteScalar(DbCommand comm, Trans t)
        {
            comm.Connection.Close();
            comm.Connection = t.DbConnection;
            comm.Transaction = t.DbTransaction;
            object obj = comm.ExecuteScalar();
            return obj;
        }
    }
    public class Trans : IDisposable
    {
        private DbConnection _conn;
        private DbTransaction _dbTran;
        public DbConnection DbConnection
        {
            get { return this._conn; }
        }
        public DbTransaction DbTransaction
        {
            get { return this._dbTran; }
        }
        public Trans()
            : this(DBUtility._connString)
        {

        }
        public Trans(string connectionString)
        {
            _conn = DBUtility.CreateConnection(connectionString);
            _conn.Open();
            _dbTran = _conn.BeginTransaction();
        }
        public void Commit()
        {
            _dbTran.Commit();
            this.Close();
        }
        public void RollBack()
        {
            _dbTran.Rollback();
            this.Close();
        }
        public void Close()
        {
            if (_conn.State != ConnectionState.Closed)
                _conn.Close();
        }
        #region IDisposable Members

        public void Dispose()
        {
            Close();
        }

        #endregion
    }
}

 

 

使用上面的代码测试的代码

 

代码
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Kimbanx.Tradiz.Web.Test.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <br />
    </div>
    <fieldset>
        <legend>清空数据</legend>
        <asp:Button ID="btnClear" runat="server" Text="Clear Data" onclick="btnClear_Click" 
          />
    </fieldset>
    <br />
    <fieldset>
        <legend>使用DataReader获取数据</legend>
        <asp:Button ID="btnDataReader" runat="server" Text="DataReader" 
            onclick="btnDataReader_Click" />
    </fieldset>
    <br />
    <fieldset>
        <legend>使用DataSet获取数据</legend>
        <asp:Button ID="btnDataSet" runat="server" Text="DataSet" 
            onclick="btnDataSet_Click" />
    </fieldset>
    <br />
    <fieldset>
        <legend>使用DataTable获取数据</legend>
        <asp:Button ID="btnDataTable" runat="server" Text="DataTable" 
            onclick="btnDataTable_Click" />
    </fieldset>
    <br />
    <fieldset>
        <legend>一次添加两条数据，使用事务成功</legend>UserId:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtSucessUserId" runat="server"></asp:TextBox>
        <br />
        UserName:<asp:TextBox ID="txtSucessUserName" runat="server"></asp:TextBox>
        <br />
        Password: <asp:TextBox ID="txtSucessPassword" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnSucess" runat="server" Text="Sucess" OnClick="btnSucess_Click" />
    </fieldset>
    <br />
    <br />
    <fieldset>
        <legend>一次添加两条数据，使用事务失败</legend>UserId:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtFailUserId" runat="server"></asp:TextBox>
        <br />
        UserName:<asp:TextBox ID="txtFailUserName" runat="server"></asp:TextBox>
        <br />
          Password: <asp:TextBox ID="txtFailPassword" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnFail" runat="server" Text="Fail" OnClick="btnFail_Click" />
    </fieldset>
    <br />
    <br />
    <fieldset>
        <legend>删除一个用户</legend>UserId:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtDeleteUserId" runat="server"></asp:TextBox>
        <br />
        <br />
        <asp:Button ID="btnDelete" runat="server" Text="删除一个用户" OnClick="btnDelete_Click" />
    </fieldset>
    <br />
    <br />
    <fieldset>
        <legend>获取一个用户信息</legend>UserId:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtGetUserId" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnGet" runat="server" Text="获取一个用户" OnClick="btnGet_Click" />
        <br />
        Password:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtGetPassword" runat="server"></asp:TextBox>
        <br />
        UserName:<asp:TextBox ID="txtGetUserName" runat="server"></asp:TextBox>
        <br />
    </fieldset>
    <br />
    
    <fieldset >
    <legend >使用TransactionScope事务</legend>
    <fieldset>
        <legend>一次添加两条数据，使用事务成功</legend>UserId:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtScopeSucessUserId" runat="server"></asp:TextBox>
        <br />
        UserName:<asp:TextBox ID="txtScopeSucessUserName" runat="server"></asp:TextBox>
        <br />
        Password: <asp:TextBox ID="txtScopeSucessPassword" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnScopeSucess" runat="server" Text="ScopeSucess" OnClick="btnScopeSucess_Click" />
    </fieldset>
    <br />
    </fieldset>
     <legend >使用TransactionScope事务</legend>
    <fieldset>
        <legend>一次添加两条数据，使用事务失败</legend>UserId:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtScopeFailUserId" runat="server"></asp:TextBox>
        <br />
        UserName:<asp:TextBox ID="txtScopeFailUserName" runat="server"></asp:TextBox>
        <br />
          Password: <asp:TextBox ID="txtScopeFailPassword" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnScopeFail" runat="server" Text="ScopeFail" OnClick="btnScopeFail_Click" />
    </fieldset>
    </fieldset>
    </form>
</body>
</html>

 

 

 

代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Transactions;
using System.Data;
using Kimbanx.Tradiz.Utility.Server;
using System.Data.Common;

namespace Kimbanx.Tradiz.Web.Test
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("GetUsers");

            //DbDataReader reader = db.ExecuteDataReader(comm);
            //this.GridView1.DataSource = reader;

            DataSet ds = db.ExecuteDataSet(comm);
            
            this.GridView1.DataBind();
        }


        protected void btnDelete_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtDeleteUserId.Text))
                return;
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("DeleteUserById");
            db.AddInParameter(comm, "UserId", DbType.String, txtDeleteUserId.Text.Trim());
            db.ExecuteNonQuery(comm);

        }
        # region Transaction Example Code
        private void CustomTranscape()
        {
            using (TransactionScope scope = new TransactionScope())
            {
                DbOperation();
                BankOperation();

                scope.Complete();
            }
        }
        private void DbOperation()
        { 
            throw new Exception(); 
        }
        private void BankOperation()
        { 
            //调用银行的扣钱接口
            throw new Exception();
        }
        #endregion
        protected void btnGet_Click(object sender, EventArgs e)
        {
            if(string.IsNullOrEmpty (txtGetUserId .Text ))
                return ;
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("GetUserById");
            db.AddInParameter(comm, "UserId", DbType.String, txtGetUserId.Text.Trim());
            DbDataReader reader = db.ExecuteReader(comm );
            if (reader != null)
            {
                while (reader.Read())
                {
                    this.txtGetUserId.Text = reader.GetString(1);
                    this.txtGetUserName.Text = reader.GetString(2);
                    this.txtGetPassword.Text = reader.GetString(3);
                }
            }
            else
            {
                this.txtGetUserId.Text = string.Empty;
                this.txtGetUserName.Text = string.Empty;
                this.txtGetPassword.Text = string.Empty;
            }
        }
        #region Add User to Database
        private void AddUser(Users user)
        {
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("AddUser");
            db.AddInParameter(comm, "UserId", DbType.String, user.UserId);
            db.AddInParameter(comm, "UserName", DbType.String, user.UserName);
            db.AddInParameter(comm, "Password", DbType.String, user.Password);
            db.ExecuteNonQuery(comm);
        }
        private void AddUser(Users user,Trans t)
        {
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("AddUser");
            db.AddInParameter(comm, "UserId", DbType.String, user.UserId);
            db.AddInParameter(comm, "UserName", DbType.String, user.UserName );
            db.AddInParameter(comm, "Password", DbType.String, user.Password );
            db.ExecuteNonQuery(comm,t);
        }
        #endregion
        #region Build User Example Code
        private Users  BuildSucsssUser1()
        {
            return  new Users()
            {
                UserId = txtSucessUserId.Text.Trim()+"1",
                UserName = txtSucessUserName.Text.Trim() + "1",
                Password = txtSucessPassword.Text.Trim() + "1"
            };
          
        }
        private Users BuildSucsssUser2()
        {
            return new Users()
            {
                UserId = txtSucessUserId.Text.Trim() + "2",
                UserName = txtSucessUserName.Text.Trim() + "2",
                Password = txtSucessPassword.Text.Trim() + "2"
            };
        }
        private Users BuildFailUser1()
        {
            throw new Exception();
            return new Users()
            {
                UserId = txtFailUserId.Text.Trim() + "1",
                UserName = txtFailUserName.Text.Trim() + "1",
                Password = txtFailPassword.Text.Trim() + "1"
            };
        }
        private Users BuildFailUser2()
        { 
            return new Users()
            {
                UserId = txtFailUserId.Text.Trim() + "2",
                UserName = txtFailUserName.Text.Trim() + "2",
                Password = txtFailPassword.Text.Trim() + "2"
            };
           
        }
        #endregion
        protected void btnSucess_Click(object sender, EventArgs e)
        {
            using (Trans t = new Trans())
            {
                try
                {
                    AddUser(BuildSucsssUser1(),t);

                    AddUser(BuildSucsssUser2(),t);
                    t.Commit();
                }
                catch
                {
                    t.RollBack();
                }
            }
        }

        protected void btnFail_Click(object sender, EventArgs e)
        {


            using (Trans t = new Trans())
            {
                try
                {
                    AddUser(BuildFailUser1 (),t );


                    AddUser(BuildFailUser2(),t );
                    t.Commit();
                }
                catch
                {
                    t.RollBack();
                }
            }
        }

        protected void btnDataReader_Click(object sender, EventArgs e)
        {
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("GetUsers");

            DbDataReader reader = db.ExecuteReader(comm);
            this.GridView1.DataSource = reader;

            this.GridView1.DataBind();
        }

        protected void btnDataSet_Click(object sender, EventArgs e)
        {
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("GetUsers");

            DataSet  ds = db.ExecuteDataSet (comm);
            this.GridView1.DataSource = ds.Tables[0];

            this.GridView1.DataBind();
        }

        protected void btnDataTable_Click(object sender, EventArgs e)
        {
            DBUtility db = new DBUtility();
            DbCommand comm = db.GetStoredProcCommand("GetUsers");

            DataTable dt = db.ExecuteDataTable(comm);
            this.GridView1.DataSource = dt;

            this.GridView1.DataBind();
        }

        protected void btnClear_Click(object sender, EventArgs e)
        {
            this.GridView1.DataSource = null;
           
        }

        protected void btnScopeSucess_Click(object sender, EventArgs e)
        {
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
            {
                AddUser(BuildSucsssUser1());

                AddUser(BuildSucsssUser2());

                scope.Complete();
            }
            
        }

        protected void btnScopeFail_Click(object sender, EventArgs e)
        {
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
            {
                try
                {
                    AddUser(BuildFailUser1());

                    AddUser(BuildFailUser2());
                }
                catch { }
                finally
                {
                    scope.Complete();
                }
            }
        }
    }
}

 

 

代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Kimbanx.Tradiz.Web.Test
{
    public class Users
    {
        private Guid _tableId;

        public Guid TableId
        {
            get { return _tableId; }
        }
        private string _userId;

        public string UserId
        {
            get { return _userId; }
            set { _userId = value; }
        }
        private string _userName;

        public string UserName
        {
            get { return _userName; }
            set { _userName = value; }
        }
        private string _password;

        public string Password
        {
            get { return _password; }
            set { _password = value; }
        }

    }
}

 

 

代码
 <connectionStrings>
    <add name="sltest" connectionString="server=SHIWB\SQL2005DEV;database=sltest;uid=as;pwd=as;Pooling=true;Max Pool Size=100;Min Pool Size=3"
         providerName="System.Data.SqlClient"></add>
  </connectionStrings>