microsoft.applicationblock.data 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.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.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
applicationblocks.

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

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

"Milsnips" <mi******@hotmail.comwrote in message
news:Oz**************@TK2MSFTNGP03.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.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.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(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
"Mark Fitzpatrick" <ma******@fitzme.comwrote in message
news:OC***************@TK2MSFTNGP06.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
applicationblocks.

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

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

"Milsnips" <mi******@hotmail.comwrote in message
news:Oz**************@TK2MSFTNGP03.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.applicationblocks.data.sqlhelper.execut enonquery(configurationsettings.appsetting("db"),c ommandtype.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.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.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.ApplicationBlocks.Data.SqlHelper.Execute NonQuery, 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*******@yahoo.nospammin.comwrote in message
news:4C**********************************@microsof t.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.applicationblocks.data.sqlhelper.execut enonquery(configurationsettings.appsetting("db"),c ommandtype.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.ApplicationBlocks.Data.SqlHelper.Execute NonQuery, 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*******@yahoo.nospammin.comwrote in message
news:4C**********************************@microsof t.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.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.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*******@yahoo.nospammin.comwrote in message
news:38**********************************@microsof t.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.ApplicationBlocks.Data.SqlHelper.Execut eNonQuery, 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*******@yahoo.nospammin.comwrote in
message
news:4C**********************************@microso ft.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.applicationblocks.data.sqlhelper.execut enonquery(configurationsettings.appsetting("db"),c ommandtype.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