Design ideas needed for static data access class
-
I need to design a dataaccess class that will allow me to call the same stored procedures on different databases. I want the class to be static but also to take advantage of connection pooling. I guess each method will need to be passed the database connection string. I will also need a collection of connection objects that I will need to manage (one for each database). Are there any good reference articles on this that anyone could point me in the direction of. Do you need more info on what I'm trying to achieve to answer this query, if so please post back. Jim
-
I need to design a dataaccess class that will allow me to call the same stored procedures on different databases. I want the class to be static but also to take advantage of connection pooling. I guess each method will need to be passed the database connection string. I will also need a collection of connection objects that I will need to manage (one for each database). Are there any good reference articles on this that anyone could point me in the direction of. Do you need more info on what I'm trying to achieve to answer this query, if so please post back. Jim
Hi Jim. Are the databases all from the same provider? (e.g. are they all accessed using either the SqlClient classes, or the OleDbClient, or some other provider?) If they're all accessed using SqlClient classes, I would think a simple Array or ArrayList would work for holding your SqlConnection objects. Then your method for calling your stored procedure could loop through the connections array, performing the following tasks: open the connection, set up a SqlCommand object for the stored procedure, call the SqlCommand.ExecuteNonQuery() method, and finally close the connection.
-
Hi Jim. Are the databases all from the same provider? (e.g. are they all accessed using either the SqlClient classes, or the OleDbClient, or some other provider?) If they're all accessed using SqlClient classes, I would think a simple Array or ArrayList would work for holding your SqlConnection objects. Then your method for calling your stored procedure could loop through the connections array, performing the following tasks: open the connection, set up a SqlCommand object for the stored procedure, call the SqlCommand.ExecuteNonQuery() method, and finally close the connection.
Mike Yes they're all SqlClient. I think I will try your method and see how well it works. Thanks Jim