C# - 通用数据库连接、命令、阅读器的类
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1107824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# - A class for Generic database connection, command, reader
提问by user366312
Suppose I am designing a class that can handle any database technology to create a connection, execute command and retrieve data, etc.
假设我正在设计一个类，它可以处理任何数据库技术来创建连接、执行命令和检索数据等。
If I need to create a generic database handling class for existing RDBMSs (like SQL Server, Oracle, FireBird, et.), which .net abstract-class/Interface should I use {DbConnection, DbCommand, DbParameter,...} or {IDbConnection, IDbCommand, IDbParameter,...}?
如果我需要为现有的 RDBMS（如 SQL Server、Oracle、FireBird 等）创建一个通用的数据库处理类，我应该使用哪个 .net 抽象类/接口 {DbConnection、DbCommand、DbParameter...} 或 { IDbConnection、IDbCommand、IDbParameter、...}？
Should I use the code like
我应该使用像
public bool CreateConnection(DatabaseTypeEnum type)
{
DbConnection conn ;
if(type==DatabaseTye.Oracle)
{
//....
}
}
public DbDataReader GetData()
{
DbCommand comm;
//...
}
or,
或者，
public bool CreateConnection(DatabaseTypeEnum type)
{
IDbConnection conn ;
if(type==DatabaseTye.Oracle)
{
//....
}
}
public IDbDataReader GetData()
{
IDbCommand comm;
//...
}
And, Why?
而且，为什么？
采纳答案by Jimmy Chandra
Ermm... totally different question :)
嗯...完全不同的问题:)
OK, neither...
好吧，也不...
You are going to violate Open Close Principle when you do that... The switch / if statement in that particular place is making me uncomfortable :).
当你这样做时，你将违反开放关闭原则......那个特定地方的 switch / if 语句让我感到不舒服:)。
I'd leave the actual creation to a Factory class and your code should not care if it is talking to a SQL Server or DB2 or Oracle or whatever.
我会将实际创建留给 Factory 类，并且您的代码不应该关心它是在与 SQL Server、DB2 还是 Oracle 或其他任何东西进行通信。
Ideally, your code should only talk to IDbConnection, IDbCommand, etc. or the abstract base class (DbConnection, DbCommand, etc.). Sometimes I do find that you need to upcast to a specific provider tho (like SqlDataReader for using specific methods), but it is quite rare.
理想情况下，您的代码应该只与 IDbConnection、IDbCommand 等或抽象基类（DbConnection、DbCommand 等）对话。有时我确实发现您需要向上转换到特定的提供程序（例如使用特定方法的 SqlDataReader），但这种情况很少见。
The Factory will encapsulate this switch / if statement to a single place so it's easily maintainable. You can further abstract the actual creation in a app.config. So in app.config you choose what type of DB backend you are supporting and the Factory will pick it up from there and create the necessary DB stuffs for you.
工厂将把这个 switch / if 语句封装到一个地方，所以它很容易维护。您可以在 app.config 中进一步抽象实际创建。因此，在 app.config 中，您选择您支持的数据库后端类型，工厂将从那里选择它并为您创建必要的数据库内容。
See: this. Read about Creating DbProviderFactory and Connection part...
见：这个。阅读有关创建 DbProviderFactory 和连接部分的信息...
回答by Ricardo
Why don't you use generics?
为什么不使用泛型？
You can define your class like this for example:
你可以像这样定义你的类，例如：
public class DBHelper<T, Y, W> where T: DbConnection, new() where Y : DbCommand, new()
{
private T conn_ = new T();
private Y comm_ = new Y();
}
That's what I do to and its really easy to maintain.
这就是我所做的，它真的很容易维护。
回答by nawfal
You should use the IDbConnectionand IDbCommandsince different database vendors will have different implementation of the interfaces (for their ADO.NET thing), but betternot exactly as you have posted. You should instead make the entire class generic to support generic IDbConnectionand IDbCommand. May be like this:
您应该使用IDbConnection和IDbCommand因为不同的数据库供应商将有不同的接口实现（对于他们的 ADO.NET 东西），但最好不要与您发布的完全一样。您应该改为使整个类泛型以支持泛型IDbConnection和IDbCommand. 可能是这样的：
public class Db<T> where T : IDbConnection, new()
{
public bool CreateConnection()
{
T conn;
//now you dont need these lines since you dont have to worry about
//what type of db you are using here, since they all implement
//IDbConnection, and in your case its just T.
//if(type==DatabaseTye.Oracle)
//{
//....
//}
}
public DbDataReader GetData()
{
//get comm object from S conn
using(var conn = new S())
using (var comm = conn.CreateCommand())
//...
}
The benefit is that you can pass to this class the types you want to use for DbConnectionand DbCommandwhich will be different for MySQL .net connector and Oracle one. So you have some sort of control from outside the class. You can see to this question and my answerfor reference for a basic implementation.
好处是您可以将要使用的类型传递给此类，DbConnection并且DbCommand对于 MySQL .net 连接器和 Oracle 连接器将有所不同。因此，您可以从课堂外获得某种控制权。您可以查看此问题和我的答案，以供参考以获取基本实现。
