C# ASP.NET Table SqlDataReader Example – SqlDataReader Table Example C# ASP.NET

Posted by on Wednesday, December 23, 2009 · Leave a Comment 

C# ASP.NET Table SqlDataReader Example – SqlDataReader Table Example C# ASP.NET

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

Prerequistes:

  1. Install Visual Studio 2022
  2. Watch this video to enable web forms in Visual Studio 2022 Enable Web Forms websites in Visual Studio 2022
  3. Install SQL Server Express
  4. Download Northwind Database
  5. Attach Northwind Database to Databases in Sql Express

Notes:

  • You can build your own library of syntax examples by using same web site over and over and just adding new web forms.

Instructions:

  1. Use Visual Studio 2022
  2. Create new web site;
    • Select Create New Project
    • Select ASP.NET Web Forms Site Template
    • name of project could be CSharp_ASPNET_Syntax
    • name of solution could be CSharp_ASPNET_Syntax.
  3. Add Web Form Named TableLoadFromSqlDataReader to ADONET folder
    • Right-click ADONET folder;
    • add new item;
    • Select Web Form
    • Check place code behind in separate file
    • Web Form name could be TableLoadFromSqlDataReader
  4. Click on copy code in code below to copy code into web form TableLoadFromSqlDataReader.aspx
  5. Click on copy code in second set of code below to copy code into code-behind TableLoadFromSqlDataReader.aspx.cs
  6. Right-click on TableLoadFromSqlDataReader.aspx in solution explorer and select view in browser

Step 1: Click on Copy Code to Cut-n-paste code into TableLoadFromSqlDataReader.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="TableLoadFromSqlDataReader.aspx.cs" Inherits="ADONET_TableLoadFromSqlDataReader" %>
 
 
 
 
 
    <title></title>
 
 
    <form id="form1" runat="server">
 
<div>
        <asp:gridview id="GridView1" runat="server">
        </asp:gridview>
</div>
 
    </form>

Step 2: Click on Copy Code to Cut-n-paste code into TableLoadFromSqlDataReader.aspx.cs

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();
    }
}

Step 3: Click on Copy Code to Cut-n-paste code into web.config right after the appSettings section 

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

Filed under Code Samples · Tagged with , , ,