Progress Monitor for IdealProgrammer.com

Take Swift, Intelligent, Massive, Planned, Loving, Effective (S.I.M.P.L.E.) Action to Transform Self into Ideal

Skip Repetitive Navigational Links

Home

News

Presenters

Contact

Please login

C# ASP.NET Table SqlDataReader Example

Description: Illustrates using DataTable load from SqlDataReader in C-Sharp ASP.NET.

Example Webform Code:

< %@ Page Language="C#" AutoEventWireup="true" CodeFile="TableLoadFromSqlDataReader.aspx.cs" Inherits="ADONET_TableLoadFromSqlDataReader" %>
 
< !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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp :GridView ID="GridView1" runat="server">
        </asp>
    </div>
    </form>
</body>
</html>

Example Code Behind:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
 
partial class ADONET_TableLoadFromSqlDataReader : System.Web.UI.Page
{
 
    protected void // ERROR: Handles clauses are not supported in C#
Page_Load(object sender, System.EventArgs e)
    {
        SqlConnection con = new SqlConnection("Server=(local)\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI");
 
        SqlCommand cmd = new SqlCommand();
 
        cmd.CommandText = "SELECT EmployeeID, FirstName + ' ' + LastName as FullName FROM Employees";
        cmd.Connection = con;
 
        DataTable Table1;
        Table1 = new DataTable("Employees");
        //creating a table named Employees
        DataRow Row1;
        //declaring row for the table
 
        DataColumn EmployeeID = new DataColumn("EmployeeID");
        //declaring a column named EmployeeID
        EmployeeID.DataType = System.Type.GetType("System.Int32");
        //setting the datatype for the column
        Table1.Columns.Add(EmployeeID);
        //adding the column to table
        DataColumn FullName = new DataColumn("FullName");
        FullName.DataType = System.Type.GetType("System.String");
        Table1.Columns.Add(FullName);
 
 
 
        try {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            //(CommandBehavior.SingleRow)
            while (reader.Read()) {
 
                Row1 = Table1.NewRow();
                //declaring a new row
                Row1["EmployeeID"] = reader.GetInt32(0);
                //filling the row with values. Item property is used to set the field value.
                Row1["FullName"] = reader.GetString(1);
                //filling the row with values. adding FullName
                Table1.Rows.Add(Row1);
 
 
            }
            reader.Close();
        }
        finally {
            con.Close();
        }
        GridView1.DataSource = Table1;
        GridView1.DataBind();
    }
}

Example Connection String:

<connectiontableloadfromsqldatareaders>
    <add name="Northwind_ConnectionTableLoadFromSqlDataReader"
      connectionTableLoadFromSqlDataReader="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" />
    <add name="Pubs_ConnectionTableLoadFromSqlDataReader"
      connectionTableLoadFromSqlDataReader="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI" />
  </connectiontableloadfromsqldatareaders>