How to Convert SqlDataReader or DataReader to DataTable in Asp.Net C# VB.Net

In many cases we need to convert SqlDataReader or DataReader to a DataTable to meet some technical challenges. Here I will show you how to convert SqlDataReader to a DataTable in a simple way. To understand the example please read the article from beginning to last.

To do the Example First Create a SQL Server Database Table:

CREATE TABLE [dbo].[tblStock](
    [BrandName] [varchar](max) NULL,
    [CategoryName] [varchar](max) NULL,
    [ProductName] [varchar](max) NULL,
    [LogicalQuantity] [bigint] NULL,
    [PhysicalQuantity] [bigint] NULL,
    [QuarentineQuantity] [bigint] NULL

Now Insert Some Data:

INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Handwash',55781,55780,1)
INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Soap',38786,38780,6)
INSERT INTO tblStock VALUES('Kohinoor','ToothPaste','Tibbet Clear',34503,34500,3)
INSERT INTO tblStock VALUES('Proctor & Gamble','Bubble','',43809,43800,9)
INSERT INTO tblStock VALUES('Telenor','Mobile','Data SIM',43876,43870,6)
INSERT INTO tblStock VALUES('Telenor','Mobile','GP Modem',23907,23900,7)
INSERT INTO tblStock VALUES('Uniliver','Soap','Lux',19806,19800,6)
INSERT INTO tblStock VALUES('Uniliver','TeleProducts','Brittle',20703,20700,3)
INSERT INTO tblStock VALUES('Uniliver','ToothPaste','Close Up',16755,16750,5)

Now modify the Web.Config File to Connect to Database:

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

C# Code to convert SqlDataReader or DataReader to DataTable:

    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            string sql = "SELECT * FROM tblStock";
            DataTable dt = new DataTable();
            using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
                SqlCommand cmd = new SqlCommand(sql, oConn);
                System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();

            Response.Write("Datatable row count: " + dt.Rows.Count.ToString());

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

VB.Net Code to convert SqlDataReader or DataReader to DataTable:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim sql As String = "SELECT * FROM tblStock"
            Dim dt As New DataTable()
            Using oConn As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
                Dim cmd As New SqlCommand(sql, oConn)
                Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
            End Using

            Response.Write("Datatable row count: " & dt.Rows.Count.ToString())
        End If
    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

How to convert SqlDataReader or DataReader to DataTable in Asp.Net CSHARP VB.Net

Download Code Example C#        Download Code Example VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code