为任何数据库支持编写通用的驱动程序类 [英] Writing driver class generic for any database support

查看:23
本文介绍了为任何数据库支持编写通用的驱动程序类的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

过去几天,我在使用各种数据库,例如 MySQL、oracle、Ibmdb2 等,这些数据库通过 odbc 提供程序与 dot net 连接.

For the past few days, I was working with various database such as MySQL,oracle,Ibmdb2 etc which connect with dot net through odbc providers.

例如:

1)MySQL:

Driver={MySQL ODBC 5.1 Driver};server=**********;uid=**;database=**;port=***;pwd=***;"

2)oracle:

Driver={Microsoft ODBC for Oracle};server=**********;uid=**;database=**;port=***;pwd=***;"

3)Db2:

Driver={IBM DB2 ODBC DRIVER};server=**********;uid=**;database=**;port=***;pwd=***;"

现在我的问题是

是否可以为任何数据库提供程序编写泛型类作为

is it possible to write generic class for any database provider as

Driver={My own driver};server=**********;uid=**;database=**;port=***;pwd=***;"

只需更改 web.config 中的驱动程序名称并将该 dll 文件放在我发布的 Web 应用程序或网站项目的 bin 文件夹中即可连接每个数据库.

which connects every database just by changing driver name in web.config and placing that dll file in bin folder of my published web application or website project.

推荐答案

推出自己的方案并不是什么大不了的事.这是我将如何实现它以满足最低需求的基本结构(您当然可以扩展它):

To roll one of your own isn't that big a deal. Here is a basic structure of how I would implement it for bare minimum needs (you can of course expand it):

1) 首先创建一个指定基本功能的界面.

1) First create an interface specifying the basic functionalities.

interface IDb
{
    IEnumerable<T> Get<T>(string query, Action<IDbCommand> parameterizer, 
                          Func<IDataRecord, T> selector);

    int Add(string query, Action<IDbCommand> parameterizer);

    int Save(string query, Action<IDbCommand> parameterizer);

    int SaveSafely(string query, Action<IDbCommand> parameterizer);

}

2) 创建通用帮助器类,该类不仅应实现接口,还应由 IDbConnection 类型指定.该类应该更好(不一定)可实例化(非静态),以便您可以传递所需的连接字符串来实例化它.

2) Create the generic helper class which should not only implements the interface but also should be specified by the type IDbConnection. The class should be better (not necessarily) instantiable (not static) so that you can pass the required connection string to instantiate it.

这是一个完全懒惰的实现:

Here is a fully lazy implementation:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;

public class Db<T> : IDb where T : IDbConnection, new()
{
    string connectionString;

    public Db(string connectionString)
    {
        this.connectionString = connectionString;
    }

    IEnumerable<S> Do<R, S>(string query, Action<IDbCommand> parameterizer, 
                            Func<IDbCommand, IEnumerable<R>> actor, Func<R, S> selector)
    {
        using (var conn = new T())
        {
            using (var cmd = conn.CreateCommand())
            {
                if (parameterizer != null)
                    parameterizer(cmd);
                cmd.CommandText = query;
                cmd.Connection.ConnectionString = connectionString;

                cmd.Connection.Open();

                foreach (var item in actor(cmd))
                    yield return selector(item);
            }
        }
    }

    public IEnumerable<S> Get<S>(string query, Action<IDbCommand> parameterizer, Func<IDataRecord, S> selector)
    {
        return Do(query, parameterizer, ExecuteReader, selector);
    }

    static IEnumerable<IDataRecord> ExecuteReader(IDbCommand cmd)
    {
        using (var r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            while (r.Read())
                yield return r;
    }

    public int Add(string query, Action<IDbCommand> parameterizer)
    {
        return Do(query, parameterizer, ExecuteReader, r => Convert.ToInt32(r[0])).First();
    }

    public int Save(string query, Action<IDbCommand> parameterizer)
    {
        return Do(query, parameterizer, ExecuteNonQuery, noAffected => noAffected).First();
    }

    static IEnumerable<int> ExecuteNonQuery(IDbCommand cmd)
    {
        yield return cmd.ExecuteNonQuery();
    }

    public int SaveSafely(string query, Action<IDbCommand> parameterizer)
    {
        // 'using' clause ensures rollback is called, so no need to explicitly rollback
        return Do(query, parameterizer, cmd => 
        {
            using (cmd.Transaction = cmd.Connection.BeginTransaction())
            {
                var noAffected = ExecuteNonQuery(cmd);
                cmd.Transaction.Commit();
                return noAffected;
            }
        }, noAffected => noAffected).First();
    }
}

这只做了基本的ExecuteNonQueryExecuteReader 之类的操作,以及简单的Transactions.没有存储过程.Add 函数用于插入和检索最后插入的 id 和喜欢.让事情变得懒惰并且只使用一个核心执行函数 Do(它被调用用于各种 db 操作)让我很疯狂,这就是为什么 Do 看起来复杂,但它非常干燥.最好分开.你也可以去掉 Linq.

This only does the basic ExecuteNonQuery and ExecuteReader like operations, and simple Transactions. No stored procedures. The Add function works for inserting and retrieving the last inserted id and likes. It was crazy of me to have made things lazy and to have used just one core execution function Do (which is called for various db actions), and that is why Do looks complicated, but its very DRY. Ideally its better to be separated. You can rid of Linq too.

3) 最后提供静态包装器 Db,在可实例化的 Db 类周围没有通用约束,这样您就不必继续传递 T 参数每次做一个数据库查询.例如像这样:

3) Lastly provide static wrapper Db with no generic constraints around the instantiable Db class so that you don't have to keep passing the T parameter every time to do a db query. For instance like this:

public static class Db
{
    static IDb db = GetDbInstance();

    static IDb GetDbInstance()
    {
        // get these two from config file or somewhere
        var connectionString = GetConnectionString();
        var driver = GetDbType();   // your logic to decide which db is being used

        // some sort of estimation of your db
        if (driver == SQLite)
            return new Db<SQLiteConnection>(connectionString);
        else if (driver == MySQL)
            return new Db<MySqlConnection>(connectionString);
        else if (driver == JET)
            return new Db<OleDbConnection>(connectionString);
        //etc

        return null;
    }

    public static void Parameterize(this IDbCommand command, string name, 
                                    object value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        command.Parameters.Add(parameter);
    }

    public static IEnumerable<T> Get<T>(string query, 
                                        Action<IDbCommand> parameterizer, 
                                        Func<IDataRecord, T> selector)
    {
        return db.Get(query, parameterizer, selector);
    }

    public static int Add(string query, Action<IDbCommand> parameterizer)
    {
        return db.Add(query, parameterizer);
    }

    public static int Save(string query, Action<IDbCommand> parameterizer)
    {
        return db.Save(query, parameterizer);
    }

    public static int SaveSafely(string query, Action<IDbCommand> parameterizer)
    {
        return db.SaveSafely(query, parameterizer);
    }
}

4) 现在我将在某处创建一个额外的静态函数 GetDbInstance 以便它推断正确的数据库参数,如连接字符串、提供程序类型等.还有一个扩展方法来简化查询的参数化.我把它们都放在上面的静态 Db 类中,但这是你的选择(有些人在 Db 类本身中编写它,但我更喜欢它在外面,因为功能应该是你的应用程序的).

4) Now I would create an additional static function GetDbInstance somewhere so that it infers the right database parameters like connection string, provider type etc. Also have an extension method to ease parameterization of queries. I put both of them in the above static Db class but that's your choice (some people write it in the Db class itself but I prefer it outside because the functionality should be your application's).

5) 请注意对您喜欢的数据库进行中性查询.

5) Take care to have neutral queries that work on the databases you prefer.

或者

您可以在 DbProviderFactory 下使用System.Data.Common 来检测您拥有的 DbConnection/provider 的类型.你可以只有一个非通用的 Db 类,然后做:

You can utilize DbProviderFactory under System.Data.Common to detect the type of DbConnection/provider you have. You can have just one non-generic Db class and do:

public class Db
{
    string connectionString;
    DbProviderFactory factory;

    public Db(string driver, string connectionString)
    {
        this.factory = DbProviderFactories.GetFactory(driver);
        this.connectionString = connectionString;
    }

    //and your core function would look like
    IEnumerable<S> Do<R, S>(string query, Action<IDbCommand> parameterizer, 
                            Func<IDbCommand, IEnumerable<R>> actor, 
                            Func<R, S> selector)
    {
        using (var conn = factory.CreateConnection())
        {
            // and all the remaining code..
        }
    }
}

您的 GetDbInstance 方法如下所示:

Your GetDbInstance method would look like:

static IDb GetDbInstance()
{
    string connectionString = GetConnectionString();
    string driver = GetDriver();

    return Db(driver, connectionString);
}

Pro:您摆脱了 if-else 编程风格,并且将根据配置中的提供程序和连接字符串实例化正确版本的 Db 类文件.

Pro: You get rid of the if-else style of programming and the right version of Db class will be instantiated depending on the provider and connection string in the config file.

缺点:您需要在配置文件中指定正确的提供程序/驱动程序.

Con: You need to specify the right provider/driver in the configuration file.

来自您的 C# 代码的示例查询如下所示:

A sample query from your C# code would look like:

string query = "SELECT * FROM User WHERE id=@id AND savedStatus=@savedStatus";
var users = Db.Get(sql, cmd =>
{
    cmd.Parameterize("id", 1);
    cmd.Parameterize("savedStatus", true);
}, selector).ToArray();

您所要做的就是调用Db.GetDb.Save 等.函数GetDbInstance 是这里的关键,它可以找到在正确的 dll 中调用函数,帮助类很好地管理资源,同时还完成各种数据库操作的任务.这样的类将避免每次打开和关闭连接、释放资源、必须包含数据库 dll 命名空间等的麻烦.这就是所谓的 DbAL.您还可以使用 附加层来帮助 DbAL 在各种强类型模型类之间进行通信.我只是喜欢通过接口和约束实现多态的强大功能,这是非常非常面向对象的!:)

All you have to do is call Db.Get, Db.Save etc. The function GetDbInstance is the key here which finds the functions in the right dlls to be called, and the helper class manages the resources well while additionally doing its task of various db operations. Such a class would avoid the hassle of opening and closing connections, freeing resources, having to include database dll namespace etc every time. This is what is called DbAL. You can have an additional layer to help DbAL communicate between various strongly typed model classes as well. I simply love the power of polymorphism via interfaces and constraints which is very very OOP! :)

这篇关于为任何数据库支持编写通用的驱动程序类的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆