Post reply

More than one operation on a connection at the same time

  • January 26, 2004 at 8:36 am

    #101853

    Hello listers,

    Don't know if I'm in the rigth forum...

    Anybody knows why in ADO.NET we cannot use an object Connection to do more than one operation in the database at the same time e.g. while reading a DataReader object doing an update on a table?

    This limitation was also there on DB-Library.

    Is it a limitation from those API or is it from SQL Server itself?

    We don't have this restriction on Oracle...

    Best regards,

    Carl

  • January 27, 2004 at 8:10 am

    #491762

    Well, this is a "brand new" feature (not a bug) in ADO.NET!  This is not a limitation of SQLServer.  In ADO 2.x, I used to use the strategy of making one Connection Object and use it for all Recordsets.  However, the strategy has now changed with ADO.NET.  There are a few reasons for this.

    In ADO, it was optional for the Connection Object to use a Transaction:

    Dim adoCon As ADODB.Connection

    Dim bFail As Boolean

    adoCon.BeginTrans

    'Do Something, if it falis, Rollback Transaction

    If (bFail) Then adoCon.RollbackTrans Else adoCon.CommitTrans

    However, I believe that now, the SqlClient.SqlConnection object uses Transactions internally, and they cannot be turned off.  The reason being is that (according to Documentation in MSDN), only ONE SqlClient.SqlDataReader can be used on a Connection at one time.  What may not be apparently clear, is that even when you are using a SqlClient.SqlDataAdapter to fill a DataSet, internally it's using a SqlDataReader!

    The only way around this is to use a Different Connection Object in EACH and EVERY routine that requires a connection to the Database.  No more "One Connection Object for every operation", no more "One shot, one kill", no more "There can be only one Highlander!" .

    If you're familiar with Connection Pooling, you'll realize that there is no decrease in performance in opening more than one Connection Object at a time...they'll all use the same connection pool (As long as the Connection String is EXACTLY the same for all of them!).  So, instantiating a new Object in each routine may be tedious as a programmer...but it's the only way in ADO.NET.  If it gets too tedious in your Code, create a wrapper class (DataService Class) for Data operations (i.e. DataService.Execute("Update [Table] Set Field = 1"))

  • January 27, 2004 at 8:35 am

    #491766

    Our concern is not about the decrease of performance related to the numerous connection needed.

    Our concern is more about locking issues: If I open SqlDataReader on one table because I want to update one column of the same table I will be blocked since I must use another connection.

    Carl

  • January 27, 2004 at 8:42 am

    #491768

    So why not just Execute an Update Statement instead of using a DataReader?  It seems like your using the wrong object for the job...maybe you need to explain a little more.

  • January 27, 2004 at 8:55 am

    #491772

    Thank's for your replies Tymberwyld, all of this is related to the way our own database acces layer is implemented.

    Best regards,

    Carl

  • March 30, 2004 at 8:47 am

    #501086

    Hello Everyone,

    M.A.R.S. (Multiple Active Result Set) on one connection comming with ADO.NET 2.0 should resolve my concerns.

    "The new version of ADO.NET shipped with Whidbey has some great features and makes our developers life a little bit easier. Here are some features of ADO.NET v2.0:

    Multiple Active Result Sets (MARS) allows a connection to support multiple concurrent commands and multiple open sets of results. You can open a result set from a query, and then execute subsequent commands over the same connection while the previous one is still open and being accessed."

    For more information look at:

    http://weblogs.asp.net/klaus.aschenbrenner/archive/2003/12/28/46181.aspx

    Carl

     

  • March 30, 2004 at 8:51 am

    #501089

    Sounds interesting...no doubt it will come with a performance penalty

  • March 30, 2004 at 8:53 am

    #501091

    Hmm, what about using an XMLReader and the Command.ExecuteXMLReader instead?  I believe (but I'm not positive) that you can release the connection since once the xml is retrieved, all you're doing is reading an XML Doc, not the DB table (i.e. it's not using a cursor).  Remember to append the "FOR XML (AUTO, RAW, etc)" to your Select Command.

  • March 30, 2004 at 9:00 am

    #501094

    Tymberwild,

    If it is well implemented, it should decrease performance for sure, but it should not be human perceptible.

    Oracle behaves that way for a long time.

    Carl

  • March 30, 2004 at 9:02 am

    #501095

    Point well taken

  • March 30, 2004 at 9:12 am

    #501098

    Thanks for your comments on this thread Tymberwyld.

    Carl

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply