数据库类(Access专用) 未测试

using  System;
using  System.Data;
using  System.Configuration;
using  System.Data.OleDb;


///   <summary>
///  通用数据库类(Access专用)未测试
///   <author> ZhengFei </author>
///   <version> 2.0版 </version>
///   <realseDate> 2007-5-10 </realseDate>
///   </summary>
namespace  AccessDataBase
{
     public   class  AccDataBase
    {
         string  _ConnStr  =   "" ;
         ///   <summary>
         ///  不带参数的构造函数（默认取web.config中的连接字符串）
         ///   </summary>
         public  AccDataBase()
        {
            _ConnStr  =  ConfigurationManager.ConnectionStrings[ " AccConn " ].ToString();
        }
         ///   <summary>
         ///  构造函数（重载，带连接字符串）
         ///   </summary>
         ///   <param name="connstr"> 数据库连接字符串 </param>
         public  AccDataBase( string  connstr)
        {
             try
            {
                 this ._ConnStr  =  connstr;
            }
             catch  (Exception ep)
            {
                 throw  ep;
            }
        }
         ///   <summary>
         ///  数据库链接字符串
         ///   </summary>
         public   string  ConnStr
        {
             get
            {
                 return  _ConnStr;
            }
             set
            {
                _ConnStr  =  value;
            }
        }
         ///   <summary>
         ///  获得一个已连接数据库的OleDbConnection对象
         ///   </summary>
         ///   <returns> 返回一个已连接数据库的OleDbConnection对象 </returns>
         public  OleDbConnection GetConn()
        {
            OleDbConnection Conn  =   new  OleDbConnection(_ConnStr);
             try
            {
                Conn.Open();
            }
             catch
            {
                 throw ;
            }
             return  Conn;
        }
         ///   <summary>
         ///  释放资源，并回收垃圾
         ///   </summary>
         ///   <returns></returns>
         public   void  Dispose(OleDbConnection Conn)
        {
             if  (Conn  !=   null )
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }
         ///   <summary>
         ///  生成Command对象
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         ///   <param name="Conn"> OledbConnection对象 </param>
         ///   <returns></returns>
         public  OleDbCommand CreateCommand( string  SQL, OleDbConnection Conn)
        {
             if  (Conn.State  !=  ConnectionState.Open)
            {
                 try
                {
                    Conn.Open();

                }
                 catch
                {
                     throw ;
                }
            }
            OleDbCommand Cmd  =   new  OleDbCommand(SQL, Conn);
             return  Cmd;
        }

         ///   <summary>
         ///  返回adapter对象
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         ///   <param name="Conn"> SqlConnection连接对象 </param>
         ///   <returns> 返回adapter对象 </returns>
         public  OleDbDataAdapter CreateDataAdapter( string  SQL, OleDbConnection Conn)
        {
             if  (Conn.State  !=  ConnectionState.Open)
            {
                 try
                {
                    Conn.Open();
                }
                 catch
                {
                     throw ;
                }
            }
            OleDbDataAdapter Da  =   new  OleDbDataAdapter(SQL, Conn);
             return  Da;
        }
         public  OleDbDataAdapter CreateDataAdapter(OleDbCommand cmd)
        {
            OleDbDataAdapter Da  =   new  OleDbDataAdapter(cmd);
             return  Da;
        }
         ///   <summary>
         ///  验证是否有数据
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         ///   <returns></returns>
         public   bool  ExistDate( string  SQL)
        {
            OleDbConnection Conn  =   this .GetConn();
            OleDbDataReader dr;
            dr  =   this .CreateCommand(SQL, Conn).ExecuteReader();
             if  (dr.Read())
            {
                Dispose(Conn);
                 return   true ;
            }
             else
            {
                Dispose(Conn);
                 return   false ;
            }
        }
         ///   <summary>
         ///  返回一个带参数的OledbCommand对象
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         ///   <param name="Conn"> OLEDBConnection对象 </param>
         ///   <param name="sp"> OleDbParameter数组 </param>
         ///   <returns></returns>
         public  OleDbCommand CreateCommand( string  SQL, OleDbConnection Conn, OleDbParameter[] sp)
        {
             try
            {
                 if  (Conn.State  !=  ConnectionState.Open)
                {
                    Conn.Open();
                }
            }
             catch
            {
                 throw ;
            }
            OleDbCommand Cmd  =   new  OleDbCommand(SQL, Conn);
             if  (sp  !=   null )
            {
                Cmd.Parameters.Clear();
                 foreach  (OleDbParameter s  in  sp)
                    Cmd.Parameters.Add(s);
            }
             return  Cmd;
        }
         ///   <summary>
         ///  执行无参数的OLEDBCommand对象（无返回）
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         public   void  Execute( string  SQL)
        {
            OleDbConnection conn  =   new  OleDbConnection(ConnStr);
            conn.Open();
             try
            {
                CreateCommand(SQL, conn).ExecuteNonQuery();
            }
             catch
            {
                 throw ;
            }
             finally
            {
                Dispose(conn);
            }
             return ;
        }
         ///   <summary>
         ///  执行有参数的OLEDBCommand对象（无返回）
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         ///   <param name="SP"> OleDbParameter参数数组 </param>
         public   void  Execute( string  SQL, OleDbParameter[] SP)
        {
            OleDbConnection conn  =   new  OleDbConnection(ConnStr);
            conn.Open();
             try
            {
                CreateCommand(SQL, conn, SP).ExecuteNonQuery();
            }
             catch
            {
                 throw ;
            }
             finally
            {
                Dispose(conn);
            }
             return ;
        }
         ///   <summary>
         ///  执行SQL语句返回OledbDataReader（记得关闭）
         ///   </summary>
         ///   <param name="SQL"></param>
         ///   <returns></returns>
         public  OleDbDataReader ExecuteReader( string  SQL)
        {
            OleDbDataReader dr;
            OleDbConnection conn  =   new  OleDbConnection(ConnStr);
            conn.Open();
             try
            {
                dr  =  CreateCommand(SQL, conn).ExecuteReader();
            }
             catch
            {
                 throw ;
            }
             return  dr;
        }
         ///   <summary>
         ///  执行SQL语句返回OledbDataReader（记得关闭）
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         ///   <param name="SP"> OleDbParameter数组 </param>
         ///   <returns></returns>
         public  OleDbDataReader ExecuteReader( string  SQL, OleDbParameter[] SP)
        {
            OleDbDataReader dr;
            OleDbConnection conn  =   new  OleDbConnection(ConnStr);
            conn.Open();
             try
            {
                dr  =  CreateCommand(SQL, conn, SP).ExecuteReader();
            }
             catch
            {
                 throw ;
            }
             return  dr;
        }
         ///   <summary>
         ///  执行SQL语句返回DataSet
         ///   </summary>
         ///   <param name="SQL"></param>
         ///   <returns></returns>
         public  DataSet ExecuteDataSet( string  SQL,  string  TableName)
        {
            DataSet ds  =   new  DataSet();
            OleDbConnection conn  =   new  OleDbConnection(ConnStr);
            conn.Open();
             try
            {
                CreateDataAdapter(SQL, conn).Fill(ds, TableName);
            }
             catch
            {
                 throw ;
            }
             finally
            {
                Dispose(conn);
            }

             return  ds;
        }
         ///   <summary>
         ///  执行SQL语句返回DataSet
         ///   </summary>
         ///   <param name="SQL"> SQL语句 </param>
         ///   <param name="TableName"> 表名 </param>
         ///   <param name="sp"> 参数数组 </param>
         ///   <returns></returns>
         public  DataSet ExecuteDataSet( string  SQL,  string  TableName, OleDbParameter[] sp)
        {
            DataSet ds  =   new  DataSet();
            OleDbConnection conn  =   new  OleDbConnection(ConnStr);
            conn.Open();
             try
            {
                CreateDataAdapter(CreateCommand(SQL, conn, sp)).Fill(ds, TableName);
            }
             catch
            {
                 throw ;
            }
             finally
            {
                Dispose(conn);
            }
             return  ds;
        }
         ///   <summary>
         ///  创建输入参数
         ///   </summary>
         ///   <param name="ParameterName"> 参数名称 </param>
         ///   <param name="DbType"> 参数类型 </param>
         ///   <param name="Size"> 参数大小 </param>
         ///   <param name="Value"> 参数值 </param>
         ///   <returns> 新的参数 </returns>
         public  OleDbParameter MakeInputParameter( string  ParameterName, OleDbType DbType,  int  Size,  object  Value)
        {
             return  MakeParameter(ParameterName, DbType, Size, ParameterDirection.Input, Value);
        }

         ///   <summary>
         ///  创建输出参数
         ///   </summary>
         ///   <param name="ParameterName"> 参数名称 </param>
         ///   <param name="DbType"> 参数类型 </param>
         ///   <param name="Size"> 参数大小 </param>
         ///   <returns> 新的参数 </returns>
         public  OleDbParameter MakeOutParameter( string  ParameterName, OleDbType DbType,  int  Size)
        {
             return  MakeParameter(ParameterName, DbType, Size, ParameterDirection.Output,  null );
        }

         ///   <summary>
         ///  创建储存过程参数
         ///   </summary>
         ///   <param name="ParameterName"> 参数名称 </param>
         ///   <param name="DbType"> 参数类型 </param>
         ///   <param name="Size"> 参数大小 </param>
         ///   <param name="Direction"> 参数方法 </param>
         ///   <param name="Value"> 参数值 </param>
         ///   <returns> 新的参数 </returns>
         public  OleDbParameter MakeParameter( string  ParameterName, OleDbType DbType, Int32 Size, ParameterDirection Direction,  object  Value)
        {
            OleDbParameter AccParam;
             if  (Size  >   0 )
            {
                AccParam  =   new  OleDbParameter(ParameterName, DbType, Size);
            }
             else
            {
                AccParam  =   new  OleDbParameter(ParameterName, DbType);
            }

            AccParam.Direction  =  Direction;
             if  ( ! (Direction  ==  ParameterDirection.Output  &&  Value  ==   null ))
            {
                AccParam.Value  =  Value;
            }
             return  AccParam;
        }
         ///   <summary>
         ///  查询指定数量数据，返回DataSet（用于分页）
         ///   </summary>
         ///   <param name="TableName"> 表名 </param>
         ///   <param name="PK"> 主键 </param>
         ///   <param name="Fields"> 要查询的字段 </param>
         ///   <param name="Sort"> 排序字段 </param>
         ///   <param name="Filter"> 查询条件 </param>
         ///   <param name="PageSize"> 每页大小 </param>
         ///   <param name="CurrentPage"> 当前页码 </param>
         ///   <returns></returns>
         public  DataSet GetDataSet( string  TableName,  string  PK,  string  Fields,  string  Sort,  string  Filter,  int  PageSize,  int  CurrentPage)
        {
            DataSet ds  =   new  DataSet();
            OleDbConnection conn = new  OleDbConnection(ConnStr);
            conn.Open();
             string  sql  =   "" , and  =   "" ;
             int  num  =   0 ;
             if  (Fields  ==   "" ) Fields  =   " * " ;
             if  (Sort  ==   "" )
            {
                Sort  =   string .Format( " order by {0} asc " , PK);
            }
             else
            {
                Sort  =   string .Format( " order by {0} " , Sort);
            }
             if  (PageSize  <=   0 ) PageSize  =   10 ;
             if  (CurrentPage  <   1 ) CurrentPage  =   1 ;
            num  =  (CurrentPage  -   1 )  *  PageSize;
             if  (Filter  !=   "" )
            {
                Filter  =   " where ( "   +  Filter  +   " ) " ;
                and  =   " and " ;
            }
             else
            {
                and  =   " where " ;
            }
             if  (CurrentPage  ==   1 )
            {
                sql  =   string .Format( " select top {0} {1} from {2} {3} {4} " , PageSize, Fields, TableName, Filter, Sort);
            }
             else
            {
                sql  =   string .Format( " select top {0} {1} from {2} {3} {4} ({5} not in(select top {6} {7} from {8} {9} {10})) {11} " , PageSize, Fields, TableName, Filter, and, PK, num, PK, TableName, Filter, Sort, Sort);
            }
            ds  =  ExecuteDataSet(sql, TableName);
             return  ds;
        }
         ///   <summary>
         ///  查询指定数量数据，返回OleDbDataReader（用于分页）
         ///   </summary>
         ///   <param name="TableName"> 表名 </param>
         ///   <param name="PK"> 主键 </param>
         ///   <param name="Fields"> 要查询的字段 </param>
         ///   <param name="Sort"> 排序字段 </param>
         ///   <param name="Filter"> 查询条件 </param>
         ///   <param name="PageSize"> 每页大小 </param>
         ///   <param name="CurrentPage"> 当前页码 </param>
         ///   <returns></returns>
         public  OleDbDataReader GetDataReader( string  TableName,  string  PK,  string  Fields,  string  Sort,  string  Filter,  int  PageSize,  int  CurrentPage)
        {
            OleDbDataReader dr;
            OleDbConnection conn  =   new  OleDbConnection(ConnStr);
            conn.Open();
             string  sql  =   "" , and  =   "" ;
             int  num  =   0 ;
             if  (Fields  ==   "" ) Fields  =   " * " ;
             if  (Sort  ==   "" )
            {
                Sort  =   string .Format( " order by {0} asc " , PK);
            }
             else
            {
                Sort  =   string .Format( " order by {0} " , Sort);
            }
             if  (PageSize  <=   0 ) PageSize  =   10 ;
             if  (CurrentPage  <   1 ) CurrentPage  =   1 ;
            num  =  (CurrentPage  -   1 )  *  PageSize;
             if  (Filter  !=   "" )
            {
                Filter  =   " where ( "   +  Filter  +   " ) " ;
                and  =   " and " ;
            }
             else
            {
                and  =   " where " ;
            }
             if  (CurrentPage  ==   1 )
            {
                sql  =   string .Format( " select top {0} {1} from {2} {3} {4} " , PageSize, Fields, TableName, Filter, Sort);
            }
             else
            {
                sql  =   string .Format( " select top {0} {1} from {2} {3} {4} ({5} not in(select top {6} {7} from {8} {9} {10})) {11} " , PageSize, Fields, TableName, Filter, and, PK, num, PK, TableName, Filter, Sort, Sort);
            }
            dr  =  ExecuteReader(sql);
             return  dr;
        }
    }
}