DB Connection Procedure – reusing in Solution
I have a .net solution that has a number of WebMethod procedures in various projets. A number of them are DB calls - either getting info or making changes.
Consequently there are a number of lines of code in various Projects that are identical & look like this:
xxxxxxxxxxxxxxxxxxxxxx
Try
strInfo = "....."
cn.connectionstring = strInfo
cn.Open()
try
......do something
catch e as exception
end try
Catch ex as SqlConnection
finally
if cn,state <> adstateclosed then
cn,close
cn.dispose
end if
end try
xxxxxxxxxxxxxxx
what I would like to do is have a procedure that creates a connection that can be called from anywhere in the solution as a whole.
If somebody could give me the benefit of their experience I would appreciate it.
Kind regards
Ross Petersen
Software/Hardware used:
Software/Hardware used:
ASKED:
February 20, 2006 1:28 PM
UPDATED:
February 22, 2006 9:06 AM
Answer Wiki:
Ross,
You can create a public class with static functions to handle the opening and closing of you connections. Since the "....do something" will be different you can try something like:
public class DbConnection
{
private static SQLConnection mConnection = new SQLConnection(ConnectionSource);
public static void openConnection(bool aOpenAConnection)
{
if ( aOpenAConnection )
mConnection.Open();
else
mConnection.Close();
}
}
ConnectionSource is just that, the source for the connection, assuming that all of your forms will use the same connection. If not, you can pass the connection in.
Hmm. You could put all of the data access code in the class, using a switch statement to handle the different cases of "... do something". Of course, you will need to adjust it a bit for connection sharing.
I hope these suggestions help.
Charles
To see all answers submitted to the Answer Wiki: View Answer History.
I would suggest creating a public class as the first answerer mentioned (a public funtion in a DLL that is referenced by the project), but also I would store your DB connection string in a text file that your shared DLL loads in when it is called.
That way, your static funtion “GetConnection” does not have to hard-code a connection string, therefore forcing you to recompile when the connection string changes.
If you have a seperate connection string per WebProject, you can also store the connection string in the web.config very easily by using the Configuration section of the Global.asax. That way, your shared method could actually create all the database setup for you, and then you could just pass it the connection string from each project.
An even better solution might be to use the Enterprise Data Access Application Block, which will take care of most of this for you.