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
