Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

FREE 12 month online training for ASP.NET & MS Expression Studio and a Free copy of MS Expression Web with Windows Server Purchase
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

Author: Aadil

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.

 

<script runat="server">

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

    If IsPostBack = False Then

      BindGrid()

    End If

  End Sub

 

  Private Sub BindGrid()

    Dim objConn As New SqlConnection("Server=127.0.0.1;UID=user;PWD=pass;Database=Northwind")

    Dim objCmd As New SqlCommand("GetAllEmployees", objConn)

    objCmd.CommandType = CommandType.StoredProcedure

    objConn.Open()

    Dim DR As SqlDataReader = objCmd.ExecuteReader()

    gvEmployees.DataSource = DR

    gvEmployees.DataBind()

    DR.Close()

    DR = Nothing

    objCmd.Dispose()

    objCmd = Nothing

    objConn.Close()

    objConn = Nothing

  End Sub

script>

 

<html>

<head>

  <title>title>

head>

<body style="font-family:Verdana;font-size:10pt;">

  <form runat="server">

    <asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="true">asp:GridView>

  form>

body>

html>

 

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.

Article Comments
Great help.you saved my time
Keep up good works....

Posted on 5/31/2006 1:13:37 AM by Nishshanka

All this talk of DataTable, but you didn't use a DataTable at all ... you bound a GridView to a DataReader returned from ExecuteReader. There is a big difference ... the way you have done it, there are a few things you cannot do with the GridView, like paging or sorting.

Posted on 6/5/2006 9:53:03 PM by Ben

I would like to squash a fallacy here, you were never required to use a dataset to fill a DataTable, you can call the DataAdapter.Fill method with a table directly. Although I do not see this used very much in examples I have observed on the net, I use it regularly as the DataSet is quite large and is unnecessary to use to acquire your data. If you are a .net developer who is still using 1.1 and still using datasets to get your data, take a look at just filling the datatable, much more effecient.

As for this articles discussion of filling a datatable from a datareader, while this is possible, it is not recommended as a standard practice.
DataReaders serve a specific purpose to retrieve a very lightweight object that delivers a read only forward only cursor on your back end data.
DataTables on the other hand allow you to use the data in any manner you wish. You can sort data in the datatable, select part of it into a new datatable, or datareader.

It is incumbent upon the developer to learn what value each of these objects bring to the table and then select the correct tool for the job at hand.

Posted on 12/20/2006 2:03:59 PM by John Vonesh

Could any1 please assist me how to pass the parameters in to the SP in the above article

Posted on 2/14/2008 12:09:01 PM by Manjunath

Hi Manjunath

I found this tutorial really helpful in explaining the use of parameters. http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx Hope it helps.

Posted on 9/21/2008 8:49:07 AM by Don

The dataadapter has a fill function which can set a range of records to retrieve from the database. This is very helpful for paging large amounts of data.

What happens when you have millions of records and you want just a subset of data and you use a datareader instead of a dataadapter? How do you configure the datareader to retrieve that subset?

I have yet to find a way to do that with a datareader.

Please advise if anyone has found a way to do this.

Thanks

Posted on 12/11/2008 1:20:43 PM by Mike

Hi
Thanks for such good and easy code.I have used this code in by "PROJECT"

Posted on 12/11/2009 5:36:02 AM by vishal wattamwar

In The above code there is no datatable at all. Datatable uses a Load method to load the the data from datareader resultset.

DataTable dt;
dt.Load(dr);

Posted on 5/18/2010 3:01:06 AM by Samrat Dutta

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to create a Hijri Calendar control in .Net Framework 2.0

Disclaimer - Privacy
© 2002-2014 DevASP.net