CRUD Operations in ASP.Net Using WCF Service

Overview

In this article we will do Create, Retrieve, Update and Delete (CRUD) operations using the Windows Communication Foundation (WCF) services. In other words, in this article we will create two applications, the first for the WCF Service and the second to use the service. So before starting anything with it we should be familiar with WCF. So let's learn about it.

WCF is a framework for building service-oriented applications. So using WCF services, we can send data asynchronously from one service endpoint to another. Where endpoint can be part of a continuously available service hosted by IIS. In WCF services three important things are necessary to understand. We commonly callthem the ABCs of WCF Services, in other words:

• Address
   
• Binding
   
• Contract 

Where address specifies where the service is, binding specifies what type of binding mode we want to use (where binding mode may be HTTP, TCP, MSMQ and so on) and contract specifies what service you want to provide. Let's learn about it, how we will do it.

Application I: WCF Service

Step 1

First we will create a WCF Service Application.

In Visual Studio 2010 select "File" -> "New" -> "Project..." and select "WCF" -> "WCF Service Application". For more see Figure 1.

Figure1

Step 2
 
Now watch in Solution Explorer, there will be the three files there IService1.cs, Service1.svc and Web.config. It is unnecessary to rename the file. For increased convenience, IService1 -> IEmployeeService and Service1 -> EmployeeService. After renaming the file your Solution Explorer will look like Figure 2.

Figure 2

Step 3

Some default lines of code we will see in all these three files. So we can change it depending on our requirements. So in this article we will create a service for Employee Records that can add an employee, update employee information, search for an employee, display an employee and delete an employee.

IEmployeeService.cs: Replace the default code of this file with the following code. 

namespace EmployeeServices
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.
    [ServiceContract]
    public interface IEmployeeService
    {

        [OperationContract]
        string GetData(int value);

        [OperationContract]
        string AddEmployyeeRecord(Employee emp);

        [OperationContract]
        DataSet GetEmployeeRecords();

        [OperationContract]
        string DeleteRecords(Employee emp);

        [OperationContract]
        DataSet SearchEmployeeRecord(Employee emp);

        [OperationContract]
        string UpdateEmployeeContact(Employee emp);


    }


    // Use a data contract as illustrated in the sample below to add composite types to service operations.
    [DataContract]
    public class Employee
    {

        string _empID = "";
        string _name = "";
        string _email = "";
        string _phone = "";
        string _gender = "";

        [DataMember]
        public string EmpID
        {
            get { return _empID; }
            set { _empID = value; }
        }

        [DataMember]
        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        [DataMember]
        public string Email
        {
            get { return _email; }
            set { _email = value; }
        }

        [DataMember]
        public string Phone
        {
            get { return _phone; }
            set { _phone = value; }
        }

        [DataMember]
        public string Gender
        {
            get { return _gender; }
            set { _gender = value; }
        }
    }
}

Basically in the preceding code we have declared the four methods for CRUD operations. Now we will implement these methods in EmployeeService.cs.

Step 4

In EmployeeService.cs we will define the body of all these four methods. So replace the default line of code with the following code. 

namespace EmployeeServices
{
    // 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 EmpService : IEmployeeService
    {


        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }

        //C- Add Employee Record
        public string AddEmployyeeRecord(Employee emp)
        {
            string result = "";
            try
            {

                SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP35\\SQLEXPRESS;Initial Catalog=EmployeeDb;User ID=sa;Password=Password$2;");
                SqlCommand cmd = new SqlCommand();

                string Query = @"INSERT INTO tblEmployee (EmpID,Name,Email,Phone,Gender)
                                               Values(@EmpID,@Name,@Email,@Phone,@Gender)";

                cmd = new SqlCommand(Query, con);
                cmd.Parameters.AddWithValue("@EmpID", emp.EmpID);
                cmd.Parameters.AddWithValue("@Name", emp.Name);
                cmd.Parameters.AddWithValue("@Email", emp.Email);
                cmd.Parameters.AddWithValue("@Phone", emp.Phone);
                cmd.Parameters.AddWithValue("@Gender", emp.Gender);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                result = "Record Added Successfully !";
            }
            catch (FaultException fex)
            {
                result = "Error";
            }

             return result;
        }

        //Retrieve Data
        //Retrive Record
        public DataSet GetEmployeeRecords()
        {
            DataSet ds = new DataSet();
            try
            {
                SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP35\\SQLEXPRESS;Initial Catalog=EmployeeDb;User ID=sa;Password=Password$2;");
                string Query = "SELECT * FROM tblEmployee";

                SqlDataAdapter sda = new SqlDataAdapter(Query, con);
                sda.Fill(ds);
            }
            catch (FaultException fex)
            {
                throw new FaultException<string>("Error: "+fex);
            }

            return ds;
        }

        //Delete Record
        public string DeleteRecords(Employee emp)
        {
            string result = "";
            SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP35\\SQLEXPRESS;Initial Catalog=EmployeeDb;User ID=sa;Password=Password$2;");
            SqlCommand cmd = new SqlCommand();
            string Query = "DELETE FROM tblEmployee Where EmpID=@EmpID";
            cmd = new SqlCommand(Query, con);
            cmd.Parameters.AddWithValue("@EmpID", emp.EmpID);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            result = "Record Deleted Successfully!";
            return result;
        }

        //Search Employee Record
        public DataSet SearchEmployeeRecord(Employee emp)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP35\\SQLEXPRESS;Initial Catalog=EmployeeDb;User ID=sa;Password=Password$2;");
                string Query = "SELECT * FROM tblEmployee WHERE EmpID=@EmpID";

                SqlDataAdapter sda = new SqlDataAdapter(Query, con);
                sda.SelectCommand.Parameters.AddWithValue("@EmpID", emp.EmpID);
                sda.Fill(ds);
            }
            catch (FaultException fex)
            {
                throw new FaultException<string>("Error:  " + fex);
            }
            return ds;
        }

        //UPDATE RECORDS
        //Update by Phone Roll
        public string UpdateEmployeeContact(Employee emp)
        {
            string result = "";
            SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP35\\SQLEXPRESS;Initial Catalog=EmployeeDb;User ID=sa;Password=Password$2;");
            SqlCommand cmd = new SqlCommand();

            string Query = "UPDATE tblEmployee SET Email=@Email,Phone=@Phone WHERE EmpID=@EmpID";

            cmd = new SqlCommand(Query, con);
            cmd.Parameters.AddWithValue("@EmpID", emp.EmpID);
            cmd.Parameters.AddWithValue("@Email", emp.Email);
            cmd.Parameters.AddWithValue("@Phone", emp.Phone);
            con.Open();
            cmd.ExecuteNonQuery();
            result = "Record Updated Successfully !";
            con.Close();

            return result;
        }


    }
}

Step 5

Now configure the Web.config file using the following code. 

<?xml version="1.0"?>
<configuration>

  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>

  <system.serviceModel>
    <bindings>
      <wsHttpBinding>
        <binding name="wsHttpBinding_IService1" />
      </wsHttpBinding>
    </bindings>
    <client>
      <endpoint address=""
                binding="wsHttpBinding"
                contract="EmployeeServices.IEmployeeService"
                bindingConfiguration="wsHttpBinding_IEmployeeService" />
    </client>

    <behaviors>
      <serviceBehaviors>
        <behavior>
          <serviceMetadata httpGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>

    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
  </system.serviceModel>

 <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>

</configuration>

 

Note:

Now build your application and run the service (F5), then you will see the following in the web browser.

Now copy the URL from the browser, the URL will look like the following: http://localhost:5530/EmpService.svc. To use this service we will create another application.

Application II: Web Application

We can use this service in any type of application but for this example article we will create a web application.

Step I

In Visual Studio 2010 select "File" -> "New" -> "Project..." and select "Web" -> "ASP.NET Web Application".

*WebApplication

So for doing the CRUD operations we will create four aspx pages in this applciation. First create a master page.

1. AddNewEmployee.aspx
    
2. DisplayEmployeeRecord.aspx
    
3. UpdateEmployee.aspx
    
4. DeleteEmployee.aspx 

MasterPage: Employee.Master 

<%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Employee.master.cs" Inherits="Application.Employee" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <asp:ContentPlaceHolder ID="head" runat="server">
    </asp:ContentPlaceHolder>
    <style>
      body{width:100%; background-color:Silver;}
      .page-wrapper{width:700px; background-color:Orange; padding:0px; height:auto; overflow:hidden;}
      .header{height:50px; width:99%; padding:1%; background-color:Green; color:White; font-family:Segoe UI; font-size:30px;}
      .menu{height:30px; width:99%; padding:1%; background-color:White; color:White; font-family:Segoe UI; font-size:14px; clear:both; float:left; text-align:left;}
      .footer{height:30px; width:99%; padding:1%; background-color:Black; color:White; font-family:Segoe UI; font-size:12px; text-align:left; vertical-align:bottom; clear:both;}
      .button{width:110px; height:30px; border-style:none; background-color:Green; font-family:Verdana; font-size:15px; color:White;}
      .button:hover{background-color:Black;}
      .textBox{width:400px; background-color:White; border:1px Solid Navy; padding:5px; color:Black; font-family:Segoe UI; }
      .linkbutton{width:120px; padding:7px; background-color:Green;color:White; font-family:Verdana; font-size:16px; margin-right:1px; margin-top:2px; text-decoration:none;}
      .linkbutton:hover{background-color:Orange;}
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <center>
    <div class="page-wrapper">
    <div class="header">
    CRUD Operation Using WCF Service
    </div>
    <div class="menu">
    <table style="float:left">
    <tr>
    <td><a href="AddNewEmployee.aspx" class="linkbutton" >Add New</a></td>
    <td><a href="UpdateEmployee.aspx" class="linkbutton" >Update</td>
    <td><a href="DeleteEmployee.aspx" class="linkbutton" >Delete</td>
    <td><a href="DissplayEmployeeRecord.aspx" class="linkbutton" >Display</td>
    </tr>
    </table>
    </div>
        <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">

        </asp:ContentPlaceHolder>
        <div class="footer">
        By C# Corner 2014
        </div>
    </div>
    </center>
    </form>
</body>
</html>

Step II

To use the CRUD services, we need to make reference to this current service that is running on IIS. To make the reference use the following procedure.

In Solution Explorer, right-click on the application name and move to "Add Service Reference...". For more see the following Figure 3.

Figure 3:

Step III

First create a SQL database and table in which we will do CRUD operations.

 

EmployeeDb (Database) tblEmployee (table).

Create database EmployeeDb;

CREATE TABLE tblEmployee
(
  EmpId int NOT NULL PRIMARY KEY,
  Name nvarchar(50),
  Email nvarchar(50),
  Phone nvarchar(10),
  Gender char(10)
);

Provide the following code for AddNewEmployee.aspx 

<%@ Page Title="" Language="C#" MasterPageFile="~/Employee.Master" AutoEventWireup="true" CodeBehind="AddNewEmployee.aspx.cs" Inherits="Application.AddNewEmployee" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <table style="width:100%; color:Black; font-family:Segoe UI; font-size:14px; float:left; text-align:left;">
<tr>
<td colspan="2"><asp:Label ID="lblMsg" runat="server" Font-Size="Medium" ></asp:Label></td>
</tr>
<tr>
<td>Employee ID</td>
<td><asp:TextBox ID="txtEmpID" runat="server" CssClass="textBox" ></asp:TextBox></td>
</tr>
<tr>
<td>Name</td>
<td><asp:TextBox ID="txtName" runat="server" CssClass="textBox" ></asp:TextBox></td>
</tr>
<tr>
<td>Email</td>
<td><asp:TextBox ID="txtEmail" runat="server" CssClass="textBox" ></asp:TextBox></td>
</tr>
<tr>
<td>Phone</td>
<td><asp:TextBox ID="txtPhone" runat="server" CssClass="textBox" ></asp:TextBox></td>
</tr>
<tr>
<td>Gender</td>
<td>
<asp:RadioButtonList ID="rbtnGender" runat="server" RepeatColumns="2" CssClass="textBox" >
    <asp:ListItem Selected="True">Male</asp:ListItem>
    <asp:ListItem>Female</asp:ListItem>
</asp:RadioButtonList></td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnSave" runat="server" Text="Save" CssClass="button"
        onclick="btnSave_Click" />
<asp:Button ID="bntReset" runat="server" Text="Reset" CssClass="button"
        onclick="bntReset_Click" />
</td>
</tr>
</tr>
</table>
</asp:Content>

Provide the following code for AddNewEmployee.aspx.cs 

namespace Application
{
    public partial class AddNewEmployee : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                txtEmpID.Text = "";
                txtName.Text = "";
                txtEmail.Text = "";
                txtPhone.Text = "";
                lblMsg.Text = "";
                txtEmpID.Focus();
            }

        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            //Adding New Employee Records

            if ((txtEmpID.Text != "") || (txtName.Text != "") || (txtEmail.Text != "") || (txtPhone.Text != ""))
            {
                try
                {
                    MyService.Employee employee = new MyService.Employee();
                    employee.EmpID = txtEmpID.Text;
                    employee.Name = txtName.Text;
                    employee.Email = txtEmail.Text;
                    employee.Phone = txtPhone.Text;
                    employee.Gender = rbtnGender.SelectedItem.Text;

                    MyService.EmployeeServiceClient client = new MyService.EmployeeServiceClient();
                    lblMsg.Text = "Employee ID: " + employee.EmpID + ", " + client.AddEmployyeeRecord(employee);
                }
                catch (Exception ex)
                {
                    lblMsg.Text = "Employee ID must be unique! ";
                }


            }
            else
            {

                lblMsg.Text = "All fields are mandatory! ";
                lblMsg.ForeColor = System.Drawing.Color.Red;
            }


        }

        protected void bntReset_Click(object sender, EventArgs e)
        {
            ClearForm();
        }

        private void ClearForm()
        {
           txtEmpID.Text = "";
            txtName.Text = "";
            txtEmail.Text = "";
            txtPhone.Text = "";
            lblMsg.Text = "";
            txtEmpID.Focus();
        }
    }
}

Step IV

Provide the following code for DisplayEmployeeRecord.aspx 

<asp:GridView ID="grdEmployees" runat="server">
       <AlternatingRowStyle BackColor="White" />
       <HeaderStyle BackColor="#003300" Font-Bold="True" ForeColor="White" />
   </asp:GridView>

Provide the following code for DisplayEmployeeRecord.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
           if (!Page.IsPostBack)
           {
               DataSet ds = new DataSet();
               MyService.EmployeeServiceClient client = new MyService.EmployeeServiceClient();
               ds = client.GetEmployeeRecords();
               grdEmployees.DataSource = ds;
               grdEmployees.DataBind();
           }
 }

Step V

Provide the following code for UpdateEmployee.aspx 

<%@ Page Title="" Language="C#" MasterPageFile="~/Employee.Master" AutoEventWireup="true" CodeBehind="UpdateEmployee.aspx.cs" Inherits="Application.UpdateEmployee" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">

<table id="panSearch" runat="server" style="width:auto; color:Black; font-family:Segoe UI; font-size:14px; float:right; text-align:left; padding:10px; padding-bottom:20px;" >
<tr>
<td>Enter Employee ID</td>
<td><asp:TextBox ID="txtSearch" runat="server" CssClass="textBox" Placeholder="Enter Employee ID e.g 201" ></asp:TextBox></td>
<td><asp:Button ID="btnSearch" runat="server" Text="Search" CssClass="button"
        onclick="btnSearch_Click" ></asp:Button></td>
</tr>
<tr>

<td colspan="3" ><asp:Label ID="lblSearchResult" runat="server" Font-Size="Medium" ></asp:Label></td></tr>
</table>

 <table id="panUpdate" runat="server" style="width:100%; color:Black; font-family:Segoe UI; font-size:14px; float:left; text-align:left;">
<tr>
<td colspan="2"><asp:Label ID="lblMsg" runat="server" Font-Size="Medium" ></asp:Label></td>
</tr>
<tr>
<td>Employee ID</td>
<td><asp:Label ID="lblEmpID" runat="server" ></asp:Label></td>
</tr>
<tr>
<td>Email</td>
<td><asp:TextBox ID="txtEmail" runat="server" CssClass="textBox" ></asp:TextBox></td>
</tr>
<tr>
<td>Phone</td>
<td><asp:TextBox ID="txtPhone" runat="server" CssClass="textBox" ></asp:TextBox></td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="bntUpdated" runat="server" Text="Update" CssClass="button" onclick="bntUpdated_Click"
         />
<asp:Button ID="btnCancel" runat="server" Text="Cancel" CssClass="button" onclick="btnCancel_Click"
        />
</td>
</tr>
</table>
</asp:Content>

Provide the following code for UpdateEmployee.aspx.cs 

namespace Application
{
    public partial class UpdateEmployee : System.Web.UI.Page
    {
        MyService.Employee employee = new MyService.Employee();
        MyService.EmployeeServiceClient client = new MyService.EmployeeServiceClient();

        DataSet ds;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                SetPanel(true, false);
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            if (txtSearch.Text != "")
            {
                employee.EmpID = txtSearch.Text.Trim();
                ds = new DataSet();
               ds= client.SearchEmployeeRecord(employee);

               if (ds.Tables[0].Rows.Count > 0)
               {
                   lblEmpID.Text = ds.Tables[0].Rows[0]["EmpID"].ToString();
                   txtEmail.Text = ds.Tables[0].Rows[0]["Email"].ToString();
                   txtPhone.Text = ds.Tables[0].Rows[0]["Phone"].ToString();
                   SetPanel(false, true);

               }
               else
               {
                   lblSearchResult.Text = "Please Enter Employee ID !";
                   lblSearchResult.ForeColor = System.Drawing.Color.White;
               }

            }
            else
            {
                lblSearchResult.Text = "Please Enter Employee ID !";
            }
        }

        private void SetPanel(bool pSearch, bool pUpdate)
        {
            panSearch.Visible = pSearch;
            panUpdate.Visible = pUpdate;
        }

        protected void bntReset_Click(object sender, EventArgs e)
        {
            SetPanel(true, false);
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            SetPanel(true, false);
            lblMsg.Text = "";
        }

        protected void bntUpdated_Click(object sender, EventArgs e)
        {
            employee.EmpID = lblEmpID.Text.Trim();
            employee.Email= txtEmail.Text;
            employee.Phone = txtPhone.Text;

           string result= client.UpdateEmployeeContact(employee);
           lblSearchResult.Text = result;
           SetPanel(true, false);
           txtPhone.Text = "";
           txtEmail.Text = "";
           lblEmpID.Text = "";

        }


    }
}

Step VI

Provide the following code for DeleteEmployee.aspx 

<%@ Page Title="" Language="C#" MasterPageFile="~/Employee.Master" AutoEventWireup="true" CodeBehind="DeleteEmployee.aspx.cs" Inherits="Application.DeleteEmployee" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<table id="panSearch" runat="server" style="width:auto; color:Black; font-family:Segoe UI; font-size:14px; float:left; text-align:left; padding:10px; padding-bottom:20px;" >
<tr>
<td>Employee ID</td>
<td><asp:TextBox ID="txtSearch" runat="server" CssClass="textBox" Placeholder="Enter employee id to delete it" ></asp:TextBox></td>
<td><asp:Button ID="btnDelete" runat="server" Text="Delete" CssClass="button" onclick="btnDelete_Click"
        ></asp:Button></td>
</tr>
<tr>
<td colspan="3" ><asp:Label ID="lblSearchResult" runat="server" Font-Size="Medium" ></asp:Label></td></tr>
</table>
 <hr />
 <div style="float:left" >
 <asp:GridView ID="grdEmployees" runat="server">
        <AlternatingRowStyle BackColor="White" />
        <HeaderStyle BackColor="#003300" Font-Bold="True" ForeColor="White" />
    </asp:GridView>
    </div>
</asp:Content>

Provide the following code for DeleteEmployee.aspxcs 

namespace Application
{
    public partial class DeleteEmployee : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindGridData();
            }
        }

        //Bind Grid
        public void BindGridData()
        {
            DataSet ds = new DataSet();
            MyService.EmployeeServiceClient client = new MyService.EmployeeServiceClient();
            ds = client.GetEmployeeRecords();
            grdEmployees.DataSource = ds;
            grdEmployees.DataBind();
        }


        protected void btnDelete_Click(object sender, EventArgs e)
        {
            MyService.EmployeeServiceClient client = new MyService.EmployeeServiceClient();

            MyService.Employee employee = new MyService.Employee();
            employee.EmpID = txtSearch.Text.Trim();
            string result = client.DeleteRecords(employee);

            if (result == "Record Deleted Successfully!")
            {
                BindGridData();
                lblSearchResult.Text = "Employee ID: " + txtSearch.Text.Trim() + "Deleted Successfully!";
            }
            else
            {
                lblSearchResult.Text = "Employee ID: " + txtSearch.Text.Trim() + "Not Found!";
            }
        }
    }
}

Now run this web applciation to test it; it will look like the following.

ADD NEW RECORD into tblEmployee (SQL).

DISPLAY ALL RECORDS

UPDATE RECORD BY EMP ID

DELETE RECORD BY EMP ID

Summary

In this article we learned about WCF services to do CRUD operations.