A Beginner's CRUD Using WCF in ASP.NET

Math08avan

Rate me:

4.57/5 (4 votes)

16 Jul 2015CPOL4 min read

32.9K

1.9K

A simple example for CRUD actions using WCF service in ASP.NET

Introduction

WCF stands for Windows Communication Foundation. It is a framework for building, configuring, and deploying network-distributed services. I recommend you to visit A Beginner's Tutorial for Understanding Windows Communication Foundation (WCF) article to know about WCF.

In this example, I will show, how to create a WCF service and how to consume that WCF service.

Using the code

Our sample application show the student details. To implement our sample demo, we need to do the following steps:

Create student table & stored procedures
Create a WCF service
Create a web based application to view the Student details.

Database : SQL Srver 2012 R2
IDE : Visual Studio 2013

Part 1 : Create Studet Table & Stored Procedures

1. Open SQL Server Management Studio

2. Create a databse "TestDB"

3. Create a table "Student" with the following columns

StudentId
FirstName
LastName
RegisterNo
Department

Set StudentId as a primary key and set Identity Specification is "yes"

C++

CREATE TABLE [dbo].[Student](
	[StudentId] [bigint] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[RegisterNo] [varchar](50) NOT NULL,
	[Department] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
	[StudentId] ASC
)
) ON [PRIMARY]

4. Create the following stored procedues for CRUD action

Get Student Details

C++

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStudent]
AS
BEGIN
	SELECT 
		StudentId	,
		FirstName	,
		LastName	,
		RegisterNo	,
		Department
	FROM Student
END

Add/Edit Student Details

C++

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddStudent]
	@StudentId		BIGINT		,
	@FirstName		VARCHAR(50)	,
	@LastName		VARCHAR(150),
	@RegisterNo		VARCHAR(50)	,
	@Department		VARCHAR(50)
AS

IF(@StudentId= 0)
BEGIN
	INSERT INTO Student
	(
		FirstName	,
		LastName	,
		RegisterNo	,
		Department
	)
	values
	(
		@FirstName	,
		@LastName	,
		@RegisterNo	,
		@Department
	)
END
ELSE
BEGIN
	UPDATE Student
	SET FirstName = @FirstName	,
	LastName = @LastName		,
	RegisterNo = @RegisterNo	,
	Department = @Department
	WHERE StudentId = @StudentId
END

Delete Student Detail

C++

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteStudent]
	@StudentId	BIGINT
AS
BEGIN
	DELETE FROM Student
	WHERE StudentId = @StudentId
END

Part 2 : Create a WCF service

1. Open Visual Studio 2013

2. Go to File menu-> New -> Project

3. Select Installed -> Templates -> Visual C# ->WCF then select "WCF Service Application" and give name "WcfDemo"

4. Open IService1.cs file and delete the existing "DataContract" details, Then add a new Student DataContract with the student details.<div class="code-samples">

[DataContract]
    public class Student
    {
        [DataMember]
        public long StudentId;
        [DataMember]
        public string FirstName;
        [DataMember]
        public string LastName;
        [DataMember]
        public string RegisterNo;
        [DataMember]
        public string Department;
    }

5. Delete all the "OperationContract" listed in the interface IService1, then add new OperationContracts with FaultContract to do the CRUD action.

[ServiceContract]
    public interface IService1
    {

        [OperationContract]
        [FaultContract(typeof(ExceptionMessage))]
        List<student> GetStudents();

        [OperationContract]
        [FaultContract(typeof(ExceptionMessage))]
        void AddStudents(Student student);

        [OperationContract]
        [FaultContract(typeof(ExceptionMessage))]
        void DeleteStudent(long StudentId); 
    }

6. Client can encounter three type of error while invoke the service.

1st is communication errors such as network availability, wrong address, host process not running, and so on. These are manifested on the client-side by the CommunicationException.
2nd is related to the state of the proxy and the channels, such as trying to access an already closed proxy, resulting in an ObjectDisposedException, or a mismatch in the contract and the binding security protection level.
3rd is originated in the service call, either by the service throwing an exception or as a result of the service calling another object or resource and having that internal call throw an exception.

Add a new DataContract in the IService1.cs file, to handle the Exception.

[DataContract]
    public class ExceptionMessage
    {
        private string infoExceptionMessage;
        public ExceptionMessage(string Message)
        {
            this.infoExceptionMessage = Message;
        }
        [DataMember]
        public string errorMessageOfAction
        {
            get { return this.infoExceptionMessage; }
            set { this.infoExceptionMessage = value; }
        }
    }

7. Open web.config file to add the database connection details.

<connectionstrings> <add connectionstring="Server=YourServerName;Database=TestDB;User ID=DBUserId;Password=DBPassword" name="MyDbConn" providername="System.Data.SqlClient" /> </connectionstrings>

8. Expand Service1.svc file and open Service1.svc.cs file. Remove all the function inside the Service1 class.

Define a static string variable to get the web.config connection string setails.

public static String ConnectionString = ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString;

Need to add the System.Configuration namespace.

Here we are going to implement the interface methods.

Add the following code to GetStudents() method.

public List<student> GetStudents()
    {
        List<student> studentList = new List<student>();
        DataTable resourceTable = new DataTable();
        SqlDataReader resultReader = null;
        SqlConnection connection = new SqlConnection(ConnectionString);
        SqlCommand command = new SqlCommand("GetStudent", connection);
        command.CommandType = CommandType.StoredProcedure;
        try
        {
            connection.Open();
            resultReader = command.ExecuteReader();
            resourceTable.Load(resultReader);
            resultReader.Close();
            connection.Close();
            studentList = (from DataRow dr in resourceTable.Rows
                           select new Student()
                           {
                               StudentId = Convert.ToInt64(dr["StudentId"]),
                               FirstName = dr["FirstName"].ToString(),
                               LastName = dr["LastName"].ToString(),
                               RegisterNo = dr["RegisterNo"].ToString(),
                               Department = dr["Department"].ToString()
                           }).ToList();
            }
        catch (Exception exception)
        {
            if (resultReader != null || connection.State == ConnectionState.Open)
            {
                resultReader.Close();
                connection.Close();
            }
            throw new FaultException<exceptionmessage>(new ExceptionMessage(exception.Message));
        }
        return studentList;
    }

Add the following code to AddStudents() method.

public void AddStudents(Student student)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("AddStudent", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@StudentId", student.StudentId));
                command.Parameters.Add(new SqlParameter("@FirstName", student.FirstName));
                command.Parameters.Add(new SqlParameter("@LastName", student.LastName));
                command.Parameters.Add(new SqlParameter("@RegisterNo", student.RegisterNo));
                command.Parameters.Add(new SqlParameter("@Department", student.Department));
                object result = command.ExecuteScalar();
                connection.Close();
            }
        }
        catch (SqlException exception)
        {
            throw new FaultException<exceptionmessage>(new ExceptionMessage(exception.Message));
        }
    }

Add the following code to DeleteStudent() method.

public void DeleteStudent(long StudentId)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("DeleteStudent", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@StudentId", StudentId));
                int result = command.ExecuteNonQuery();
                connection.Close();
            }
        }
        catch (SqlException exception)
        {
            throw new FaultException<exceptionmessage>(new ExceptionMessage(exception.Message));
        }
    }

Please take a look, how to throw the FaultContract exception to client.

throw new FaultException<exceptionmessage>(new ExceptionMessage(exception.Message));

9. Build and Run WCF service. WCF Test Client will appear. You can debuge your service.

10. To view our service in a website.

Launch the copied address in a browser. Port Number differ to your machine. Remember this address to add the reference in web application.

Part 3 : Create a Web Application

1. Add New ASP.NET Web Application Project into that Solution, give name "WcfDemoWebApp", in the next step select Web Forms.

2. Open Default.aspx page. Delete all the contents inside the "<asp:Content" tag and add the following code.

<h4>Simple WCF Student Service</h4>
<asp:Label ID="lblMsg" runat="server"></asp:Label>
<table>
    <tr>
        <td>
            Student Id :
        </td>
        <td>
            <asp:TextBox ID="txtStudentId" runat="server" Enabled="false" />
        </td>
    </tr>
    <tr>
        <td>
            First Name :
        </td>
        <td>
            <asp:TextBox ID="txtFirstName" runat="server" style="width: 300px"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Last Name :
        </td>
        <td>
            <asp:TextBox ID="txtLastName" runat="server" style="width: 300px"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Register No. :
        </td>
        <td>
            <asp:TextBox ID="txtRegisterNo" runat="server" style="width: 300px"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Department :
        </td>
        <td>
            <asp:TextBox ID="txtDepartment" runat="server" style="width: 300px"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Button ID="ButtonInsert" runat="server" Text="Add" OnClick="InsertButton_Click"/>
            <asp:Button ID="ButtonUpdate" runat="server" visible="false" Text="Update" OnClick="InsertButton_Click"/>
            <asp:Button ID="ButtonDelete" runat="server" visible="false" Text="Delete" OnClick="DeleteButton_Click"/>
            <asp:Button ID="ButtonCancel" runat="server" visible="false" Text="Cancel" OnClick="CancelButton_Click"/>
        </td>
    </tr>
</table>
<asp:GridView ID="GridViewStudentDetails" DataKeyNames="StudentId" AutoGenerateColumns="false"
        runat="server" OnSelectedIndexChanged="GridViewStudentDetails_SelectedIndexChanged" Width="700">
        <HeaderStyle BackColor="#0A9A9A" ForeColor="White" Font-Bold="true" Height="30" />
        <AlternatingRowStyle BackColor="#f5f5f5" />
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:LinkButton ID="lbtnSelect" runat="server" CommandName="Select" Text="Select" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="First Name">
                <ItemTemplate>
                    <asp:Label ID="lblFirstName" runat="server" Text='<%#Eval("FirstName") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Last Name">
                <ItemTemplate>
                    <asp:Label ID="lblLastName" runat="server" Text='<%#Eval("LastName") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Register No.">
                <ItemTemplate>
                    <asp:Label ID="lblRegisterNo" runat="server" Text='<%#Eval("RegisterNo") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Department">
                <ItemTemplate>
                    <asp:Label ID="lblDepartment" runat="server" Text='<%#Eval("Department") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

Our web application will be like this

To edit/ Delete the student details, "Select" link attached with each row. on click "Select", student details mappaed in the above form. you caan edit or delete the student details.

3. Add the service reference to our web application.

Right click our web application -> Add -> Service Reference

In Add Service Reference window, paste our service address we copied earlier, then click the Discover button. It will find our service. Then press Ok button to add in our web application.

After the successfull addition of our service, Service References folder show our service reference in our web application.

4. Open Default.aspx.cs file.

Add the following usings

using WcfDemoWebApp.ServiceReference1;
using System.ServiceModel;

Create a service client proxy

ServiceReference1.Service1Client proxy;

Add the following code to PageLoad() function.

if (!IsPostBack)
    {
        try
           {
                proxy = new ServiceReference1.Service1Client();
                GridViewStudentDetails.DataSource = proxy.GetStudents();
                GridViewStudentDetails.DataBind();
            }
            catch (FaultException<exceptionmessage> exceptionFromService)
            {
                lblMsg.Text = "Error while loading student details :" + exceptionFromService.Detail.errorMessageOfAction;
            }
            catch (Exception exception)
            {
                lblMsg.Text = "Error while loading student details :" + exception.Message;
            }
        }

Service exception can be captured in the following way.

catch (FaultException<exceptionmessage> exceptionFromService)
    {
        lblMsg.Text = "Error while loading student details :" + exceptionFromService.Detail.errorMessageOfAction;
    }

In our application, we have InsertButton_Click, DeleteButton_Click, CancelButton_Click and GridViewStudentDetails_SelectedIndexChanged events.
InsertButton_Click used to both add and update student details. Add the following code

protected void InsertButton_Click(object sender, EventArgs e)
    {
        try
        {
            long StudentId = 0;
            if (txtStudentId.Text != null && txtStudentId.Text != string.Empty)
            {
                StudentId = Convert.ToInt64(txtStudentId.Text);
            }
            string FirstName = txtFirstName.Text.Trim();
            string LastName = txtLastName.Text.Trim();
            string RegisterNo = txtRegisterNo.Text.Trim();
            string Department = txtDepartment.Text.Trim();

            proxy = new ServiceReference1.Service1Client();
            ServiceReference1.Student newStudent =
            new ServiceReference1.Student()
            {
                StudentId = StudentId,
                FirstName = FirstName,
                LastName = LastName,
                RegisterNo = RegisterNo,
                Department = Department
            };

            proxy.AddStudents(newStudent);

            GridViewStudentDetails.DataSource = proxy.GetStudents();
            GridViewStudentDetails.DataBind();
            lblMsg.Text = "Record Saved Successfully";
        }
        catch (FaultException<exceptionmessage> exceptionFromService)
        {
            if (ButtonInsert.Visible == true)
            {
                lblMsg.Text = "Error while adding new customer details :" + exceptionFromService.Detail.errorMessageOfAction;
            }
            else
            {
                lblMsg.Text = "Error while updating customer details :" + exceptionFromService.Detail.errorMessageOfAction;
            }
        }
        catch (Exception exception)
        {
            if (ButtonInsert.Visible == true)
            {
                lblMsg.Text = "Error while adding new customer details :" + exception.Message;
            }
            else
            {
                lblMsg.Text = "Error while updating customer details :" + exception.Message;
            }
        }

        ResetAll();
    }

private void ResetAll()
    {
        ButtonInsert.Visible = true;
        ButtonUpdate.Visible = false;
        ButtonDelete.Visible = false;
        ButtonCancel.Visible = false;
        txtStudentId.Text = "";
        txtFirstName.Text = "";
        txtLastName.Text = "";
        txtRegisterNo.Text = "";
        txtDepartment.Text = "";
    }

DeleteButton_Click used to delete a student from the database. Add the following code

protected void DeleteButton_Click(object sender, EventArgs e)
    {
        try
        {
            long StudentId = Convert.ToInt64(txtStudentId.Text);
            proxy = new ServiceReference1.Service1Client();
            proxy.DeleteStudent(StudentId);
        }
        catch (FaultException<exceptionmessage> exceptionFromService)
        {
            lblMsg.Text = "Error while deleteing student details :" + exceptionFromService.Detail.errorMessageOfAction;
        }
        catch (Exception exception)
        {
            lblMsg.Text = "Error while deleteing student details :" + exception.Message;
        }
    }

DeleteButton_Click used to delete a student from the database. Add the following code

protected void CancelButton_Click(object sender, EventArgs e)
    {
        ResetAll();
    }

CancelButton_Click used to clear a student details from the form. Add the following code

protected void CancelButton_Click(object sender, EventArgs e)
    {
        ResetAll();
    }

GridViewStudentDetails_SelectedIndexChanged used to map the student details to form. Add the following code

protected void GridViewStudentDetails_SelectedIndexChanged(object sender, EventArgs e)
    {
        txtStudentId.Text = GridViewStudentDetails.DataKeys[GridViewStudentDetails.SelectedRow.RowIndex].Value.ToString();
        txtFirstName.Text = (GridViewStudentDetails.SelectedRow.FindControl("lblFirstName") as Label).Text;
        txtLastName.Text = (GridViewStudentDetails.SelectedRow.FindControl("lblLastName") as Label).Text;
        txtRegisterNo.Text = (GridViewStudentDetails.SelectedRow.FindControl("lblRegisterNo") as Label).Text;
        txtDepartment.Text = (GridViewStudentDetails.SelectedRow.FindControl("lblDepartment") as Label).Text;
        //make invisible Insert button during update/delete
        ButtonInsert.Visible = false;
        ButtonUpdate.Visible = true;
        ButtonDelete.Visible = true;
        ButtonCancel.Visible = true;
    }

5. Set WcfDemoWebApp as the StartUp project.

6. Run the application. You can Add / Edit and Delete student details.

Summary

I hope that my demo work will be useful to beginner's.

History

1st Version : 2015-07-16

2nd Version : 2015-07-19

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.