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
