DB Connection Procedure – reusing in Solution

5 pts.
Tags:
VB.NET
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • GreatPretender
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following