Thursday, July 23, 2015

Select, Edit, update and Delete in Gridview with store procedure using Linq

In this article I will explain how to Select, Edit, update and Delete in Gridview with store procedure using Linq

Description:

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:
Select, Edit, update and Delete in Gridview with store procedure using Linq

  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. 

No comments:

Post a Comment