CRUD Operations Using WCF in ASP.Net

This article explains the following topics:

How to create a WCF service
How to consume the WCF service in your ASP.Net application
How to bind a GridView using the WCF service
How to do insert, edit, update and delete operations on the GridView using the WCF service

The Database

Set up your database depending on the requirements. In the following WCF CRUD example I use the following tables and Stored Procedures.

Mas_Employee table

Figure 1: Mas_Employee table

Mas_Department table

Figure 2: Mas_Department table

To get the emplyees by am optional parameter:

Create Procedure Get_AllEmployees
@Id int = null
AS
Begin
Select E.Id, E.Name, E.Salary,E.DeptID,D.DeptName
From Mas_Employee E
Join Mas_Department D
On E.DeptId = D.DeptId
where D.Status = 1
And Id = Isnull(@Id, Id)
End

To Insert emplyee details into the Mas_Employee table:

Create Procedure USP_Emp_Insert
@Name varchar(50),
@Salary int,
@DeptId int
AS
Begin
Insert into Mas_Employee
(Name,Salary,DeptId) Values
(@Name,@Salary,@DeptId)
End

To update the emplyee details in the Mas_Employee table:

Create Procedure USP_Emp_Update
@Id int,
@Name varchar(50),
@Salary int,
@DeptId int
AS
Begin
update Mas_Employee Set
Name=@Name,
Salary=@Salary,
DeptId=@DeptId
where Id=@Id
End

To delete emplyee details in the Mas_Employee table:

Create Procedure USP_Emp_Delete
@Id int
AS
Begin
Delete From Mas_Employee
where Id=@Id
End

WCF Service and Application

Open Visual Studio then go to "File" -> "New" -> "Project..." then select WCF Service Application and provide it the name " WCF_Crud" as shown in the following image.

Figure 3: Create WCF Service Application

Two files, IService1.cs and Service1.svc, will be added under the project in the Solution Explorer as shown in the following image.

Figure 4: Solution Explorer

In the web.config.

Here you have seen some predefined code that is automatically generated when the WCF Service Application is created.

Figure 5: Web Config

Next make a ConnectionString in the Wb.Config as in the following:

<connectionStrings>
<add name="conStr" connectionString="Password=1234; User ID=sa; Database=DB_WCF; Data Source=." providerName="System.Data.SqlClient"/>
</connectionStrings>

Next open the Iservices1.cs file and remove all the default code and declare the Service Contracts, Operation Contracts and Data Contracts.

Add the following namespace first:

using System.Data;
namespace WCF_Crud
{
[ServiceContract]
public interface IService1
{
[OperationContract]
string InsertEmpDetails(EmpDetails eDatils);
[OperationContract]
DataSet GetEmpDetails(EmpDetails eDatils);
[OperationContract]
DataSet FetchUpdatedRecords(EmpDetails eDatils);
[OperationContract]
string UpdateEmpDetails(EmpDetails eDatils);
[OperationContract]
bool DeleteEmpDetails(EmpDetails eDatils);
}
// Use a data contract as illustrated in the sample below to add composite types to service operations.
[DataContract]
public class EmpDetails
{
int ? eId;
string eName = string.Empty;
string eSalary = string.Empty;
string eDeptId = string.Empty;
string eDeptName = string.Empty;
[DataMember]
public int ? Id
{
get {
return eId;
}
set {
eId = value;
}
}
[DataMember]
public string Name
{
get {
return eName;
}
set {
eName = value;
}
}
[DataMember]
public string Salary
{
get {
return eSalary;
}
set {
eSalary = value;
}
}
[DataMember]
public string DeptId
{
get {
return eDeptId;
}
set {
eDeptId = value;
}
}
[DataMember]
public string DeptName
{
get {
return eDeptName;
}
set {
eDeptName = value;
}
}
}
}

And next open the Service.svc.cs file and remove the default code and define the methods declared in the IService1.cs above.

Add the following namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace WCF_Crud
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
public class Service1: IService1
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
public string InsertEmpDetails(EmpDetails eDetails)
{
string Status;
SqlCommand cmd = new SqlCommand("USP_Emp_Insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", eDetails.Name);
cmd.Parameters.AddWithValue("@Salary", eDetails.Salary);
cmd.Parameters.AddWithValue("@DeptId", eDetails.DeptId);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
Status = eDetails.Name + " " + eDetails.Salary + " registered successfully";
}
else
{
Status = eDetails.Name + " " + eDetails.Salary + " could not be registered";
}
con.Close();
return Status;
}
public DataSet GetEmpDetails(EmpDetails eDetails)
{
SqlCommand cmd = new SqlCommand("Get_AllEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", eDetails.Id);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.ExecuteNonQuery();
con.Close();
return ds;
}
public DataSet FetchUpdatedRecords(EmpDetails eDetails)
{
SqlCommand cmd = new SqlCommand("Get_AllEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", eDetails.Id);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.ExecuteNonQuery();
con.Close();
return ds;
}
public string UpdateEmpDetails(EmpDetails eDetails)
{
string Status;
SqlCommand cmd = new SqlCommand("USP_Emp_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", eDetails.Id);
cmd.Parameters.AddWithValue("@Name", eDetails.Name);
cmd.Parameters.AddWithValue("@Salary", eDetails.Salary);
cmd.Parameters.AddWithValue("@DeptId", eDetails.DeptId);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
Status = "Record updated successfully";
}
else
{
Status = "Record could not be updated";
}
con.Close();
return Status;
}
public bool DeleteEmpDetails(EmpDetails eDetails)
{
SqlCommand cmd = new SqlCommand("USP_Emp_Delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", eDetails.Id);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
return true;
}
}
}

Go to the Solutions Explorer then right-click on Service1.svc then click on "View in Browser" as shown in the following diagram.

Figure 6: View in Browser

It will appear as shown in the following image:

Figure 7: Service1

Copy the URL. Keep it in a Notepad instance. Why? Because it will be used later when consuming this WCF service in your application. You have now created your WCF service successfully. And the next thing is to to call/consume this service in your ASP.Net application.

Create your ASP.Net application and consume the preceding new WCF service

Open Visual Studio then go to "File" -> "New" -> "Project..." then select Web -> ASP.Net Empty Web Application and name it "ConsumeWcfCrud" as shown in the following image:

Figure 8: ASP.NET Empty Application

Next add a webfrom to your project and name it Samle.aspx.

To consume/call the WCF service and its methods we need to add the service reference. For that go to Solution Explorer then right-click on the project then select add Service Reference as shown in the following image.

Figure 9: Add Service Reference

A new window will appear as shown in the following image:

Figure 10: Add Address

Paste the copied Service URL, http://localhost:53561/Service1.svc, as shown in the following image. Next click on the GO Button. Expand the Services and click on Iservice1. It will list all the functions/methods created in Services. Change the namespace ServiceReference1 to WcfCrudRef or you can use your own namespace and then click on the OK button.

Figure 11: Change namespace

References have been added to the Solution Explorer as shown in the following image.

Figure 12: Reference Added

Now it's time to design the .aspx. Copy the following code into your aspx.

<form id="form1" runat="server">
<div style="width:100%;" align="center">
<fieldset style="width:40%;">
<legend>Perform CRUD Operations using WCF</legend>
<table style="width:100%;">
<tr>
<td>Name</td>
<td>
<asp:TextBox ID="txtName" runat="server" ></asp:TextBox>
</td>
</tr>
<tr>
<td>Salary</td>
<td>
<asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>DeptId</td>
<td>
<asp:TextBox ID="txtDeptId" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td></td>
<td class="style1">
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label ID="lblStatus" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td colspan="2">
<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="grdWcfTest" runat="server" AutoGenerateColumns="False" DataKeyNames="Id"
CellPadding="5" Width="100%">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%#Eval("Name")%>'>
</asp:Label>
<asp:Label ID="lblId" runat="server" Visible="false" Text='<%#Eval("Id")%>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label ID="lblSalary" runat="server" Text='<%#Eval("Salary") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="DeptId">
<ItemTemplate>
<asp:Label ID="lblDeptId" runat="server" Text='<%#Eval("DeptId") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit">
<ItemTemplate>
<asp:LinkButton ID="lnkEdit" runat="server" Text="Edit" CausesValidation="false"
CommandArgument=' <%#Eval("Id") %>' OnCommand="lnkEdit_Command" ToolTip="Edit" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" CausesValidation="false"
CommandArgument='
<%#Eval("Id") %>' CommandName="Delete" OnCommand="lnkDelete_Command"
OnClientClick="return confirm('Are you sure you want to delete?')" ToolTip="Delete" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</fieldset>
</div>
</form>

CodeBehind

First add the following namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ConsumeWcfCrud.WcfCrudRef;
namespace ConsumeWcfCrud
{
public partial class WebForm1: System.Web.UI.Page
{
#region Variable Declaration
WcfCrudRef.Service1Client obj = new WcfCrudRef.Service1Client();
#endregion
#region User Define Methods
private void ClearControls()
{
txtName.Text = string.Empty;
txtSalary.Text = string.Empty;
txtDeptId.Text = string.Empty;
btnSubmit.Text = "Submit";
txtName.Focus();
}
private void BindEmpDetails(int ? Id)
{
EmpDetails eDetails = new EmpDetails();
DataSet ds = new DataSet();
ds = obj.GetEmpDetails(eDetails);
grdWcfTest.DataSource = ds;
grdWcfTest.DataBind();
}
private void SaveEmpDetails()
{
EmpDetails eDetails = new EmpDetails();
eDetails.Name = txtName.Text.Trim();
eDetails.Salary = txtSalary.Text.Trim();
eDetails.DeptId = txtDeptId.Text.Trim();
lblStatus.Text = obj.InsertEmpDetails(eDetails);
ClearControls();
BindEmpDetails(null);
}
private void UpdateEmpDetails()
{
EmpDetails eDetails = new EmpDetails();
eDetails.Id = Convert.ToInt32(ViewState["Id"].ToString());
eDetails.Name = txtName.Text.Trim();
eDetails.Salary = txtSalary.Text.Trim();
eDetails.DeptId = txtDeptId.Text.Trim();
obj.UpdateEmpDetails(eDetails);
lblStatus.Text = obj.UpdateEmpDetails(eDetails);
ClearControls();
BindEmpDetails(null);
}
#endregion
#region Page Event Handlers
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindEmpDetails(null);
ClearControls();
lblStatus.Text = String.Empty;
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (btnSubmit.Text == "Update")
{
UpdateEmpDetails();
}
else
{
SaveEmpDetails();
}
}
protected void lnkEdit_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)
{
EmpDetails eDetails = new EmpDetails();
eDetails.Id = int.Parse(e.CommandArgument.ToString());
ViewState["Id"] = eDetails.Id;
DataSet ds = new DataSet();
ds = obj.FetchUpdatedRecords(eDetails);
if (ds.Tables[0].Rows.Count > 0)
{
txtName.Text = ds.Tables[0].Rows[0]["Name"].ToString();
txtSalary.Text = ds.Tables[0].Rows[0]["Salary"].ToString();
txtDeptId.Text = ds.Tables[0].Rows[0]["DeptId"].ToString();
btnSubmit.Text = "Update";
}
}
protected void lnkDelete_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)
{
EmpDetails eDetails = new EmpDetails();
eDetails.Id = int.Parse(e.CommandArgument.ToString());
if (obj.DeleteEmpDetails(eDetails) == true)
{
lblStatus.Text = "Record deleted Successfully";
}
else
{
lblStatus.Text = "Record couldn't be deleted";
}
BindEmpDetails(null);
}
protected void btnCancel_Click(object sender, EventArgs e)
{
ClearControls();
lblStatus.Text = string.Empty;
}
#endregion
}
}

Run the Applicatin and perform CRUD operations.

Output

Figure 13: Output

I hope you enjoyed it. Please provide your valuable suggestions and feedback if you found this article is helpful.

31 Comments

Join the conversation! Your thoughts help the community grow.

Manoj Kumar4y ago
What is the use of status in table
Sergey Ermilov5y ago
Why are FetchUpdatedRecords(EmpDetails eDetails) and DataSet GetEmpDetails(EmpDetails eDatils) absolutely identical except for the name?
Ramendra kumar verma8y ago
It should be consume web service by jquery method
Satyaprakash Samantaray9y ago
Can u share these table script?........................
- Jaipal Reddy9y ago
  Hi Satyaprakash,please see the below script for your reference, Create table Mas_Employee ( Id int primary key identity(1,1), Name varchar(100), Salary int, DeptId int, Status bit ) Create table Mas_Department ( DeptId int primary key identity(1,1), DeptName varchar(100), Status bit )
Kumar Vaddadi9y ago
And attaching this BindingConfiguration to endpoint
Kumar Vaddadi9y ago
<readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647" maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647" />
Kumar Vaddadi9y ago
1. Even though I am supplying maxArrayLength in readerQuotas
Kumar Vaddadi9y ago
The InnerException message was 'There was an errordeserializing the object of type SelDataSerConsoleApplication1.ServiceReference1 .EventDefinition[]. The maximum array length quota (1) or the maximum items in o bject graph quota has been exceeded while reading XML data. These quotas may be increased by changing the MaxArrayLength property on XmlDictionaryReaderQuotas o r the MaxItemsInObjectGraph setting. Line 1, position 2720969.'
Kumar Vaddadi9y ago
I am unable to do this using Windows Forms ..Wcf Test Client, withdataGridView control, I am getting error:
Jaipal Reddy10y ago
Thank you Sonu Chaudhary.
Sonu Chaudhary10y ago
thanks
Jaipal Reddy11y ago
Yes Bryant Stewart, we can consume wcf service in win forms too.
Bryant Stewart11y ago
CAN I USE WINDOWS USING WCF crud FORM?
Jaipal Reddy11y ago
Thank you Rasmiranjan sahoo
Rasmiranjan sahoo11y ago
Nice article for beginners
Jaipal Reddy11y ago
Thank you indika bileeta
indika bileeta11y ago
Nice article relay appreciate your effort.
Rahul Kumar Saxena11y ago
Good show
- Jaipal Reddy11y ago
  Thank you Rahul
Debasis Saha11y ago
Nice one
- Jaipal Reddy11y ago
  Thank you Debasis Saha.
Gopi Chand11y ago
Nice one jaipal
- Jaipal Reddy11y ago
  Thank you Gopi Chand.
Santhakumar Munuswamy11y ago
Thanks for good work. keep it up
- Jaipal Reddy11y ago
  Thank you Sir
Afzaal Ahmad Zeeshan11y ago
Good article, but, just asking... You should consider using ASP.NET MVC, that is the next big thing. To use Web Forms you would have to stick to .NET instead of ASP.NET. There is a "huge" difference. :)
- Jaipal Reddy11y ago
  Thank you for valuable suggestions Afzaal Ahmad Zeeshan.
sreenivasa k11y ago
very nice explanation. Please share if you have the complete code snippet
- Jaipal Reddy11y ago
  Thank you Srinivas.
- Jaipal Reddy11y ago
  Yeah sure i will share the code snippet.