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

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

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

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

 

DbHelper数据操作类,DbProviderFactories

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

 

Code
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 >

 

 

转载于:https://www.cnblogs.com/virusswb/articles/1673254.html