数据库封装类DataOperation改进(支持事务、存储过程、简单使用例子)

今天对数据访问类进行了改进,支持存储过程、带参数的查询。虽然这种封装对于大型项目来说没有太大的好处，或者说没有什么意义，但是对于一些小小的项目或者自己写着玩的程序用这个类库还是比较节约代码的。至少使用起来很方便。将来有时间我打算把一个用于大型项目的数据库封装写上来。

1.参数集合类，用于添加参数元素。

using  System.Collections;
using  System.Data;
using  System.Data.SqlClient;
using  System.Data.Common;
using  System;

namespace  DataOperation
{
     ///   <summary>
     ///  SqlParameters是参数集合类,用于带参数、或存储过程操作数据库传递参数
     ///   </summary>
     public   class  SqlParameters:CollectionBase
    {
         public  SqlParameters()
        {
        }
         ///   <summary>
         ///  向参数集合中添加参数元素
         ///   </summary>
         ///   <param name="sqlParameter"> 参数元素 </param>
         ///   <returns> 返回添加数据的位置 </returns>
         public   int  Add(SqlParameter sqlParameter)
        {
             return  List.Add(sqlParameter);
        }
         ///   <summary>
         ///  从参数集合中指定的移出元素
         ///   </summary>
         ///   <param name="sqlParameter"></param>
         public   void  Remove(SqlParameter sqlParameter)
        {
            List.Remove(sqlParameter);
        }
         ///   <summary>
         ///  索引
         ///   </summary>
         public  SqlParameter  this [ int  index]
        {
             get
            {
                 return  (SqlParameter)List[index];
            }
             set
            {
                List[index] = value;
            }
        }
         ///   <summary>
         ///  索引
         ///   </summary>
         public  SqlParameter  this [ string  sqlParameterName]
        {
             get
            {
                SqlParameter sqlParameter;
                 foreach ( object  ob  in  List)
                {
                    sqlParameter  =  (SqlParameter)ob;
                     if (sqlParameter.ParameterName == sqlParameterName)
                    {
                         return  sqlParameter;
                    }
                }
                 return   null ;
            }
             set
            {
                SqlParameter sqlParameter;
                 foreach ( object  ob  in  List)
                {
                    sqlParameter  =  (SqlParameter)ob;
                     if (sqlParameter.ParameterName == sqlParameterName)
                    {
                        sqlParameter  =  value;
                         return ;
                    }
                }
            }
        }

    }
}

2.数据库操作主类

using  System;
using  System.Collections;
using  System.Data;
using  System.Data.SqlClient;

namespace  DataOperation
{
     ///   <summary>
     ///  MsSql操作类
     ///   </summary>
     sealed   public   class  DataMsSql
    {
         private  SqlConnection dataConnection  =   null ;
         private  SqlTransaction dbTransaction  =   null ;

         #region  构造函数
         ///   <summary>
         ///  无参构造函数
         ///   </summary>
         public  DataMsSql()
        {
            dataConnection  =   new  SqlConnection();
        }
         ///   <summary>
         ///  有参构造函数
         ///   </summary>
         ///   <param name="connectionString"> 数据库联接串 </param>
         public  DataMsSql( string  connectionString)
        {
            dataConnection  =   new  SqlConnection(connectionString);
        }
         #endregion

         #region  属性
         ///   <summary>
         ///  设置或获取SqlConnection类型数据库联接dataConnection的值
         ///   </summary>
         public  SqlConnection DataConnection
        {
             get
            {
                 if  (dataConnection  ==   null )
                    dataConnection  =   new  SqlConnection();
                 return  dataConnection;
            }
             set
            {
                dataConnection  =  value;
            }
        }
         ///   <summary>
         ///  获取数据库联接的状态
         ///   </summary>
         public  ConnectionState SqlState
        {
             get
            {
                 return  dataConnection.State;
            }
        }
         #endregion

         #region  状态操作
         ///   <summary>
         ///  打开数据库联接
         ///   </summary>
         public   void  Open()
        {
             if  (dataConnection.State  !=  ConnectionState.Open)
                dataConnection.Open();
        }
         ///   <summary>
         ///  关闭数据库联接
         ///   </summary>
         public   void  Close()
        {
             if  (dataConnection.State  !=  ConnectionState.Closed)
                dataConnection.Close();
        }
         #endregion

         #region  一般数据语句操作
         ///   <summary>
         ///  获取检索出来首行首列的值
         ///   </summary>
         ///   <param name="sqlStr"> sql查询串 </param>
         ///   <returns> 结果 </returns>
         public   object  ExecuteScalar( string  sqlStr,SqlParameters sqlParameters)
        {
             if  (SqlState  ==  ConnectionState.Closed)
                 this .Open();
            SqlCommand cmd  =   new  SqlCommand(sqlStr, dataConnection);
            AddParameters(cmd,sqlParameters);
             return  cmd.ExecuteScalar();
        }
         public   object  ExecuteScalar( string  sqlStr)
        {
             return  ExecuteScalar(sqlStr, null );
        }
             ///   <summary>
         /// 检索数据以SqlDataReader形式返检索结果
         ///   </summary>
         ///   <param name="sqlStr"> sql查询串 </param>
         ///   <returns> SqlDataReader数据集 </returns>
         public  SqlDataReader GetDataReader( string  sqlStr,SqlParameters sqlParameters)
        {
             if  (SqlState  ==  ConnectionState.Closed)
                 this .Open();
            SqlCommand cmd  =   new  SqlCommand(sqlStr, dataConnection);
            AddParameters(cmd,sqlParameters);
             return  cmd.ExecuteReader();
        }
         public  SqlDataReader GetDataReader( string  sqlStr)
        {
             return  GetDataReader(sqlStr, null );
        }
             ///   <summary>
         ///  检索数据以DataTable形式返检索结果
         ///   </summary>
         ///   <param name="sqlStr"> sql查询串 </param>
         ///   <returns> DataTale数据集 </returns>
         public  DataTable GetDataTable( string  sqlStr,SqlParameters sqlParameters)
        {
            DataTable dt  =   new   DataTable();
            SqlCommand cmd  =   new  SqlCommand(sqlStr, dataConnection);
            AddParameters(cmd,sqlParameters);
            SqlDataAdapter adapter  =   new  SqlDataAdapter(cmd);
            adapter.Fill(dt);
             return  dt;
        }
         public  DataTable GetDataTable( string  sqlStr)
        {
             return  GetDataTable(sqlStr, null );
        }
             ///   <summary>
         ///  执行出检索以外的其它数据操作返回影响条数
         ///   </summary>
         ///   <param name="sqlStr"> sql操作语句 </param>
         ///   <returns>  操作影响条数 </returns>
         public   int  ExecuteSql(String sqlStr,SqlParameters sqlParameters)
        {
             if  (SqlState  ==  ConnectionState.Closed)
                 this .Open();
            SqlCommand cmd  =   new  SqlCommand(sqlStr, DataConnection);
            AddParameters(cmd,sqlParameters);
             return  cmd.ExecuteNonQuery();
        }
         public   int  ExecuteSql(String sqlStr)
        {
             return  ExecuteSql(sqlStr, null );
        }
         #endregion

         #region  存储过程操作
         ///   <summary>
         ///  执行存储过程获得DataTable数据
         ///   </summary>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> DataTable形式的结果集 </returns>
         public  DataTable StoredProcGetDataTable( string  storedProcedureName)
        {
             return  StoredProcGetDataTable(storedProcedureName, null );
        }
         ///   <summary>
         ///  执行存储过程获得DataTable数据
         ///   </summary>
         ///   <param name="sqlParameters"> 存储过程参数ArrayList类型的SqlParameter集合 </param>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> DataTable形式的结果集 </returns>
         public  DataTable StoredProcGetDataTable( string  storedProcedureName,SqlParameters sqlParameters)
        {
            SqlCommand cmd  =   new  SqlCommand();
            cmd.Connection  =  dataConnection;
            cmd.CommandType  =  CommandType.StoredProcedure;
            cmd.CommandText  =  storedProcedureName;
            AddParameters(cmd,sqlParameters);
            SqlDataAdapter dataAdapter  =   new  SqlDataAdapter(cmd);
            DataTable dt  =   new  DataTable();
            dataAdapter.Fill(dt);
             return  dt;
        }
         ///   <summary>
         ///  执行存储过程返回影响条数
         ///   </summary>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> 影响条数 </returns>
         public   int  StoredProcExecute( string  storedProcedureName)
        {
             return  StoredProcExecute(storedProcedureName, null );
        }
         ///   <summary>
         ///  执行存储过程返回影响条数
         ///   </summary>
         ///   <param name="sqlParameters"> 存储过程参数ArrayList类型的SqlParameter集合 </param>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> 影响条数 </returns>
         public   int  StoredProcExecute( string  storedProcedureName,SqlParameters sqlParameters)
        {
            SqlCommand cmd  =   new  SqlCommand();
            cmd.Connection  =  dataConnection;
            cmd.CommandType  =  CommandType.StoredProcedure;
            cmd.CommandText  =  storedProcedureName;
            AddParameters(cmd,sqlParameters);
             return  cmd.ExecuteNonQuery();
        }
         ///   <summary>
         ///  执行存储过程获得数据并以SqlDataReader返回结果
         ///   </summary>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> SqlDataReader形式的结果集 </returns>
         public  SqlDataReader StoredProcGetDataReader( string  storedProcedureName)
        {
             return  StoredProcGetDataReader(storedProcedureName, null );
        }
         ///   <summary>
         ///  执行存储过程获得数据并以SqlDataReader返回结果
         ///   </summary>
         ///   <param name="sqlParameters"> 存储过程参数ArrayList类型的SqlParameter集合 </param>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> SqlDataReader形式的结果集 </returns>
         public  SqlDataReader StoredProcGetDataReader( string  storedProcedureName,SqlParameters sqlParameters)
        {
            SqlCommand cmd  =   new  SqlCommand();
            cmd.Connection  =  dataConnection;
            cmd.CommandType  =  CommandType.StoredProcedure;
            cmd.CommandText  =  storedProcedureName;
            AddParameters(cmd,sqlParameters);
             return  cmd.ExecuteReader();
        }
         ///   <summary>
         ///  执行存储过程获得数据并返回首行首列
         ///   </summary>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> 返回首行首列 </returns>
         public   object  StoredProcExecuteScalar( string  storedProcedureName)
        {
             return  StoredProcExecuteScalar(storedProcedureName, null );
        }
         ///   <summary>
         ///  执行存储过程获得数据并返回首行首列
         ///   </summary>
         ///   <param name="sqlParameters"> 存储过程参数ArrayList类型的SqlParameter集合 </param>
         ///   <param name="storedProcedureName"> 存储过程名称 </param>
         ///   <returns> 返回首行首列 </returns>
         public   object  StoredProcExecuteScalar( string  storedProcedureName,SqlParameters sqlParameters)
        {
            SqlCommand cmd  =   new  SqlCommand();
            cmd.Connection  =  dataConnection;
            cmd.CommandType  =  CommandType.StoredProcedure;
            cmd.CommandText  =  storedProcedureName;
            AddParameters(cmd,sqlParameters);
             return  cmd.ExecuteScalar();
        }
         #endregion

         #region  事务操作
         ///   <summary>
         ///  开启事务
         ///   </summary>
         public   void  BeginTransaction()
        {
            dbTransaction  =  dataConnection.BeginTransaction();
        }
         ///   <summary>
         ///  提交事务
         ///   </summary>
         public   void  Commit()
        {
            dbTransaction.Commit();
        }
         ///   <summary>
         ///  回滚事务
         ///   </summary>
         public   void  Rollback()
        {
            dbTransaction.Rollback();
        }
         #endregion
         private   void  AddParameters(SqlCommand cmd,SqlParameters sqlParameters)
        {
             if  ( null != sqlParameters  &&   0 != sqlParameters.Count)
             foreach  (SqlParameter parameter  in  sqlParameters)
            {
                cmd.Parameters.Add(parameter);
            }
        }

    }
}

下面举一个使用的简单例子：

             // 实例化一个操作类
            DataOperation.DataMsSql dms  =   new  DataOperation.DataMsSql( " Server=.;database=XJSXM;uid=XJSXM;pwd=XJSXM " );
             // sql语句
             string  sqlCommand  =   " select * from tblBZWD where ZDBH=@zdbh " ;
             // 参数集合
            DataOperation.SqlParameters sqlParas  =   new  DataOperation.SqlParameters();
             // 添加一个参数
            sqlParas.Add( new  System.Data.SqlClient.SqlParameter( " @zdbh " , 254 ));
            DataTable dt  =  dms.GetDataTable(sqlCommand,sqlParas);