Hi friends,in this article I will explain about How to bind
gridview using SqlDataAdapter, DataTable and Stored procedure in Sql server.
In previous articles i already explained about How to bind data to Gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in ASP.NET using VB.NET/C#.NET,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="Gridview1" 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 dt As New DataTable()
Try
Dim
con As New SqlConnection(ConfigurationManager.ConnectionStrings("Con").ConnectionString)
Dim
adp As New SqlDataAdapter("display_EMP_DATA",
con)
adp.SelectCommand.CommandType = CommandType.StoredProcedure
adp.Fill(dt)
If
dt.Rows.Count > 0 Then
GridView1.DataSource = dt
GridView1.DataBind()
Else
GridView1.DataSource = Nothing
GridView1.DataBind()
End
If
Catch
ex As Exception
Response.Write("Error Occured: " & ex.ToString())
Finally
dt.Clear()
dt.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()
{
DataTable
dt = new DataTable();
try {
SqlConnection
con = new
SqlConnection(ConfigurationManager.ConnectionStrings("Con").ConnectionString);
SqlDataAdapter
adp = new
SqlDataAdapter("display_EMP_DATA", con);
adp.SelectCommand.CommandType
= CommandType.StoredProcedure;
adp.Fill(dt);
if (dt.Rows.Count > 0) {
GridView1.DataSource
= dt;
GridView1.DataBind();
}
else {
GridView1.DataSource
= null;
GridView1.DataBind();
}
}
catch (Exception ex) {
Response.Write("Error
Occured: " + ex.ToString());
}
finally {
dt.Clear();
dt.Dispose();
}
}
Public
_Default()
{
Load
+= Page_Load;
}
}
|
Save and run the page it will like as shown in the below
figure.
I think you like my blog why are waiting following me on facebook fan page Aspdotnet-roja
No comments:
Post a Comment