How to fill DataTable or dataset using SqlDatareader in asp.net(c# & Vb.Net)?

In this article I will explain how to Bind or fill Datatable or dataset using SqlDataReader  in asp.net using C# or vb.net.

In this I article I will fetch data from database using data reader and fill it to Datable. I will execute command using execute datareader to get data.

Implementation:

1.      In code behind file add namespace i.e.

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

2.      Now Create connection to database

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

3.      Now create function to get data and call this function on Page Load:

protected void DataTableFromDatareader()

    {

        string con = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

        using (SqlConnection conStr = new SqlConnection(con))

        {

            using (SqlCommand cmd = new SqlCommand("select * from student_info"))

            {

                cmd.Connection = conStr;

                conStr.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())

                {

                    //Create a new DataTable.

                    DataTable dt = new DataTable("Customers");

                    //Load DataReader into the DataTable.

                    dt.Load(dr);

                }

                conStr.Close();

            }

        }

    }

Asp.NET code using c#:

using System.Data.SqlClient; using System.Data; using System.Configuration; public partial class gridopen : System.Web.UI.Page {     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);     protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)         {             DataTableFromDatareader();         }     }         protected void DataTableFromDatareader()     {         string con = ConfigurationManager.ConnectionStrings["con"].ConnectionString;         using (SqlConnection conStr = new SqlConnection(con))         {             using (SqlCommand cmd = new SqlCommand("select * from student_info"))             {                 cmd.Connection = conStr;                 conStr.Open();                 using (SqlDataReader dr = cmd.ExecuteReader())                 {                     //Create a new DataTable.                     DataTable dt = new DataTable("Customers");                     //Load DataReader into the DataTable.                     dt.Load(dr);                 }                 conStr.Close();             }         }     }

Using Vb.NET:

Imports System.Data.SqlClient Imports System.Data Imports System.Configuration Partial Public Class gridopen     Inherits System.Web.UI.Page     Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)     Protected Sub Page_Load(sender As Object, e As EventArgs)         If Not IsPostBack Then             DataTableFromDatareader()         End If     End Sub     Protected Sub DataTableFromDatareader()         Dim con As String = ConfigurationManager.ConnectionStrings("con").ConnectionString         Using conStr As New SqlConnection(con)             Using cmd As New SqlCommand("select * from student_info")                 cmd.Connection = conStr                 conStr.Open()                 Using dr As SqlDataReader = cmd.ExecuteReader()                     'Create a new DataTable.                     Dim dt As New DataTable("Customers")                     'Load DataReader into the DataTable.                     dt.Load(dr)                 End Using                 conStr.Close()             End Using         End Using     End Sub End Class