Thoughts on ADO.Net DataReader

[stingrae]

I was wondering what your thoughts were on the ADO.Net DateReader.

One thing I HATE is that you cannot have a DataReader open, and yet at the same time perform other operations on the database.

For example, if you've got a data areader open and whilst open you attempt an insert on a (completely different) table, you get the following error:

Additional information: There is already an open DataReader associated with this Connection which must be closed first.

you could do this in ADO 2.x

tsk tsk tsk

[Edneeis]

Usually it seems that you wouldn't need to do both at the sametime, but if you do you just have to use a different connection object. The Datareader locks the connection object, since its a forward-only, read-only reader, but you should be able to do other things to the database as long as you aren't using the same connection object.

[Pirate]

What's the big problem if you closed datareader connection after you're done . .

[stingrae]

guys, the problem is this. say you have a datareader going through a group of records, and depending upon the values in one (or more) of the fields, you wish to run a simple update query to set a flag on another table. something like:

do while DataReader.Read  = True
   If DataReader(Field) = ValueX then
       Run CommandX
   End If
loop
DataReader.Close

this will fail at the Run Command Line. The way I get around this is by storing values into an array, and then running all the commands. It is annoying though, because the progress bars and other things don't look so smooth, plus it is a bit of extra coding and to top it off, is a pain in the proverbial!

just my $0.02!

[pvb]

You're gonna lose a big benefit of the datareader if you're doing operations while looping through the resultset(because you're holding that connection open and wasting resources) so why not load up a dataset, and spare your connection for use on your updates or for other clients who need a connection?

for example, i have a State table and a State2 table, both identical in structure:

Create Table State
(
		StateCode char ( 2 ) not null
	,	StateName varchar ( 50 ) not null
)

The State table has, you guessed it, a list of the states. The second one(State2) is empty. So the following just loops thru and inserts each row in the State table into the State2 table:

Imports System.Data
Imports System.Data.SqlClient
 
Module Module1
 
    Sub Main()
        Dim connString As String = "user id=sa;password=sa;database=scratch;server=DeathAngel;"
        Dim cn As New SqlConnection(connString)
        Dim ds As New DataSet("States")
        Dim cmdText As String = "Select StateCode, StateName From State"
        Dim cmd As New SqlCommand(cmdText, cn)
        Dim da As New SqlDataAdapter(cmd)
        Try
            cn.Open()
            da.Fill(ds)
            For Each dataRow As DataRow In ds.Tables(0).Rows
                Add(dataRow("StateCode").ToString(), dataRow("StateName").ToString(), cn)
            Next
        Finally
            If Not da Is Nothing Then
                da.Dispose()
            End If
            If Not cmd Is Nothing Then
                cmd.Dispose()
            End If
            If Not cn Is Nothing Then
                cn.Close()
                cn.Dispose()
            End If
        End Try
        Console.WriteLine("Done...")
        Console.ReadLine()
    End Sub
 
    Sub Add(ByVal stateCode As String, ByVal stateName As String, ByRef cn As SqlConnection)
        Dim cmdText As String = "Insert Into State2 ( StateCode , StateName ) Values " & _
            "( @StateCode , @StateName )"
        Dim cmd As New SqlCommand(cmdText, cn)
        cmd.Parameters.Add(New SqlParameter("@StateCode", stateCode))
        cmd.Parameters.Add(New SqlParameter("@StateName", stateName))
        cmd.ExecuteNonQuery()
    End Sub
End Module

The resultset here is only 51 rows, but what if your resultset was say 1000 rows. That means you're holding the connection open and locked for no one else(including yourself) to use.

Another solution if using the datareader is that important is to just open another connection for the updates.