在 C# 中处理多个数据库连接的最佳方法是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/958540/
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
What is the best way to handle multiple database connections in C#
提问by Saobi
If say I need to run two separate SQL statements against two separate databases. Right now I do (pseudocode):
如果说我需要针对两个单独的数据库运行两个单独的 SQL 语句。现在我做（伪代码）：
Try{
declare variable connectionA to DatabaseA
declare variable connectionB to DatabaseB
connectionA.open()
connectionB.open()
declare variable SQLCmdA with ConnectionA and one SQL statement
declare variable SQLCmdB with ConnectionB and another SQL statement
SQLCmdA.executeNonQuery()
SQLCmdB.executeNonQuery()
}
Catch ()
{
print error message
}
Finally(){
connectionA.close()
connectionB.close()
SQLCmdA.Dispose()
SQLCmdB.Dispose()
}
The above seems very clumsy. And if I have three different sql statements, i would need three different SQLCmd variables.
以上看起来很笨拙。如果我有三个不同的 sql 语句，我将需要三个不同的 SQLCmd 变量。
Is there a "standard" way of doing such things, especially in terms of efficiency, performance? if anyone can provide a simple improved pseudocode, that'd be great.
有没有一种“标准”的方式来做这些事情，尤其是在效率、性能方面？如果有人可以提供简单的改进伪代码，那就太好了。
In addition, do I need to worry about implementing Connection Pooling, to conserve resource and speed up the program? If so, how do I implement it in this case?
另外，我是否需要担心实现连接池，以节省资源并加快程序速度？如果是这样，在这种情况下我该如何实施？
Thanks!
谢谢！
采纳答案by Patrick
Instead of adding variables, why not make a class?
与其添加变量，不如创建一个类？
public class MyDatabaseConnection {
public MyDatabaseConnection(string connectionString) {
this.connectionString = connectionString;
// create a database connection perhaps
}
// some methods for querying a database
public void execute(string query) { }
}
In this case it's easy to add a third database connection
在这种情况下很容易添加第三个数据库连接
MyDatabaseConnection con1 = new MyDatabaseConnection("Server=localhost");
MyDatabaseConnection con2 = new MyDatabaseConnection("Server=other_server");
MyDatabaseConnection con3 = new MyDatabaseConnection("Server=third_one");
And execute an sql query on each
并对每个执行 sql 查询
MyDatabaseConnection[] cons = new MyDatabaseConnection[]{ con1, con2, con3 };
foreach (MyDatabaseConnection con in cons) {
con.execute(someSqlCommandText);
}
回答by Michael Todd
If you need all two (or three, or...) connetions open at the same time and need to retain the SqlCommand for each of them, then yes, you're probably going to have to do it the way you're doing it.
如果您需要同时打开所有两个（或三个，或...）连接并且需要为每个连接保留 SqlCommand，那么是的，您可能必须按照您的方式进行操作它。
However, if you only need one connection open at a time, you could use a single connection and single command, then change things as needed.
但是，如果您一次只需要打开一个连接，则可以使用单个连接和单个命令，然后根据需要进行更改。
回答by Noldorin
If you're going to be doing low-level database access, this seems fine to me. Of course, if you only need one database connection open at any time, you could abstract most of the code into a method (that takes an SQL command/text as a parameter and returns the result), but this may not be the case in your situation.
如果您要进行低级数据库访问，这对我来说似乎没问题。当然，如果您随时只需要打开一个数据库连接，您可以将大部分代码抽象为一个方法（该方法将 SQL 命令/文本作为参数并返回结果），但在你的情况。
You could also make things slightly neater by making use of usingstatements, as such:
您还可以通过使用using语句使事情变得更整洁，例如：
using(var sqlConnectionA = new ...)
using(var sqlConnectionB = new ...)
{
try
{
// Perform queries here.
}
catch (SqlException exSql)
{
// SQL error
}
}
