Microsoft KB Archive/310348

= How To Avoid the Boxing Penalty When You Use the DataReader in Visual Basic .NET =

Article ID: 310348

Article Last Modified on 5/13/2007

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q310348



For a Microsoft Visual C# .NET version of this article, see 312855.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data.OleDb
 * System.Data.SqlClient

IN THIS TASK

 * SUMMARY
 * Description of the Technique
 * Requirements
 * Create Project and Add Code
 * Pitfalls
 * REFERENCES



SUMMARY
This article describes how to use the Get  methods (such as GetChar, GetDouble, and GetInt32) to avoid the boxing penalty when you use the DataReader object.

back to the top

Description of the Technique
When you use the Item property to read columns from a DataReader, the values are boxed and then unboxed. If the values are repeatedly boxed and unboxed, the heap quickly fills and increases the frequency of garbage collections. This also slightly impacts performance because Microsoft Visual Studio .NET converts and copies data more than necessary.

NOTE: Boxing means that the data is copied onto the heap as System.Object. When you cast to a specific data type, the values are unboxed and copied onto your variables on the stack or into another object.

To avoid the boxing penalty, use the Get methods (such as GetChar, GetDouble, and GetInt32) that return the data as a simple data type instead of as System.Object.

back to the top

Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
 * Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
 * Microsoft Visual Studio .NET

This article assumes that you are familiar with the following topics:
 * Visual Studio .NET
 * ADO.NET fundamentals and syntax

back to the top

Create Project and Add Code
 Start Visual Studio .NET. Create a new Windows Application in Visual Basic .NET. By default, Form1 is added to the project. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not. Add a Button control to Form1. Change the Name property of the button to btnTest, and change the Text property to Test .  Use the Imports statement on the System and System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. Add this code to the &quot;General Declarations&quot; section of Form1: Imports System Imports System.Data Imports System.Data.OleDb Imports System.Data.SqlClient   Copy and paste the following code in the btnTest_Click event: Dim myConnString as String = _ &quot;User ID=sa;password=sa;Initial Catalog=northwind;Data Source=myServer&quot; Dim mySelectQuery As String = _ &quot;Select * From Customers&quot; Dim con As New SqlConnection(myConnString) Dim myCommand As New SqlCommand(mySelectQuery, con) con.Open Dim myReader As SqlDataReader = myCommand.ExecuteReader Dim str1 As String

While myReader.Read 'This code uses the GetString method. 'str1 = str1 & myReader.GetString(0) & &quot;, &quot; 'This code uses the Item method. str1 = str1 & myReader.Item(0) & &quot;, &quot; End While MessageBox.Show(str1)

myReader.Close con.Close  Modify the connection string (myConnString) as appropriate for your environment.</li> Save your project. On the Debug menu, click Start to run your project.</li> Click Test. Notice that the query uses one of the methods (GetString or Item) to return data, and the message boxes display this data.</li> To compare the difference in performance or to time the code, use the QueryPerformanceCounter function to time application code. For more information about QueryPerformanceCounter, see the REFERENCES section.</li></ol>

back to the top

Pitfalls
The disadvantage of using the Get method is that you must check for NULL before you access the field. To check for NULL, use the IsDBNull method.

back to the top

<div class="references_section">