KishoR NaiK: ADO.net - Convert SqlDataReader to DataTable

A DataReader is a read and forward-only way of reading data. It is quiet fast when compared to fetching data using a DataSet or Datatable. Infact internally, a DataSet uses a DataReader to populate itself.
A dataset or datatable is extremely handy when it comes to binding it to a control like a GridView. So to make use of both the DataReader and DataTable in the same solution, we can fetch the data using a DataReader and then convert it to a DataTable and bind it to the Gridview Control.

Let see how to convert SqlDataReader to DataTable

Step 1
Download northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a northwind database into MS-SQL server

Step 3
Create a stored procedures for retriving customer data from Customer Table,it is look like this

CREATE PROCEDURE dbo.Sp_CustomerData
AS
    BEGIN
    
        SELECT Customers.CompanyName,Customers.ContactName,
               Customers.City,Customers.Country,Customers.Phone
               
               FROM Customers             
    
    END

Step 4
Create a Web Project.

Step 5
Add connectionString in Web.config file,it is look like this

<connectionStrings>
        <add name="ConStr" connectionString="Data Source=SHREE\SHREE;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Step 6
Create a Customer class in App_Code folder,it is look like this

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;  


public static class Customer
{
    #region Methods

    public static DataTable GetCustomerData()
    {
        try
        {
            // Connection Open
            SqlConnection SqlCon = new SqlConnection();
            SqlCon.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.Trim();
            SqlCon.Open();

            // Create a Command
            SqlCommand SqlComm = new SqlCommand();
            SqlComm.Connection = SqlCon;
            SqlComm.CommandType = CommandType.StoredProcedure;
            SqlComm.CommandText = "Sp_CustomerData";

            // Read a forward only data
            SqlDataReader SqlDr = SqlComm.ExecuteReader();

            // Convert SqlDataReader to DataTable
            DataTable Table = new DataTable();
            Table.Load(SqlDr);


            return Table; 
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion
}

DataTable in ADO.NET contains a Load() method which enables the DataTable to be filled using a IDataReader or SqlDatareader.

Step 7
Bind the customer data in gridview,it is look like this

.aspx code

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <asp:GridView ID="gvCustomer" runat="server" AllowPaging="True" CellPadding="4" 
                    ForeColor="#333333" GridLines="None" 
                    onpageindexchanging="gvCustomer_PageIndexChanging">
                    <RowStyle BackColor="#EFF3FB" />
                    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                    <EditRowStyle BackColor="#2461BF" />
                    <AlternatingRowStyle BackColor="White" />
                </asp:GridView>
            </ContentTemplate> 
        </asp:UpdatePanel>
    </div>
    </form>
</body>
</html>

.aspx.cs [Code behind]

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                BindData(); 
            }
        }
        catch (Exception)
        { 
        }
    }

    #region Methods
    /// <summary>
    /// Bind the customer data in gridview
    /// </summary>
    private void BindData()
    {
        try
        {
            DataTable Dt = Customer.GetCustomerData();

            if (Dt != null)
            {
                if (Dt.Rows.Count > 0)
                {
                    gvCustomer.DataSource = Dt;
                    gvCustomer.DataBind();  
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion
    protected void gvCustomer_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            gvCustomer.PageIndex = e.NewPageIndex;
            BindData(); 
        }
        catch (Exception)
        { }
    }
}

Run the project

Download
Download Source Code

KishoR NaiK

Pages

Monday, September 6, 2010

ADO.net - Convert SqlDataReader to DataTable

No comments:

Post a Comment

Followers

Visitors