microsoft.appli cationblock.dat a DLL (.net2.0) causing sql timeout error

[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.appli cationblocks.da ta.sqlhelper.ex ecutenonquery(c onfigurationset tings.appsettin g("db"),command type.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

[Mark Fitzpatrick]

Paul,
Give the Enterprise Library a try. I found the data block in that to
be much more stable. It's very similar and more neutral than the older
applicationbloc ks.

http://practices.gotdotnet.com/projects/entlib

--
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

"Milsnips" <mi******@hotma il.comwrote in message
news:Oz******** ******@TK2MSFTN GP03.phx.gbl...

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.appli cationblocks.da ta.sqlhelper.ex ecutenonquery(c onfigurationset tings.appsettin g("db"),command type.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

[Milsnips]

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(S haredFunctions. db)
Dim cmd As New SqlCommand(sql. ToString, cn)
Try
cn.Open()
cmd.ExecuteNonQ uery()
Catch ex As SqlException
'error occured
Finally
cmd.Dispose()
cmd = Nothing
If cn.State = Data.Connection State.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
"Mark Fitzpatrick" <ma******@fitzm e.comwrote in message
news:OC******** *******@TK2MSFT NGP06.phx.gbl.. .

Paul,
Give the Enterprise Library a try. I found the data block in that
to be much more stable. It's very similar and more neutral than the older
applicationbloc ks.

http://practices.gotdotnet.com/projects/entlib

--
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

"Milsnips" <mi******@hotma il.comwrote in message
news:Oz******** ******@TK2MSFTN GP03.phx.gbl...

>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.appl icationblocks.d ata.sqlhelper.e xecutenonquery( configurationse ttings.appsetti ng("db"),comman dtype.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

[Peter Bromberg [C# MVP]]

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

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


"Milsnips" wrote:

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.appli cationblocks.da ta.sqlhelper.ex ecutenonquery(c onfigurationset tings.appsettin g("db"),command type.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

[Milsnips]

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.Appli cationBlocks.Da ta.SqlHelper.Ex ecuteNonQuery, 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
"Peter Bromberg [C# MVP]" <pb*******@yaho o.nospammin.com wrote in message
news:4C******** *************** ***********@mic rosoft.com...

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

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


"Milsnips" wrote:

>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.appl icationblocks.d ata.sqlhelper.e xecutenonquery( configurationse ttings.appsetti ng("db"),comman dtype.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

[Peter Bromberg [C# MVP]]

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


"Milsnips" wrote:

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.Appli cationBlocks.Da ta.SqlHelper.Ex ecuteNonQuery, 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
"Peter Bromberg [C# MVP]" <pb*******@yaho o.nospammin.com wrote in message
news:4C******** *************** ***********@mic rosoft.com...

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

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


"Milsnips" wrote:

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.appli cationblocks.da ta.sqlhelper.ex ecutenonquery(c onfigurationset tings.appsettin g("db"),command type.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

[Milsnips]

Thanks for the reply peter, i'll give that a try.

regards,
Paul

"Peter Bromberg [C# MVP]" <pb*******@yaho o.nospammin.com wrote in message
news:38******** *************** ***********@mic rosoft.com...

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


"Milsnips" wrote:

>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.Appl icationBlocks.D ata.SqlHelper.E xecuteNonQuery, so i dont
have
an object to close/dispose.

Also, i did the full code with sqlconection, sqlcommand, and so on, did
executenonquer y, 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
"Peter Bromberg [C# MVP]" <pb*******@yaho o.nospammin.com wrote in
message
news:4C******* *************** ************@mi crosoft.com...

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

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


"Milsnips" wrote:

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.appl icationblocks.d ata.sqlhelper.e xecutenonquery( configurationse ttings.appsetti ng("db"),comman dtype.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