Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The DataReader: Background
Let's start by discussing the DataReader very briefly. (For more background information, go to MSDN.)
As I said before, DataReader isn't really a container. It is marshaled by reference, and I'm using it here especially as a baseline in the performance tests.
NOTE
Let me stress that I don't think the DataReader should be used between tiers in a well-factored architecture. In a way, DataReaders are the reason that many systems are created as a mishmash of presentation, business, and data code. Again, I discuss DataReader here as a baseline, not as a best practice.
The core functionality of the DataReader is to stream data from the database very efficiently. It's also the case that the DataSet (or, rather, DataAdapter) uses a DataReader behind the curtains to fill the DataSet with data.
As a matter of fact, there isn't a class called DataReader in the .NET Framework. Instead, there are two interfaces, IDataReader and IDataRecord, and the classes implementing those interfaces are provider-specific. The classes are called, for example, SqlDataReader (specific to SQL Server), OleDbDataReader (for OLE DB–supporting databases) and OdbcDataReader (for ODBC-supporting databases). Because I'm mostly working with SQL Server, I will use the SqlDataReader class in this article.
Many of you might argue that it's preferred to program against the interfaces instead so that you easily can switch among SqlDataReader, OleDataReader, or another DataReader implementation. I can agree with that. Currently, a few (small) caveats come with that, but I'm sure these will be dealt with in an upcoming version of .NET Framework.
Some Pros and Cons with the DataReader
As I have already said, the DataReader isn't really a container (at least, not the way I see it), so the pros and cons examples are affected by that:
Performance in the AppDomain. The performance is very good for streaming data from the database, but only within the same AppDomain.
Performance, cross-AppDomains and cross-machines. The situation is the opposite regarding performance if you use a DataReader from another AppDomain than the one in which it was created. Because the DataReader is marshaled by reference, all method calls have to go back to the source AppDomain.
Connected operations only. The DataReader is useful only when it has a connection open to the database. So, even though you can quickly get data out from the database and have the connection occupied for an as short time as possible, you risk starting to do a lot of work for each row in the DataReader and having the connection open for a very long time.
NOTE
In my opinion, it's really dangerous to hand out DataReaders to clients that have been written by other developers. How can you know that they won't hold on to the DataReader for a very long time? What you can know is that, in that case, it will hurt.
Occupied connection. You cannot use one and the same connection from two different DataReaders at the same time. You must loop through the first DataReader (or close it) before using the connection for another DataReader.
Interoperability. You can't send a DataReader to a client that isn't a .NET client. It doesn't make sense for other clients.
Exposed database. The DataReader is weak at encapsulating the database. The schema itself is somewhat exposed, but the user of a DataReader also has a very direct coupling to the database. This will affect maintainability in the long run.
Only about reading. It's probably obvious and well known, but it's worth mentioning anyway. This is important when you think about the programming model because then you can't rely only on the DataReader.
DataReader Code Examples
To get a feeling of how to work with a DataReader (or, rather, System.Data.SqlClient.SqlDataReader), take a look at the UML diagram in Figure 1. (Note, that I have listed only a selection of the methods there.)
)
Figure 1 DataReader in UML.
In Listing 1, you find some code for when data is fetched from the database with the help of a stored procedure. As you can see, you don't instantiate an SqlDataReader with New. Instead, you create one with the help from a command (or, rather, SqlCommand).
Listing 1: Code for Creating a DataReader
Dim aCommand As New _
SqlCommand(SprocOrder_FetchWithLines, _
_GetConnection())
aCommand.CommandType = _
CommandType.StoredProcedure
aCommand.Parameters.Add("@id", _
SqlDbType.Int).Value = id
Return aCommand.ExecuteReader _
(CommandBehavior.CloseConnection Or _
CommandBehavior.SequentialAccess)
Browsing through the information in the DataReader, the code could look like it does in Listing 2. Note that here I'm browsing through a DataReader with two resultsets. That's the reason of the call to NextResult().
Listing 2: Code for Browsing Through a DataReader
Dim anOrder As SqlDataReader = _
_service.FetchOrderAndLines(_GetRandomId())
anOrder.Read()
_id = anOrder.GetInt32(OrderColumns.Id)
_customerId = anOrder.GetInt32 _
(OrderColumns.CustomerId)
_orderDate = anOrder.GetDateTime _
(OrderColumns.OrderDate)
anOrder.NextResult()
While anOrder.Read
_productId = anOrder.GetInt32 _
(OrderLineColumns.ProductId)
_priceForEach = anOrder.GetDecimal _
(OrderLineColumns.PriceForEach)
_noOfItems = anOrder.GetInt32 _
(OrderLineColumns.NoOfItems)
_comment = anOrder.GetString _
(OrderLineColumns.Comment)
End While
anOrder.Close()
Before we take a look on the results of the DataReader in the tests, let's discuss the tests a bit.