timeout exception

[pmcguire]

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 DataAccessCompo nent. This class inherits from System.Componen tModel.Componen t 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 GetOrganization s(
Optional ByVal anOrganizationI D As Object = Nothing) As dsNutme
Dim organizations As New dsNutme

organizations.E nforceConstrain ts = Fals
If Not anOrganizationI D Is Nothing The
SQLDAtblOrganiz ation.SelectCom mand.Parameters ("@Organization ID").Value = CType(anOrganiz ationID, Long
Els
SQLDAtblOrganiz ation.SelectCom mand.Parameters ("@Organization ID").Value = System.DBNull.V alu
End I
SQLDAtblOrganiz ation.Fill(orga nizations
Return organization
End Functio

Public Function UpdateOrganizat ions(ByVal organizationCha nges
As dsNutmeg) As dsNutme
If Not (organizationCh anges Is Nothing) The
SQLDAtblOrganiz ation.Update(or ganizationChang es
Return organizationCha nge
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 DataAccessCompo nen
dim ds as new dsNutme

ds.Merge(mDAC.G etOrganizations ()

' 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

[Cor Ligthert]

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 UpdateOrganizat ions(ByVal organizationCha nges _
As dsNutmeg) As dsNutmeg
If Not (organizationCh anges Is Nothing) Then
SQLDAtblOrganiz ation.Update(or ganizationChang es)
Return organizationCha nges
Else
Return Nothing
End If
End Function

In my opinion is that the same as
Public Sub UpdateOrganizat ions(ByVal organizationCha nges _
AS dsNutMeg)
If Not (organizationCh anges Is Nothing) Then
SQLDAtblOrganiz ation.Update(or ganizationChang es)
End Sub

Cor

[pmcguire]

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.Invalid OperationExcept ion' 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 GetOrganization s(
Optional ByVal anOrganizationI D As Object = Nothing) As dsNutme
Dim organizations As New dsNutme

organizations.E nforceConstrain ts = Fals
If Not anOrganizationI D Is Nothing The
SQLDAtblOrganiz ation.SelectCom mand.Parameters ("@Organization ID").Value = CType(anOrganiz ationID, Long
Els
SQLDAtblOrganiz ation.SelectCom mand.Parameters ("@Organization ID").Value = System.DBNull.V alu
End I
SQLConnNutmegDa ta.Open(
SQLDAtblOrganiz ation.Fill(orga nizations
SQLConnNutmegDa ta.Close(
Return organization
End Functio

I've tried using PerformanceCoun ters 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

[Cor Ligthert]

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

[pmcguire]

Hmm. I tried the following scheme

dim connString as String=myConnec tionString
dim conn as SQLClient.SQLCo nnection
dim SQLDA as SQLClient.SQLDa taAdapter

if len(conn.Connec tionString)=0 then
conn=New SqlClient.SQLCo nnection(connSt ring)
SQLDA.SelectCom mand.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

[Cor Ligthert]

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=myConnec tionString
dim conn as SQLClient.SQLCo nnection
dim SQLDA as SQLClient.SQLDa taAdapter

if len(conn.Connec tionString)=0 then
conn=New SqlClient.SQLCo nnection(connSt ring)
SQLDA.SelectCom mand.Connection =conn
end if
conn.Open
SQLDA.Fill(ds)

conn.Dispose

Can you try it?

Cor

[pmcguire]

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 DataAccessCompo nent. It does not seem to help if I instantiate a New DataAccessCompo nent 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 DataAccessCompo nent 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=myConnec tionString
dim conn as SQLClient.SQLCo nnection
dim SQLDA as SQLClient.SQLDa taAdapter

if len(conn.Connec tionString)=0 then
conn=New SqlClient.SQLCo nnection(connSt ring)
SQLDA.SelectCom mand.Connection =conn
end if
conn.Open
SQLDA.Fill(ds)

conn.Dispose

Can you try it?

Cor

[Cor Ligthert]

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.SQLCo nnection(myConn ectionString)
dim SQLDA as new SQLClient.SQLDa taAdapter(sqlst ring, 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 DataAccessCompo nent. It does not seem to help if I instantiate a New
DataAccessCompo nent 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 DataAccessCompo nent release them? What else can I dispose :-).
Thanks,

--
Pat

dim connString as String=myConnec tionString
dim conn as SQLClient.SQLCo nnection
dim SQLDA as SQLClient.SQLDa taAdapter

if len(conn.Connec tionString)=0 then
conn=New SqlClient.SQLCo nnection(connSt ring)
SQLDA.SelectCom mand.Connection =conn
end if
conn.Open
SQLDA.Fill(ds)

conn.Dispose

Can you try it?

Cor

[pmcguire]

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>>Con figuration 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.SQLCo nnection(myConn ectionString)
dim SQLDA as new SQLClient.SQLDa taAdapter(sqlst ring, 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

DataAccessCompo nent. It does not seem to help if I instantiate a New
DataAccessCompo nent 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 DataAccessCompo nent release them? What else can I dispose :-).

Thanks,

--
Pat

> dim connString as String=myConnec tionString
> dim conn as SQLClient.SQLCo nnection
> dim SQLDA as SQLClient.SQLDa taAdapter
>
> if len(conn.Connec tionString)=0 then
> conn=New SqlClient.SQLCo nnection(connSt ring)
> SQLDA.SelectCom mand.Connection =conn
> end if
> conn.Open
> SQLDA.Fill(ds)
conn.Dispose

Can you try it?

Cor

[pmcguire]

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 UpdateOrganizat ions(ByVal organizationCha nges _
As dsNutmeg) As dsNutmeg
If Not (organizationCh anges Is Nothing) Then
SQLDAtblOrganiz ation.Update(or ganizationChang es)
Return organizationCha nges
Else
Return Nothing
End If
End Function

In my opinion is that the same as
Public Sub UpdateOrganizat ions(ByVal organizationCha nges _
AS dsNutMeg)
If Not (organizationCh anges Is Nothing) Then
SQLDAtblOrganiz ation.Update(or ganizationChang es)
End Sub

Cor