Insert, Update, Delete in Web Service

Web Service is a method of communication between 2 electric devices over World Wide Web. It is a software system to design communicates 2 devices through network.

This tutorial will help you to insert, update, delete, select, search data into SQL Server by using Web Service.  For this we will use 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
•     Web Service
•     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.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.

Web Service
Write click in App_Code and add a Web Service in your project.

Select Operation
Declare an object of web service and List in .cs page.

WSWebService oWSWebService = new WSWebService();
IList<object> oList = new List<object>();

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 = oWSWebService.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 = oWSWebService.LoadStudent();
GridView1.DataBind();
}

Write the following code in web service.
LINQDataClassesDataContext oLINQDataClassesDataContext = new LINQDataClassesDataContext();

[WebMethod]
public List<Country> LoadCountry()
{
var result = from c in oLINQDataClassesDataContext.Countries
select c;
return result.ToList();
}

[WebMethod]
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;
}

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 = “”;
}

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

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

Web Service:
[WebMethod]
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();
}

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

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

Web Service:
[WebMethod]
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();
}

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

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

Web Service:
[WebMethod]
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();
}

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

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

Web Service:
[WebMethod]
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;
}