有感于Kim的数据库类，偶也来共享一个数据库工厂。

以前作项目时用过的，感觉对数据库操作挺方便，希望能对大家有用^_^

怎么用代码的方式发布不了呢？已经试了八次了。。。还是不行，难道是偶的人品不好？^_^

看来大家只能看文本格式的代码了，真不好意思。

 

 

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace NCMS.DataAccess
{
 /// <summary>
 /// DBAccess 的摘要说明。
 /// </summary>

 public class DBAccess:IDBAccess
 {

  #region DBAccess的成员变量
  private DBType _DBType=new DBType();
  private string _conString;
  private DBFactory _DBFactory=DBFactory.getDBFactory();
  #endregion

  public DBAccess()
  {
   //从web.config文件中读取数据库类型字符串
            string dbtype=ConfigurationSettings.AppSettings["DatabaseType"];
   if (dbtype=="SqlServer")
   {
    _DBType=DBType.SqlServer;
    SetConnectionString();
   }
  }
       
  /// <summary>
  /// 无条件查询记录
  /// </summary>
  /// <param name="tablename">查询的表名</param>
  /// <param name="columns">查询的属性</param>
  /// <returns></returns>
  public DataSet Select(string tablename,string columns) {
   DataSet ds=new DataSet();
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionString();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
            try{
    //创建Command对象
    string selectstring="SELECT "+columns+" FROM "+tablename;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=selectstring;
    cmd.Connection=conn;
    //创建Adapter对象
    IDbDataAdapter ad=dbfactory.createDataAdapter();
    ad.SelectCommand=cmd;
    //执行查询
    conn.Open();
    ad.Fill(ds);
            }catch(Exception err){
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
            }finally{
    if(conn!=null) conn.Close();
            }
   return ds;
  }
       

  /// <summary>
  /// 无条件查询记录
  /// </summary>
  /// <param name="tablename">查询的表名</param>
  /// <param name="columns">查询的属性</param>
  /// <returns></returns>
  public DataSet SelectMain(string tablename,string columns)
  {
   DataSet ds=new DataSet();
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionStringMain();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string selectstring="SELECT "+columns+" FROM "+tablename;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=selectstring;
    cmd.Connection=conn;
    //创建Adapter对象
    IDbDataAdapter ad=dbfactory.createDataAdapter();
    ad.SelectCommand=cmd;
    //执行查询
    conn.Open();
    ad.Fill(ds);
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
   return ds;
  }

  /// <summary>
  /// 条件查询
  /// </summary>
  /// <param name="tablename">查询的表名</param>
  /// <param name="columns">查询的属性</param>
  /// <param name="conditions">约束条件</param>
  /// <returns></returns>
  public DataSet Select(string tablename,string columns,string conditions)
  {
   DataSet ds=new DataSet();
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionString();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string selectstring="SELECT "+columns+" FROM "+tablename +" Where "+conditions;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=selectstring;
    cmd.Connection=conn;
    //创建Adapter对象
    IDbDataAdapter ad=dbfactory.createDataAdapter();
    ad.SelectCommand=cmd;
    //执行查询
    conn.Open();
    ad.Fill(ds);
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
   return ds;
  }
       

  /// <summary>
  /// 条件查询
  /// </summary>
  /// <param name="tablename">查询的表名</param>
  /// <param name="columns">查询的属性</param>
  /// <param name="conditions">约束条件</param>
  /// <returns></returns>
  public DataSet SelectMain(string tablename,string columns,string conditions)
  {
   DataSet ds=new DataSet();
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionStringMain();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string selectstring="SELECT "+columns+" FROM "+tablename +" Where "+conditions;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=selectstring;
    cmd.Connection=conn;
    //创建Adapter对象
    IDbDataAdapter ad=dbfactory.createDataAdapter();
    ad.SelectCommand=cmd;
    //执行查询
    conn.Open();
    ad.Fill(ds);
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
   return ds;
  }

 

  /// <summary>
  /// 插入一条完整的记录
  /// </summary>
  /// <param name="tablename">插入记录所在表的表名</param>
  /// <param name="values">所有属性值</param>
  public void Insert(string tablename,string values){
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionString();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string insertstring="INSERT INTO "+tablename +" VALUES ("+values+")";
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=insertstring;
    cmd.Connection=conn;
    //执行插入
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

  /// <summary>
  /// 插入一条完整的记录
  /// </summary>
  /// <param name="tablename">插入记录所在表的表名</param>
  /// <param name="values">所有属性值</param>
  public void InsertMain(string tablename,string values)
  {
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionStringMain();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string insertstring="INSERT INTO "+tablename +" VALUES ("+values+")";
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=insertstring;
    cmd.Connection=conn;
    //执行插入
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

 

        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <param name="tablename">要插入记录所在表的表名</param>
        /// <param name="columns">属性集</param>
        /// <param name="values">属性值</param>
  public void Insert(string tablename,string columns,string values){
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionString();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string insertstring="INSERT INTO "+tablename +" ("+columns+") VALUES ("+values+")";
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=insertstring;
    cmd.Connection=conn;
    //执行插入
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

  
  /// <summary>
  /// 插入一条记录
  /// </summary>
  /// <param name="tablename">要插入记录所在表的表名</param>
  /// <param name="columns">属性集</param>
  /// <param name="values">属性值</param>
  public void InsertMain(string tablename,string columns,string values)
  {
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionStringMain();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string insertstring="INSERT INTO "+tablename +" ("+columns+") VALUES ("+values+")";
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=insertstring;
    cmd.Connection=conn;
    //执行插入
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

 

  /// <summary>
  /// 更新本车间数据库记录
  /// </summary>
  /// <param name="tablename">要更新记录所在表的表名</param>
  /// <param name="values">属性值集</param>
  /// <param name="conditions">约束条件</param>
  public void Update(string tablename,string values,string conditions)
  {
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionString();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string updatestring="UPDATE "+tablename+" SET "+values+" WHERE "+conditions;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=updatestring;
    cmd.Connection=conn;
    //执行更新
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

  /// <summary>
  /// 更新主服务器数据库记录
  /// </summary>
  /// <param name="tablename">要更新记录所在表的表名</param>
  /// <param name="values">属性值集</param>
  /// <param name="conditions">约束条件</param>
  public void UpdateMain(string tablename,string values,string conditions)
  {
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionStringMain();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string updatestring="UPDATE "+tablename+" SET "+values+" WHERE "+conditions;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=updatestring;
    cmd.Connection=conn;
    //执行更新
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

  /// <summary>
  /// 条件删除
  /// </summary>
  /// <param name="tablename">要删除记录所在表的表名</param>
  /// <param name="conditions">约束条件</param>
  public void Delete(string tablename,string conditions){
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionString();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string deletestring="DELETE FROM "+tablename+" WHERE "+conditions;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=deletestring;
    cmd.Connection=conn;
    //执行删除
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

 

  /// <summary>
  /// 条件删除
  /// </summary>
  /// <param name="tablename">要删除记录所在表的表名</param>
  /// <param name="conditions">约束条件</param>
  public void DeleteMain(string tablename,string conditions)
  {
   //创建数据库工厂
   IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
   //创建数据库连接对象
   SetConnectionStringMain();
   IDbConnection conn=dbfactory.createDBConnection(_conString);
   try
   {
    //创建Command对象
    string deletestring="DELETE FROM "+tablename+" WHERE "+conditions;
    IDbCommand cmd=dbfactory.createDBCommand();
    cmd.CommandText=deletestring;
    cmd.Connection=conn;
    //执行删除
    conn.Open();
    cmd.ExecuteNonQuery();
   }
   catch(Exception err)
   {
    DBException DBErr=new DBException(err.Message);
    DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
    throw(DBErr);
   }
   finally
   {
    if(conn!=null) conn.Close();
   }
  }

  //根据DatabaseType设置本车间数据库连接字符串
  private void SetConnectionString(){
   switch(_DBType) {
    case DBType.SqlServer:
    {
     _conString=ConfigurationSettings.AppSettings["SqlConnectionString"];
     break;
    }
    case DBType.Oracle:
    {
     _conString=ConfigurationSettings.AppSettings["OracleConnectionString"];
     break;
    }
   default:break;
   }
  }

  
  //根据DatabaseType设置主服务器数据库连接字符串
  private void SetConnectionStringMain()
  {
   switch(_DBType)
   {
    case DBType.SqlServer:
    {
     _conString=ConfigurationSettings.AppSettings["SqlConnectionStringMain"];
     break;
    }
    case DBType.Oracle:
    {
     _conString=ConfigurationSettings.AppSettings["OracleConnectionStringMain"];
     break;
    }
    default:break;
   }
  }
  
 }
}

using System;

namespace NCMS.DataAccess
{
 /// <summary>
 /// DBException 的摘要说明。
 /// </summary>
 public class DBException:ApplicationException
 {
  public enum SourceErrorLayer:int{
   success=0,DataAccessLayer,BusinessLayer,WebServiceLayer,UnKnownLayer
  }

        #region DBException的属性
  /// <summary>
  /// 返回请求状态,true--请求成功,false--请求失败
  /// </summary>
  private bool _IsError;
  public bool IsError
  {
   get{
    return _IsError;
   }
   set{
    if(!_IsError)
     _IsError=value;
   }
  }
  /// <summary>
  /// 返回出错层
  /// </summary>
  private SourceErrorLayer _ErrorLayer;
  public SourceErrorLayer ErrorLayer
  {
   get{
    return ErrorLayer;
   }
   set{
    ErrorLayer=value;
    if(value.CompareTo(SourceErrorLayer.success)!=0)
    {
     _IsError=true;
     //_ErrorMsg="系统内部错误!";
    }
    else
    {
     _IsError=false;
     //_ErrorMsg="请求成功!";
    }
   }
  }
  /// <summary>
  /// 出错信息
  /// </summary>
  private string _ErrorMsg;
  public string ErrorMsg
  {
   get{
    return _ErrorMsg;
   }
   set{
    _ErrorMsg=value;
   }
  }
  /// <summary>
  /// 内部错误信息
  /// </summary>
  public String InnerErrorMsg
  {
   get{
    return base.Message;
   }
  }
        #endregion
  public DBException()
  {
   
   _ErrorLayer=SourceErrorLayer.success;
   _IsError=false;
   //ErrorMessage="请求成功!";
  }
  public DBException(string message)
  {
   
   _ErrorMsg=message;
   _IsError=true;
   
  }
 }
}

using System;

namespace NCMS.DataAccess
{
 /// <summary>
 /// DBFactory 的摘要说明。
 /// </summary>
 public class DBFactory
 {
  protected DBFactory(){}
  private static DBFactory _DBfactory=new DBFactory();
        public static DBFactory getDBFactory(){
   return _DBfactory;
        }
  //工厂方法
  public IAbstractDBFactory CreateDBFactory(DBType aType)
  {

   IAbstractDBFactory _AbstractDBFactory=null;
  
   switch(aType)
   {
    case DBType.SqlServer:
    {
     _AbstractDBFactory=new SqlDBFactory();
     break;
    }
    case DBType.Oracle:
    {
     _AbstractDBFactory = new OracleDBFactory();
     break;
    }
    
    default: break;
   }
   return _AbstractDBFactory;
  }
 }
}

 

using System;

namespace NCMS.DataAccess
{
 /// <summary>
 /// DBType 的摘要说明。
 /// </summary>
 public enum DBType{
  SqlServer=1,
     Oracle
 }
}

 

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

namespace NCMS.DataAccess
{
 /// <summary>
 /// IAbstractDBFactory 的摘要说明。
 /// </summary>
 public interface IAbstractDBFactory
 {
  IDbConnection createDBConnection(string connectionstring);
  IDbCommand createDBCommand();
  //createDBCommand的重载 Add By Lucas.F
  IDbCommand createDBCommand(string strcomm,IDbConnection connection);
  IDbDataAdapter createDataAdapter();
  IDbDataParameter createDataParameter();
  IDbDataParameter createDataParameter(string parametername,object parametervalue);
  
  IDataReader createDataReader(IDbCommand aDBCommand);
 }
}

using System;
using System.Data;

namespace NCMS.DataAccess
{
 /// <summary>
 /// IDBAccess 的摘要说明。
 /// </summary>
 public interface IDBAccess
 {
  DataSet Select(string tablename,string columns);
  DataSet Select(string tablename,string columns,string conditions);
  void Insert(string tablename,string values);
  void Insert(string tablename,string columns,string values);
  void Update(string tablename,string values,string conditions);
  void Delete(string tablename,string conditions);
 }
}

 

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

namespace NCMS.DataAccess
{
 /// <summary>
 /// OracleDBFactory 的摘要说明。
 /// 代替换OleDb->Oracle
 /// </summary>
 public class OracleDBFactory:IAbstractDBFactory
 {
  public OracleDBFactory()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
#region 覆写IAbstractDBFactory的成员

  public IDbConnection createDBConnection(string connectionstring)
  {
   return new System.Data.OleDb.OleDbConnection(connectionstring);
  }

  public IDbCommand createDBCommand()
  {
   return new System.Data.OleDb.OleDbCommand();
  }
  //createDBCommand的重载 ADD By Lucas.F
        public IDbCommand createDBCommand(string strcomm,IDbConnection connection)
  {
   return new OleDbCommand(strcomm,(OleDbConnection)connection);
  }
  public IDbDataAdapter createDataAdapter()
  {
   return new System.Data.OleDb.OleDbDataAdapter();
  }
  public IDbDataParameter createDataParameter()
  {
   return new System.Data.OleDb.OleDbParameter();
  }

  public IDbDataParameter createDataParameter(string parametername,object parametervalue)
  {
   return new System.Data.OleDb.OleDbParameter(parametername,parametervalue);
  }

  public IDataReader createDataReader(IDbCommand aDBCommand)
  {
   return aDBCommand.ExecuteReader();
  }

 

  #endregion
 }
}

 

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

namespace NCMS.DataAccess
{
 /// <summary>
 /// SqlDBFactory 的摘要说明。
 /// </summary>
 public class SqlDBFactory:IAbstractDBFactory
 {
  public SqlDBFactory()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
#region 覆写IAbstractDBFactory的成员
  public IDbConnection createDBConnection(string connectionstring){
   return new SqlConnection(connectionstring);
  }
  public IDbCommand createDBCommand()
  {
   return new SqlCommand();
  }
  //createDBCommand的重载
  public IDbCommand createDBCommand(string strcomm,IDbConnection connection)
  {
   return new SqlCommand(strcomm,(SqlConnection)connection);
   
  }
  public IDbDataAdapter createDataAdapter()
  {
   return new SqlDataAdapter();
  }
  public IDbDataParameter createDataParameter()
  {
   return new SqlParameter();
  }

  public IDbDataParameter createDataParameter(string parametername,object parametervalue)
  {
   return new SqlParameter(parametername,parametervalue);
  }
  public IDataReader createDataReader(IDbCommand aDBCommand)
  {
   return aDBCommand.ExecuteReader();
  }

#endregion
 }
}