In this article we will learn
how to fill or populate dataset with data from database in asp.net using c# and
vb.net. First of we will fetch data from Sql database using Sql query then fill data in Dataset using SqlDataAdapter.
Implementation:
1.
Create a database i.e. “Blog”. 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)
|
2. Now insert some data in this table using “insert”
command.
INSERT INTO student_info(Student_Name,Age,Class) VALUES('Akhsay',12, 'first')
INSERT INTO student_info(Student_Name,Age,Class) VALUES('Raghav',25, 'M.Sc')
INSERT INTO student_info(Student_Name,Age,Class) VALUES('aksht', 23, 'Bsc')
INSERT INTO student_info(Student_Name,Age,Class) VALUES('ankit', 23, 'MBA')
INSERT INTO student_info(Student_Name,Age,Class) VALUES('anil', 23, 'MCA')
|
3. Create Connection: Now create
connection in webcofig file as given
below.
<connectionStrings>
<add name="con" connectionString="Data
Source=localhost; Initial Catalog= Blog; Integrated Security=true;" providerName="System.Data.SqlClient"/>
</connectionStrings>
|
4. ASP.NET code behind
File using C#:
In code behind, add following code:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
|
Now
Fetch data and Fill Dataset:
SqlConnection
con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
protected
void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
Fill_dataset();
}
}
//Fetch data
from database
public void Fill_dataset()
{
if
(con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
DataSet
ds = new DataSet();
SqlDataAdapter
dataadapater = new SqlDataAdapter("Select * from student_info", con);
dataadapater.Fill(ds);
}
|
VB.NET code behind file:
In code behind, add following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
|
Now
Fetch data and Fill Dataset:
Partial Public
Class gridview
Inherits
System.Web.UI.Page
Protected
Sub Page_Load(sender As
Object, e As EventArgs)
If Not IsPostBack Then
Fill_dataset ()
End If
End Sub
'Fetch data
from database
Public Sub Fill_dataset ()
Dim
con As New
SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
If
con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
Dim ds
As New DataSet()
Dim
dataadapater As New
SqlDataAdapter("Select * from
student_info", con)
dataadapater.Fill(ds)
End Sub
End Class
|
Demo:
Use this code, create your own
application and check result. You can use this code in any application by
customizing it. If you have any doubt, you can ask by commenting in comment
section.
