在 C# 中处理多个数据库连接的最佳方法是什么 [英] What is the best way to handle multiple database connections in C#

问题描述

如果说我需要针对两个单独的数据库运行两个单独的 SQL 语句.现在我做(伪代码):

If say I need to run two separate SQL statements against two separate databases. Right now I do (pseudocode):

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() 
} 

上面看起来很笨拙.如果我有三个不同的 sql 语句，我将需要三个不同的 SQLCmd 变量.

The above seems very clumsy. And if I have three different sql statements, i would need three different SQLCmd variables.

是否有做这些事情的标准"方式，尤其是在效率、性能方面?如果有人可以提供一个简单的改进伪代码，那就太好了.

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?

谢谢！

推荐答案

与其添加变量，不如做一个类?

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");

并在每个节点上执行一个 sql 查询

And execute an sql query on each

MyDatabaseConnection[] cons = new MyDatabaseConnection[]{ con1, con2, con3 };
foreach (MyDatabaseConnection con in cons) {
    con.execute(someSqlCommandText);
}

这篇关于在 C# 中处理多个数据库连接的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！