通用数据库操作辅助类DbHelper

http://www.cnblogs.com/yelaiju/archive/2010/09/22/1832916.html

 

使用方式
DbHelper db;
OpenFileDialog ofd  =   new  OpenFileDialog();
ofd.Filter  =   " SQLite数据文件(*.db3;*.db;*.sqlite)|*.db3;*.db;*.sqlite " ;
if  (ofd.ShowDialog()  ==  DialogResult.OK)
{
    txtDataSource.Text  =  ofd.FileName;
    db  =   new  DbHelper( " data source= "   +  txtDataSource.Text, DbProvider.Sqlite);
}
db.ReExNum(txtSql.Text); 

接口
using  System.Data;
using  System.Collections.Generic;
using  System.Data.Common;

///   <summary>
///  该类用于对数据库进行操作
///  Design by 火地晋
///   </summary>
namespace  System.Data
{
     public   interface  IDbObject
    {
         ///   <summary>
         ///  定义一个DataReader的列表，已备检索
         ///   </summary>
        List < DbDataReader >  DataReaderList {  get ;  set ; }
         ///   <summary>
         ///  
         ///   </summary>
        DbConnection Connection {  get ;  set ; }
         string  ConnectionString {  get ;  set ; }
        DbProvider DbProviderType {  get ;  set ; }
        
         ///   <summary>
         ///  返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         int  ReExNum( string  strSql,  params  DbParameter[] parameters);
         ///   <summary>
         ///  返回结果的存储过程
         ///   </summary>
         ///   <param name="strSql"> 任何SQL语句 </param>
         ///   <param name="parameters"> 参数值 </param>
         ///   <returns></returns>
        DbDataReader ReSelectdr( string  strSql,  params  DbParameter[] parameters);
         ///   <summary>
         ///  返回dateSet
         ///   </summary>
         ///   <param name="strSql"></param>
         ///   <param name="parameters"></param>
         ///   <param name="tableName"></param>
         ///   <returns></returns>
        DataSet ReSelectds( string  strSql,  string  tableName,  params  DbParameter[] parameters);
        DataTable ReSelectdtb( string  strSql,  params  DbParameter[] parameters);
         ///   <summary>
         ///  通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象
         ///   </summary>
        DbDataAdapter ReSelectdat( string  strSql,  params  DbParameter[] parameters);

         void  ExSQL( string  strSql,  params  DbParameter[] parameters);
         ///   <summary>
         ///      執行SQL查詢語句,返回記錄條數
         ///   </summary>
         ///   <param name="strSql"> Select語句(在select语句中,使用Count(*)函数) </param>
         ///   <returns> 返回查詢到之記錄條數 </returns>
         int  ReSelectNum( string  strSql,  params  DbParameter[] parameters);
         ///   <summary>
         ///  使用SqlDataAdapter返回指定范围的数据
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数名 </param>
         ///   <param name="start"> 起始行 </param>
         ///   <param name="maxRecord"> 记录数 </param>
         ///   <param name="tableName"> 表名 </param>
         ///   <returns></returns>
        DataSet ReSelectds( string  strSql, DbParameter[] parameters,  int  start,  int  maxRecord,  string  tableName);
         ///   <summary>
         ///  返回执行操作成功的数目，不关闭连接
         ///   </summary>
         ///   <param name="strSql"> 执行的查询语句或存储过程 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         int  ReExNumNoClose( string  strSql,  params  DbParameter[] parameters);
         ///   <summary>
         ///  返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         int  ReExNumNoClose( string  strSql,  out   string  error,  params  DbParameter[] parameters);

         ///   <summary>
         ///  返回执行操作成功的数目，并返回发生的错误信息
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         int  ReExNum( string  strSql,  out   string  error,  params  DbParameter[] parameters);

         ///   <summary>
         ///  返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         string  ReExStr( string  strSql,  params  DbParameter[] parameters);
         ///   <summary>
         ///  如果数据库连接已关闭,则打开
         ///   </summary>
         ///   <returns></returns>
         bool  OpenConnection();
         ///   <summary>
         ///  关闭数据库连接
         ///   </summary>
         void  CloseConnection();
    }
     public   enum  DbProvider
    {
        Sql = 0 ,
        Sqlite = 1 ,
        OleDb = 2 ,
        Oracle = 3 ,
        MySql = 4
    }
}
 
类
using  System;
using  System.Collections.Generic;
using  System.Text;
using  System.Data.Common;

namespace  System.Data
{
     public   class  DbHelper : IDbObject
    {
         #region  属性
         public  List < DbDataReader >  DataReaderList {  get ;  set ; }
         public  DbConnection Connection {  get ;  set ; }
         public   string  ConnectionString {  get ;  set ; }
         public  DbProvider DbProviderType {  get ;  set ; }
         private  DbProviderFactory dbFactory;
         #endregion

         #region  构造函数
         public  DbHelper()
        {
            DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings[ " ConnectionString " ], DbProvider.Sql);
        }
         public  DbHelper( string  connectionString)
        {
            DbHelperInstance(connectionString, DbProvider.Sql);
        }
         public  DbHelper(DbProvider dbProviderType)
        {
            DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings[ " ConnectionString " ], dbProviderType);
        }
         public  DbHelper( string  connectionString, DbProvider dbProviderType)
        {
            DbHelperInstance(connectionString, dbProviderType);
        }
         ///   <summary>
         ///  初始化
         ///   </summary>
         ///   <param name="connectionString"></param>
         ///   <param name="dbProviderType"></param>
         public   void  DbHelperInstance( string  connectionString, DbProvider dbProviderType)
        {
             this .DbProviderType  =  dbProviderType;
             this .ConnectionString  =  connectionString;
            DataReaderList  =   new  List < DbDataReader > ();
            CreateFactory();
             this .Connection  =   this .dbFactory.CreateConnection();
             this .Connection.ConnectionString  =   this .ConnectionString;
        }
         #endregion
         ///   <summary>
         ///  创建数据操作工厂
         ///   </summary>
         private   void  CreateFactory()
        {
             switch  (DbProviderType)
            {
                 case  DbProvider.Sql:
                     this .dbFactory  =  System.Data.SqlClient.SqlClientFactory.Instance;
                     break ;
                 case  DbProvider.Sqlite:
                     this .dbFactory  =  System.Data.SQLite.SQLiteFactory.Instance;
                     break ;
                 case  DbProvider.OleDb:
                     this .dbFactory  =  System.Data.OleDb.OleDbFactory.Instance;
                     break ;
                 case  DbProvider.Oracle:
                     this .dbFactory  =  System.Data.OracleClient.OracleClientFactory.Instance;
                     break ;
                 case  DbProvider.MySql:
                     this .dbFactory  =  MySql.Data.MySqlClient.MySqlClientFactory.Instance;
                     break ;
            }
        }
         ///   <summary>
         ///  创建操作对象
         ///   </summary>
         ///   <param name="procNameOrExText"> 如果包含@,则采用CommandType.Text </param>
         ///   <param name="parameters"></param>
         ///   <returns></returns>
         private  DbCommand BuilderQueryCommand( string  procNameOrExText,  params  DbParameter[] parameters)
        {
             if  (parameters  ==   null   ||  parameters.Length  ==   0 )
            {
                DbCommand command  =   this .dbFactory.CreateCommand();
                command.CommandText  =  procNameOrExText;
                command.Connection  =   this .Connection;
                 return  command;
            }
             if  (procNameOrExText.IndexOf( ' @ ' )  >   0 ) // 存储过程
            {
                 return  BuilderQueryCommandText(procNameOrExText, parameters);
            }
             else
            {
                 return  BuilderQueryCommandStorPro(procNameOrExText, parameters);
            }
        }

         ///   <summary>
         ///  根据存储过程名称和参数生成对应的SQL命令对象
         ///   </summary>
         ///   <param name="strSql"> 存储过程名或者 </param>
         ///   <param name="parameters"> 存储过程参数 </param>
         ///   <returns></returns>
         private  DbCommand BuilderQueryCommandStorPro( string  strSql,  params  DbParameter[] parameters)
        {
            DbCommand command  =   this .dbFactory.CreateCommand();
            command.CommandText  =  strSql;
            command.CommandType  =  CommandType.StoredProcedure;
            command.Connection  =   this .Connection;
             if  (parameters  !=   null )
            {
                 foreach  (DbParameter p  in  parameters)
                {
                    command.Parameters.Add(p);
                }
            }
             return  command;
        }
         private  DbCommand BuilderQueryCommandText( string  strSql,  params  DbParameter[] parameters)
        {
            DbCommand command  =   this .dbFactory.CreateCommand();
            command.CommandText  =  strSql;
            command.Connection  =   this .Connection;
             if  (parameters  !=   null )
            {
                 foreach  (DbParameter p  in  parameters)
                {
                    command.Parameters.Add(p);
                }
            }
             return  command;
        }
         public  DbParameter CreateDbParameter( string  parameterName)
        {
             return  CreateDbParameter(parameterName, DBNull.Value, DbType.Object,  0 , ParameterDirection.Input);
        }
         public  DbParameter CreateDbParameter( string  parameterName,  object  value)
        {
             return  CreateDbParameter(parameterName, value, DbType.Object,  0 , ParameterDirection.Input);
        }
         public  DbParameter CreateDbParameter( string  parameterName,  object  value, DbType dbType)
        {
             return  CreateDbParameter(parameterName,value,dbType, 0 ,ParameterDirection.Input);
        }
         public  DbParameter CreateDbParameter( string  parameterName,  object  value, DbType dbType,  int  size)
        {
             return  CreateDbParameter(parameterName,value,dbType,size,ParameterDirection.Input);
        }
         public  DbParameter CreateDbParameter( string  parameterName,  object  value, DbType dbType,  int  size, ParameterDirection parameterDirection)
        {
            DbParameter pat  =   this .dbFactory.CreateParameter();
            pat.ParameterName  =  parameterName;
            pat.Value  =  value;
            pat.DbType  =  dbType;
            pat.Size  =  size;
            pat.Direction  =  parameterDirection;
             return  pat;
        }
         ///   <summary>
         ///  返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         public   int  ReExNum( string  strSql,  params  DbParameter[] parameters)
        {
             int  effect  =   0 ;
             if  ( ! OpenConnection())  return   - 1 ;
            DbTransaction trans  =  Connection.BeginTransaction();
             try
            {
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                cmd.Transaction  =  trans;
                 // 根据是否为存储过程来执行不同的处理
                 if  (cmd.CommandType  ==  CommandType.StoredProcedure)
                {
                     object  result  =  cmd.ExecuteScalar();
                    effect  =  result  ==   null   ?   - 1  : Convert.ToInt16(result);
                }
                 else
                {
                    effect  =  cmd.ExecuteNonQuery();
                }
                trans.Commit();
                 return  effect;
            }
             catch
            {
                trans.Rollback();
                Connection.Close();
                 return   - 1 ;
            }
             finally
            {
                Connection.Close();
            }
        }
         ///   <summary>
         ///  返回结果的存储过程
         ///   </summary>
         ///   <param name="strSql"> 任何SQL语句 </param>
         ///   <param name="parameters"> 参数值 </param>
         ///   <returns></returns>
         public  DbDataReader ReSelectdr( string  strSql,  params  DbParameter[] parameters)
        {
             try
            {
                DbDataReader reader;
                 if  ( ! OpenConnection())  return   null ;
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                reader  =  cmd.ExecuteReader(CommandBehavior.CloseConnection); // 在dr关闭之后,就不需要进行cnn的关闭操作了
                DataReaderList.Add(reader); // 添加进dr列表,已备检索
                 return  reader;
            }
             catch
            {
                 return   null ;
            }
        }
         ///   <summary>
         ///  返回dateSet
         ///   </summary>
         ///   <param name="strSql"></param>
         ///   <param name="parameters"></param>
         ///   <param name="tableName"></param>
         ///   <returns></returns>
         public  DataSet ReSelectds( string  strSql,  string  tableName,  params  DbParameter[] parameters)
        {
             try
            {
                DataSet ds  =   new  DataSet();
                 if  ( ! OpenConnection())  return   null ;
                DbDataAdapter myDa  =   this .dbFactory.CreateDataAdapter();
                myDa.SelectCommand  =  BuilderQueryCommand(strSql, parameters);
                myDa.Fill(ds, tableName);
                 return  ds;
            }
             catch
            {
                 return   null ;
            }
             finally
            {
                Connection.Close();
            }
        }
         public  DataTable ReSelectdtb( string  strSql,  params  DbParameter[] parameters)
        {
             try
            {
                DataTable dt  =   new  DataTable();
                 if  ( ! OpenConnection())  return   null ;
                DbDataAdapter myDa  =   this .dbFactory.CreateDataAdapter();
                myDa.SelectCommand  =  BuilderQueryCommand(strSql, parameters);
                myDa.Fill(dt);
                 return  dt;
            }
             catch
            {
                 return   null ;
            }
             finally
            {
                Connection.Close();
            }
        }
         ///   <summary>
         ///  通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象
         ///   </summary>
         public  DbDataAdapter ReSelectdat( string  strSql,  params  DbParameter[] parameters)
        {
             if  ( ! OpenConnection())  return   null ;
             try
            {
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                DbDataAdapter myDa  =   this .dbFactory.CreateDataAdapter();
                myDa.SelectCommand  =  cmd;
                 return  myDa;
            }
             catch
            {
                Connection.Close();
                 return   null ;
            }
        }

         public   void  ExSQL( string  strSql,  params  DbParameter[] parameters)
        {
             if  ( ! OpenConnection())  return ;
            DbTransaction trans  =  Connection.BeginTransaction();
             try
            {
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                cmd.Transaction  =  trans;
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
             catch
            {
                trans.Rollback();
                Connection.Close();
                 return ;
            }
             finally
            {
                Connection.Close();
            }
        }
         ///   <summary>
         ///   執行SQL查詢語句,返回記錄條數
         ///   </summary>
         ///   <param name="strSql"> Select語句(在select语句中,使用Count(*)函数) </param>
         ///   <returns> 返回查詢到之記錄條數 </returns>
         public   int  ReSelectNum( string  strSql,  params  DbParameter[] parameters)
        {
             int  effect  =   0 ;
             try
            {
                DbDataReader dr  =  ReSelectdr(strSql, parameters);
                 if  (dr.Read())
                {
                    effect  =  Convert.ToInt32(dr.GetValue( 0 ));
                }
                 return  effect;
            }
             catch
            {
                 return  effect;

            }
        }
         ///   <summary>
         ///  使用SqlDataAdapter返回指定范围的数据
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数名 </param>
         ///   <param name="start"> 起始行 </param>
         ///   <param name="maxRecord"> 记录数 </param>
         ///   <param name="tableName"> 表名 </param>
         ///   <returns></returns>
         public  DataSet ReSelectds( string  strSql, DbParameter[] parameters,  int  start,  int  maxRecord,  string  tableName)
        {
             try
            {
                DataSet ds  =   new  DataSet();
                OpenConnection();
                DbDataAdapter myDa  =   this .dbFactory.CreateDataAdapter();
                myDa.SelectCommand  =  BuilderQueryCommand(strSql, parameters);
                myDa.Fill(ds, start, maxRecord, tableName);
                 return  ds;
            }
             catch
            {
                Connection.Close();
                 return   null ;
            }
             finally
            {
                Connection.Close();
            }
        }
         ///   <summary>
         ///  返回执行操作成功的数目，不关闭连接
         ///   </summary>
         ///   <param name="strSql"> 执行的查询语句或存储过程 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         public   int  ReExNumNoClose( string  strSql,  params  DbParameter[] parameters)
        {
             int  effect  =   0 ;
             if  ( ! OpenConnection())  return   - 1 ;
            DbTransaction trans  =  Connection.BeginTransaction();
             try
            {
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                cmd.Transaction  =  trans;
                 // 根据是否为存储过程来执行不同的处理
                 if  (cmd.CommandType  ==  CommandType.StoredProcedure)
                {
                     object  result  =  cmd.ExecuteScalar();
                    effect  =  result  ==   null   ?   - 1  : Convert.ToInt16(result);
                }
                 else
                {
                    effect  =  cmd.ExecuteNonQuery();
                }
                trans.Commit();
                 return  effect;
            }
             catch
            {
                trans.Rollback();
                 return  effect;
            }
        }
         ///   <summary>
         ///  返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         public   int  ReExNumNoClose( string  strSql,  out   string  error,  params  DbParameter[] parameters)
        {
             int  effect  =   0 ;
            error  =   "" ;
             if  ( ! OpenConnection())  return   - 1 ;
            DbTransaction trans  =  Connection.BeginTransaction();
             try
            {
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                cmd.Transaction  =  trans;
                 if  (cmd.CommandType  ==  CommandType.StoredProcedure)
                {
                     object  result  =  cmd.ExecuteScalar();
                    effect  =  result  ==   null   ?   - 1  : Convert.ToInt16(result);
                }
                 else
                {
                    effect  =  cmd.ExecuteNonQuery();
                }
                effect  =  cmd.ExecuteNonQuery();
                trans.Commit();
                 return  effect;
            }
             catch  (Exception ex)
            {
                trans.Rollback();
                error  =  ex.Message;
                 return  effect;
            }
        }

         ///   <summary>
         ///  返回执行操作成功的数目，并返回发生的错误信息
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         public   int  ReExNum( string  strSql,  out   string  error,  params  DbParameter[] parameters)
        {
             int  effect  =   0 ;
            error  =   "" ;
             if  ( ! OpenConnection())  return   - 1 ;
            DbTransaction trans  =  Connection.BeginTransaction();
             try
            {
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                cmd.Transaction  =  trans;
                 if  (cmd.CommandType  ==  CommandType.StoredProcedure)
                {
                     object  result  =  cmd.ExecuteScalar();
                    effect  =  result  ==   null   ?   - 1  : Convert.ToInt16(result);
                }
                 else
                {
                    effect  =  cmd.ExecuteNonQuery();
                }
                trans.Commit();
                 return  effect;
            }
             catch  (Exception ex)
            {
                trans.Rollback();
                error  =  ex.Message;
                 return  effect;
            }
             finally
            {
                Connection.Close();
            }
        }

         ///   <summary>
         ///  返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。
         ///   </summary>
         ///   <param name="strSql"> 存储过程名 </param>
         ///   <param name="parameters"> 参数组 </param>
         ///   <returns></returns>
         public   string  ReExStr( string  strSql,  params  DbParameter[] parameters)
        {
             string  error  =   string .Empty;
             int  effect  =   0 ;
             if  ( ! OpenConnection())  return   null ;
            DbTransaction trans  =  Connection.BeginTransaction();
             try
            {
                DbCommand cmd  =  BuilderQueryCommand(strSql, parameters);
                cmd.Transaction  =  trans;
                effect  =  cmd.ExecuteNonQuery();
                trans.Commit();
                 if  (effect  ==   0 )
                {
                    error  =   " 操作成功记录数为0，请检查意外的错误。 "   +   "  sql语句： "   +  strSql;
                }
                 else
                {
                    error  =   "" ;
                }
            }
             catch  (Exception ex)
            {
                trans.Rollback();
                error  =   " sql语句： "   +  strSql  +   "  错误信息： "   +  ex.Message;
            }
             finally
            {
                Connection.Close();
            }
             return  error;
        }
         ///   <summary>
         ///  如果数据库连接已关闭,则打开
         ///   </summary>
         ///   <returns></returns>
         public   bool  OpenConnection()
        {
             if  (Connection.State  ==  ConnectionState.Closed)
            {
                 try
                {
                    Connection.Open();
                }
                 catch
                {
                     return   false ;
                }
            }
             return   true ;
        }
         public   void  CloseConnection()
        {
            Connection.Close();
        }
    }
}