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:

In the previous article I have explained Sql server Local vs. Global temporarytable, Asp.net Select, Edit, Update and Delete record in Gridview using EntityFramework and How to set up ADO.net Entity Framework project or website inasp.net.

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)

)

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

)

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

begin

Select * from Movie

End

Store Procedure to Delete Record

Create PROCEDURE Sp_DeleteMovieRecord

(

@id int

)

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:

<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">

<asp:TemplateField HeaderText="Name">

<asp:Label ID="lblname" runat="server" Text='<%# Eval("Name") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtname" runat="server" />

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Enter Movie Name" ControlToValidate="txtname"></asp:RequiredFieldValidator>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Genre">

<asp:Label ID="lblgenre" runat="server" Text='<%# Eval("Genre") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtgenre" runat="server" Text='<%# Eval("Genre") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtgenre" runat="server" />

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Enter Genre" ControlToValidate="txtgenre"></asp:RequiredFieldValidator>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Budget(In Crore)">

<asp:Label ID="lblcost" runat="server" Text='<%# Eval("Cost") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtcost" runat="server" Text='<%# Eval("Cost") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtcost" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Enter Budget" ControlToValidate="txtcost"></asp:RequiredFieldValidator>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Poster">

<asp:Image ID="Image1" ImageUrl='<%# Eval("Poster") %>' runat="server" width="200px"/>

</ItemTemplate>

<asp:FileUpload ID="FileUpload1" runat="server" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField>

<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>

<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>

<asp:ImageButton ID="imgbtninsert" runat="server" ImageUrl="~/btnimages/insert.png" CommandName="Insert"/>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</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 2013. I hope you enjoyed this article.

Thursday, September 10, 2015

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

2 comments:

Popular Posts

Labels