DBHelper类

    using System;     using System.Data;     using System.Data.Common;     using System.Configuration; namespace MyDBHelper {     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 增加参数         #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 执行         #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 执行事务         #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();             }         }     } }