MSDE/ADO.Net

[Shane Brodie]

Either MSDE or ADO.Net hangs when I rapidly run a series of identical
database reads. The only useful message I get is
System.InvalidOperationException which tells me I have a timeout somewhere.
But, where?

Here is the database related code that is being executed.

'***** SNIP *****

objCN.Open()

objCmd.Connection = objCN

objCmd.CommandType = CommandType.Text

objReader = objCmd.ExecuteReader

If objReader.HasRows Then

objReader.Read()

'ignore changed events whie loading

Loading = True

tctOrder.Tag = objReader.GetValue(0)

tabCustomer.Tag = objReader.GetValue(1)

txtSumOrderID.Text = OrderID

txtSumOrderStatus.Text = objReader.GetString(7)

txtSumSubmissionDate.Text = Format(objReader.GetDateTime(3), "dddd MMMM dd,
yyyy")

......

objReader.Close()

objReader = Nothing

objCmd.Dispose()

objCN.Close()

objCN.Dispose()

' ***** END SNIP *****



Then ...

'**** SNIP *****

Try

objCN.Open()

objCmd.Connection = objCN

objCmd.CommandType = CommandType.Text

objReader = objCmd.ExecuteReader

If objReader.HasRows Then

While objReader.Read

nodGroup = nodRoot.Nodes.Add(objReader.GetString(1))

nodGroup.Tag = "GRP" & objReader.GetValue(0)

nodGroup.Expand()

End While

End If

objReader.Close()

objReader = Nothing

Catch sqlEx As SqlClient.SqlException

MsgBox(sqlEx.ToString)

Catch ex As Exception

MsgBox(ex.ToString)

End Try

Try

objCmd = New SqlClient.SqlCommand("SELECT * FROM vwGroupOrderItems WHERE
OrderID = " & CType(tctOrder.Tag, Long) & ";")

objCmd.Connection = objCN

objCmd.CommandType = CommandType.Text

objReader = objCmd.ExecuteReader

If objReader.HasRows Then

While objReader.Read

nodGroup = GetGroup("GRP" & objReader.GetValue(1))

nodItem = nodGroup.Nodes.Add(objReader.GetString(6) & " - " &
objReader.GetString(4))

nodItem.Tag = "ITM" & objReader.GetValue(0)

End While

End If

Catch sqlEx As SqlClient.SqlException

MsgBox(sqlEx.ToString)

Catch ex As Exception

MsgBox(ex.ToString)

End Try

tvwOrderDetail.EndUpdate()

tvwOrderDetail.ExpandAll()



'***** END SNIP *****

Regards



Shane Brodie

 

[Marina]

You have a ton of code here, with lot's of spaces in between. I would
suggest you post only the snippet where the error occurrs, and tells us the
line where it occurrs.

What I do notice is that, it doesn't seem necessary to do the 'If
objreader.HasRows' test before looping through the results. Your code
doesn't care if there are any rows - it just cares that for every row there
is, something happens. If there aren't any, nothing happens. So that is
just an extra test that doesn't do anything.

I do not think that InvalidOperationException is a timeout issue - I'm not
sure why you do. That is typically a SqlException, with a message that says
it's a timeout problem.

One possible reason why this would happen, is that you are trying to read
from a data reader without testing to make sure there is a row (you have a
code snippet where you do a .Read, followed by value gets).

But to get a better response, you need to include only the relevant code,
and say which line is causing the problem.

 

[Shane Brodie]

You are absolutely right:

I have a trace of the error as follows:

3/10/2004 8:21:56 AM cboDelProv_SelectedIndexChanged. Exception:
System.InvalidOperationException: 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.
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DMSF.frmMain.cboDelProv_SelectedIndexChanged(Object sender, EventArgs
e) in C:\Projects\.Net\DMSF\DMSF\frmMain.vb:line 2832

As indicated, the error is occurring on a
System.Data.SqlClient.SqlConnection.Open() call. I have reviewed all of my
database code. I open my connections, do the work, close the connection and
dispose of the connection any command objects and any readers(objReader =
Nothing).

In my Try ...Catch ... End Try blocks wrapping each and every database
operation, I specifically look for SQLExceptions, ie:

Catch ex As Exception
MsgBox(ex.ToString)
Trace.Write(Now & " ROUTINE NAME. Exception: " & (ex.ToString))
Catch sqlEx As SqlClient.SqlException
MsgBox(sqlEx.ToString)
Trace.Write(Now & " ROUTINE NAME. SQL Exception: " & (sqlEx.ToString))

The problem appears to be related to connection pooling. I appear to be
running out of connections (even though I close/dispose of my connections
after each use) I am using the MSDE SP3 database locally on a Windows XP
Pro SP1 system.

The problem does not appear to be database throttling, as the lockup is
permanent until I kill and restart the application, and there is no telltale
entry in the application logs.

When I look at the processes running on MSDE, there are 2-3 related to my
application running when the error occurs, and all are stutus="sleeping"
with command = "AWAITING COMMAND". They do not disappear until the
application terminates.

Any insight would be greatly appreciated.

Regards

Shane

 

[Marina]

Ok, I see. So this isn't a command timeout, but a pooling problem.

It looks like your second snippet doesn't close its connection, or maybe you
just didn't post that part.

Also, no need to dispose connections or other objects, closing the
connection should be enough to release it back into the pool.

Other then making sure that all connections get closed, I am not sure what
else you can do to fix this. I would recommend searching google groups, as
I'm sure others have encountered the same kind of problem, perhaps one of
them found a fix.