This sample application demonstrates the various ways to use a DataReader to retrieve data and use it to fill a ComboBox, a TextBox, and other controls. A DataReader is also used behind the scenes by the DataAdapter object (to fill a DataSet) and the Command object (to execute SQL statements, such as the ones used in this application to add several stored procedures to the Northwind database). The focus of the application is, however, on data retrieval, which is the only time when you work directly with the DataReader object.
This application requires the release version of Visual Studio .NET Professional (or greater) and access to the Northwind database residing in SQL Server or the Microsoft Data Engine (MSDE). To install MSDE, do the following:
1. Open the Start menu, click Programs, click Microsoft .NET Framework SDK, and then click Samples and Quickstart Tutorials.
2. Click "Step 1: Install the .NET Framework Samples Database".
3. Open a command window, and CD to
4. Type: osql -E -S (local)\NetSDK -i InstNwnd.sql
Simply press F5.
1. A DataReader will tie up the existing connection because the data is streamed instead of retrieved and cached, like with a DataSet. Therefore, you will want to close a connection used by a DataReader as soon as possible. The best way to do this is to pass the CommandBehavior.CloseConnection enum to the ExecuteXXXX command.
2. Although a DataReader is faster and requires less overhead than a DataSet, a DataReader has the following limitations (among others):
a) It has no inherent support for the serialization of relational data as XML with a corresponding schema. You can call ExecuteXMLReader and retrieve an XML stream, but this is only if the T-SQL "FOR XML" clause is used in the query (SQL Server 2000 only). If you want to retrieve relational data that can be readily serialized as XML then you should use a DataSet.
b) It cannot be instantiated directly. You must go through the Command object to get a DataReader object.
c) It cannot be used to create a DataView. If you need to filter and/or sort the data then you will need a DataSet.
d) It does not permit disconnected access to data.
e) It cannot be used for databinding (i.e., it does not support the IList or IListSource interface). You can only "databind" by iterating through its Items collection, retrieving the values, and adding them to the Items collection of a control or another object that does implement one of those interfaces. This object can then be used for true databinding. Examples of both are demonstrated with the two ComboBox controls (see Note 4, below).
3. The data is retrieved forward-only. Once it is streamed in, you cannot go back through the rows in the DataReader unless you have stored the data in an object that supports this, such as an ArrayList or a DataTable.
4. One of the best uses for a DataReader is for filling user interface controls like a ComboBox. "Filling" is not the same as databinding. You cannot databind a ComboBox to a DataReader like you can databind the equivalent ASP .NET control--the DropDownList. The ComboBox requires a DataSource that implements the IList or IListSource interfaces. Therefore, you must use iterate through the DataReader and use ComboBox.Items.Add to fill the ComboBox. With a DropDownList you can set the DataSource property directly to the DataReader. Furthermore, with a ComboBox you cannot assign values to the DisplayMember and ValueMember properties when using a DataReader directly. With a DropDownList, however, you can do this. Following is how you would use a DataReader to databind to a DropDownList in ASP.NET:
With DropDownList1
.DataSource = sdr
.DataTextField = "CategoryName"
.DataValueField = "CategoryID"< BR > .DataBind()
End With
In the code you will notice that only the "CategoryName" field is used to fill the ComboBox. There is no value for the ComboBox items.
5. If the SQL statement contains output parameters or return values, these will not be available until after the DataReader is closed.
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic .NET (Q308049)