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.