C# DataTable SqlDataReader Example – SqlDataReader Datatable Example C#

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

C# DataTable SqlDataReader Example – SqlDataReader Datatable Example C#

Purpose: – Illustrates using DataTable loaded from SqlDataReader in C-Sharp.

Prerequistes:

  1. Install C# (Express or Standard Edition)
  2. Install SQL Server Express
  3. Download Northwind and Pubs Databases
  4. Attach Northwind Database to Databases in Sql Express
  5. Attach pubs Database to Databases in Sql Express

Notes:

  • Console Application is used to simplify things, but Windows Forms or Web Forms could also be used
  • You can build your own library of syntax examples by using same project over and over and just adding new classes to it.

Instructions:

  1. Use Visual Studio 2022 or current version
  2. Create new project;
    • Click File/New Project
    • Select Console Application Template
    • Select C-Sharp for Language
    • name of project could be CSharp_Syntax.
  3. Add New folder named “ADONET”
    • Right-click project name in solution explorer;
    • add new folder;
    • name of folder could be: ADONET
  4. Add Class named clsDataTableSqlDataReader to ADONET folder
    • Right-click ADONET folder;
    • add new item;
    • Select Class
    • Class name could be clsDataTableSqlDataReader
  5. Click on copy code in code below to copy code into class clsDataTableSqlDataReader.cs
  6. Click on copy code in second set of code below to copy code into Program.cs
  7. Click on green arrow in toolbar in VS or press F5 to run program.

Step 1: Click on Copy Code to Cut-n-paste code into clsDataTableSqlDataReader.cs

using System;
using System.Data;
using System.Data.SqlClient;
public class clsDataTableSqlDataReader
{
static SqlConnection con;
 
public void Main()
{
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();
}
 
foreach  (DataRow row in  Table1.Rows)
{
Console.WriteLine(row["EmployeeID"].ToString().PadRight(10) + row["FullName"].ToString());
 
}
Console.ReadLine();
}
}

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace CSharp_Syntax
{
class Program
{
static void Main(DataTableSqlDataReader[] args)
{
//ADONET
 
clsDataTableSqlDataReader myDataTableSqlDataReader = new clsDataTableSqlDataReader();
myDataTableSqlDataReader.Main();
 
}
}
}

Step 3: Click on green arrow in toolbar in VS or press F5 to run program.

Filed under Code Samples · Tagged with , ,