编写驱动程序类通用的任何数据库支持 [英] Writing driver class generic for any database support
问题描述
在过去的几天里，我正与各种数据库如MySQL，甲骨文，IBMDB2等，这点与净透ODBC提供连接。
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();
}
}
此只做基本的的ExecuteNonQuery 和的ExecuteReader 一样操作，简单的交易秒。没有storeprocs。该添加函数适用于插入和检索最后插入的ID和喜欢。 这是疯狂的我已经把事情懒惰，已经习惯了只是一个核心执行功能不要（这就是所谓的各种数据库操作），这就是为什么不要看起来很复杂，但它很干。理想的情况下它能够更好地分离。你可以摆脱的LINQ 了。
This only does the basic ExecuteNonQuery and ExecuteReader like operations, and simple Transactions. No storeprocs. 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 dont 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类本身，而是我之外preFER它，因为功能应该是你的应用程序）。
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）小心对数据库的工作中性查询您preFER。
5) Take care to have neutral queries that work on the databases you prefer.
或的
您可以使用 DbProviderFactory 下 System.Data.Common 来检测的DbConnection /供应商，你的类型。你可以只有一个非通用 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 方法将如下所示：
static IDb GetDbInstance()
{
string connectionString = GetConnectionString();
string driver = GetDriver();
return Db(driver, connectionString);
}
临：你摆脱的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＃code示例查询看起来像：
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.get方法就会， Db.Save 等功能 GetDbInstance 这里的关键是它找到了正确的DLL函数被调用，以及辅助类管理的资源也同时另外做各种数据库操作的任务。这样的类将避免打开和关闭连接，释放资源的麻烦，必须包括数据库的DLL名称空间等每一次。这就是所谓的 DBAL 。你可以有一个附加层，以帮助各种强类型的模型类之间DBAL沟通为好。我只是爱多态性的力量，通过接口，这是非常非常OOP的限制！ ：）
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屋！
