Datareader fails ... ???

[GilbertLiddell]

Hi,

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

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQL [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection("server=192.168.1.10;uid=sa;pwd=;database=ebiz2000Q")[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataReader[/SIZE]
[SIZE=2]strSQL = "Select * from v_ebiz_queue"[/SIZE]
[SIZE=2]myCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand(strSQL, myConnection)[/SIZE]
[SIZE=2]myConnection.Open()[/SIZE]
[SIZE=2]dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)[/SIZE]
[SIZE=2]dr.Read()[/SIZE]
[SIZE=2][COLOR=#008000]'do stuff[/COLOR][/SIZE]
[SIZE=2]dr.Close()[/SIZE]
[SIZE=2]myConnection.Close()[/SIZE]

The app hangs on the dr.close()
What the hell am I doing wrong here??

Cheers in advance, G

 

[Arg81]

You could adjust your code to get the error message trapped:

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

 

[Arg81]

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/ado.net/DoNotUseCommandBehaviorCloseConnectionWithUsing.aspx

Hope that helps

 

[GilbertLiddell]

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??

 

[cjard]

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 )

 

[GilbertLiddell]

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.

[SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][SIZE=2][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Counter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0
strSQL = "Select * from v_ebiz_queue"
myCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand(strSQL, myConnection)
myConnection.Open()
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
[INDENT][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] dr.Read()
[INDENT]Counter = Counter + 1
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] Counter = 3 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][INDENT][/SIZE][SIZE=2][COLOR=#0000ff]Exit[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]While
[/INDENT][/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/INDENT][/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE]
[/INDENT][SIZE=2]myConnection.Close()
[/SIZE][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE]

PS. If I remove everything to do with the data reader like this -

[SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][SIZE=2][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Counter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0
strSQL = "Select * from v_ebiz_queue"
myCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand(strSQL, myConnection)
myConnection.Open()
[INDENT][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE]
[/INDENT][SIZE=2]myConnection.Close()
[/SIZE][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE]

The connection opens and closes no problem. I bet it's something simple??

 

[cjard]

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 )