I have a vb.NET solution with a Windows Application project that contains my user interface and a Class Library project that contains (among other things) a class I call DataAccessComponent. This class inherits from System.ComponentModel.Component and contains a single SQLConnection object and a bunch of SQLDataAdapter object which reference it. It also exposes 2 methods per SQLDataAdapter object that all look more or less the same. For example
Public Function GetOrganizations(
Optional ByVal anOrganizationID As Object = Nothing) As dsNutme
Dim organizations As New dsNutme
organizations.EnforceConstraints = Fals
If Not anOrganizationID Is Nothing The
SQLDAtblOrganization.SelectCommand.Parameters("@Or ganizationID").Value = CType(anOrganizationID, Long
Els
SQLDAtblOrganization.SelectCommand.Parameters("@Or ganizationID").Value = System.DBNull.Valu
End I
SQLDAtblOrganization.Fill(organizations
Return organization
End Functio
Public Function UpdateOrganizations(ByVal organizationChanges
As dsNutmeg) As dsNutme
If Not (organizationChanges Is Nothing) The
SQLDAtblOrganization.Update(organizationChanges
Return organizationChange
Els
Return Nothin
End I
End Functio
My question (and I DO have one), is this: why, after several "get" calls, for exampl
Private Sub GetDat
dim mDAC as New DataAccessComponen
dim ds as new dsNutme
ds.Merge(mDAC.GetOrganizations()
' Do something with the data
End Su
, am I suddenly getting a timout exception with the advice that it might have somtehing to do with exceeding the available connections in my connection pool? I BELIEVED that I was just repeatedly using the same connection. Is this not the case? Am I not releasing connection resources properly? How SHOULD I go about doing what I'm trying to do
Thanks
Pa 9 2868
Hi Pat,
See this text on the page I give the link beneath it.
Note that the code does not explicitly open and close the Connection. The
Fill method implicitly opens the Connection that the DataAdapter is using if
it finds that the connection is not already open. If Fill opened the
connection, it will also close the connection when Fill is finished. This
can simplify your code when dealing with a single operation such as a Fill
or an Update. However, if you are performing multiple operations that
require an open connection, you can improve the performance of your
application by explicitly calling the Open method of the Connection,
performing the operations against the data source, then calling the Close
method of the Connection. You should strive to keep connections to the data
source open for a minimal amount of time to free up the resource to be used
by other client applications. http://msdn.microsoft.com/library/de...ataadapter.asp
As well I looked at your update just to make you aware on it. Public Function UpdateOrganizations(ByVal organizationChanges _ As dsNutmeg) As dsNutmeg If Not (organizationChanges Is Nothing) Then SQLDAtblOrganization.Update(organizationChanges) Return organizationChanges Else Return Nothing End If End Function
In my opinion is that the same as
Public Sub UpdateOrganizations(ByVal organizationChanges _
AS dsNutMeg)
If Not (organizationChanges Is Nothing) Then
SQLDAtblOrganization.Update(organizationChanges)
End Sub
Cor
Thanks for the response. I changed my "Get" procedures to explicitly open and close the connection object for each fill, but no joy. I still get the following message after a number of successful (say ca. 100) connections
"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dl
Additional information: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
The new "Get" method format is reproduced below
Public Function GetOrganizations(
Optional ByVal anOrganizationID As Object = Nothing) As dsNutme
Dim organizations As New dsNutme
organizations.EnforceConstraints = Fals
If Not anOrganizationID Is Nothing The
SQLDAtblOrganization.SelectCommand.Parameters("@Or ganizationID").Value = CType(anOrganizationID, Long
Els
SQLDAtblOrganization.SelectCommand.Parameters("@Or ganizationID").Value = System.DBNull.Valu
End I
SQLConnNutmegData.Open(
SQLDAtblOrganization.Fill(organizations
SQLConnNutmegData.Close(
Return organization
End Functio
I've tried using PerformanceCounters to examine the state of my connection pool. Either I'm using them incorrectly, or that is not the problem because at no time do they show my # of pools or my # of pooled connections > 3. Incidentally, the statistics do not appear to be sensitive to whether I explicitly open and close my connection or not.
I have no idea how to proceed, but obviously the situation is unacceptable as it stands -- the user has to frequently exit and re-enter the application
BTW, the "update" format was copied right out of a Microsft walkthrough for setting-up a webservice. I didn't even really examine it until you pointed out the fact that the return value was unnecessary. I agree with you, OTOH, why did MS think that it was necessary in the walkthrough
Thanks again, and ANY advice would be MUCH appreciated
Pat
Hi Pat,
Angel says always to use dispose for the connection instead the close with
more than 100 connections.
There seems, with the connection, to be something extra under the hood that
the team who build that has used, however from the way he write that, I
always get the idea they are not really happy with it, and therefore they
changed it for the next version. However, you can give it a try.
Cor
Hmm. I tried the following scheme
dim connString as String=myConnectionString
dim conn as SQLClient.SQLConnection
dim SQLDA as SQLClient.SQLDataAdapter
if len(conn.ConnectionString)=0 then
conn=New SqlClient.SQLConnection(connString)
SQLDA.SelectCommand.Connection=conn
end if
conn.Open
SQLDA.Fill(ds)
conn.Close
The results were the same as when I simply used
SQLDA.Fill(ds)
and allowed the DataAdapter to implicitly open and close the connection.
--
Pat
"Cor Ligthert" wrote: Hi Pat,
Angel says always to use dispose for the connection instead the close with more than 100 connections.
There seems, with the connection, to be something extra under the hood that the team who build that has used, however from the way he write that, I always get the idea they are not really happy with it, and therefore they changed it for the next version. However, you can give it a try.
Cor
Hi Pat,
I do not if it helps, however what I meant in my last message was to change
it in (see the last row inline). dim connString as String=myConnectionString dim conn as SQLClient.SQLConnection dim SQLDA as SQLClient.SQLDataAdapter
if len(conn.ConnectionString)=0 then conn=New SqlClient.SQLConnection(connString) SQLDA.SelectCommand.Connection=conn end if conn.Open SQLDA.Fill(ds)
conn.Dispose
Can you try it?
Cor
Woops. In fact I meant to write conn.Dispose. This seems to have no effect. I have also tried altering the Connection Timeout and the Max Pool Size in the connection string. Changing the Connection Timeout produces no discernable effect, but changing the Max Pool Size produces predictable results: increasing the Max Pool Size increases the number of connections I can make before the whole thing blows up, decreasing it does the opposite. Still, this does not actually solve the problem, it merely covers it up for a little while longer.
Also, as I said before, the whole thing is wrapped up in a Class I call DataAccessComponent. It does not seem to help if I instantiate a New DataAccessComponent each time I want to connect to the datasource.
Clearly I am grabbing connections out of the pool and not realsing them back into it, but how? Doesn't DISPOSE release them? If not, wouldn't Disposing the DataAccessComponent release them? What else can I dispose :-).
Thanks,
--
Pat
"Cor Ligthert" wrote: Hi Pat,
I do not if it helps, however what I meant in my last message was to change it in (see the last row inline).
dim connString as String=myConnectionString dim conn as SQLClient.SQLConnection dim SQLDA as SQLClient.SQLDataAdapter
if len(conn.ConnectionString)=0 then conn=New SqlClient.SQLConnection(connString) SQLDA.SelectCommand.Connection=conn end if conn.Open SQLDA.Fill(ds) conn.Dispose
Can you try it?
Cor
Hi Pat
Can you change it to this, I see for your other commands no reasons and I
see as well your SQL string anymore in your code. Bassicly is this all you
need.
\\\
dim conn as New SQLClient.SQLConnection(myConnectionString)
dim SQLDA as new SQLClient.SQLDataAdapter(sqlstring, conn)
conn.Open
SQLDA.Fill(ds)
conn.Dispose
/// Woops. In fact I meant to write conn.Dispose. This seems to have no
effect. I have also tried altering the Connection Timeout and the Max Pool
Size in the connection string. Changing the Connection Timeout produces no
discernable effect, but changing the Max Pool Size produces predictable
results: increasing the Max Pool Size increases the number of connections I
can make before the whole thing blows up, decreasing it does the opposite.
Still, this does not actually solve the problem, it merely covers it up for
a little while longer. Also, as I said before, the whole thing is wrapped up in a Class I call
DataAccessComponent. It does not seem to help if I instantiate a New
DataAccessComponent each time I want to connect to the datasource. Clearly I am grabbing connections out of the pool and not realsing them
back into it, but how? Doesn't DISPOSE release them? If not, wouldn't
Disposing the DataAccessComponent release them? What else can I dispose :-). Thanks,
-- Pat
dim connString as String=myConnectionString dim conn as SQLClient.SQLConnection dim SQLDA as SQLClient.SQLDataAdapter
if len(conn.ConnectionString)=0 then conn=New SqlClient.SQLConnection(connString) SQLDA.SelectCommand.Connection=conn end if conn.Open SQLDA.Fill(ds) conn.Dispose
Can you try it?
Cor
Thanks Cor.
Yes, this is much more streamlined. As it turns out, I solved my problem, and it had nothing to do with any of this. I went to the Project Properties>>Configuration Properties page and unchecked SQL Server debugging. Apparently, with SQL Server debugging enabled, the connections are not returned to the pool properly regardless of disposal. This seemed to clear up my problem, though it probably will have some other unintended side-effects :-). Thanks again for the help.
Pat
--
Pat
"Cor Ligthert" wrote: Hi Pat
Can you change it to this, I see for your other commands no reasons and I see as well your SQL string anymore in your code. Bassicly is this all you need.
\\\ dim conn as New SQLClient.SQLConnection(myConnectionString) dim SQLDA as new SQLClient.SQLDataAdapter(sqlstring, conn) conn.Open SQLDA.Fill(ds) conn.Dispose ///
Woops. In fact I meant to write conn.Dispose. This seems to have no effect. I have also tried altering the Connection Timeout and the Max Pool Size in the connection string. Changing the Connection Timeout produces no discernable effect, but changing the Max Pool Size produces predictable results: increasing the Max Pool Size increases the number of connections I can make before the whole thing blows up, decreasing it does the opposite. Still, this does not actually solve the problem, it merely covers it up for a little while longer. Also, as I said before, the whole thing is wrapped up in a Class I call
DataAccessComponent. It does not seem to help if I instantiate a New DataAccessComponent each time I want to connect to the datasource. Clearly I am grabbing connections out of the pool and not realsing them
back into it, but how? Doesn't DISPOSE release them? If not, wouldn't Disposing the DataAccessComponent release them? What else can I dispose :-). Thanks,
-- Pat
> dim connString as String=myConnectionString > dim conn as SQLClient.SQLConnection > dim SQLDA as SQLClient.SQLDataAdapter > > if len(conn.ConnectionString)=0 then > conn=New SqlClient.SQLConnection(connString) > SQLDA.SelectCommand.Connection=conn > end if > conn.Open > SQLDA.Fill(ds) conn.Dispose
Can you try it?
Cor
I think I see whay you might want to return the dataset from the update method. If you have checked the "Refresh the DataSet" option in the Advanced SQL Generation Options of the Data Adapter coniguration wizard, I believe returning the dataset will allow you to retrieve the (possibly) new PK values. I haven't verified this yet, but it makes sense.
--
Pat
"Cor Ligthert" wrote: Hi Pat,
See this text on the page I give the link beneath it. Note that the code does not explicitly open and close the Connection. The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it will also close the connection when Fill is finished. This can simplify your code when dealing with a single operation such as a Fill or an Update. However, if you are performing multiple operations that require an open connection, you can improve the performance of your application by explicitly calling the Open method of the Connection, performing the operations against the data source, then calling the Close method of the Connection. You should strive to keep connections to the data source open for a minimal amount of time to free up the resource to be used by other client applications.
http://msdn.microsoft.com/library/de...ataadapter.asp
As well I looked at your update just to make you aware on it.
Public Function UpdateOrganizations(ByVal organizationChanges _ As dsNutmeg) As dsNutmeg If Not (organizationChanges Is Nothing) Then SQLDAtblOrganization.Update(organizationChanges) Return organizationChanges Else Return Nothing End If End Function In my opinion is that the same as Public Sub UpdateOrganizations(ByVal organizationChanges _ AS dsNutMeg) If Not (organizationChanges Is Nothing) Then SQLDAtblOrganization.Update(organizationChanges) End Sub
Cor This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Pietje de kort |
last post by:
Hello all,
I am trying to build a class that does something, and may timeout while
doing so. Ofcouse I want to be a bit elegant, so I came up with the code
found below. Problem is, I can't catch...
|
by: Anders Borum |
last post by:
Hello!
I am working on improving my threading skills and came across a question.
When working with the ReaderWriterLock class, I am getting an unhandled
exception if I acquire a WriterLock with...
|
by: ArunPrakash |
last post by:
Hi,
I want to implement a method which will poll database for messages.
But i want to provide a method that will specify a timeout until which
it will be polled and after that it will throw an...
|
by: Jason |
last post by:
Hi all
I get the following error when executing a rather intense stored procedure
from an ASPX page.
I have tried:
- Increasing timeouts on IIS 5.0 (all areas that even mention timeout)
- use...
|
by: Chris Langston |
last post by:
I have a Web Server running IIS 5 or 6 on Windows 2K and Windows 2003 Server
that is experiencing strange shutdown problems. We are using ASP.NET v1.1
and our application is written in VB.NET
...
|
by: Nils Magnus Englund |
last post by:
Hi,
I've made a HttpModule which deals with user authentication. On the first
request in a users session, it fetches data from a SQL Server using the
following code:
using (SqlConnection...
|
by: VB Programmer |
last post by:
When I run my ASP.NET 2.0 web app on my dev machine it works perfect. When
I precomile it to my web deployment project and then copy the debug files to
my web server I get this problem when trying...
|
by: dadocsis |
last post by:
I have a custom VB.net application that runs a query against an SQL
server that takes more than 3 minutes. Right now after 3 minutes it
times out. So far after doing my research I have changed the...
|
by: Nick Craig-Wood |
last post by:
Did anyone write a contextmanager implementing a timeout for
python2.5?
I'd love to be able to write something like
with timeout(5.0) as exceeded:
some_long_running_stuff()
if exceeded:...
|
by: Scorpion657 |
last post by:
Hey I really need help.
I have a Website coded using ASP.NET and VB and for some reason, i'm getting the following error when I try to upload or access a large file which is stored in the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |