How to Fill or populate Datatable using stored procedure in Asp.Net (c#&vb.net)?

Introduction:

In this article I will learn how to fill or populate Datatable using Stored Procedure with data from database in asp.net. First of we will fetch data from Sql database using Stored Procedure then  fill data in Dataset using SqlDataAdapter.

Implementation: Follow following steps

Create a database i.e. “Test”.  Then create a table “Student_Info”.

Column Name	Datatype
Student_id	Int(Primary Key. So set Is Identity=True)
Student_Name	Varchar(500)
Age	int
Class	Varchar(50)

Now insert some data in this table using “insert” command.

Create Stored Procedure:

create proc Fill_DataTable

as

begin

select * from student_info

end

Create Connection: Now create connection in  webconfig file as given below.

<connectionStrings>

    <add name="con" connectionString="Data Source=localhost; Initial Catalog= Blog; Integrated Security=true;" providerName="System.Data.SqlClient"/>

  </connectionStrings>

ASP.NET code behind File using C#:

Add following Namespaces:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

C# Code to get data and Fill Data Table:

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

    protected void Page_Load(object sender, EventArgs e) 

    {

        if (!IsPostBack)

        {

            Bind_datatable();

        }

    }

    //Fetch data from database and bind to gridview

public void    Bind_datatable ()

    {     

        if (con.State == ConnectionState.Open)

        {

            con.Close();

        }

        con.Open();

        DataTable dt = new DataTable ();

        SqlCommand cmd = new SqlCommand();

        cmd.Connection = con;

        cmd.CommandText = "Fill_ DataTable ";

        cmd.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter dataadapater = new SqlDataAdapter();

    dataadapater.SelectCommand = cmd;

        dataadapater.Fill(dt);      

    }

VB.NET code:

Add following namespaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Now Write Following Code:

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)

Protected Sub Page_Load(sender As Object, e As EventArgs)

    If Not IsPostBack Then

        Bind_datatable()

    End If

End Sub

'Fetch data from database and fill dataset

Public Sub  Bind_datatable ()

    If con.State = ConnectionState.Open Then

        con.Close()

    End If

    con.Open()

    Dim dt As New DataTable ()

    Dim cmd As New SqlCommand()

    cmd.Connection = con

    cmd.CommandText = "Fill_DataTable"

    cmd.CommandType = CommandType.StoredProcedure

    Dim dataadapater As New SqlDataAdapter()

    dataadapater.SelectCommand = cmd

    dataadapater.Fill(dt)

End Sub