Introduction: In this post I try to explain how to bind
Datalist control in Asp.net using Store procedure.
Description:
I have created a table name STUDENT_DETAIL and insert data
into table. STUDENT_ID is primary key.
STUDENT_ID
|
int
|
STUDENT_NAME
|
varchar(50)
|
STUDENT_ADDRESS
|
varchar(50)
|
STUDENT_CLASS
|
varchar(50)
|
Create a Store Procedure:
CREATE PROCEDURE DISPLAY_DATA
AS
BEGIN
SET NOCOUNT
ON;
SELECT * FROM dbo.STUDENT_DETAIL
END
Now open the Visual Studio>Go to File>New>Website.
Add the Connectionstring in web.config file of website.
<configuration>
<connectionStrings>
<add name="connection" connectionString="Data Source=SYS-1F78031ED0A;Initial
Catalog=TestBlog;Integrated Security=True"/>
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
</configuration>
After that add new web
form to website, drag and drop the Datalist data control from Toolbox.
<asp:DataList ID="dlstudent"
runat="server"
DataKeyField="STUDENT_ID">
<HeaderStyle Font-Bold="True" BorderColor="Black" />
<HeaderTemplate>
<table border="1"><tr
style="background-color:Blue;color:White;">
<td><b>Student Name</b> </td>
<td>Student
Address</td>
<td>Student Class</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr style="font-style:italic;">
<td align="center"><asp:Label ID="lblname"
runat="server"
Text='<%# Eval("STUDENT_NAME") %>'></asp:Label></td>
<td align="center"><asp:Label ID="lbladdress"
runat="server"
Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label></td>
<td align="center"><asp:Label ID="lblclass"
runat="server"
Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label></td>
</tr>
</ItemTemplate>
</asp:DataList>
<table border="1">
<tr style="color:Red;"><td>
<asp:Label ID="lblmessage" runat="server" Text=""></asp:Label></td></tr></table>
Now go to .aspx.cs page.
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
SqlConnection con
= new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
bindDatalist();
}
}
private void bindDatalist()
{
try
{
SqlCommand
cmd = new SqlCommand("DISPLAY_DATA", con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter
adp = new SqlDataAdapter(cmd);
DataTable
dt = new DataTable();
adp.Fill(dt);
if
(dt.Rows.Count > 0)
{
dlstudent.DataSource = dt;
dlstudent.DataBind();
con.Close();
}
else
{
dlstudent.DataSource = null;
dlstudent.DataBind();
lblmessage.Text = "No Data Found";
}
}
catch (Exception ex)
{
}
}
In VB
Dim con
As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
Protected Sub Page_Load(ByVal
sender As Object,
ByVal e As
System.EventArgs) Handles
Me.Load
If
con.State = ConnectionState.Closed Then
con.Open()
End If
If Not IsPostBack Then
BindDatalist()
End If
End Sub
Private Sub BindDatalist()
Try
Dim
cmd As New SqlCommand("DISPLAY_DATA",
con)
Dim
adp As New SqlDataAdapter(cmd)
cmd.CommandType = CommandType.StoredProcedure
Dim
dt As New DataTable()
adp.Fill(dt)
If
dt.Rows.Count > 0 Then
dlstudent.DataSource = dt
dlstudent.DataBind()
Else
dlstudent.DataSource = Nothing
dlstudent.DataBind()
lblmessage.Text = "No Data Found"
End
If
Catch
ex As Exception
End Try
End Sub
Run the project and check the result.
Related Articles on Datalist
No comments:
Post a Comment