Results 1 to 7 of 7

Thread: Datareader fails ... ???

  1. #1
    GilbertLiddell is offline VB.NET Forum Newbie
    .NET Framework
    .NET 1.1 (VS 2003)
    Join Date
    May 2007
    Posts
    8
    Reputation
    0

    Datareader fails ... ???

    Hi,

    I have used the datareader countless times for returning data yet it's now giving me issues.

    Code:
    Dim strSQL AsString
    Dim myConnection AsNew SqlClient.SqlConnection("server=192.168.1.10;uid=sa;pwd=;database=ebiz2000Q")
    Dim myCommand AsNew SqlClient.SqlCommand
    Dim dr As SqlClient.SqlDataReader
    strSQL = "Select * from v_ebiz_queue"
    myCommand = New SqlClient.SqlCommand(strSQL, myConnection)
    myConnection.Open()
    dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    dr.Read()
    'do stuff
    dr.Close()
    myConnection.Close()
    The app hangs on the dr.close()
    What the hell am I doing wrong here??

    Cheers in advance, G

  2. #2
    Arg81's Avatar
    Arg81 is offline VB.NET Forum Idol
    .NET Framework
    .NET 2.0
    Join Date
    Mar 2005
    Location
    Midlands, UK
    Posts
    949
    Reputation
    308
    You could adjust your code to get the error message trapped:

    Code:
     
    TRY
       MyConnection.Open
       dr = myCommand.ExecuteReader(commandBehavior.CloseConnection)
       dr.Read()
    CATCH ex AS exception
       messagebox.show(ex.message)
    FINALLY
       dr.close()
       myConnection.close()
    END TRY
    I have no personal experience with dataReaders, but that should at least trap an error which you can post.

    On another note, there is a seperate forum for DataSets, DataReaders etc so no doubt the post "may" get moved there. Just a heads up

  3. #3
    Arg81's Avatar
    Arg81 is offline VB.NET Forum Idol
    .NET Framework
    .NET 2.0
    Join Date
    Mar 2005
    Location
    Midlands, UK
    Posts
    949
    Reputation
    308
    I think it's hanging because you are using .ExecuteReader(commandBehavior.CloseConnection) with an SQL statement.

    A quick 5 sec search on google returned this:

    It's customary for me to use using statements with disposable objects such as connection and file system objects. Doing so ensures that the objects will close and dispose of any valuable resources. I noticed that when you use a using statement on a connection object which is used by a command object to return a reader and the ExecuteReader method is passed the CommandBehavior.CloseConnection enumerated value, the result is a drop in performance by about 84%. This is, by using a CommandBehavior.CloseConnection strategy to close a connection whenever a reader closes along with a using statement to do the same, you are causing the CLR to have to clean up the same resources two different times. This is what causes performance to suffer. Using this method results in your application requiring approximately 84% more time to cleanup than if you just used one method or the other.
    there's a couple of examples on the page: http://www.dotnetfun.com/articles/ad...WithUsing.aspx

    Hope that helps

  4. #4
    GilbertLiddell is offline VB.NET Forum Newbie
    .NET Framework
    .NET 1.1 (VS 2003)
    Join Date
    May 2007
    Posts
    8
    Reputation
    0
    Hi thanks for the reply. Removing (commandBehavior.CloseConnection) has no effect and no exception is thrown.

    It's weird, if i remove dr.close and myconnection.close then the application runs ok, if either or both of dr.close and myconnection.close are in, then it just hangs.

    I don't want to leave out the dr.close and myconnection.close cos thats bad.

    Any other ideas??

  5. #5
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1716
    Quote Originally Posted by GilbertLiddell View Post
    It's weird, if i remove dr.close and myconnection.close then the application runs ok
    Umm.. you say "CommandBehavour.CloseConnection" meaning the connection will be closed when the reader reaches the end.. So read it to the end and then dont close it (because it's already closed )

  6. #6
    GilbertLiddell is offline VB.NET Forum Newbie
    .NET Framework
    .NET 1.1 (VS 2003)
    Join Date
    May 2007
    Posts
    8
    Reputation
    0
    Quote Originally Posted by cjard View Post
    Umm.. you say "CommandBehavour.CloseConnection" meaning the connection will be closed when the reader reaches the end.. So read it to the end and then dont close it (because it's already closed )
    So why would the below code hang on the myconnection.close()
    I've already closed the datareader.

    Code:
    Dim Counter AsInteger = 0
    strSQL = "Select * from v_ebiz_queue"
    myCommand = New SqlClient.SqlCommand(strSQL, myConnection)
    myConnection.Open()
    dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    
    While dr.Read()
    Counter = Counter + 1
    If Counter = 3 Then
    ExitWhile
    EndIf
    EndWhile
    myConnection.Close()
    PS. If I remove everything to do with the data reader like this -

    Code:
    Dim Counter AsInteger = 0
    strSQL = "Select * from v_ebiz_queue"
    myCommand = New SqlClient.SqlCommand(strSQL, myConnection)
    myConnection.Open()
    myConnection.Close()
    
    The connection opens and closes no problem. I bet it's something simple??

  7. #7
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1716
    Umm.. you say "CommandBehavour.CloseConnection" meaning the connection will be closed when the reader reaches the end.. So read it to the end and then dont close it (because it's already closed )

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Harvest time tracking