Hi friends,in this article I will explain about How to bind data
to Gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in ASP.NET.
in previous articles i already explained about How to generate Text number in ASP.NET using VB.NET/C#, How to Generate Random Numbers in ASP.NET.,How do I disable right click on my web page,javascript - disable right click on webpage and Basic Seo Interview Questions and Answers for Beginners, SEO Interview Questions and Answers
Create a Database e.g. "Aspdotnet-roja" and a
table under that DataBase in Sql Server and name it "EMP" as shown in
figure:-
And create the stored procedure as display_emp_data to display the emp data as shown below.
|
CREATE PROCEDURE
display_EMP_DATA
AS
BEGIN
SELECT * FROM EMP
END
|
And open the .aspx
page and drag and drop the Gridview.
In we.config file write the following code to create the connection
string in the <connectionStrings>
Tag.
|
<connectionStrings>
<add name="con" connectionString="database=Aspdotnet-Roja;uid=sa;password=123;" />
</connectionStrings>
|
And imports below namespaces
|
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
|
Add a GridView control in design page of your asp.net
website under <BODY> tag
|
<asp:GridView ID="empgrid" runat="server" AllowSorting="True"
AutoGenerateColumns="False" BorderColor="#000099" BorderStyle="Double" >
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="EMP_id"
HeaderText="Emp_ID"
/>
<asp:BoundField DataField="empname" HeaderText="Emp_Name" />
<asp:BoundField DataField="SALARY"
HeaderText="Salary"
/>
</Columns>
<EditRowStyle BackColor="#FF99FF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FF99FF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#CCFFFF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1"
/>
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE"
/>
</asp:GridView>
|
And write the following code in code behind.
In VB.NET:
|
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class
_Default
Inherits
System.Web.UI.Page
Protected
Sub Page_Load(ByVal
sender As Object,
ByVal e As
System.EventArgs) Handles
Me.Load
If Not IsPostBack Then
Binddata()
End If
End Sub
Private Sub Binddata()
Dim
con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
Dim
ds As New DataSet()
Try
Dim
cmd As New SqlCommand("display_EMP_DATA",
con)
cmd.CommandType = CommandType.StoredProcedure
Dim
adp As New SqlDataAdapter(cmd)
adp.Fill(ds)
If
ds.Tables(0).Rows.Count > 0 Then
empgrid.DataSource = ds
empgrid.DataBind()
Else
empgrid.DataSource = Nothing
empgrid.DataBind()
End
If
Catch
ex As Exception
Response.Write("Error Occured: " & ex.ToString())
Finally
ds.Clear()
ds.Dispose()
End Try
End Sub
End Class
|
In C#.NET:
|
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Configuration;
partial class
_Default : System.Web.UI.Page
{
protected void Page_Load(object sender,
System.EventArgs e)
{
if (!IsPostBack) {
Binddata();
}
}
private void Binddata()
{
SqlConnection
con = new
SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString);
DataSet
ds = new DataSet();
try {
SqlCommand
cmd = new
SqlCommand("display_EMP_DATA", con);
cmd.CommandType
= CommandType.StoredProcedure;
SqlDataAdapter
adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
if (ds.Tables[0].Rows.Count > 0) {
empgrid.DataSource
= ds;
empgrid.DataBind();
}
else {
empgrid.DataSource
= null;
empgrid.DataBind();
}
}
catch (Exception ex) {
Response.Write("Error
Occured: " + ex.ToString());
}
finally {
ds.Clear();
ds.Dispose();
}
}
Public
_Default()
{
Load
+= Page_Load;
}
}
|
Save and run the page it will like as shown in the below
figure.
No comments:
Post a Comment