In
this article I will explain how to Select, Edit, update and Delete in Gridview
with store procedure using Linq
Description:
In
the previous article I have explained Bind Gridview with Stored Procedure usingLinq to Sql, Insert Data into Database using Store procedure in Linq to sql and
How to Bind, Edit, Update and Delete in Gridview Control using LINQ.
I
have created a table Tb_Student and having records. To display, Update and
Delete the students detail in Gridview create store procedures.
Implementation:
First
of all I have created Store Procedures to Get, Update and Delete.
Store
Procedure to get data:-
CREATE Proc
Sp_GetStudentData
As begin
Select * from dbo.Tb_Student
end
Store
Procedure to Update data:-
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
Store
Procedure to Delete data:-
CREATE Proc
Sp_DeleteStudents
(
@id int
)
As begin
Delete from
Tb_Student where id =@id
end
Now
drag and drop the Gridview control from toolbox to webform.
Gridview
markup:
<asp:GridView ID="grdstudent" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="4" DataKeyNames="Id" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%# Eval("Student_Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Student_Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City/State">
<ItemTemplate>
<asp:Label ID="lbladdress" runat="server" Text='<%# Eval("Student_Address") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtaddress" runat="server" Text='<%# Eval("Student_Address") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Roll No.">
<ItemTemplate>
<asp:Label ID="lblrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
Create
object of dataContext
C#:
ProjectDataClassesDataContext db = new ProjectDataClassesDataContext();
VB:
Dim db As New ProjectDataClassesDataContext
Code to
populate the gridview
C#:
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindGrid();
}
}
public void BindGrid()
{
grdstudent.DataSource = db.Sp_GetStudentData();
grdstudent.DataBind();
}
VB:
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGrid()
End If
End Sub
Public Sub BindGrid()
grdstudent.DataSource = db.Sp_GetStudentData
grdstudent.DataBind()
End Sub
PageIndex
event of Gridview for paging
C#:
protected void
grdstudent_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdstudent.PageIndex = e.NewPageIndex;
BindGrid();
}
VB:
Protected Sub
grdstudent_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles grdstudent.PageIndexChanging
grdstudent.PageIndex = e.NewPageIndex
BindGrid()
End Sub
Enable the
editing mode of Gridview record
C#:
protected void grdstudent_RowEditing(object sender, GridViewEditEventArgs e)
{
grdstudent.EditIndex = e.NewEditIndex;
BindGrid();
}
VB:
Protected Sub
grdstudent_RowEditing(sender As Object, e As GridViewEditEventArgs) Handles grdstudent.RowEditing
grdstudent.EditIndex = e.NewEditIndex
BindGrid()
End Sub
Update the
Record
Write
the code on RowUpdating event of Gridview
C#:
protected void
grdstudent_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{
int id = Convert.ToInt32(grdstudent.DataKeys[e.RowIndex].Value);
TextBox
txtname = (TextBox)grdstudent.Rows[e.RowIndex].FindControl("txtname");
TextBox
txtaddress = (TextBox)grdstudent.Rows[e.RowIndex].FindControl("txtaddress");
TextBox
txtrollno = (TextBox)grdstudent.Rows[e.RowIndex].FindControl("txtrollno");
db.Sp_UpdateStudents(id, txtname.Text,
txtaddress.Text, Convert.ToInt32(txtrollno.Text));
db.SubmitChanges();
grdstudent.EditIndex = -1;
BindGrid();
Response.Write("<script
type=\"text/javascript\">alert('Record has been Updated Successfully');</script>");
}
catch(Exception ex)
{
}
}
VB:
Protected Sub
grdstudent_RowUpdating(sender As Object, e As GridViewUpdateEventArgs) Handles grdstudent.RowUpdating
Try
Dim id As Integer = Convert.ToInt32(grdstudent.DataKeys(e.RowIndex).Value)
Dim txtname As TextBox = DirectCast(grdstudent.Rows(e.RowIndex).FindControl("txtname"), TextBox)
Dim txtaddress As TextBox = DirectCast(grdstudent.Rows(e.RowIndex).FindControl("txtaddress"), TextBox)
Dim txtrollno As TextBox = DirectCast(grdstudent.Rows(e.RowIndex).FindControl("txtrollno"), TextBox)
db.Sp_UpdateStudents(id,
txtname.Text, txtaddress.Text, Convert.ToInt32(txtrollno.Text))
db.SubmitChanges()
grdstudent.EditIndex = -1
BindGrid()
Response.Write("<script
type=""text/javascript"">alert('Record has been updated
successfully');</script>")
Catch ex As Exception
End Try
End Sub
Cancel the
editing record
Write
the code on RowCancelingEdit event of gridview
C#:
protected void grdstudent_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grdstudent.EditIndex = -1;
BindGrid();
}
VB:
Protected Sub
grdstudent_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs) Handles grdstudent.RowCancelingEdit
grdstudent.EditIndex = -1
BindGrid()
End Sub
Delete the
record
To
delete the record write the given code on RowDeleting event of gridview.
C#:
protected void
grdstudent_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
int id = Convert.ToInt32(grdstudent.DataKeys[e.RowIndex].Value);
db.Sp_DeleteStudents(id);
db.SubmitChanges();
BindGrid();
Response.Write("<script
type=\"text/javascript\">alert('Record has been Deleted
Successfully');</script>");
}
catch(Exception ex)
{
}
}
VB:
Protected Sub
grdstudent_RowDeleting(sender As Object, e As GridViewDeleteEventArgs) Handles grdstudent.RowDeleting
Try
Dim id As Integer = Convert.ToInt32(grdstudent.DataKeys(e.RowIndex).Value)
db.Sp_DeleteStudents(id)
db.SubmitChanges()
BindGrid()
Response.Write("<script
type=""text/javascript"">alert('Record has been Deleted
successfully');</script>")
Catch ex As Exception
End Try
End Sub
Now build, run the project/website and check the result.
Result:
In this article we have learn How to Select, Edit, Update and Delete in Gridview with Store procedure using LINQ to SQL (C#, VB). I hope you enjoyed this article.
.png)
No comments:
Post a Comment