Make SQLConnection visible through out the Service

Question

0.00/5 (No votes)

See more:

Hi friends, I have one web service in which I have 3 vb.net modules

1. ApplicationManager.vb

2. AgencyManager.vb

3. InventoryManager.vb

and my service InventoryService.asmx in which I call the GeAllAgencies in the ApplicationManager.vb and in turn that method calls the GetAgency in AgencyManager.vb

and that GetAgency function calls the methods in InventoryManager.vb like GetAllItems,GetAvailableItems ... so on there are more methods.

My problem is that I am opening SQLConnection() every time in every module like in GetAgency() function in ApplicationManager, GetAgency() function in AgencyManager and at last all the methods in InventoryManager opens a connection for each individual, this cause me to open a lot of connections and problem is that when I run my service for the first time I am getting the data fastly but for the second time (I mean in the same run I invoke first service and in the second service invoke) I am unable to get the data. I thought this problem is occurred due to more connections and my question is

how to open a sqlconnection (I want to open in InventoryService.asmx) so it is visible to whole application and with the same con object.

can I make the single "SQLConnection" that is visible to whole appliation and I saved my connection string in web.config file from which I am accessing through with the following code

Dim constr As String = ConfigurationManager.ConnectionStrings("connstr").ConnectionString

Please tell me How to do this?

Thanks in Advance
Ganesh

Posted 15-Oct-12 20:58pm

Ganesh KP

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

I.explore.code · Accepted Answer · 2012-10-15T22:02:00

Generally, the rule is to open the SQL connection as late as possible and close as quickly as possible. Preferably you should open/close connections only the Data access layer i.e. the class(es) that directly access the data and no where else.

What you can do is create a singleton connection instance i.e. in a class that can be reused throughout the data access layer so that you don't have to open/close repeatedly. Open once ...keep using it as long as you want to ...finally, close it when you are all done (even if you don't close it, it will timeout after the default time anyway).

Open to more ideas though.