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 已经提供了访问数据库的工厂模式

相关文章推荐

LeetCode | Reverse Words in a String

题目: Given an input string, reverse the string word by word. For example, Given s = "the sky is bl...

Struts2 Action的访问路径

1.     Action的访问路径 扩展名缺省以.action结尾,请参考:default.properties文件,可以通过配置改变这一点:  上述配置,将使得可以通过.action或.do或.w...

LeetCode | Path Sum II

题目: Given a binary tree and a sum, find all root-to-leaf paths where each path's sum equals the gi...

LeetCode | Minimum Window Substring

题目: Given a string S and a string T, find the minimum window in S which will contain all the ch...

interview experience

interview experience

LeetCode | Substring with Concatenation of All Words

题目: You are given a string, S, and a list of words, L, that are all of the same length. Find all s...

欢迎使用CSDN-markdown编辑器

欢迎使用Markdown编辑器写博客本Markdown编辑器使用StackEdit修改而来,用它写博客,将会带来全新的体验哦: Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和...

LeetCode | Multiply Strings

题目: Given two numbers represented as strings, return multiplication of the numbers as a string. N...

LeetCode总结

最近完成了www.leetcode.com的online judge中151道算法题目。除各个题目有特殊巧妙的解法以外,大部分题目都是经典的算法或者数据结构,因此做了如下小结
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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