C#中数据库的访问(工厂模式)(UserLib.Database)

转载 2006年06月15日 16:06:00
1. 首先我们先来定义数据库的类型
 
using System;
 
namespace UserLib.Database
{
     ///<summary>
     ///数据库类型
     ///</summary>
     public enum DatabaseType
     {
         OleDb,             //OleDb 数据库
         SqlServer,         //SqlServer 数据库
        Odbc,              //Odbc 数据库
     }
}
 
2. 接着我们定义一个数据库访问的接口,提供通用的访问方法
 
using System;
using System.Data;
using System.Data.Common;
 
namespace UserLib.Database
{
    ///<summary>
    ///数据库通用接口
    ///</summary>
    public interface IDatabase
    {
        ///<summary>
        ///打开数据库
        ///</summary>
        ///<param name="connString">连接字符串</param>
        void Open(string connString);
 
        ///<summary>
        ///关闭数据库
        ///</summary>
        void Close();
 
        ///<summary>
        ///数据集
        ///</summary>
        DataSet DataSetDB { get;}
 
        ///<summary>
        ///数据连接
        ///</summary>
        DbConnection ConnectionDB { get;}
 
        ///<summary>
        ///获取数据库类型
        ///</summary>
        ///<returns>数据库类型</returns>
        DatabaseType GetDatabaseType();
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<returns>影响行数</returns>
        int ExecuteSQL(string sql);
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="cmd">数据命令</param>
        ///<returns>影响行数</returns>
        int ExecuteSQL(DbCommand cmd);
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<returns>第一行第一列值</returns>
        object ExecuteSQLReturn(string sql);
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="cmd">数据命令</param>
        ///<returns>第一行第一列值</returns>
        object ExecuteSQLReturn(DbCommand cmd);
 
        ///<summary>
        ///返回DataReader对象
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<returns>DataReader对象</returns>
        DbDataReader ReturnDataReader(string sql);
 
        ///<summary>
        ///返回DataReader对象
        ///</summary>
        ///<param name="cmd">查询命令</param>
        ///<returns>DataReader对象</returns>
        DbDataReader ReturnDataReader(DbCommand cmd);
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<param name="tbName">表名称</param>
        ///<returns>数据集</returns>
        DataSet AddTable(string sql, string tbName);
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="cmdSelect">查询命令</param>
        ///<param name="tbName">表名称</param>
        ///<returns>数据集</returns>
        DataSet AddTable(DbCommand cmdSelect, string tbName);
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<param name="tbName">表名称</param>
        ///<param name="da">导出数据适配器</param>
        ///<returns>数据集</returns>
        DataSet AddTable(string sql, string tbName, out DbDataAdapter da);
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="cmdSelect">查询命令</param>
        ///<param name="tbName">表名称</param>
        ///<param name="da">导出数据适配器</param>
        ///<returns>数据集</returns>
        DataSet AddTable(DbCommand cmdSelect, string tbName, out DbDataAdapter da);
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="da">数据适配器</param>
        ///<param name="tbName">表名称</param>
        ///<returns>数据集</returns>
        DataSet AddTable(DbDataAdapter da, string tbName);
 
        ///<summary>
        ///移除表
        ///</summary>
        ///<param name="tbName">表名称</param>
        void RemoveTable(string tbName);
 
        ///<summary>
        ///移除表
        ///</summary>
        ///<param name="index">表的索引</param>
        void RemoveTable(int index);
 
        ///<summary>
        ///在数据集中加入关系
        ///</summary>
        ///<param name="name">关系名称</param>
        ///<param name="prnTable">主表名称</param>
        ///<param name="prnCol">主表中的列</param>
        ///<param name="chdTable">子表名称</param>
        ///<param name="chdCol">子表中的列</param>
        ///<returns>数据集</returns>
        DataSet AddRelation(string name, string prnTable, string prnCol, string chdTable, string chdCol);
 
        ///<summary>
        ///在数据集中加入关系
        ///</summary>
        ///<param name="name">关系名称</param>
        ///<param name="prnTable">主表名称</param>
        ///<param name="prnCol">主表中的列</param>
        ///<param name="chdTable">子表名称</param>
        ///<param name="chdCol">子表中的列</param>
        ///<param name="dr">导出关系</param>
        ///<returns>数据集</returns>
        DataSet AddRelation(string name, string prnTable, string prnCol, string chdTable, string chdCol, out DataRelation dr);
 
        ///<summary>
        ///在数据集中加入关系
        ///</summary>
        ///<param name="dr">关系</param>
        ///<returns>数据集</returns>
        DataSet AddRelation(DataRelation dr);
 
        ///<summary>
        ///移除关系
        ///</summary>
        ///<param name="relationName">关系名称</param>
        void RemoveRelation(string relationName);
 
        ///<summary>
        ///移除关系
        ///</summary>
        ///<param name="index">关系索引</param>
       void RemoveRelation(int index);
 
    }
}
 
3. 接着我们实现IDatabase接口, 提供一个具体的数据库访问类
   例: (Sql Server)
 
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
 
namespace UserLib.Database
{
    ///<summary>
    /// SqlServer数据库的连接与处理
    ///</summary>
    public class SqlServer : IDatabase
    {
        private SqlConnection connDB;             //Connection(连接) 对象
        private DataSet dsDB = new DataSet();     //DataSet(数据集) 对象
 
        ///<summary>
        ///构造函数
        ///</summary>
        public SqlServer() { }
 
        ///<summary>
        ///构造函数
        ///</summary>
        ///<param name="connString">连接字符串</param>
        public SqlServer(string connString)
        {
            Open(connString);
        }
 
        ///<summary>
        ///打开数据库
        ///</summary>
        ///<param name="connString">连接字符串</param>
        public void Open(string connString)
        {
            connDB = new SqlConnection(connString);
            connDB.Open();
        }
 
        ///<summary>
        ///关闭数据库
        ///</summary>
        public void Close()
        {
            connDB.Close();
        }
 
        ///<summary>
        ///数据库连接
        ///</summary>
        public DbConnection ConnectionDB
        {
            get
            {
                return connDB;
            }
        }
 
        ///<summary>
        ///数据集
        ///</summary>
        public DataSet DataSetDB
        {
            get
            {
                return dsDB;
            }
        }
 
        ///<summary>
        ///获取数据库类型
        ///</summary>
        ///<returns>数据库类型</returns>
        public DatabaseType GetDatabaseType()
        {
            return DatabaseType.SqlServer;
        }
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<returns>影响行数</returns>
        public int ExecuteSQL(string sql)
        {
            SqlCommand cmdDB = connDB.CreateCommand();
            cmdDB.CommandText = sql;
            int n = cmdDB.ExecuteNonQuery();
            return n;
        }
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="cmd">数据命令</param>
        ///<returns>影响行数</returns>
        public int ExecuteSQL(DbCommand cmd)
        {
            SqlCommand cmdDB = new SqlCommand();
            cmdDB = (SqlCommand)cmd;
            int n = cmdDB.ExecuteNonQuery();
            return n;
        }
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<returns>第一行第一列值</returns>
        public object ExecuteSQLReturn(string sql)
        {
            SqlCommand cmdDB = connDB.CreateCommand();
            cmdDB.CommandText = sql;
            object obj = cmdDB.ExecuteScalar();
            return obj;
        }
 
        ///<summary>
        ///执行SQL语句
        ///</summary>
        ///<param name="cmd">数据命令</param>
        ///<returns>第一行第一列值</returns>
        public object ExecuteSQLReturn(DbCommand cmd)
        {
            SqlCommand cmdDB = new SqlCommand();
            cmdDB = (SqlCommand)cmd;
            object obj = cmdDB.ExecuteScalar();
            return obj;
        }
 
        ///<summary>
        ///返回DataReader对象
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<returns>DataReader对象</returns>
        public DbDataReader ReturnDataReader(string sql)
        {
            SqlCommand cmdDB = connDB.CreateCommand();
            cmdDB.CommandText = sql;
            return cmdDB.ExecuteReader();
        }
 
        ///<summary>
        ///返回DataReader对象
        ///</summary>
        ///<param name="cmd">查询命令</param>
        ///<returns>DataReader对象</returns>
        public DbDataReader ReturnDataReader(DbCommand cmd)
        {
            SqlCommand cmdDB = new SqlCommand();
            cmdDB = (SqlCommand)cmd;
            return cmdDB.ExecuteReader();
        }
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<param name="tbName">表名称</param>
        ///<returns>数据集</returns>
        public DataSet AddTable(string sql, string tbName)
        {
            SqlDataAdapter daTable = new SqlDataAdapter(sql, connDB);
            //根据Select命令生成Insert Delete Update命令
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
            daTable.InsertCommand = cmdBuilder.GetInsertCommand();
            daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
            daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
            //把数据库中的键包含进来
            daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            daTable.Fill(dsDB, tbName);
            return dsDB;
        }
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="cmdSelect">查询命令</param>
        ///<param name="tbName">表名称</param>
        ///<returns>数据集</returns>
        public DataSet AddTable(DbCommand cmdSelect, string tbName)
        {
            SqlDataAdapter daTable = new SqlDataAdapter((SqlCommand)cmdSelect);
            //根据Select命令生成Insert Delete Update命令
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
            daTable.InsertCommand = cmdBuilder.GetInsertCommand();
            daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
            daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
            //把数据库中的键包含进来
            daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
           daTable.Fill(dsDB, tbName);
            return dsDB;
        }
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="sql">SQL语句</param>
        ///<param name="tbName">表名称</param>
        ///<param name="da">导出数据适配器</param>
        ///<returns>数据集</returns>
        public DataSet AddTable(string sql, string tbName, out DbDataAdapter da)
        {
            SqlDataAdapter daTable = new SqlDataAdapter(sql, connDB);
            //根据Select命令生成Insert Delete Update命令
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
            daTable.InsertCommand = cmdBuilder.GetInsertCommand();
            daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
            daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
            //把数据库中的键包含进来
            daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da = daTable;
            daTable.Fill(dsDB, tbName);
            return dsDB;
        }
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="cmdSelect">查询命令</param>
        ///<param name="tbName">表名称</param>
        ///<param name="da">导出数据适配器</param>
        ///<returns>数据集</returns>
        public DataSet AddTable(DbCommand cmdSelect, string tbName, out DbDataAdapter da)
        {
            SqlDataAdapter daTable = new SqlDataAdapter((SqlCommand)cmdSelect);
            //根据Select命令生成Insert Delete Update命令
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
            daTable.InsertCommand = cmdBuilder.GetInsertCommand();
            daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
            daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
            //把数据库中的键包含进来
            daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da = daTable;
            daTable.Fill(dsDB, tbName);
            return dsDB;
        }
 
        ///<summary>
        ///在数据集中加入有SQL语句生成的表
        ///</summary>
        ///<param name="da">数据适配器</param>
        ///<param name="tbName">表名称</param>
        ///<returns>数据集</returns>
        public DataSet AddTable(DbDataAdapter da, string tbName)
        {
            SqlDataAdapter daTable = new SqlDataAdapter();
            daTable = (SqlDataAdapter)da;
            //根据Select命令生成Insert Delete Update命令
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
            daTable.InsertCommand = cmdBuilder.GetInsertCommand();
            daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
            daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
            //把数据库中的键包含进来
            daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            daTable.Fill(dsDB, tbName);
            return dsDB;
        }
 
        ///<summary>
        ///移除表
        ///</summary>
        ///<param name="tbName">表名称</param>
        public void RemoveTable(string tbName)
        {
            dsDB.Tables.Remove(tbName);
        }
 
        ///<summary>
        ///移除表
        ///</summary>
        ///<param name="index">表的索引</param>
        public void RemoveTable(int index)
        {
            dsDB.Tables.RemoveAt(index);
        }
 
        ///<summary>
        ///在数据集中加入关系
        ///</summary>
        ///<param name="name">关系名称</param>
        ///<param name="prnTable">主表名称</param>
        ///<param name="prnCol">主表中的列</param>
        ///<param name="chdTable">子表名称</param>
        ///<param name="chdCol">子表中的列</param>
        ///<returns>数据集</returns>
        public DataSet AddRelation(string name, string prnTable,string prnCol,string chdTable,string chdCol)
        {
            dsDB.Relations.Add(name, dsDB.Tables[prnTable].Columns[prnCol], dsDB.Tables[chdTable].Columns[chdCol]);
            return dsDB;
        }
 
        ///<summary>
        ///在数据集中加入关系
        ///</summary>
        ///<param name="name">关系名称</param>
        ///<param name="prnTable">主表名称</param>
        ///<param name="prnCol">主表中的列</param>
        ///<param name="chdTable">子表名称</param>
        ///<param name="chdCol">子表中的列</param>
        ///<param name="dr">导出关系</param>
        ///<returns>数据集</returns>
        public DataSet AddRelation(string name, string prnTable,string prnCol,string chdTable,string chdCol,out DataRelation dr)
        {
            DataRelation drTables = new DataRelation(name, dsDB.Tables[prnTable].Columns[prnCol], dsDB.Tables[chdTable].Columns[chdCol]);
            dr = drTables;
            dsDB.Relations.Add(drTables);
            return dsDB;
        }
 
        ///<summary>
        ///在数据集中加入关系
        ///</summary>
        ///<param name="dr">关系</param>
        ///<returns>数据集</returns>
        public DataSet AddRelation(DataRelation dr)
        {
            dsDB.Relations.Add(dr);
            return dsDB;
        }
 
        ///<summary>
        ///移除关系
        ///</summary>
        ///<param name="relationName">关系名称</param>
        public void RemoveRelation(string relationName)
        {
            dsDB.Relations.Remove(relationName);
        }
 
        ///<summary>
        ///移除关系
        ///</summary>
        ///<param name="index">关系索引</param>
        public void RemoveRelation(int index)
        {
            dsDB.Relations.RemoveAt(index);
        }
    }
}
 
4. 最后定义数据库工厂, 实现通用的数据库访问
 
using System;
using System.Data;
 
namespace UserLib.Database
{
     ///<summary>
     ///数据库工厂
     ///</summary>
     public class DBFactory
     {
        ///<summary>
        ///私有构造函数
        ///</summary>
        private DBFactory() { }
 
        ///<summary>
        ///将字符串转成DatabaseType枚举类型
        ///</summary>
        ///<param name="src">字符串</param>
        ///<returns>DatabaseType枚举类型</returns>
        private static DatabaseType ToDatabaseType(string src)
        {
            switch (src.ToLower())
            {
                case "oledb":
                    return DatabaseType.OleDb;
                case "sqlserver":
                    return DatabaseType.SqlServer;
                case "odbc":
                    return DatabaseType.Odbc;
                default:
                    throw new Exception("类型不存在");
            }
        }
 
         ///<summary>
         ///创建数据库类实例
         ///</summary>
         ///<param name="dbType">数据库类型</param>
         ///<returns>数据库类实例</returns>
         public static IDatabase Create(DatabaseType dbType)
         {
              switch(dbType)
              {
                   case DatabaseType.OleDb:
                       return new OleDb();
                   case DatabaseType.SqlServer:
                       return new SqlServer();
                case DatabaseType.Odbc:
                    return new Odbc();
                   default:
                       return null;
              }
         }
 
        ///<summary>
        ///创建数据库类实例
        ///</summary>
        ///<param name="dbType">数据库类型</param>
        ///<returns>数据库类实例</returns>
        public static IDatabase Create(string dbType)
        {
            return Create(ToDatabaseType(dbType));
        }
 
         ///<summary>
         ///创建数据库类实例
         ///</summary>
         ///<param name="dbType">>数据库类型</param>
         ///<param name="connString">数据库连接字符串</param>
         ///<returns>数据库类实例</returns>
         public static IDatabase Create(DatabaseType dbType,string connString)
         {
              switch(dbType)
              {
                   case DatabaseType.OleDb:
                       return new OleDb(connString);
                   case DatabaseType.SqlServer:
                       return new SqlServer(connString);
                case DatabaseType.Odbc:
                    return new Odbc(connString);
                   default:
                       return null;
              }
         }
 
        ///<summary>
        ///创建数据库类实例
        ///</summary>
        ///<param name="dbType">>数据库类型</param>
        ///<param name="connString">数据库连接字符串</param>
        ///<returns>数据库类实例</returns>
        public static IDatabase Create(string dbType, string connString)
        {
            return Create(ToDatabaseType(dbType), connString);
        }
 
     }
}
 
5. 用法
 
using System;
using UserLib.Database;
 
namespace Test
{
    ...
    string ConnStr="...";
    IDatabase db = DBFactory.Create(DatabaseType.SqlServer, ConnStr);
    ...
}
 
6. 备注
 
以上程序在.Net Framework 1.1 环境下编写
.Net 2.0 已经提供了访问数据库的工厂模式

抽象工厂模式:实现ASP.NET访问不同数据库

在ASP.NET网站开发中可能会遇到要更换数据库,如将Aceess换为SQL Server ,由于Aceess引用System.Data.OleDb命名空间,SQL Server引用Syste...
  • judyge
  • judyge
  • 2015年05月07日 08:43
  • 316

简单工厂模式构建数据访问层以实现多种数据库之间的切换

1、新建一个 数据库访问基类DBHandler ///   /// 数据库访问基类  ///   public abstract class DBHandler  {      public...

C#初级抽象工厂模式用法示例

  • 2014年05月28日 16:47
  • 5KB
  • 下载

c#3层开发工厂模式

  • 2008年09月07日 21:14
  • 2KB
  • 下载

简单工厂模式构建数据访问层以实现多种数据库之间的切换

1、新建一个 数据库访问基类DBHandler [csharp] view plain copy print? ///   /// 数据库访问基类  ///   ...
  • lmy_1
  • lmy_1
  • 2016年12月16日 07:45
  • 739

C#抽象工厂模式Demo

  • 2016年06月29日 22:08
  • 15.55MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:C#中数据库的访问(工厂模式)(UserLib.Database)
举报原因:
原因补充:

(最多只允许输入30个字)