Insert, Update, Delete in WCF

Windows Communication Foundation or WCF is a framework for building service oriented applications. By using WCF we can transfer data as asynchronous message.

This article describes, step by step,CRUD operations in WCF; Create, Read, Update, Delete (CRUD) operations is WCF; insert, update, delete, select, search operations in WCF; insert, update, delete, select, search data into SQL Server by using WCF.  For this we will used two table Students and Countries. We will save Student ID, Name, Address, Phone and Country of a Student in Students table.  In Countries table there will be some predefine country data. We will show the entire country list in a DropDownList and student list in a GridView. Summary of the articles are:

•     UI Design
•     Database Design
•     LINQ to SQL Data Class
•     Add WCF Service
•     Add WCF Service Reference
•     Select Operation
•     Insert Operation
•     Update Operation
•     Delete Operation
•     Search Operation

UI Design
Create a new asp.net, C# project.  Design your UI. You can use the following asp.net code.

<table>

<tr>

<td style=”width: 120px”>

ID</td>

<td>

<asp:TextBox ID=”txtID” runat=”server” Enabled=”False”></asp:TextBox>

</td>

</tr>

<tr>

<td style=”width: 120px”>

Name</td>

<td>

<asp:TextBox ID=”txtName” runat=”server” Width=”250px”></asp:TextBox>

</td>

</tr>

<tr>

<td>

Address</td>

<td>

<asp:TextBox ID=”txtAddress” runat=”server” TextMode=”MultiLine” Width=”250px” Height=”50px”></asp:TextBox>

</td>

</tr>

<tr>

<td>

Phone</td>

<td>

<asp:TextBox ID=”txtPhone” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

<td>

Country</td>

<td>

<asp:DropDownList ID=”ddlCountry” runat=”server” Width=”150px”>

</asp:DropDownList>

</td>

</tr>

<tr>

<td>

&nbsp;</td>

<td>

<asp:Button ID=”btnAdd” runat=”server” Text=”Add” Width=”80px”

onclick=”btnAdd_Click” />

<asp:Button ID=”btnDelete” runat=”server” onclick=”btnDelete_Click” Text=”Delete”

Width=”80px” />

<asp:Button ID=”btnEdit” runat=”server” onclick=”btnEdit_Click” Text=”Edit”

Width=”80px” />

<asp:Button ID=”btnClear” runat=”server” onclick=”btnClear_Click” Text=”Clear”

Width=”80px” />

</td>

</tr>

<tr>

<td>

&nbsp;</td>

<td>

&nbsp;</td>

</tr>

<tr>

<td>

Search By ID</td>

<td>

<asp:TextBox ID=”txtSearch” runat=”server”></asp:TextBox>

<asp:Button ID=”btnSearch” runat=”server” onclick=”btnSearch_Click”

Text=”Search” Width=”80px” />

</td>

</tr>

</table>

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”

Width=”600px” AutoGenerateSelectButton=”True”

onselectedindexchanged=”GridView1_SelectedIndexChanged”>

<Columns>

<asp:BoundField DataField=”StudentID” HeaderText=”ID” />

<asp:BoundField DataField=”StudentName” HeaderText=”Name” />

<asp:BoundField DataField=”Address” HeaderText=”Address” />

<asp:BoundField DataField=”Phone” HeaderText=”Phone” />

<asp:BoundField DataField=”CountryID” HeaderText=”Country ID” />

<asp:BoundField DataField=”CountryName” HeaderText=”Country Name” />

</Columns>

</asp:GridView>

Database Design
Create two table Students and Countries in your database. Sample scripts for both tables are given bellow

CREATE TABLE [dbo].[Students](
[StudentID] [bigint] IDENTITY(1,1) NOT NULL,
[StudentName] [nvarchar](100) NULL,
[Address] [nvarchar](150) NULL,
[Phone] [nvarchar](50) NULL,
[CountryID] [nchar](2) NOT NULL,
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Countries](
[CountryID] [nchar](2) NOT NULL,
[CountryName] [nvarchar](150) NOT NULL,
[Nationality] [nvarchar](100) NULL,
CONSTRAINT [PK_country] PRIMARY KEY CLUSTERED
(
[CountryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

You need to insert some sample data in Countries table.

LINQ to SQL Data Class
Write click in App_Code and add a DataContext file (LINQ to SQL Data Classes) in your project. Keep its name as LINQDataClasses(you can choice any name). Drag Students and Countries table in DataContext file (LINQ to SQL Data Classes). If you assign foreign key then all the relation will be created automatically. Others wise you may need to create relation.

Add WCF Service
Write click in your project and add WCF Service. Give its name as WCFService (you can give different name). Some files will be generated. WCFService.svc is WCF Service and IWCFService.cs is an interface it does contain Service contracts and Data Contracts. WCFService.cs is a normal class inherited by interface and all methods will be present here.

Add WCF Service Reference
Write click in your project and click Add Service Reference. Click Discover button. Give WCFServiceReference as Namespace (you can give any). Click ok. Reference of Your WCF Service is included in App_WebReferences folder in your project.

For each operation we need to write code in aspx.cs page, in WCF Service file, in Interface file

Select Operation
Declare an object of WCF service in .cs page.

WCFServiceClient oWCFServiceClient = new WCFServiceClient();

Write the following code in page load event.

if (!IsPostBack)
{
LoadCountry();
LoadStudent();
}

Write the following code in page load event to bind country DropDownList
public void LoadCountry()
{
ddlCountry.DataSource = oWCFServiceClient.LoadCountry();
ddlCountry.DataTextField = “CountryName”;
ddlCountry.DataValueField = “CountryID”;
ddlCountry.DataBind();
}

Write the following code in page load event to bind GridView.
public void LoadStudent()
{
GridView1.DataSource = oWCFServiceClient.LoadStudent();
GridView1.DataBind();
}

Write the following code in GridView SelectedIndexChanged event.
GridViewRow row = GridView1.SelectedRow;
txtID.Text = row.Cells[1].Text;
txtName.Text = row.Cells[2].Text;
txtAddress.Text = row.Cells[3].Text;
txtPhone.Text = row.Cells[4].Text;
ddlCountry.Text = row.Cells[5].Text;

This method will clear all the HTML controls.
public void Clear()
{
txtPhone.Text = “”;
txtName.Text = “”;
txtAddress.Text = “”;
ddlCountry.SelectedIndex = 0;
txtID.Text = “”;
}

In WCF Service:
LINQDataClassesDataContext oLINQDataClassesDataContext = new LINQDataClassesDataContext();

public List<Country> LoadCountry()
{
var result = from c in oLINQDataClassesDataContext.Countries
select c;
return result.ToList();
}
public List<object> LoadStudent()
{
var result = from s in oLINQDataClassesDataContext.Students
select new { s.StudentID, s.StudentName, s.Address, s.Phone, s.CountryID, s.Country.CountryName };
List<object> ostudent = new List<object>();
foreach (var s in result)
{
ostudent.Add(s);
}
return ostudent;
}

In Interface:
[OperationContract]
List<Country> LoadCountry();

[OperationContract]
List<Student> LoadStudent();

Insert Operation
To insert or save data in to database write the following code.

In Button Click Event(btnAdd_Click):
oWCFServiceClient.InsertData(txtName.Text, txtAddress.Text, txtPhone.Text, ddlCountry.SelectedValue.ToString());
LoadStudent();

In WCF Service:
public void InsertData(string StudenName, string Address, string Phone, string CountryID)
{
Student oStudent = new Student();
oStudent.StudentName = StudenName;
oStudent.Address = Address;
oStudent.Phone = Phone;
oStudent.CountryID = CountryID;
oLINQDataClassesDataContext.Students.InsertOnSubmit(oStudent);
oLINQDataClassesDataContext.SubmitChanges();
}

In Interface:
[OperationContract]
void InsertData(string StudenName, string Address, string Phone, string CountryID);

Update Operation
To update the records of a student write the following code.

In Button Click Event(btnEdit_Click):
oWCFServiceClient.UpdateData(Convert.ToInt16(txtID.Text), txtName.Text, txtAddress.Text, txtPhone.Text, ddlCountry.SelectedValue.ToString());
LoadStudent();

In WCF Service:
public void UpdateData(int StudentID, string StudenName, string Address, string Phone, string CountryID)
{

var result = (from st in oLINQDataClassesDataContext.Students
where st.StudentID == StudentID
select st).Single();
result.StudentName = StudenName;
result.Address = Address;
result.Phone = Phone;
result.CountryID = CountryID;
oLINQDataClassesDataContext.SubmitChanges();
}

In Interface:
[OperationContract]
void UpdateData(int StudentID, string StudenName, string Address, string Phone, string CountryID);

Delete Operation
To delete the records of a student write the following code.

In Button Click Event(btnDelete_Click):
oWCFServiceClient.DeleteData(Convert.ToInt16(txtID.Text));
LoadStudent();
Clear();

In WCF Service:
public void DeleteData(int StudentID)
{
var result = (from st in oLINQDataClassesDataContext.Students
where st.StudentID == StudentID
select st).Single();
oLINQDataClassesDataContext.Students.DeleteOnSubmit(result);
oLINQDataClassesDataContext.SubmitChanges();
}

In Interface:
[OperationContract]
void DeleteData(int StudentID);

Search Operation
To search the records of a particular student write the following code.

In Button Click Event(btnSearch_Click):
string StudentID = txtSearch.Text;
if (StudentID == “”)
StudentID = “0″;
GridView1.DataSource = oWCFServiceClient.SearchStudent(Convert.ToInt16(StudentID));
GridView1.DataBind();

In WCF Service:
public IList<object> SearchStudent(int StudentID)
{
IList<Object> items = new List<Object>();
if (StudentID == 0)
{
var result = from std in oLINQDataClassesDataContext.Students
select new { std.StudentID, std.StudentName, std.Address, std.Phone, std.CountryID, std.Country.CountryName };
foreach (var per in result)
{
items.Add(per);
}
}
else
{
var result = from std in oLINQDataClassesDataContext.Students
where std.StudentID == StudentID
select new { std.StudentID, std.StudentName, std.Address, std.Phone, std.CountryID, std.Country.CountryName };

foreach (var per in result)
{
items.Add(per);
}
}

return items;
}

In Interface:
[OperationContract]
void IList<object> SearchStudent(int StudentID)

This is all about CRUD operations in WCF. Hope you all enjoy this article.