Introduction: In this
article I am going to explain how to Display, Insert, Edit, Update and delete
data using datalist control in asp.net
Description:
In
this example I have insert the data into database table using FooterTemplate of
datalist control. To make this example live use the code step wise.
Create table
Tb_Student
(
Id int not
null identity,
Student_Name varchar(50),
Student_Address varchar(200),
RollNo int
)
Now
create store procedures to Get, Insert, update and Delete from database.
Procedure
to GET data:
Create Proc [dbo].[Sp_GetStudentData]
As begin
Select * from dbo.Tb_Student
end
Procedure
to INSERT the data/records:
Create Proc
Sp_InsertStudents
(
@sname varchar(50),
@saddress varchar(200),
@rollno int
)
As begin
Insert into dbo.Tb_Student values(@sname,@saddress,@rollno)
end
Procedure
to UPDATE records:
Create Proc
Sp_UpdateStudents
(
@id int,
@sname varchar(50),
@saddress varchar(200),
@rollno int
)
As begin
Update Tb_Student set
Student_Name=@sname,Student_Address=@saddress,RollNo=@rollno where id =@id
end
Procedure
to DELETE the records:
Create Proc
Sp_DeleteStudents
(
@id int
)
As begin
Delete from
Tb_Student where id =@id
end
After
that add a webform to website/project. Drag and drop the Datalist control from toolbox
to webform. Design the datalist as mention below:
HTML markup
of Page:
<asp:DataList ID="dlstudent" runat="server" DataKeyField="Id"
oncancelcommand="dlstudent_CancelCommand" oneditcommand="dlstudent_EditCommand"
onupdatecommand="dlstudent_UpdateCommand"
ondeletecommand="dlstudent_DeleteCommand"
onitemcommand="dlstudent_ItemCommand" ShowFooter="true"
ShowHeader="true">
<HeaderTemplate>
<table><tr>
<th>Student Name</th><th>Student Address</th><th>RollNo</th></tr>
</HeaderTemplate>
<ItemTemplate>
<tr align="center">
<td><%# DataBinder.Eval(Container.DataItem,
"Student_Name")%></td>
<td> <%# DataBinder.Eval(Container.DataItem,"Student_Address") %></td>
<td> <%# DataBinder.Eval(Container.DataItem,
"RollNo") %></td>
<td><asp:ImageButton ID="imgbtnedit" runat="server" ImageUrl="~/images/btnedit.png" CommandName="edit"
ToolTip="Edit"
Width="32px"
CausesValidation="false"
/></td>
<td><asp:ImageButton ID="imgbtndelete" runat="server" ImageUrl="~/images/btndelete.png" CommandName="Delete"
ToolTip="Delete"
Width="32px"
CausesValidation="false"/></td>
</tr>
</ItemTemplate>
<EditItemTemplate>
<tr>
<td><asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Student_Name") %>'></asp:TextBox></td>
<td><asp:TextBox ID="txtaddress" runat="server" Text='<%# Eval("Student_Address") %>'></asp:TextBox></td>
<td><asp:TextBox ID="txtrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:TextBox></td>
<td><asp:ImageButton ID="imgbtncancel" runat="server" ImageUrl="~/images/btncancel.png" CommandName="cancel"
ToolTip="Cancel"
Width="32px"
CausesValidation="false"/></td>
<td><asp:ImageButton ID="imgbtnupdate" runat="server" ImageUrl="~/images/btnupdate.png" CommandName="update"
ToolTip="Update"
Width="32px"
CausesValidation="false"/></td>
</tr>
</EditItemTemplate>
<FooterTemplate>
<tr>
<td><asp:TextBox ID="txtsname" runat="server"></asp:TextBox ><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="txtsname"
ErrorMessage="Enter
Student name"></asp:RequiredFieldValidator></td>
<td><asp:TextBox ID="txtsaddress" runat="server"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="txtsaddress"
ErrorMessage="Enter
Student Address"></asp:RequiredFieldValidator></td>
<td><asp:TextBox ID="txtsrollno" runat="server"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
ControlToValidate="txtsrollno"
ErrorMessage="Enter
Roll No."></asp:RequiredFieldValidator>
<asp:CompareValidator ID="Comp1" runat="server" ErrorMessage="Enter numeric value only" ControlToValidate="txtsrollno"
Operator="DataTypeCheck"
Type="Integer"
></asp:CompareValidator></td>
<td><asp:ImageButton ID="imgbtnadd" runat="server" ImageUrl="~/images/btnadd.png" CommandName="Insert"
ToolTip="Add New
Record" Width="32px" CausesValidation="true"/></td>
</tr>
</FooterTemplate>
</asp:DataList>
Now
we have written the code. Firstly add the namespace.
C#:
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
VB:
Imports
System.Data
Imports
System.Data.SqlClient
Imports
System.Configuration
Create a
connection:
C#:
SqlConnection
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
VB:
Private
con As New
SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
Write the code
to bind the data to Datalist:
C#:
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
BindDatalist();
}
}
public void BindDatalist()
{
try
{
SqlDataAdapter
adp = new SqlDataAdapter("Sp_GetStudentData", con);
adp.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable
dt = new DataTable();
adp.Fill(dt);
if
(dt.Rows.Count > 0)
{
dlstudent.DataSource = dt;
dlstudent.DataBind();
}
}
catch (Exception ex)
{
throw
ex;
}
}
VB:
Protected Sub Page_Load(sender As
Object, e As
EventArgs) Handles Me.Load
If Not IsPostBack Then
BindDatalist()
End If
End Sub
Public Sub BindDatalist()
Try
Dim
adp As New
SqlDataAdapter("Sp_GetStudentData",
con)
adp.SelectCommand.CommandType =
CommandType.StoredProcedure
Dim
dt As New
DataTable()
adp.Fill(dt)
If
dt.Rows.Count > 0 Then
dlstudent.DataSource = dt
dlstudent.DataBind()
End If
Catch
ex As Exception
Throw
ex
End Try
End Sub
Write
the below given code to Update the
Record:
C#:
protected void dlstudent_UpdateCommand(object
source, DataListCommandEventArgs e)
{
try
{
int
id = Convert.ToInt32(dlstudent.DataKeys[e.Item.ItemIndex].ToString());
TextBox
txtname = (TextBox)e.Item.FindControl("txtname");
TextBox
txtaddress = (TextBox)e.Item.FindControl("txtaddress");
TextBox
txtroll = (TextBox)e.Item.FindControl("txtrollno");
SqlCommand
cmd = new SqlCommand("Sp_UpdateStudents", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@sname", txtname.Text);
cmd.Parameters.AddWithValue("@saddress", txtaddress.Text);
cmd.Parameters.AddWithValue("@rollno", Convert.ToInt32(txtroll.Text));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
Response.Write("<script
type=\"text/javascript\">alert('Updated
Successfully!!!');</script>");
dlstudent.EditItemIndex = -1;
BindDatalist();
}
catch (Exception ex)
{
throw
ex;
}
}
VB:
Protected Sub dlstudent_UpdateCommand(source As Object, e As DataListCommandEventArgs)
Try
Dim
id As Integer =
Convert.ToInt32(dlstudent.DataKeys(e.Item.ItemIndex).ToString())
Dim
txtname As TextBox = DirectCast(e.Item.FindControl("txtname"), TextBox)
Dim
txtaddress As TextBox = DirectCast(e.Item.FindControl("txtaddress"),
TextBox)
Dim
txtroll As TextBox = DirectCast(e.Item.FindControl("txtrollno"), TextBox)
Dim
cmd As New
SqlCommand("Sp_UpdateStudents",
con)
cmd.CommandType =
CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@id", id)
cmd.Parameters.AddWithValue("@sname", txtname.Text)
cmd.Parameters.AddWithValue("@saddress", txtaddress.Text)
cmd.Parameters.AddWithValue("@rollno",
Convert.ToInt32(txtroll.Text))
con.Open()
cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()
Response.Write("<script
type=""text/javascript"">alert('Updated
Successfully!!!');</script>")
dlstudent.EditItemIndex = -1
BindDatalist()
Catch
ex As Exception
Throw
ex
End Try
End Sub
Code
to Delete the records:
C#:
protected void dlstudent_DeleteCommand(object
source, DataListCommandEventArgs e)
{
try
{
int
id = Convert.ToInt32(dlstudent.DataKeys[e.Item.ItemIndex].ToString());
SqlCommand
cmd = new SqlCommand("Sp_DeleteStudents", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
Response.Write("<script
type=\"text/javascript\">alert('Deleted
Successfully!!!');</script>");
BindDatalist();
}
catch (Exception ex)
{
throw
ex;
}
}
VB:
Protected Sub dlstudent_DeleteCommand(source As Object, e As DataListCommandEventArgs)
Try
Dim
id As Integer =
Convert.ToInt32(dlstudent.DataKeys(e.Item.ItemIndex).ToString())
Dim
cmd As New
SqlCommand("Sp_DeleteStudents",
con)
cmd.CommandType =
CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@id", id)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()
Response.Write("<script type=""text/javascript"">alert('Deleted
Successfully!!!');</script>")
BindDatalist()
Catch
ex As Exception
Throw
ex
End Try
End Sub
Code
to Insert the Data into database
table:
C#:
protected void dlstudent_ItemCommand(object
source, DataListCommandEventArgs e)
{
try
{
if
(e.CommandName == "Insert")
{
TextBox
txtname = (TextBox)e.Item.FindControl("txtsname");
TextBox
txtaddress = (TextBox)e.Item.FindControl("txtsaddress");
TextBox
txtrollno = (TextBox)e.Item.FindControl("txtsrollno");
SqlCommand
cmd = new SqlCommand("Sp_InsertStudents", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sname",txtname.Text);
cmd.Parameters.AddWithValue("@saddress",txtaddress.Text);
cmd.Parameters.AddWithValue("@rollno",txtrollno.Text);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
Response.Write("<script
type=\"text/javascript\">alert('Inserted
Successfully!!!');</script>");
BindDatalist();
}
}
catch (Exception ex)
{
throw
ex;
}
}
VB:
Protected Sub dlstudent_ItemCommand(source As Object, e As DataListCommandEventArgs)
Try
If
e.CommandName = "Insert" Then
Dim
txtname As TextBox = DirectCast(e.Item.FindControl("txtsname"), TextBox)
Dim
txtaddress As TextBox = DirectCast(e.Item.FindControl("txtsaddress"),
TextBox)
Dim
txtrollno As TextBox = DirectCast(e.Item.FindControl("txtsrollno"), TextBox)
Dim
cmd As New
SqlCommand("Sp_InsertStudents",
con)
cmd.CommandType =
CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@sname", txtname.Text)
cmd.Parameters.AddWithValue("@saddress", txtaddress.Text)
cmd.Parameters.AddWithValue("@rollno", txtrollno.Text)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()
Response.Write("<script
type=""text/javascript"">alert('Inserted Successfully!!!');</script>")
BindDatalist()
End
If
Catch
ex As Exception
Throw
ex
End Try
End Sub
Code
to enable the edit items of datalist:
C#:
protected void dlstudent_EditCommand(object
source, DataListCommandEventArgs e)
{
dlstudent.EditItemIndex =
e.Item.ItemIndex;
BindDatalist();
}
VB:
Protected Sub dlstudent_EditCommand(source As Object, e As DataListCommandEventArgs)
dlstudent.EditItemIndex =
e.Item.ItemIndex
BindDatalist()
End Sub
Code
to cancel the edit intms of datalist
control:
C#:
protected void dlstudent_CancelCommand(object
source, DataListCommandEventArgs e)
{
dlstudent.EditItemIndex = -1;
BindDatalist();
}
VB:
Protected Sub dlstudent_CancelCommand(source As Object, e As DataListCommandEventArgs)
dlstudent.EditItemIndex = -1
BindDatalist()
End Sub
Build,
run the project/website and check the result.
In this article we have learn display, edit, update and delete data with datalist control in asp.net. I hope you enjoyed this article.
In this article we have learn display, edit, update and delete data with datalist control in asp.net. I hope you enjoyed this article.
.png)
No comments:
Post a Comment