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

Discussion in 'ASP .Net' started by Milsnips, Nov 16, 2006.

  1. Milsnips

    Milsnips Guest

    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
    Milsnips, Nov 16, 2006
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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
    >
    >
    Mark Fitzpatrick, Nov 16, 2006
    #2
    1. Advertising

  3. Milsnips

    Milsnips Guest

    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" <> wrote in message
    news:OCM$%...
    > 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" <> wrote in message
    > news:...
    >> 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
    >>
    >>

    >
    >
    Milsnips, Nov 16, 2006
    #3
  4. RE: microsoft.applicationblock.data DLL (.net2.0) causing sql timeout

    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.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
    >
    >
    >
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Nov 16, 2006
    #4
  5. Milsnips

    Milsnips Guest

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

    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
    "Peter Bromberg [C# MVP]" <> wrote in message
    news:...
    > 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.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
    >>
    >>
    >>
    Milsnips, Nov 17, 2006
    #5
  6. Re: microsoft.applicationblock.data DLL (.net2.0) causing sql time

    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.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
    > "Peter Bromberg [C# MVP]" <> wrote in message
    > news:...
    > > 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.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
    > >>
    > >>
    > >>

    >
    >
    >
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Nov 17, 2006
    #6
  7. Milsnips

    Milsnips Guest

    Re: microsoft.applicationblock.data DLL (.net2.0) causing sql time

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

    regards,
    Paul

    "Peter Bromberg [C# MVP]" <> wrote in message
    news:...
    > 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.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
    >> "Peter Bromberg [C# MVP]" <> wrote in
    >> message
    >> news:...
    >> > 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.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
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
    Milsnips, Nov 17, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Charles A. Lackman
    Replies:
    1
    Views:
    1,340
    smith
    Dec 8, 2004
  2. SpamProof
    Replies:
    0
    Views:
    544
    SpamProof
    Oct 21, 2003
  3. Nikhil Patel

    using UIProcess applicationblock

    Nikhil Patel, Oct 18, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    626
    Nikhil Patel
    Oct 18, 2004
  4. Pratik Gupte
    Replies:
    1
    Views:
    620
  5. Replies:
    0
    Views:
    544
Loading...

Share This Page