When using ADO.Net it is possible to have a query return more than one result set, translated into an DataSet , or by returning more than one DataTable sequentialy.
If you use a DataSet to get each of the individual query results you can simply use the [DataSetInstance].Tables collection to interact with the results of each query. If you prefer, you can return a SqlDataReader from the Sql command, SqlDataReader sqlReader = sqlCmd.ExecuteReader(). Once you have the sqlDataReader you read to the end. Then you can begin processing the next result set by calling the sqlReader.NextResult() method.
If you’re like me, I prefer to get a DataTable or DataSet from the database, and then iterate over the objects locally.
This releases the connection from the database server more quickly, while allowing me the power of the DataReader class. To do this you would simply return a DataSet object from the database. Then, instead of using an SqlDataReader you use a DataTableReader, DataTablereader sqlReader = myDataSet. CreateDataReader(). After processing the first DataTable, I move to the next, just like with the SqlDataReader by invoking the NextResult() method.
Technically, when use the DataTableReader class, you iterate through the same data twice. ADO iterates through each table once in a fire hose cursor style, populating your in memory data set. Then, when you use the DataTableReader, you iterate through all of the data once again. This may not sound intuitive up front. The value of using two passes is thatt here is a lot of marshalling between SQL Server (or any data store supporting a cursor) and your client. If your client processing of each record is slow, especially if it is doing a lot of work on each row, then you are slowing down the data store, and hold onto database resources for a longer period of time. Using DataSets, DataTables and DataTableReaders as a default practice assures you are releasing database resources as quickly as possible.
I like using the SqlDataReader and DataTableReader classes because they provide methods for retrieving data from the result set and casting it to a Dot.Net data type. For example, each reader provides the method GetString(int32), where int32 is the ordinal position of the column you are seeking. You can replace int32 with the string name for the column. This performs slower because it has to resolve the string name to the ordinal value on each execution. If I am really trying to squeeze the most performance out of my application, I will use the ordinal position instead of the column name.
One of the biggest problems you have with a converting records from a DataTable is handling null values. If the database returns null, then calling GetString(int32) will throw an error. It doesn’t know how to convert a database null into a string. You can use the IsDBNull(int32) method to determine if the value is null first. For example, string myValue = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); returns an empty string if null, otherwise it returns the value of column at ordinal position 2 converted to a string. I have created my own extension methods on the DataTableReader Class performing the IsDBNull check so I don’t have to write it every time I use it.
In future editorials we’ll unpack some of these ideas with more details. In the meantime, don’t be afraid of returning multiple result sets in your queries.
Cheers,
Ben