What is the best way to handle multiple database connections in C #

Question

If I say that I need to run two separate SQL statements against two separate databases. Right now I (pseudo-code):

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 awkward. And if I have three different sql statements, I will need three different SQLCmd variables.

Is there a “standard” way to do such things, especially in terms of efficiency, productivity? if someone could provide a simple improved pseudo code, that would be great.

Also, do I need to worry about implementing a connection pool, saving resources and speeding up the program? If so, how to implement this in this case?

Thanks!

Answer 1

Instead of adding variables, why not create 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 is 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 sql query on each

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

Answer 2

If you are going to make access to databases at a low level, it seems fine to me. Of course, if you only need one database connection, open at any time, you can abstract most of the code in the method (which takes the SQL command / text as a parameter and returns the result), but this may not be the case in your situation.

You can also make things a little neater using using as such:

 using(var sqlConnectionA = new ...) using(var sqlConnectionB = new ...) { try { // Perform queries here. } catch (SqlException exSql) { // SQL error } } 

Answer 3

If you need all two (or three or ...) connections to open at the same time and they need to save SqlCommand for each of them, then yes, you probably have to do it the way you do it.

However, if you only need one connection open at a time, you can use one connection and one command, and then change things as needed.