Managing DataReaders vs DataSets
A student prompted this post by asking about passing DataReaders from business objects, and what that means for managing connections, etc. I have a lot to say on the topic as it leads towards so many other subjects; I'm struggling to keep my points organized and concise. Let me take a stab at it . . .
DataReaders are attractive for speed purposes, according to conventional wisdom, but I'm confident in sharing that DataSets and DataTables are my standard approach for windows apps because 1) handling data requests asynchronously is easy in .Net making the performance gains not so evident (sometimes tilting in favor of DataSets!) and 2) avoiding DataReaders allows me to use a cleaner data-access layer in my apps that only exposes DataSets/DataTables instead of the trickier DataReader, thus making for a more consistent design.
Taking these points in reverse order, I say the DataReader is trickier because you must manage the connection carefully. A good data-access layer might use the following to generate a DataReader:
Public Function getDataReader( byval SQL as String ) as IDataReader
dim cn as new SqlConnection( System.Configuration.ConfigurationSettings.AppSettings( "DSNSQLSERVER" ).ToString() )
cn.Open()
dim cmd as New SqlCommand( SQL, cn )
dim dr as IDataReader = cmd.ExecuteReader( CommandBehavior.CloseConnection )
return dr
End Function
A few coments: I would avoid coupling my user-interface layer to any specific database implementation; this means working with the interface IDataReader instead of a specific SqlDataReader, OleDbDataReader, or any of the others. Notice the CommandBehavior enum argument to ExecuteReader; this means our db connection will be closed when we close our DataReader. Unless you plan to pass Connection instances as arguments (yuck), this is a necessary step if you're in the business of passing DataReaders around this way. If I closed the connection within my getDataReader method, my DataReader would be empty; DataReaders require an open connection to the database, so all my client methods have to be sure to call dr.Close() in order for my connection to be released appropriately. Conceptually, I don't like the idea of returning a data container with a direct link to the datasource; it doesn't seem like a clean design to me, especially when ADO.Net does such a good job with disconnecting data from the source.
Here is a simple, hypothetical data-access layer:
Class dbLayer
Public Function getDataSet( byval SQL as String ) as Object
dim cn as new SqlConnection( System.Configuration.ConfigurationSettings.AppSettings( "DSNSQLSERVER" ).ToString() )
cn.Open()
dim da as New SqlDataAdapter( SQL, cn )
dim ds as New DataSet()
da.Fill( ds )
cn.Close()
da.Dispose()
return ds
End Function
Public Function getDataReader( byval SQL as String ) as Object
dim cn as new SqlConnection( System.Configuration.ConfigurationSettings.AppSettings( "DSNSQLSERVER" ).ToString() )
cn.Open()
dim cmd as New SqlCommand( SQL, cn )
dim dr as IDataReader = cmd.ExecuteReader( CommandBehavior.CloseConnection )
return dr
End Function
End Class
Both the "getData..." methods accept a SQL string and return a generic object; you'll see why in a few paragraphs. In a real app, I would introduce an custom db interface layer to program my client against, but I want to wrap this up before work on Monday, so I cut that out.
On paper, the DataReader is the fastest way to obtain "a forward only, read-only set of data," right? Not always.
Let's use the following client code in a windows form:
Dim startTime as DateTime
Dim dbQueryCompleteProcessor As New AsyncCallback( AddressOf Me.dbQueryComplete )
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim obj As new dbLayer
Dim del As New DBWorkDelegate( AddressOf obj.getDataSet )
starttime = system.DateTime.Now
del.BeginInvoke( "SELECT TOP 100 LastName FROM Employees ORDER BY LastName", dbQueryCompleteProcessor, del )
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim obj As new dbLayer
Dim del As New DBWorkDelegate( AddressOf obj.getDataReader )
startTime = System.DateTime.Now
del.BeginInvoke( "SELECT TOP 100 LastName FROM Employees ORDER BY LastName", dbQueryCompleteProcessor, del )
End Sub
Public Delegate Function DBWorkDelegate( byval strSQL as String ) as Object
Sub dbQueryComplete( ByVal ar As IAsyncResult )
if me.InvokeRequired then
dim objArgs( 0 ) as Object
objArgs( 0 ) = ar
me.Invoke( new AsyncCallback( AddressOf dbQueryComplete ), objArgs )
Else
Dim del As DBWorkDelegate
del = CType(ar.AsyncState, DBWorkDelegate)
Dim objResult as Object = del.EndInvoke( ar )
if typeof objResult is DataSet then
listbox2.DisplayMember = "LastName"
listbox2.DataSource = ctype( objResult, DataSet ).Tables( 0 )
else
dim objDR as IDataReader = ctype( objResult, IDataReader )
while( objDr.Read() )
listbox2.Items.Add( objDr.GetString( 0 ) )
End While
objDR.Close()
End If
ListBox1.Items.Add( "Time: " & System.DateTime.Now.Subtract( startTime ).TotalMilliseconds )
End if
End Sub
If you're following along at home, you'll want to slap two button and two listbox controls onto the form (leave their default vb.net names).
My two button click events create the worker delegate and invoke it; nothing I haven't already discussed in earlier posts. The only interesting tidbit here is the dbQueryComplete method; it starts by synchronizing our asynchronous stuff (discussed more here), and then processes the results based on the type of object that's returned from our data-access layer. This is one reason why our data-access methods all return object, we only need a single dbWorkDelegate to handle them in our client application. We could break this up so we use a different delegate for each method, but I'm striving for consistency and ease of use.
You'll also see some timer code in there; it's interesting to note that once we return more than 10 or 20 records, our DataReader starts to perform more slowly with this architecture. Apparently, iterating through the IDataReader is slower than just assigning the Datasource property to a DataTable or DataGrid.
What this is moving towards is a pattern for accessing data and asynchronous communications; wouldn't it be great if there was something that tackled this stuff for us? Luckily for us, there's the open source Asynchronous Invocation Application Block and Data Access Application Block from Microsoft. This is the MSFT attempt to abstract some of the details away for these frequently used programming tasks. Instead of having to roll you own, you can leverage the codebase MSFT has made available; for the record, I like rolling my own for a number of reasons, including the understanding/appreciation it provides as to how the abstractions are working on our behalf! Never underestimate getting behind the scenes!
Try this sample code out and appreciate that this example is contrived; you have to imagine a larger scale application making broader use of the dblayer Building on this, I will post about the Application Blocks sometime soon, so stay tuned.
Happy .Netting