Thursday, September 10, 2015

ADO.Net entity framework CRUD function using store procedure in Gridview

In this article I am going to share how we can do CRUD (Create, Read, Update and Delete) operations in Gridview using ADO.Net Entity Framework

Description:

Create a table movie. I am going to insert, edit, update and delete the record into table using  Gridview.
ADO.Net entity framework CRUD function using store procedure in Gridview


Implementation:
First of all create store procedure to Insert, update, Select and delete the record.

Store Procedure to Insert Record
Create PROCEDURE Sp_InsertMovieDetail
(
@name varchar(100),
@genre varchar(100),
@cost int,
@poster varchar(max)
)
AS
BEGIN
            SET NOCOUNT ON;
Insert into Movie(Name,Genre,Cost,Poster) values(@name,@genre,@cost,@poster)
END

Store Procedure to Update Record
Create PROCEDURE Sp_UpdateMovieDetail
(
@id int,
@name varchar(100),
@genre varchar(100),
@cost int
)
AS
BEGIN
            SET NOCOUNT ON;
Update Movie set Name=@name,Genre=@genre,Cost=@cost where Id =@id
END

Store Procedure to select record
Create Proc Sp_GetMovieData
as
begin
Select * from Movie
End

Store Procedure to Delete Record
Create PROCEDURE Sp_DeleteMovieRecord
(
@id int
)
AS
BEGIN
            SET NOCOUNT ON;
Delete from Movie where Id =@id
END

Add webform to project/website. Drag and drop the Gridview control from toolbox to webform.

HTML Markup of Gridview:
    <fieldset style="width:450px;">
            <legend>Entity Framework Tutorial</legend>
                <asp:GridView ID="grdmovie" runat="server" Width="550px" AutoGenerateColumns="False" ShowFooter="True" 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("Name") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtname" runat="server" />
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Enter Movie Name" ControlToValidate="txtname"></asp:RequiredFieldValidator>
            </FooterTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Genre">
            <ItemTemplate>
               <asp:Label ID="lblgenre" runat="server" Text='<%# Eval("Genre") %>'></asp:Label>
            </ItemTemplate>
             <EditItemTemplate>
                <asp:TextBox ID="txtgenre" runat="server" Text='<%# Eval("Genre") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtgenre" runat="server" />
                 <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Enter Genre" ControlToValidate="txtgenre"></asp:RequiredFieldValidator>
            </FooterTemplate>
             <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Budget(In Crore)">
            <ItemTemplate>
               <asp:Label ID="lblcost" runat="server" Text='<%# Eval("Cost") %>'></asp:Label>
            </ItemTemplate>
             <EditItemTemplate>
                <asp:TextBox ID="txtcost" runat="server" Text='<%# Eval("Cost") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtcost" runat="server"></asp:TextBox>
                 <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Enter Budget" ControlToValidate="txtcost"></asp:RequiredFieldValidator>
            </FooterTemplate>
             <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
           <asp:TemplateField HeaderText="Poster">
            <ItemTemplate>
                 <asp:Image ID="Image1" ImageUrl='<%# Eval("Poster") %>' runat="server" width="200px"/>
            </ItemTemplate>            
            <FooterTemplate>
                <asp:FileUpload ID="FileUpload1" runat="server" />
                   </FooterTemplate>
                <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
            <asp:TemplateField>
            <ItemTemplate>
             <asp:ImageButton ID="imgbtnedit" runat="server" ImageUrl="~/btnimages/edit.png" CommandName="Edit" CausesValidation="false"/> <asp:ImageButton ID="imgbtndelete" runat="server" ImageUrl="~/btnimages/delete.png" CommandName="Delete" CausesValidation="false"/>
            </ItemTemplate>
                <EditItemTemplate>
                    <asp:ImageButton ID="imgcancel" runat="server" ImageUrl="~/btnimages/cancel.png" CommandName="Cancel" CausesValidation="false"/><asp:ImageButton ID="imgupdate" runat="server" ImageUrl="~/btnimages/update.png" CommandName="Update" CausesValidation="false"/>
                     </EditItemTemplate>
            <FooterTemplate>
                <asp:ImageButton ID="imgbtninsert" runat="server" ImageUrl="~/btnimages/insert.png" CommandName="Insert"/>
            </FooterTemplate>
                <ItemStyle VerticalAlign="Top" Width="400px" />
        </asp:TemplateField>
    </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>
        </fieldset>

Instantiate the Entity dbcontext
C#:
DemoEntities db = new DemoEntities();

VB:
Private db As New DemoEntities()

Create a function to fetch the record and bind to gridview and call it on page load event.
C#:
   protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
        }
    }
    public void BindGrid()
    {
        try
        {
            grdmovie.DataSource = db.Sp_GetMovieData();
            grdmovie.DataBind();
        }
        catch (Exception ex)
        { }
    }  

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()
        Try
            grdmovie.DataSource = db.Sp_GetMovieData()
            grdmovie.DataBind()
        Catch ex As Exception
        End Try
    End Sub

Edit the record
C#:
protected void grdmovie_RowEditing(object sender, GridViewEditEventArgs e)
    {
        grdmovie.EditIndex = e.NewEditIndex;
        BindGrid();
    }

VB:
  Protected Sub grdmovie_RowEditing(sender As Object, e As GridViewEditEventArgs) Handles grdmovie.RowEditing
        grdmovie.EditIndex = e.NewEditIndex
        BindGrid()
    End Sub

Write the code on RowCommand event of Gridview to Insert, Update and delete the record.
C#:
protected void grdmovie_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Insert")
        {
            TextBox txtname = (TextBox)grdmovie.FooterRow.FindControl("txtname");
            TextBox txtgenre = (TextBox)grdmovie.FooterRow.FindControl("txtgenre");
            TextBox txtcost = (TextBox)grdmovie.FooterRow.FindControl("txtcost");
            FileUpload fileupload = (FileUpload)grdmovie.FooterRow.FindControl("FileUpload1");
            string filepath = Server.MapPath("~/images/") + Guid.NewGuid() + fileupload.PostedFile.FileName;
            fileupload.SaveAs(filepath);
            string fl = filepath.Substring(filepath.LastIndexOf("\\"));
            string[] split = fl.Split('\\');
            string newpath = split[1];
            string imagepath = "~/images/" + newpath;
            db.Sp_InsertMovieDetail(txtname.Text, txtgenre.Text, Convert.ToInt32(txtcost.Text), imagepath);
            Response.Write("<script>alert('Record Insert Successfully');</script>");
            txtname.Text = string.Empty;
            txtcost.Text = string.Empty;
            txtgenre.Text = string.Empty;
            BindGrid();
        }
        if (e.CommandName == "Delete")
        {
            ImageButton imgbtn = (ImageButton)e.CommandSource;
            GridViewRow gvrow = ((GridViewRow)imgbtn.NamingContainer);
            int id = Convert.ToInt32(grdmovie.DataKeys[gvrow.RowIndex].Value);
            Movie objtb = new Movie();
            db.Sp_DeleteMovieRecord(id);           
            db.SaveChanges();
            BindGrid();
            Response.Write("<script>alert('Record Deleted Successfully');</script>");
        }
        if (e.CommandName == "Update")
        {
            ImageButton imgbtn = (ImageButton)e.CommandSource;
            GridViewRow gvrow = ((GridViewRow)imgbtn.NamingContainer);
            int id = Convert.ToInt32(grdmovie.DataKeys[gvrow.RowIndex].Value.ToString());
            TextBox txtname = (TextBox)grdmovie.Rows[gvrow.RowIndex].FindControl("txtname");
            TextBox txtgenre = (TextBox)grdmovie.Rows[gvrow.RowIndex].FindControl("txtgenre");
            TextBox txtcost = (TextBox)grdmovie.Rows[gvrow.RowIndex].FindControl("txtcost");
            db.Sp_UpdateMovieDetail(id,txtname.Text, txtgenre.Text, Convert.ToInt32(txtcost.Text));
            db.SaveChanges();           
            grdmovie.EditIndex = -1;
            BindGrid();
            Response.Write("<script>alert('Record Deleted Successfully');</script>");
        }
    }
protected void grdmovie_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
      
    }  
    protected void grdmovie_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

    }

VB:
Protected Sub grdmovie_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles grdmovie.RowCommand
        If e.CommandName = "Insert" Then
            Dim txtname As TextBox = DirectCast(grdmovie.FooterRow.FindControl("txtname"), TextBox)
            Dim txtgenre As TextBox = DirectCast(grdmovie.FooterRow.FindControl("txtgenre"), TextBox)
            Dim txtcost As TextBox = DirectCast(grdmovie.FooterRow.FindControl("txtcost"), TextBox)
            Dim fileupload As FileUpload = DirectCast(grdmovie.FooterRow.FindControl("FileUpload1"), FileUpload)
            Dim filepath As String = Server.MapPath("~/images/") & Guid.NewGuid().ToString() & fileupload.PostedFile.FileName
            fileupload.SaveAs(filepath)
            Dim fl As String = filepath.Substring(filepath.LastIndexOf("\"))
            Dim split As String() = fl.Split("\"c)
            Dim newpath As String = split(1)
            Dim imagepath As String = Convert.ToString("~/images/") & newpath
            db.Sp_InsertMovieDetail(txtname.Text, txtgenre.Text, Convert.ToInt32(txtcost.Text), imagepath)
            Response.Write("<script>alert('Record Insert Successfully');</script>")
            txtname.Text = String.Empty
            txtcost.Text = String.Empty
            txtgenre.Text = String.Empty
            BindGrid()
        End If
        If e.CommandName = "Delete" Then
            Dim imgbtn As ImageButton = DirectCast(e.CommandSource, ImageButton)
            Dim gvrow As GridViewRow = DirectCast(imgbtn.NamingContainer, GridViewRow)
            Dim id As Integer = Convert.ToInt32(grdmovie.DataKeys(gvrow.RowIndex).Value)
            Dim objtb As New Movie()
            db.Sp_DeleteMovieRecord(id)
            db.SaveChanges()
            BindGrid()
            Response.Write("<script>alert('Record Deleted Successfully');</script>")
        End If
        If e.CommandName = "Update" Then
            Dim imgbtn As ImageButton = DirectCast(e.CommandSource, ImageButton)
            Dim gvrow As GridViewRow = DirectCast(imgbtn.NamingContainer, GridViewRow)
            Dim id As Integer = Convert.ToInt32(grdmovie.DataKeys(gvrow.RowIndex).Value.ToString())
            Dim txtname As TextBox = DirectCast(grdmovie.Rows(gvrow.RowIndex).FindControl("txtname"), TextBox)
            Dim txtgenre As TextBox = DirectCast(grdmovie.Rows(gvrow.RowIndex).FindControl("txtgenre"), TextBox)
            Dim txtcost As TextBox = DirectCast(grdmovie.Rows(gvrow.RowIndex).FindControl("txtcost"), TextBox)
            db.Sp_UpdateMovieDetail(id, txtname.Text, txtgenre.Text, Convert.ToInt32(txtcost.Text))
            db.SaveChanges()
            grdmovie.EditIndex = -1
            BindGrid()
            Response.Write("<script>alert('Record Updated Successfully');</script>")
        End If
    End Sub
Protected Sub grdmovie_RowDeleting(sender As Object, e As GridViewDeleteEventArgs) Handles grdmovie.RowDeleting

    End Sub
    Protected Sub grdmovie_RowUpdating(sender As Object, e As GridViewUpdateEventArgs) Handles grdmovie.RowUpdating

    End Sub

Cancel the edit mode
C#:
protected void grdmovie_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        grdmovie.EditIndex = -1;
        BindGrid();
    }

VB:
Protected Sub grdmovie_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs) Handles grdmovie.RowCancelingEdit
        grdmovie.EditIndex = -1
        BindGrid()
    End Sub

Build the project and run, test it. Hope this helps you.



  In this article we have learn to how to Select, Edit, Update and Delete the record using Entity Framework 6.0 in asp.net Visual studio 2013I hope you enjoyed this article. 

2 comments: