microsoft.applicationblock.data DLL (.net2.0) causing sql timeout error

  • Thread starter Thread starter Milsnips
  • Start date Start date
M

Milsnips

hi there,

i;m doing a loop of a few hundred records and inserting into database using
the MS data dll, and the following line

dim sql as string = "mysql code is here..."
microsoft.applicationblocks.data.sqlhelper.executenonquery(configurationsettings.appsetting("db"),commandtype.text,sql)

After a large number of records, i see its not closing the sql process, and
the process remain in "sleeping" mode.
How can i fix this?

thanks,
Paul
 
Ok, will give it a try

i just did another test with the my code, and instead of using the
application block, i created the connection and sqlcommand objects,
executenonquery, then closed, disposed and set both objects to nothing,

Dim cn As New SqlConnection(SharedFunctions.db)
Dim cmd As New SqlCommand(sql.ToString, cn)
Try
cn.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
'error occured
Finally
cmd.Dispose()
cmd = Nothing
If cn.State = Data.ConnectionState.Open Then cn.Close()
cn.Dispose()
cn = Nothing
End Try

after about approx. 150+ records, the error still occured, im trying to
import data from a CSV file via web that has up to 500-2000 records.

thanks,
Paul
 
Your post title is somewhat misleading - are you REALLY getting a sql
timeout, or are you just seeing the SQL process in Enterprise Manager
"Sleeping".
If #2, that is normal, and has nothing to do with timeouts.
As long as you are closing your ADO.NET SqlConnection object after each
"call" you should be fine.
Peter
 
Hi Peter,

I tried a number of ways, and what happens is it creates about 150 or so sql
process, and then throws the error saying connection pool max. reached.

What i'm using is the line,
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery, so i dont have
an object to close/dispose.

Also, i did the full code with sqlconection, sqlcommand, and so on, did
executenonquery, then closed and disposed all objects and it still appears
to be happening. Bear in mind, my web page uploads a CSV file, and on button
click, it reads in the file, and tries importing/updating about 2000 records
on one postback.

thanks,
Paul
 
OK. If you are doing 150, that's enough to bomb out the connection pool which
holds 100 by default. So what is happening is that you are creating new
connections but they aren't getting closed / disposed.

The SqlHelper ExecuteNonQuery method has a number of overloads, one of which
allows you to pass in your own SqlConnection. So in the line after the
ExecuteNonQuery, you should call the .Close() method on your connection, and
that should take care of it.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com
 
Back
Top