工厂模式连接数据库(二)

 如果是连接池用单例来实现。
=========================================================================
 

 

作者：陈刚，桂林人,97年毕业于广西师范大学数学系，暂于IBM中国研究中心兼职从事软件开发(2004.2-?)，专注基于java平台的软件开发。
email:   glchengang@hotmail.com
blog:  glchengang.yeah.net

 

      随着模式概念的普及，了解模式和使用模式的程序员越来越多，很多人在学习模式的时候，都会有这样一种疑惑：“有必要搞得这么复杂吗？”。的确，因为教程的例子过于简单化（这样方便读者学习），或者是作者选例子的时候并没有很好体现所讲模式的优点，很多情况下如果仅就其例子的问题来说，用模式是太复杂了。因此才导致这样的误解：“模式就是把简单的问题复杂化吗？”。当然不是，随着你开发实践的不断丰富，你终会发现模式强大威力，而且模式也并非贵族化的编程方式，它就是一些经过提炼了的解决问题的方法技巧。

 

      通过学习模式，程序员开始告别过去准直线式的代码方式，模式开扩了我们的视野，强化了我们面向对象编程的思维方式。然而现在又出现了另一个普遍的问题，盲目应用模式。模式是问题的解决方案，先有问题才有模式，模式是依附于所要解决的问题的而生的。必须了解模式在很多情况下是以提高代码的复杂度为代价来增强灵活性、可复用性。如果在自已的代码中使用某一模式仅只提高了代码的复杂度，而其它方面收效甚微，或者某部份代码根本就不存在灵活性及高复用性的需求，那么我们就没有必要为使用模式而放弃更直观简单的代码写法。

 

      一流的高手90%精力关注问题的解决方案，因为找到了好的解决方案，再写起代码会很轻松代码也简洁流畅，看这样的代码是一种享受和提高；二流的熟手90%精力关注代码实现，因为问题的解决方案并非最佳，实现的代码也会比较复杂；三流菜鸟记流水帐,90%精力在敲键盘，常常做了大半才发现行不通，回过头来再用90%的时间敲键盘，根本不会用到任何模式，写出来的代码的只有他自已才能看懂。做出来的软件也是支离破碎，做一丁点改动都要大费周折，而且你还不知道改动后会产生什么问题，大有住危房里的感觉。

 

      在这里还是举一个滥用模式的例子吧。我曾参与过一个大集团公司ＯＡ系统的第二期开发，开发沿用原有代码架构并增加新的功能模块。文档很少我读原代码时就被它程序里的代码转来转去搞得头大如斗，最后读懂了：原代码架构总体采用工厂模式，而且是最复杂的抽象工厂模式。它把所有模块类都通过工厂生成还工厂套工厂，并且每一个模块类都有一个接口，每个接口也只有一个模块现实类，因为涉及权限控制还用了代理(proxy)模式。　读懂代码后我开始嵌入代码，发现每新增一个类，都要到六个Java文件中去增加相应代码，而在类中每增加一个方法，也要到它的接口等四个Java文件中去增加相应代码。天呀！！！记得当时我的小姆指常会不听使唤，就是因为频繁的使用Ctrl C 、Ctrl V，小姆指按着Ctrl键给累的。整个项目组苦不堪言，真烦透了。项目结束后我回顾发现：代理模式用得还对（现在针对权限这类横向控制有AOP编程这种新的解决办法了）但工厂模式在这里根本就是画蛇添足，不仅没有解决什么问题，反而增加代码复杂度和耦合性，降低了开发效率连维护难度都提高了。而且那种每个类简单的加一个接口的方式，更是没有道理，这让我很想说周星驰说过的一句话：“球~~~不是这么踢~~~~的，接口~~~不是这么用~~~的”。言归正传，我们先来看这样一个常见问题：某系统需要支持多种类型的数据库。用过Oracle、MSSQL等数据库的人都知道，它们的SQL编写方式都各有些不同。比如说Oracle的唯一标识自动 1字段用的是序列，MSSQL改一下字段属性就成了，还有各种各自特有的SQL用法。为了支持多数据库，难道我们要开发多套系统？当然NO。请看下面的解决方案。

 

      即然数据库存在多种，我们可以将系统中所有对数据库的操作抽象出来，写成一个个方法组合到一个类中，有几种数据库我们就写几个这样的类。具体设计类图如下：

 

 

 

简要说明：

• OracleDataOperate、SqlserverDataOperate、MysqlDataOperate，分别代表Oracle、Sqlserver、Mysql这三种数据库的操作类。继承自AbstractDataOperate
• AbstractDataOperate是一个抽象类，包含了那些不同种类数据库都是一样代码的操作方法。继承自DataOperate
• DataOperate是上面说的数据操作类的统一接口，只有两个方法：取得一条记录、插入一条记录。
• DataOperateFactory是一个工厂方法，统一用它的方法来得到数据库操作类的实例。
• SampleClass是我们系统的某个功能模块的类。
• People是一个实体类，代表一条记录。三个字段　oid唯一标识符、name姓名、date生日。

 

详细说明：

１、所有系统功能模块类只认DataOperat这个接口还不必管具体的实现类是OracleDataOperate还SqlserverDataOperate。DataOperate源代码如下：

public interface DataOperate {

    //根据记录的唯一标识取出一条记录

    People getPeople(String oid);

    //插入一条记录

    boolean insertPeople(People people);

}

 

２、AbstractDataOperate、OracleDataOperate、SqlserverDataOperate、MysqlDataOperate都是继承DataOperate接口的，没什么好说的，省略。

 

３、DataOperateFactory。我们看看工厂方法怎么写的。

public class DataOperateFactory {

    public static final int ORACLE = 0; //定义三个表示数据库类型的常量

    public static final int MYSQL = 1;

    public static final int SQLSERVER = 2;

 

    private static DataOperate db;

    private static int dataType = MYSQL;

   

    public static DataOperate getInstance() {

        if (db == null) {

            if (dataType == ORACLE) //根据dateType返回相应的实现类

                return new OracelDataOperate();

            if (dataType == MYSQL)

                return new MysqlDataOperate();

            if (dataType == SQLSERVER)

                return new SqlserverDataOperate();

        }

        return db;

    }

}

 

４、接下来就看看使用端是如何调用工厂方法和使用数据操作类的。

 

public class SampleClass {

private DataOperate db; //声明一个数据库操作类，注意这里用的是接口噢

   

    public void sampleMethod() {

        db = DataOperateFactory.getInstance();//得到单一实例

        People p = db.getPeople("123"); //取得一条记录

        db.insertPeople(p);//再插回去

    }

}

 

　　我们发现SampleClass中根本没有出现OracelDataOperate、MysqlDataOperate等的影子，这就是接口的威力。客户端不必针对OracelDataOperate等写不同的代码，它只关心DataOperate即可，具体要取那个类的逻辑就由DataOperateFactory负责了。

 

总结：

• 从例子中我们可以看到什么是面向接口的编程方式。SampleClass使用数据操作类可以不必关心具体是那个类，只要是符合接口的都行
• 要实例？只须调用DataOperateFactory.getInstance()即可，其它的交于DataOperateFactory这个工厂来做吧，使用端什么都不用关心。
• 我们要支持新的数据库类型，只须要象OracelDataOperate那样，再写一个继承AbstractDataOperate的类即可，比如SysbaseDataOperate。然后到DataOperateFactory中加入相应代码即可。
• 如果我们想要可配置性更高，可以用private static int dataType = MYSQL;中的值设置到一个文本文件中。

　　对于开发支持多种数据库的系统，强烈建议使用hibernate，我现在做的系统就是用hibernate的，开发时用Mysql，到要给客户时将数据库换了DB2，程序不用做任何改动，真正的无逢移植。不过这样，本文所提到的方法就没什么用了.

 

作者Blog： http://blog.youkuaiyun.com/glchengang/
=====================================================================================
 简单工厂模式就是根据提供给它的数据,返回几个可能类中的一个类的实例.通常它返回的类都有一个共同的父类和共同的方法，但是每个方法执行的任务不同，而且根据不同的数据进行了优化。比如X是一个基类，xy和xz是从它派生出来的，XFactory类根据给出的参数决定返回那一个子类，返回那一个类的实例与程序员无关，因为这些类有同样的方法，只是实现不同，返回那个类的实例取决于工厂，工厂功能可能很复杂，但通常都是相当简间的.

 

    接下来我们用一个例子来说明这个简单的工厂模式.假如我们要输入一个人的姓名，

    有两个方式，一种是“firstname lastname” 和“fristname,lastname”形式.我们的任务就是要确定lastname和firstname是否是逗号来确定姓名的先后.

第一步，设计一个父类:

    public class CName
    {
        protected  string frName ,lName;
        public string getFristName() { return frName;}
        public string getLastName() { return lName;}
    }

第二步,设计两个子类,它是由类CName的派生的。它们现实了接口，并在构造function中将名字分成了两部分.在CFirstFirst类中，做了一个简单的假设：最后一个空格前面的所有部分都属与firstname.

    public class CFirstFirst : CName 
    {
        public CFirstFirst (string sName) 
        {
            int i = sName.Trim().Indexof(” “);
            if ( i >0 ) 
            { 
                frName  = name.Substring(0,i).Trim();
                lName = name.Substring(i 1).Trim();
            }
            else  
            {
                lName = sName;
                frName = “”;
            }
        }
    }

     在LastFist类中，用逗号给lastname划分界限.当空格或逗号不存在时，两个类都提供了错误校正处理.

    public class LastFrist  :CName  
    {
        public LastFrist(string sName) 
        {
            int i = sName.Indexof(”,”);
            if ( i>0 ) 
            {
                if ( i >0 ) 
                { 
                    lName = name.Substring(0,i).Trim();
                    frName = name.Substring(i 1).Trim();
                }
                else  
                {
                    lName = sName;
                    frName = “”;
                }
            }
        }
    }

两种情况下，都把分开的名字保存在基类的CName中保护变量中.所以在派生类中根本不需要任何getFirstName和getLastName方法，因为基类中已给出.

第三步: 构造简单工厂.

     现在很容易给出简单工厂类.只检查逗号来决定返回那个类的实例

    public class NameFactory ()
    { 
        public CName  getName(string sname)   
        {
            int i = sname.Indexof(”,”);
            if ( i >0 )
            {
                return new LastFrist  (sname);
            }
            else
            {
                return new CFirstFirst (sname);
            }
        }
    }

第四步。使用工厂类:

    string sName = “cedy hao”;
    NameFactory  nf = new NameFactory ();
    CName  name = nf.getName(sName);
    string sFristName = name.getFristName();
    string sLastName = name.getLastName();

    这种方法把和数据相关的问题与类的其它的方法分隔开来。采用抽象工厂模式设计出的系统类图如下：


数据库层抽象工厂解决方案归档：

(1)AbstractDbFactory.cs

using System;
using System.Data;

namespace DbService
{
    ///
    /// 数据库抽象工厂接口
    ///
    public interface AbstractDbFactory
    {
        ///
        /// 建立默认连接
        ///
        /// 数据库连接
        IDbConnection CreateConnection();

        ///
        /// 根据连接字符串建立Connection对象
        ///
        /// 连接字符串
        /// Connection对象
        IDbConnection CreateConnection(string strConn);

        ///
        /// 建立Command对象
        ///
        /// Command对象
        IDbCommand CreateCommand();

        ///
        /// 建立DataAdapter对象
        ///
        /// DataAdapter对象
        IDbDataAdapter CreateDataAdapter();

        ///
        /// 根据Connection建立Transaction
        ///
        /// Connection对象
        /// Transaction对象
        IDbTransaction CreateTransaction(IDbConnection myDbConnection);

        ///
        /// 根据Command建立DataReader
        ///
        /// Command对象
        /// DataReader对象
        IDataReader CreateDataReader(IDbCommand myDbCommand);

        ///
        /// 获得连接字符串
        ///
        /// 连接字符串
        string GetConnectionString();
    }
}

(2)Factory.cs

using System;
using System.Configuration;

namespace DbService
{
    ///
    /// Factory类
    ///
    public sealed class Factory
    {
        private static volatile Factory singleFactory = null;
        private static object syncObj = new object();
        
        ///
        /// Factory类构造函数
        ///
        private Factory()
        {
        }

        ///
        /// 获得Factory类的实例
        ///
        /// Factory类实例
        public static Factory GetInstance()
        {
            if(singleFactory == null)
            {
                lock(syncObj)
                {
                    if(singleFactory == null)
                    {
                        singleFactory = new Factory();
                    }
                }
            }
            return singleFactory;
        }

        ///
        /// 建立Factory类实例
        ///
        /// Factory类实例
        public AbstractDbFactory CreateInstance()
        {
            AbstractDbFactory abstractDbFactory = null;
            switch(ConfigurationSettings.AppSettings["DatabaseType"].ToLower())
            {
                case "sqlserver":
                {
                    abstractDbFactory = new SqlFactory();
                    break;
                }
                case "oledb":
                {
                    abstractDbFactory = new OleDbFactory();
                    break;
                }
                case "odbc":
                {
                    abstractDbFactory = new OdbcFactory();
                    break;
                }
            }   
            return abstractDbFactory;
        }
    }
}

     以下3个类分别是Factory针对SqlServer专用连接、OleDb连接和Odbc连接时的具体实现：

(3)SqlFactory.cs

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

namespace DbService
{
    ///
    /// 针对SqlServer专用连接的工厂
    ///
    public class SqlFactory : AbstractDbFactory
    {
        ///
        /// 构造函数
        ///
        public SqlFactory()
        {
        }

        ///
        /// 建立默认Connection对象
        ///
        /// Connection对象
        public IDbConnection CreateConnection()
        {
            return new SqlConnection();
        }

        ///
        /// 根据连接字符串建立Connection对象
        ///
        /// 连接字符串
        /// Connection对象
        public IDbConnection CreateConnection(string strConn)
        {
            return new SqlConnection(strConn);
        }

        ///
        /// 建立Command对象
        ///
        /// Command对象
        public IDbCommand CreateCommand()
        {
            return new SqlCommand();
        }

        ///
        /// 建立DataAdapter对象
        ///
        /// DataAdapter对象
        public IDbDataAdapter CreateDataAdapter()
        {
            return new SqlDataAdapter();
        }

        ///
        /// 根据Connection建立Transaction
        ///
        /// Connection对象
        /// Transaction对象
        public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
        {
            return myDbConnection.BeginTransaction();
        }

        ///
        /// 根据Command建立DataReader
        ///
        /// Command对象
        /// DataReader对象
        public IDataReader CreateDataReader(IDbCommand myDbCommand)
        {
            return myDbCommand.ExecuteReader();
        }

        ///
        /// 获得连接字符串
        ///
        /// 连接字符串
        public string GetConnectionString()
        {
            string strServer = ConfigurationSettings.AppSettings["SqlServerServer"];
            string strDatabase = ConfigurationSettings.AppSettings["SqlServerDatabase"];
            string strUid = ConfigurationSettings.AppSettings["SqlServerUid"];
            string strPwd = ConfigurationSettings.AppSettings["SqlServerPwd"];
            string strConnectionString = "Server = " strServer "; Database = " strDatabase "; Uid = " strUid "; Pwd = " strPwd ";";
            return strConnectionString;
        }
    }
}

(4)OleDbFactory.cs

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

namespace DbService
{
    ///
    /// 针对OleDb连接的工厂
    ///
    public class OleDbFactory : AbstractDbFactory
    {
        ///
        /// 构造函数
        ///
        public OleDbFactory()
        {
        }

        ///
        /// 建立默认Connection对象
        ///
        /// Connection对象
        public IDbConnection CreateConnection()
        {
            return new OleDbConnection();
        }

        ///
        /// 根据连接字符串建立Connection对象
        ///
        /// 连接字符串
        /// Connection对象
        public IDbConnection CreateConnection(string strConn)
        {
            return new OleDbConnection(strConn);
        }

        ///
        /// 建立Command对象
        ///
        /// Command对象
        public IDbCommand CreateCommand()
        {
            return new OleDbCommand();
        }

        ///
        /// 建立DataAdapter对象
        ///
        /// DataAdapter对象
        public IDbDataAdapter CreateDataAdapter()
        {
            return new OleDbDataAdapter();
        }

        ///
        /// 根据Connection建立Transaction
        ///
        /// Connection对象
        /// Transaction对象
        public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
        {
            return myDbConnection.BeginTransaction();   
        }

        ///
        /// 根据Command建立DataReader
        ///
        /// Command对象
        /// DataReader对象
        public IDataReader CreateDataReader(IDbCommand myDbCommand)
        {
            return myDbCommand.ExecuteReader();
        }

        ///
        /// 获得连接字符串
        ///
        /// 连接字符串
        public string GetConnectionString()
        {
            string strProvider = ConfigurationSettings.AppSettings["OleDbProvider"];
            string strDataSource = ConfigurationSettings.AppSettings["OleDbDataSource"];
            string strConnectionString = "Provider = " strProvider ";Data Source = " strDataSource ";";
            return strConnectionString;
        }
    }
}

(5)OdbcFactory.cs

using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;

namespace DbService
{
    ///
    /// 针对Odbc连接的工厂
    ///
    public class OdbcFactory : AbstractDbFactory
    {
        ///
        /// 构造函数
        ///
        public OdbcFactory()
        {
        }

        ///
        /// 建立默认Connection对象
        ///
        /// Connection对象
        public IDbConnection CreateConnection()
        {
            return new OdbcConnection();
        }

        ///
        /// 根据连接字符串建立Connection对象
        ///
        /// 连接字符串
        /// Connection对象
        public IDbConnection CreateConnection(string strConn)
        {
            return new OdbcConnection(strConn);
        }

        ///
        /// 建立Command对象
        ///
        /// Command对象
        public IDbCommand CreateCommand()
        {
            return new OdbcCommand();
        }

        ///
        /// 建立DataAdapter对象
        ///
        /// DataAdapter对象
        public IDbDataAdapter CreateDataAdapter()
        {
            return new OdbcDataAdapter();
        }

        ///
        /// 根据Connection建立Transaction
        ///
        /// Connection对象
        /// Transaction对象
        public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
        {
            return myDbConnection.BeginTransaction();
        }

        ///
        /// 根据Command建立DataReader
        ///
        /// Command对象
        /// DataReader对象
        public IDataReader CreateDataReader(IDbCommand myDbCommand)
        {
            return myDbCommand.ExecuteReader();
        }

        ///
        /// 获得连接字符串
        ///
        ///
        public string GetConnectionString()
        {
            string strDriver = ConfigurationSettings.AppSettings["OdbcDriver"];
            string strDBQ = ConfigurationSettings.AppSettings["OdbcDBQ"];
            string strConnectionString = "Driver={" strDriver "}; DBQ=" strDBQ ";";
            return strConnectionString;   
        }
    }
}

 

用C#实现的数据库抽象工厂(三)

     以下是在应用时真正要调用到的类：

(6)DbAccess.cs

using System;
using System.Data;

namespace DbService
{
    ///
    /// DbAccess类,即进行数据库访问时需要调用的类
    ///
    public sealed class DbAccess
    {
        ///
        /// DbAccess构造函数
        ///
        private DbAccess()
        {
        }

        ///
        /// 无条件查询操作,即查询表中所有记录
        ///
        /// 表名
        /// 列名组
        /// 无条件查询结果
        public static DataSet SelectAll(string strTableName, string[] strColumn)
        {
            DataSet ds = new DataSet();
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
            try
            {
                string strSql = "Select ";
                for(int i = 0; i < strColumn.Length - 1; i )
                {
                    strSql = (strColumn[i] ", ");
                }
                strSql = (strColumn[strColumn.Length - 1] " FROM " strTableName);
                concreteDbCommand.CommandText = strSql;
                concreteDbAdapter.SelectCommand = concreteDbCommand;    
                concreteDbAdapter.Fill(ds);
                concreteDbTrans.Commit();
            }
            catch
            {
                concreteDbTrans.Rollback();
                ds.Clear();
                throw;
            }
            finally
            {
                concreteDbConn.Close();
            }  
            return ds;
        }

        ///
        /// 条件查询操作
        ///
        /// 表名
        /// 列名组
        /// 条件
        /// 条件查询结果
        public static DataSet Select(string strTableName, string[] strColumn, string strCondition)
        {
            DataSet ds = new DataSet();
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
            try
            {
                string strSql = "Select ";
                for(int i = 0; i < strColumn.Length - 1; i )
                {
                    strSql = (strColumn[i] ", ");
                }
                strSql = (strColumn[strColumn.Length - 1] " FROM " strTableName " Where " strCondition);
                concreteDbCommand.CommandText = strSql;
                concreteDbAdapter.SelectCommand = concreteDbCommand;    
                concreteDbAdapter.Fill(ds);
                concreteDbTrans.Commit();
            }
            catch
            {
                concreteDbTrans.Rollback();
                ds.Clear();
                throw;
            }
            finally
            {
                concreteDbConn.Close();
            }
            return ds;
        }

        ///
        /// 单条记录的插入操作
        ///
        /// 表名
        /// 列名组
        /// 值组
        public static void Insert(string strTableName, string[] strColumn, object[] strValue)
        {
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();   
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            try
            {
                string strSql = "Insert INTO " strTableName " (";
                for(int i = 0; i < strColumn.Length - 1; i )
                {
                    strSql = (strColumn[i] ", ");
                }
                strSql = (strColumn[strColumn.Length - 1] ") VALUES ('");
                for(int i = 0; i < strValue.Length - 1; i )
                {
                    strSql = (strValue[i] "', '");
                }
                strSql = (strValue[strValue.Length - 1] "')");
                concreteDbCommand.CommandText = strSql;
                concreteDbCommand.ExecuteNonQuery();
                concreteDbTrans.Commit();
            }
            catch
            {
                concreteDbTrans.Rollback();
                throw;
            }
            finally
            {
                concreteDbConn.Close();
            }   
        }

        ///
        /// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
        ///
        /// 批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名
        public static void InsertSet(ref DataSet ds)
        {
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();   
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            try
            {
                foreach(DataTable dt in ds.Tables)
                {
                    foreach(DataRow dr in dt.Rows)
                    {
                        string strSql = "Insert INTO " dt.TableName " (";
                        for(int i = 0; i < dt.Columns.Count - 1; i )
                        {
                            strSql = (dt.Columns[i].Caption ", ");
                        }
                        strSql = (dt.Columns[dt.Columns.Count - 1].Caption ") VALUES ('");
                        for(int i = 0; i < dt.Columns.Count - 1; i )
                        {
                            strSql = (dr[i] "', '");
                        }
                        strSql = (dr[dt.Columns.Count - 1] "')");
                        concreteDbCommand.CommandText = strSql;
                        concreteDbCommand.ExecuteNonQuery();
                    }
                }
                concreteDbTrans.Commit();
            }

            catch
            {
                concreteDbTrans.Rollback();
                throw;
            }

            finally
            {
                concreteDbConn.Close();
            }   
        }

        ///
        /// 无条件删除操作,即删除表中所有记录
        ///
        /// 表名
        public static void DeleteAll(string strTableName)
        {
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            try
            {
                string strSql = "Delete FROM " strTableName;
                concreteDbCommand.CommandText = strSql;
                concreteDbCommand.ExecuteNonQuery();
                concreteDbTrans.Commit();
            }
            catch
            {
                concreteDbTrans.Rollback();
                throw;
            }
            finally
            {
                concreteDbConn.Close();
            }   
        }

        ///
        /// 条件删除操作
        ///
        /// 表名
        /// 条件
        public static void Delete(string strTableName, string strCondition)
        {
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            try
            {
                string strSql = "Delete FROM " strTableName " Where " strCondition;
                concreteDbCommand.CommandText = strSql;
                concreteDbCommand.ExecuteNonQuery();
                concreteDbTrans.Commit();
            }
            catch
            {
                concreteDbTrans.Rollback();
                throw;
            }
            finally
            {
                concreteDbConn.Close();
            }   
        }

        ///
        /// 无条件更新操作,即更新表中所有记录
        ///
        /// 表名
        /// 列名组
        /// 值组
        public static void UpdateAll(string strTableName, string[] strColumn, object[] strValue)
        {
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();   
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            try
            {
                string strSql = "Update " strTableName " SET ";
                for(int i = 0; i < strColumn.Length - 1; i )
                {
                    strSql = (strColumn[i] " = '" strValue[i] "', ");
                }
                strSql = (strColumn[strColumn.Length - 1] " = '" strValue[strValue.Length - 1] "' ");
                concreteDbCommand.CommandText = strSql;
                concreteDbCommand.ExecuteNonQuery();
                concreteDbTrans.Commit();
            }
            catch
            {
                concreteDbTrans.Rollback();
                throw;
            }
            finally
            {
                concreteDbConn.Close();
            }
        }

        ///
        /// 条件更新操作
        ///
        /// 表名
        /// 列名组
        /// 值组
        /// 条件
        public static void Update(string strTableName, string[] strColumn, object[] strValue, string strCondition)
        {
            Factory factory = Factory.GetInstance();
            AbstractDbFactory abstractDbFactory = factory.CreateInstance();   
            IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
            concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
            concreteDbConn.Open();
            IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
            IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
            concreteDbCommand.Connection = concreteDbConn;
            concreteDbCommand.Transaction = concreteDbTrans;
            try
            {
                string strSql = "Update " strTableName " SET ";
                for(int i = 0; i < strColumn.Length - 1; i )
                {
                    strSql = (strColumn[i] " = '" strValue[i] "', ");
                }
                strSql = (strColumn[strColumn.Length - 1] " = '" strValue[strValue.Length - 1] "' " " Where " strCondition);
                concreteDbCommand.CommandText = strSql;
                concreteDbCommand.ExecuteNonQuery();
                concreteDbTrans.Commit();
            }
            catch
            {
                concreteDbTrans.Rollback();
                throw;
            }
            finally
            {
                concreteDbConn.Close();
            }
        }
    }
}

最后一步，在Web.config中的根结点configuration下增加一些关于数据库连接字符串的变量：

 
   
 
   
   
   
   
 
   
   
 
   
   
      

     现在一切OK，大家可以通过改变Web.config中的变量来使用不同的数据库连接方式(SqlServer专用连接、OleDb连接和Odbc连接)连接不同的数据库，同时整个使用仍通过DbAccess，不受任何影响。
============================================================

今天看了看设计模式中的工场模式，感觉还不错，一时兴起，便将我原来利用简单工场模式写的一个操作数据库的类大至改成了工场模式，算是加深我对工场模式的理解吧。下面来看看实现过程：

一。采用工场模式实现对Connection对象的操作

using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DBFactory
{
    /// 抽象Connection工場類
    public abstract class ConnectionFactory
     {
        protected string connectionString;

        public ConnectionFactory(string connString)
         {
            this.connectionString = connString;
        } 

        public abstract IDbConnection GetConnection();
    } 

    /// 實現SqlConnection對象的具體工作類
    public class SqlConnection : ConnectionFactory
     {
        public SqlConnection() : this(null) {} 
        public SqlConnection(string connString) : base(connString) {} 

        public override IDbConnection GetConnection()
         {
            if(connectionString!=null)
             {
                return new System.Data.SqlClient.SqlConnection(connectionString);
            } 
            else 
             {
                return new System.Data.SqlClient.SqlConnection();
            } 
        } 
    } 

    /// 實現OleDbConnection對象的具體工場類
    public class AccessConnection :ConnectionFactory
     {
        public AccessConnection() : this(null) {} 
        public AccessConnection(string connString) :base(connString) {} 

        public override IDbConnection GetConnection()
         {
            if(connectionString!=null)
             {
                return new OleDbConnection(connectionString);
            } 
            else 
             {
                return new OleDbConnection();
            } 
        } 
    } 
} 

二。采用工场模式实现对Command对象的操作

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DBFactory
{
    /// 抽象CommandFactory類
    public abstract class CommandFactory
     {
        protected string commandText;
        protected IDbConnection iConn;

        public CommandFactory(IDbConnection conn,string commText)
         {
            this.iConn = conn;
            this.commandText = commText;
        } 

        public abstract IDbCommand GetCommand();
    } 

    /// 實現SqlCommand對象的具體類
    public class SqlCommand : CommandFactory
     {
        public SqlCommand(IDbConnection conn,string commText) : base(conn,commText) {} 

        public override IDbCommand GetCommand()
         {
            return new System.Data.SqlClient.SqlCommand(commandText,(System.Data.SqlClient.SqlConnection)iConn);
        } 
    } 

    /// 實現OleDbCommand對象的具體類
    public class AccessCommand : CommandFactory
     {
        public AccessCommand(IDbConnection conn,string commText) : base(conn,commText) {} 

        public override IDbCommand GetCommand()
         {
            return new OleDbCommand(commandText,(OleDbConnection)iConn);
        } 
    } 
} 

 

 

 

 

三。采用工场模式实现对DataAdapter对象的操作

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DBFactory
{
    /// 抽象DataAdapterFactory工場類
    public abstract class DataAdapterFactory
     {
        protected IDbCommand iComm;

        public DataAdapterFactory(IDbCommand comm)
         {
            this.iComm = comm;
        } 

        public abstract DbDataAdapter GetDataAdapter();
    } 

    /// 實現SqlDataAdapter對象的具體類
    public class SqlDataAdapter : DataAdapterFactory
     {
        public SqlDataAdapter(IDbCommand comm) : base(comm) {} 

        public override DbDataAdapter GetDataAdapter()
         {
            return new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)iComm);
        } 
    } 

    /// 實現OleDbDataAdapter對象的具體類
    public class AccessDataAdapter : DataAdapterFactory
     {
        public AccessDataAdapter(IDbCommand comm) : base(comm) {} 

        public override DbDataAdapter GetDataAdapter()
         {
            return new OleDbDataAdapter((OleDbCommand)iComm);
        } 

    } 
} 

四。这里利用简单工场模式来返回以上的抽象工场对象

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DBFactory
{
    public class SimpleFactory
     {
        public SimpleFactory() {} 

        /// 返回抽象的ConnectionFactory工場對象
        public static ConnectionFactory GetConnFactory(string connString,string dbType)
         {
            ConnectionFactory factory;
            switch(dbType.ToUpper())
             {
                case "SQL":
                    factory = new DBFactory.SqlConnection(connString);
                    break;
                case "ACCESS":
                    factory = new DBFactory.AccessConnection(connString);
                    break;
                default:
                    factory = null;
                    break;
            } 
            return factory;
        } 

        /// 返回抽象的CommandFactory工場對象
        public static CommandFactory GetCommFactory(IDbConnection conn,string commText,string dbType)
         {
            CommandFactory factory;
            switch(dbType.ToUpper())
             {
                case "SQL":
                    factory = new DBFactory.SqlCommand(conn,commText);
                    break;
                case "ACCESS":
                    factory = new DBFactory.AccessCommand(conn,commText);
                    break;
                default:
                    factory = null;
                    break;
            } 
            return factory;
        } 

        /// 返回抽象的DataAdapterFactory工場對象
        public static DataAdapterFactory GetDataAdapterFactory(IDbCommand comm,string dbType)
         {
            DataAdapterFactory factory;
            switch(dbType.ToUpper())
             {
                case "SQL":
                    factory = new DBFactory.SqlDataAdapter(comm);
                    break;
                case "ACCESS":
                    factory = new DBFactory.AccessDataAdapter(comm);
                    break;
                default:
                    factory = null;
                    break;
            } 
            return factory;
        } 
    } 
} 

五。封装的操作数据库存的类

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

namespace DBFactory
{
    public class ExecuteDB
     {
        private static string connectionString;
        private static string dbType;

        public ExecuteDB() {} 

        /// 數據庫連接字符串
        public static string ConnectionString
         {
            get 
             {
                if(connectionString==null) connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
                return connectionString;
            } 
            set {connectionString = value;} 
        } 

        /// 數據庫類型
        public static string DBType
         {
            get 
             {
                if(dbType==null) dbType = ConfigurationSettings.AppSettings["DataBaseType"];
                return dbType;
            } 
            set {dbType=value;} 
        } 

        /// 執行SQL語句返回DataSet
        public static DataSet ExcuteSql(string sqlString)
         {
            DataSet ds = new DataSet();
            ConnectionFactory objConn = SimpleFactory.GetConnFactory(ConnectionString,DBType);
            IDbConnection iConn = objConn.GetConnection();
            iConn.Open();
            CommandFactory objComm = SimpleFactory.GetCommFactory(iConn,sqlString,DBType);
            IDbCommand iComm = objComm.GetCommand();
            DataAdapterFactory objAdapter = SimpleFactory.GetDataAdapterFactory(iComm,DBType);
            DbDataAdapter dataAdaper = objAdapter.GetDataAdapter();
            dataAdaper.Fill(ds);
            iComm.Dispose();
            iConn.Close();
            iConn.Dispose();
            return ds;
        } 
    } 
} 

上面對具體的數據庫的選擇采用的是簡單工場模式來實現的（因工場模式中的具體類只能實現具體的對象，感覺不好實現）

實現方法：

string connString = "data source=192.168.1.9;initial catalog=sqldll;persist security info=False;user id=sa;password=123456;workstation id=Server;packet size=4096";
            string commString = "select * from tbl_Vip";

            ExecuteDB.ConnectionString = connString;
            ExecuteDB.DBType = "sql";
            DataGrid1.DataSource=ExecuteDB.ExcuteSql(commString);
            DataGrid1.DataBind();

转载于:https://www.cnblogs.com/Catherine2011/p/6544106.html