Using DataReader to fill a DataTable from an SQL Server Stored Procedure and display the data in a GridView control in ASP.NET 2.0
In this article I’ll try to explain how you can use the DataTable object as a container object for data from a DataReader object and to display this data in a GridView control in ASP.NET 2.0
Previously in ASP.NET 1.1, the DataTable object was available as a child of a DataSet object and you will have to fill the DataSet object from a DataAdapter and then you can get to the DataTable data for use in data binding with a data bound web control like DataGrid or GridView in ASP.NET 2.0
Now in ASP.NET 2.0 you have the facility that you can directly load data in the DataTable object from any object that implements the IDataReader interface such as the SqlDataReader. There is no need to create a dedicated DataSet object from a DataAdapter object and fill the default DataTable with data like you have done in the previous versions of ASP.NET
The DataTable is modified with many capabilities and one of them is the Load method. This method takes a DataReader and populates itself from this fast, forward only, read only data source without ever creating a DataAdapter. You just need a Command object and a DataReader and the data is available in the DataTable for use. You can specify the command type of the Command object to Stored Procedure plus any parameters that you define using the Parameters collection of the Command object and that’s it for fetching data from a database stored procedure.
I’ve created a sample application that demonstrates this whole process in a very descriptive way. I’ve created a DataReader from a SQL Server stored procedure and loaded the data in the DataTable and populated a GridView with this DataTable. Here is the code.
Don’t forget to import the System.Data.SqlClient namespace for using SQL Server. Also I’ve set the AutoGenerateColumns property to true, you can specify the columns that you want to show with the columns sub tag of the GridView control.