Problem
You need to improve performance when accessing data from a DataReader .
Solution
Use DataReader typed accessors to improve performance by eliminating repeated boxing and unboxing of object data to and from .NET Framework data types.
The sample code measures the time to access data in a DataReader using three techniques: typed accessor, column ordinal, and column name . The user specifies the technique by selecting a radio button. To ensure accuracy in each case, the routine reads all data from the DataReader 100 times and measures the total time in ticks , which are 100-nanosecond intervals.
The C# code is shown in Example 9-10.
Example 9-10. File: DataReaderPerformanceForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; // . . . Cursor.Current = Cursors.WaitCursor; int orderId; String customerId; int employeeId; DateTime orderDate; DateTime requiredDate; DateTime shippedDate; int shipVia; Decimal freight; String shipName; String shipAddress; String shipCity; String shipRegion; String shipPostalCode; String shipCountry; String sqlText = "SELECT OrderID, CustomerID, EmployeeID, " + "OrderDate, RequiredDate, ShippedDate, " + "ShipVia, Freight, ShipName, ShipAddress, ShipCity, " + "ShipRegion, ShipPostalCode, ShipCountry " + "FROM Orders"; // Create the connection and the command. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new SqlCommand(sqlText, conn); String accessMethod = typedAccessorRadioButton.Checked ? "Typed accessor": ordinalRadioButton.Checked ? "Ordinal":"Column name"; int startTick = 0; int elapsedTick = 0; conn.Open( ); for(int i = 1; i < 100; i++) { // Create the DataReader and retrieve all fields for each // record in the DataReader according to user request. SqlDataReader dr = cmd.ExecuteReader( ); if (typedAccessorRadioButton.Checked) { startTick = Environment.TickCount; while (dr.Read( )) { orderId = dr.GetInt32(0); if(!dr.IsDBNull(1)) customerId = dr.GetString(1); if(!dr.IsDBNull(2)) employeeId = dr.GetInt32(2); if(!dr.IsDBNull(3)) orderDate = dr.GetDateTime(3); if(!dr.IsDBNull(4)) requiredDate = dr.GetDateTime(4); if(!dr.IsDBNull(5)) shippedDate = dr.GetDateTime(5); if(!dr.IsDBNull(6)) shipVia = dr.GetInt32(6); if(!dr.IsDBNull(7)) freight = dr.GetDecimal(7); if(!dr.IsDBNull(8)) shipName = dr.GetString(8); if(!dr.IsDBNull(9)) shipAddress = dr.GetString(9); if(!dr.IsDBNull(10)) shipCity = dr.GetString(10); if(!dr.IsDBNull(11)) shipRegion = dr.GetString(11); if(!dr.IsDBNull(12)) shipPostalCode = dr.GetString(12); if(!dr.IsDBNull(13)) shipCountry = dr.GetString(13); } elapsedTick += Environment.TickCount-startTick; } if (ordinalRadioButton.Checked) { startTick = Environment.TickCount; while (dr.Read( )) { if (!dr.IsDBNull(0)) orderId = Convert.ToInt32(dr[0]); if (!dr.IsDBNull(1)) customerId = Convert.ToString(dr[1]); if (!dr.IsDBNull(2)) employeeId = Convert.ToInt32(dr[2]); if (!dr.IsDBNull(3)) orderDate = Convert.ToDateTime(dr[3]); if (!dr.IsDBNull(4)) requiredDate = Convert.ToDateTime(dr[4]); if (!dr.IsDBNull(5)) shippedDate = Convert.ToDateTime(dr[5]); if (!dr.IsDBNull(6)) shipVia = Convert.ToInt32(dr[6]); if (!dr.IsDBNull(7)) freight = Convert.ToDecimal(dr[7]); if (!dr.IsDBNull(8)) shipName = Convert.ToString(dr[8]); if (!dr.IsDBNull(9)) shipAddress = Convert.ToString(dr[9]); if (!dr.IsDBNull(10)) shipCity = Convert.ToString(dr[10]); if (!dr.IsDBNull(11)) shipRegion = Convert.ToString(dr[11]); if (!dr.IsDBNull(12)) shipPostalCode = Convert.ToString(dr[12]); if (!dr.IsDBNull(13)) shipCountry = Convert.ToString(dr[13]); } elapsedTick += Environment.TickCount-startTick; } if (columnNameRadioButton.Checked) { startTick = Environment.TickCount; while (dr.Read( )) { if (dr["OrderID"] != DBNull.Value) orderId = Convert.ToInt32(dr["OrderID"]); if (dr["CustomerID"] != DBNull.Value) customerId = Convert.ToString(dr["CustomerID"]); if (dr["EmployeeID"] != DBNull.Value) employeeId = Convert.ToInt32(dr["EmployeeID"]); if (dr["OrderDate"] != DBNull.Value) orderDate = Convert.ToDateTime(dr["OrderDate"]); if (dr["RequiredDate"] != DBNull.Value) requiredDate = Convert.ToDateTime(dr["RequiredDate"]); if (dr["ShippedDate"] != DBNull.Value) shippedDate = Convert.ToDateTime(dr["ShippedDate"]); if (dr["ShipVia"] != DBNull.Value) shipVia = Convert.ToInt32(dr["ShipVia"]); if (dr["Freight"] != DBNull.Value) freight = Convert.ToDecimal(dr["Freight"]); if (dr["ShipName"] != DBNull.Value) shipName = Convert.ToString(dr["ShipName"]); if (dr["ShipAddress"] != DBNull.Value) shipAddress = Convert.ToString(dr["ShipAddress"]); if (dr["ShipCity"] != DBNull.Value) shipCity = Convert.ToString(dr["ShipCity"]); if (dr["ShipRegion"] != DBNull.Value) shipRegion = Convert.ToString(dr["ShipRegion"]); if (dr["ShipPostalCode"] != DBNull.Value) shipPostalCode = Convert.ToString(dr["ShipPostalCode"]); if (dr["ShipCountry"] != DBNull.Value) shipCountry = Convert.ToString(dr["ShipCountry"]); } elapsedTick += Environment.TickCount-startTick; } dr.Close( ); } resultTextBox.Text += "Access method: " + accessMethod + "; Elapsed time: " + elapsedTick + " ticks." + Environment.NewLine; conn.Close( ); Cursor.Current = Cursors.Default;
Discussion
You can access the data in a DataReader row using a column name, a column ordinal, or a typed accessor method such as GetInt32( ) and GetString( ) . The typed accessor allows a column value to be accessed in its native data type reducing the amount of type conversion required when retrieving a column value. When the underlying type is known, this reduces the type conversion effort required when retrieving the column value and thereby improves performance. For a list of typed accessor methods for SQL Server and OLE DB data providers, see Recipe 2.8.
Each typed accessor takes a single argument: the zero-based column ordinal of the column for which to retrieve the value. An IndexOutOfRangeException is raised if the ordinal value is not valid. An InvalidCastException is raised if the accessor method specifies an invalid cast. If the column might contain a nonexistent or missing value, call the IsDBNull( ) method prior to calling the typed accessor method to avoid raising an exception in case the column value is equivalent to DBNull .
Executing the solution shows the following relative performance when accessing DataReader column values using the different methods:
Connecting to Data
Retrieving and Managing Data
Searching and Analyzing Data
Adding and Modifying Data
Copying and Transferring Data
Maintaining Database Integrity
Binding Data to .NET User Interfaces
Working with XML
Optimizing .NET Data Access
Enumerating and Maintaining Database Objects
Appendix A. Converting from C# to VB Syntax