How to fill datatable with data from database in ASP.NET(c#&vb.net)

Introduction:

In this article I will explain how to bind or fill datatable in asp.net using C# and Vb.Net. In this article I have used sql query to fetch data from database and fill the datatable 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 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();

        SqlDataAdapter dataadapater = new SqlDataAdapter("Select * from student_info", con);

        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 bind to gridview

Public Sub Bind_datatable()

    If con.State = ConnectionState.Open Then

        con.Close()

    End If

    con.Open()

    Dim dt As New DataTable()

    Dim dataadapater As New SqlDataAdapter("Select * from student_info", con)

    dataadapater.Fill(dt)

End Sub