Fill or populate dataset using stored procedure in asp.net (c# & vb.net).

Introduction:

In this article I will explain how to fill or populate dataset using Stored Procedure  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_DataSet

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)

        {

            Fill_dataset();

        }

    }

    //Fetch data from database and bind to gridview

public void Fill_dataset()

    {     

        if (con.State == ConnectionState.Open)

        {

            con.Close();

        }

        con.Open();

        DataSet ds = new DataSet();

        SqlCommand cmd = new SqlCommand();

        cmd.Connection = con;

        cmd.CommandText = "Fill_Dataset";

        cmd.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter dataadapater = new SqlDataAdapter();

    dataadapater.SelectCommand = cmd;

        dataadapater.Fill(ds);      

    }

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

        Fill_dataset()

    End If

End Sub

'Fetch data from database and fill dataset

Public Sub Fill_dataset()

    If con.State = ConnectionState.Open Then

        con.Close()

    End If

    con.Open()

    Dim ds As New DataSet()

    Dim cmd As New SqlCommand()

    cmd.Connection = con

    cmd.CommandText = "Fill_Dataset"

    cmd.CommandType = CommandType.StoredProcedure

    Dim dataadapater As New SqlDataAdapter()

    dataadapater.SelectCommand = cmd

    dataadapater.Fill(ds)

End Sub