Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 21:58pm
Ganeshcse2.2K

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
v2
Comments
Ganeshcse at 16-Oct-12 7:54am
   
Thanks but can you please mention on how to make that in small example Aman
Aman.A at 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)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 16 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100