Click here to Skip to main content
15,557,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi friends, I have one web service in which I have 3 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

1 solution

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.
Share this answer
Ganesh KP 16-Oct-12 7:54am    
Thanks but can you please mention on how to make that in small example Aman
I.explore.code 16-Oct-12 8:58am    
Create a separate class with singleton instance variable and one readonly property of type SqlConnection. Before returning the connection, check to see if it is null, if it is, instantiate it otherwise just let it return. Make sure this class can be accessed throughout your data access layer. You would need to provide the connection string to the connection instantiation code which you can probably do when the very first time your app runs.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900