WCF tutorial to Insert, Bind, Edit, update and Delete the record from sql server database in Asp.net

In this tutorial I am going to explain how to Insert, Bind, Edit, update and Delete the record using WCF service from sql server database in Asp.net using C#

Description:

In the previous article I have explained Display records in Gridview from sqlserver database using WCF service , Create and Consume WCF Service using visualstudio 2013 and How to insert record into Sql Server database using WCF service.

We have to perform the following task to Insert, Bind, Edit, update and Delete the record into Sql Server database:

Create Database and table

Create a WCF service

Create a Web application (Website) to consume the WCF service

Implementation:

I have created a Table Movie:

WCF tutorial to Insert, Bind, Edit, update and Delete the record from sql server database in Asp.net

Create store procedures to Insert, Update, Delete and fetch record.

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

Procedure to Delete record:

Create PROCEDURE Sp_DeleteMovieRecord

(

@id int

)

BEGIN

SET NOCOUNT ON;

Delete from Movie where Id =@id

END

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

Procedure to Fetch record:

Create Proc Sp_GetMovieData

begin

Select * from Movie

End

Create a WCF Service:

To create a WCF open Visual Studio >> File>> New >> Project >> Visual C# >> WCF service application >> Type the name of WCF service that you want to keep. In this tutorial WCFService is the name of service.

WCF service application will open.

First of all open the web.config file and set the connectionString.

</connectionStrings>

Now open the Iservice.cs file and remove the sample code from it.

Add the namespace

using System.Data;

Write the below given code in Iservice.cs below the ServiceContract.

public interface IService

{

[OperationContract]

void InsertMovieData(Movie objmovie);

[OperationContract]

DataSet GetMovieData();

[OperationContract]

void UpdateMovieRecord(Movie objmovie);

[OperationContract]

void DeleteMovieRecord(Movie objmovie);

}

[DataContract]

public class Movie

{

[DataMember]

public int id { get; set; }

[DataMember]

public string name {get; set;}

[DataMember]

public string genre{ get; set;}

[DataMember]

public int Cost { get; set; }

[DataMember]

public string Poster { get; set; }

}

After that move to Service.svc.cs/Service.cs file and also remove the sample code.

Add the namespace

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

Write the code to define the definition of function to insert, get, delete and update the record.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ToString());

public void InsertMovieData(Movie objmovie)

{

SqlCommand cmd = new SqlCommand("Sp_InsertMovieDetail", con);

cmd.CommandType = CommandType.StoredProcedure;

con.Open();

cmd.Parameters.AddWithValue("@name", objmovie.name);

cmd.Parameters.AddWithValue("@genre", objmovie.genre);

cmd.Parameters.AddWithValue("@cost", objmovie.Cost);

cmd.Parameters.AddWithValue("@poster", objmovie.Poster);

cmd.ExecuteNonQuery();

con.Close();

}

public DataSet GetMovieData()

{

SqlDataAdapter adp = new SqlDataAdapter("Sp_GetMovieData", con);

adp.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();

adp.Fill(ds);

return ds;

}

public void UpdateMovieRecord(Movie objmovie)

{

try

{

SqlCommand cmd = new SqlCommand("Sp_UpdateMovieDetail", con);

cmd.CommandType = CommandType.StoredProcedure;

con.Open();

cmd.Parameters.AddWithValue("@id", objmovie.id);

cmd.Parameters.AddWithValue("@name", objmovie.name);

cmd.Parameters.AddWithValue("@genre", objmovie.genre);

cmd.Parameters.AddWithValue("@cost", objmovie.Cost);

// cmd.Parameters.AddWithValue("@poster", objmovie.Poster);

cmd.ExecuteNonQuery();

con.Close();

}

catch(Exception ex)

{ }

}

public void DeleteMovieRecord(Movie objmovie)

{

SqlCommand cmd = new SqlCommand("Sp_DeleteMovieRecord", con);

cmd.CommandType = CommandType.StoredProcedure;

con.Open();

cmd.Parameters.AddWithValue("@id", objmovie.id);

cmd.ExecuteNonQuery();

con.Close();

}

Build the project and run.

Don’t close the WCF service application keep it running to consume.

Create a Web application:

To consume the WCF service creates a new website.

After creating new website go to Solution Explore and Right click on Website >> ADD >> Click on Service reference. On click Add service reference pop up will be open. Copy the URL of WCF service and paste it in displaying input. Enter the namespace and click on Go button.

In this website ServiceReference is the namespace.

Add a webform to website.

HTML Markup of Webform:

<asp:GridView ID="grdmovie" runat="server" Width="550px" AutoGenerateColumns="False" ShowFooter="True" DataKeyNames="Id"

CellPadding="4" ForeColor="#333333" GridLines="None" OnRowCommand="grdmovie_RowCommand" OnRowCancelingEdit="grdmovie_RowCancelingEdit" OnRowEditing="grdmovie_RowEditing" OnRowDeleting="grdmovie_RowDeleting" OnRowUpdating="grdmovie_RowUpdating">

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

Add the namespace

using ServiceReference;

using System.Data;

Now create the object of WCF service and write the below given code:

ServiceReference.ServiceClient objclient = new ServiceReference.ServiceClient();

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

BindGridview();

}

public void BindGridview()

{

DataSet ds = new DataSet();

ds = objclient.GetMovieData();

grdmovie.DataSource = ds;

grdmovie.DataBind();

}

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

Movie objmovie = new Movie();

objmovie.name = txtname.Text;

objmovie.genre = txtgenre.Text;

objmovie.Cost = Convert.ToInt32(txtcost.Text);

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;

objmovie.Poster = imagepath;

objclient.InsertMovieData(objmovie);

Response.Write("<script>alert('Record Insert Successfully');</script>");

txtname.Text = string.Empty;

txtcost.Text = string.Empty;

txtgenre.Text = string.Empty;

BindGridview();

}

if (e.CommandName == "Delete")

{

ImageButton imgbtn = (ImageButton)e.CommandSource;

GridViewRow gvrow = ((GridViewRow)imgbtn.NamingContainer);

int id = Convert.ToInt32(grdmovie.DataKeys[gvrow.RowIndex].Value.ToString());

Movie objmovie = new Movie();

objmovie.id = id;

objclient.DeleteMovieRecord(objmovie);

Response.Write("<script>alert('Record Delete Successfully');</script>");

BindGridview();

}

if (e.CommandName == "Update")

{

ImageButton imgbtn = (ImageButton)e.CommandSource;

GridViewRow gvrow = ((GridViewRow)imgbtn.NamingContainer);

int id = Convert.ToInt32(grdmovie.DataKeys[gvrow.RowIndex].Value.ToString());

Movie objmovie = new Movie();

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

objmovie.id = id;

objmovie.name = txtname.Text;

objmovie.genre = txtgenre.Text;

objmovie.Cost = Convert.ToInt32(txtcost.Text);

objclient.UpdateMovieRecord(objmovie);

Response.Write("<script>alert('Record Update Successfully');</script>");

grdmovie.EditIndex = -1;

BindGridview();

}

protected void grdmovie_RowEditing(object sender, GridViewEditEventArgs e)

{

grdmovie.EditIndex = e.NewEditIndex;

BindGridview();

}

protected void grdmovie_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

grdmovie.EditIndex = -1;

BindGridview();

}

protected void grdmovie_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

}

protected void grdmovie_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

}

Build and run the project.

Result:

In this article we have learn to how to Insert, Select, Edit, Update and Delete record in Gridview using WCF service in asp.net. I hope you enjoyed this article.

Friday, August 21, 2015

WCF tutorial to Insert, Bind, Edit, update and Delete the record from sql server database in Asp.net

2 comments:

Popular Posts

Labels