SqlDataReader - result set cached?

Question

When you use a SqlDataReader, is the return set completely determined by the ExecuteReader step, or can you influence what you get by writing to the source table(s) while reading? Here is an example in very rough pseudo code.

sc = new SqlCommand("select * from people order by last, first",db) ;
sdr = sc.ExecuteReader() ;

while (sdr.read())
{
    l = (string) sdr["last"] ;
    k = (string) sdr["key"] ;
    if (l.Equals("Adams")) 
    {
       sc2 = new SqlCommand("update people set last = @nm where key = @key") ;
       sc2.Parameters.Add(new SqlParameter("@nm", "Ziegler"));
       sc2.Parameters.Add(new SqlParameter("@key", k));
       sc2.ExecuteNonQuery() ;
    }
}

I've seen a lot of bad errors in other environments caused by writing to the table you are reading. Here record k gets bumped from the top of the list (Adams) to the bottom (Ziegler). I've assumed (ha!) that SqlDataReader is immune. True? False?

Answer 1

It depends on your transaction isolation level or other locking hints, but iirc by default reading from a table in sql server locks those records, and therefore the code you posted will either deadlock (sc2 will eventually timeout) or the updates will go into the the transaction log and none will be written until your reader is finished. I don't remember which off the top of my head.

Answer 2

One issue I see is that when the reader is open it owns the database connection, nothing else can use it while the reader is open. So the only way possible to do this is using a different database connection, and still it would depend on transaction level

Answer 3

If you want to assume that the read data is not altered by those updates, could you read the data into a temporary object container, and then after all the reading is done, then do your updates? It would make the issue moot.

Of course, I did find the question interesting from a "how does this really work" standpoint.

Answer 4

I think its immune as soon as you executeReader you can kill the original SQL connection used for that reader, and then you can still read through the Datareader...

I expect its get stored in memory when you run the ExecuteReader command, then gets dumped when you have finshed reading through it.

ps. In one of my recent project i do exactly that.

Edit (Obviously i'm missing something here though it is a ODBC connection i'm using - Code below)

Public Sub ExecuteReader(ByVal comAuto As OdbcCommand, _
                                ByRef drAuto As OdbcDataReader, _
                                ByVal strSQL As String)

 ' Create the connection
 conAuto = New OdbcConnection(Connection in here)

 ' Create the command
 comAuto = New OdbcCommand(SQL, conAuto)

 ' Open the connection
 conAuto.Open()

 ' Execute the SQL against the database in the connection
 dr = comAuto.ExecuteReader


    Catch ex As Exception ' Catch the error

        ' Do Something

    Finally  ' Clean up objects
        conAuto = Nothing    
        comAuto  = Nothing

       ' HERE IS WHERE I INTIALLY THOUGHT THE CONNECTION WAS BEING TERMINATED AS I UNDERSTAND IT???

    End Try

 End Sub

I presume the connection is stored in the DataReader itself? As i pass the DataReader back through the sub to the business layer. I then read through that datareader even though i have killed the OdbcConnection and OdbcCommand???

---

First of all, a direct adaption of your code:

''//no need to pass in command - you're building a new one
Public Sub ExecuteReader(ByRef dr As OdbcDataReader, ByVal SQL As String)

    ''// Create the connection
    Using conAuto As New OdbcConnection(Connection in here), _
          comAuto As New OdbcCommand(SQL, conAuto)

        ''// Open the connection
        conAuto.Open()

        ''// Execute the SQL against the database in the connection
        dr = comAuto.ExecuteReader

    End Using 
    ''//Connection is closed here.  Note that your datareader is now worthless
End Sub

Because closing the connection like this generally invalidates your datareader, I usually use a datatable in 3Tier/nTier code. Also, that you would pass the SQL data to the method as a string troubles me - it indicates that you might have sql injection issues. My methods generally look more like this:

''// note that this function is private.  This will enforce a complete separation of 
''// data layer code from other code
Private Function getConnection() As OdbcConnection
    Static connString as String = "read this from encrypted config file"
    Return New OdbcConnection(connString)
End Function

''// Note the strongly typed parameters
Public Function GetSomeReportData(ByVal SomeParameterValue As String, ByVal OtherParameterValue As Integer) As DataTable

    Dim sql As String = "SELECT * FROM MyTable WHERE Column1= ? AND Column2 = ?"
    Dim result As New DataTable()

    Using cn = getConnection(), cmd As New SqlCommand(sql, cn)
        cmd.Parameters.Add("OneParameter", OdbcType.VarChar, 100).Value = SomeParameterVale
        cmd.Parameters.Add("OtherParameter", OdbcType.Int).Value = OtherParameterValue

        cn.Open()

        Using rdr = cmd.ExecuteReader()
            result.Load(rdr)
        End Using
    End Using

    Return result
End Function

If you really want to use a datareader, you can use iterator blocks in C# to still return individual datareader results in a handy IEnumerable and still keep your connection safely enclosed in a using block.

Answer 5

If you want to do updates while you're iterating on the query results you could read it all into a DataSet.

I know you didn't ask about this, and I also know this is pseudo-code, but be sure to wrap your sc, sdr, and sc2 variables in using () statements to ensure they're disposed properly.