Insert, Update and Delete Data in GridView Using WCF Service

Today, I have provided an article showing you how to insert, delete and update data in a GridView using a WCF service from C# code.

Introduction

 
Today, I have provided an article showing you how to insert, delete and update data in a GridView using a WCF service from C# code. To insert, delete and update data in a GridView using a WCF service, we must do the following 3 things:
  1. Create Database Table
  2. Create WCF Service
  3. Create web Application
In the first step we will create a table in SQL Server; after that we create a simple function to insert, delete and update data in a GridView control using a WCF service. In a web application, add a reference of the service to insert, update and delete data in the GridView control. Let's take a look at a practical example. The example application is developed in Visual Studio 2010 and SQL Server 2008.
 

Step 1: Creating Database Table

  1. Database name:  Registration
  2. Database table name: RegistrationTable
RegistrationTable Table
 
img1.jpg
 

Step 2: Creating WCF Service

 
Now you have to create a WCF Service:
  • Go to Visual Studio 2010
  • New-> Select a project
img2.jpg
 
Now click on the project and select WCF Service Application and provide a name for the service:
 
img3.jpg
 
Now click on the Ok Button. Then you will get 3 files in Solution Explorer.
  1. IService.cs
  2. Service.svc
  3. Service.svc.cs
The following image shows the following files:
 
img4.jpg 
 
For inserting data into the database you need to write the following code in the IService1.cs file which contains the two sections:
  1. OperationContract
  2. DataContract
The OperationContract section is used to add service operations and a DataContract is used to add types to the service operations.
 
Iservice1.cs File
 
Now we create a function in the OperationContract section of the Iservice1.cs file:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Runtime.Serialization;  
  5. using System.ServiceModel;  
  6. using System.ServiceModel.Web;  
  7. using System.Text;  
  8. using System.Data.SqlClient;  
  9. using System.Data;  
  10.   
  11. namespace WCFServiceForInsert  
  12. {  
  13.     // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.  
  14.     [ServiceContract]  
  15.     public interface IService1  
  16.     {  
  17.         [OperationContract]  
  18.         string InsertUserDetails(UserDetails userInfo);  
  19.         [OperationContract]  
  20.         DataSet  SelectUserDetails();  
  21.         [OperationContract]  
  22.         bool DeleteUserDetails(UserDetails userInfo);  
  23.         [OperationContract]  
  24.         DataSet UpdateUserDetails(UserDetails userInfo);  
  25.         [OperationContract]  
  26.         void UpdateRegistrationTable(UserDetails userInfo);  
  27.     }  
  28.     // Use a data contract as illustrated in the sample below to add composite types to service operations.  
  29.     [DataContract]  
  30.     public class UserDetails  
  31.     {  
  32.         int userid;  
  33.         string username;  
  34.         string password;  
  35.         string country;  
  36.         string email;  
  37.         [DataMember]  
  38.         public int UserID  
  39.         {  
  40.             get { return userid; }  
  41.             set { userid = value; }  
  42.         }  
  43.         [DataMember]  
  44.         public string UserName  
  45.         {  
  46.             get { return username; }  
  47.             set { username = value; }  
  48.         }  
  49.         [DataMember]  
  50.         public string Password  
  51.         {  
  52.             get { return password; }  
  53.             set { password = value; }  
  54.         }  
  55.         [DataMember]  
  56.         public string Country  
  57.         {  
  58.             get { return country; }  
  59.             set { country = value; }  
  60.         }  
  61.         [DataMember]  
  62.         public string Email  
  63.         {  
  64.             get { return email; }  
  65.             set { email = value; }  
  66.         }  
  67.     }  
  68. }  
Service.svc.cs File
 
In this file we define the definition of the function InsertUserDetails(UserDetails userInfo).
 
And replace the code with the following:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Runtime.Serialization;  
  5. using System.ServiceModel;  
  6. using System.ServiceModel.Web;  
  7. using System.Text;  
  8. using System.Data.SqlClient;  
  9. using System.Data;  
  10.   
  11. namespace WCFServiceForInsert  
  12. {  
  13.     // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.  
  14.     public class Service1 : IService1  
  15.     {  
  16.         public DataSet SelectUserDetails()  
  17.         {  
  18.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  19.             con.Open();  
  20.             SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);  
  21.             SqlDataAdapter sda = new SqlDataAdapter(cmd);  
  22.             DataSet ds = new DataSet();  
  23.             sda.Fill(ds);  
  24.             cmd.ExecuteNonQuery();  
  25.             con.Close();  
  26.             return ds;  
  27.         }  
  28.         public bool DeleteUserDetails(UserDetails userInfo)  
  29.         {  
  30.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  31.             con.Open();  
  32.             SqlCommand cmd = new SqlCommand("delete from RegistrationTable where UserID=@UserID", con);  
  33.             cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);  
  34.             cmd.ExecuteNonQuery();  
  35.             con.Close();  
  36.             return true;  
  37.         }  
  38.         public DataSet UpdateUserDetails(UserDetails userInfo)  
  39.         {  
  40.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  41.             con.Open();  
  42.             SqlCommand cmd = new SqlCommand("select * from RegistrationTable where UserID=@UserID", con);  
  43.             cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);  
  44.             SqlDataAdapter sda = new SqlDataAdapter(cmd);  
  45.             DataSet ds = new DataSet();  
  46.             sda.Fill(ds);  
  47.             cmd.ExecuteNonQuery();  
  48.             con.Close();  
  49.             return ds;  
  50.         }  
  51.         public void UpdateRegistrationTable(UserDetails userInfo)  
  52.         {  
  53.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  54.             con.Open();  
  55.             SqlCommand cmd = new SqlCommand("update RegistrationTable set UserName=@UserName,Password=@Password,Country=@Country, Email=@Email where UserID=@UserID", con);  
  56.             cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);  
  57.             cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);  
  58.             cmd.Parameters.AddWithValue("@Password", userInfo.Password);  
  59.             cmd.Parameters.AddWithValue("@Country", userInfo.Country);  
  60.             cmd.Parameters.AddWithValue("@Email", userInfo.Email);  
  61.             cmd.ExecuteNonQuery();  
  62.             con.Close();  
  63.         }  
  64.         public string InsertUserDetails(UserDetails userInfo)  
  65.         {  
  66.             string Message;  
  67.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  68.             con.Open();  
  69.             SqlCommand cmd = new SqlCommand("insert into RegistrationTable(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)", con);  
  70.             cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);  
  71.             cmd.Parameters.AddWithValue("@Password", userInfo.Password);  
  72.             cmd.Parameters.AddWithValue("@Country", userInfo.Country);  
  73.             cmd.Parameters.AddWithValue("@Email", userInfo.Email);  
  74.             int result = cmd.ExecuteNonQuery();  
  75.             if (result == 1)  
  76.             {  
  77.                 Message = userInfo.UserName + " Details inserted successfully";  
  78.             }  
  79.             else  
  80.             {  
  81.                 Message = userInfo.UserName + " Details not inserted successfully";  
  82.             }  
  83.             con.Close();  
  84.             return Message;  
  85.         }  
  86.     }  
  87. }  
Testing the Service
 
Press F5 to run the service. A WCF Test Client form will be displayed and it will load the service.
 
img5.jpg
 
Now double-click the InserUserDetails() method under IService1. The InserUserDetails tab will be displayed.
 
img7.jpg
 
The service was added successfully.
 
Now open the service in the browser.
 
Now right-click on the service1.vcs -> open in browser:
 
img6.jpg
 
Now copy the URL.
 
img8.jpg
 
URL
 
http://localhost:2268/Service1.svc
 

Step 3: Create web Application (Accessing the Service)

 
Now, you have to create a web site.
  • Go to Visual Studio 2010
  • New-> Select a website application
  • Click OK
img9.gif
 
Now add a new page to the website:
  • Go to the Solution Explorer
  • Right-click on the Project name
  • Select add new item
  • Add new web page and give it a name
  • Click OK
img10.gif
 
When we click on the add the service reference the following window will be opened:
 
img11.jpg
 
Now paste the above URL in the address and click on the go button.
 
img12.jpg
 
Click on the Ok Button. Now the reference has been added in the Solution Explorer.
 
img13.jpg
 
Now create a new website and drag and drop controls onto the aspx page. The aspx code is the following:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="WcfServiceselectInsert.aspx.cs"  
  2.     Inherits="WcfServiceselectInsert" %>  
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title></title>  
  7. </head>  
  8. <body>  
  9.     <form id="form1" runat="server">  
  10.     <div>  
  11.         <div>  
  12.             <table width="84%" cellpadding="0" cellspacing="0" style="border: solid 1px #3366CC;">  
  13.                 <tr>  
  14.                     <td colspan="4" style="height: 30px; background-color: #f5712b;">  
  15.                         <span class="TextTitle" style="color: #FFFFFF;">Registration Form</span>  
  16.                     </td>  
  17.                 </tr>  
  18.                 <tr>  
  19.                     <td height="20px" colspan="0">  
  20.                     </td>  
  21.                 </tr>  
  22.                 <tr>  
  23.                     <td width="50%" valign="top">  
  24.                         <table id="TableLogin" class="HomePageControlBGLightGray" cellpadding="4" cellspacing="4"  
  25.                             runat="server" width="100%">  
  26.                             <tr>  
  27.                                 <td colspan="3" align="center">  
  28.                                     <asp:Label ID="LabelMessage" ForeColor="Red" runat="server" EnableViewState="False"  
  29.                                         Visible="False"></asp:Label><br>  
  30.                                 </td>  
  31.                             </tr>  
  32.                             <tr style="font-weight: normal; color: #000000">  
  33.                                 <td align="right">  
  34.                                     <span>UserName:</span>;  
  35.                                 </td>  
  36.                                 <td align="left" style="padding-left: 10px;">  
  37.                                     <asp:TextBox ID="TextBoxUserName" runat="server" CssClass="textbox" Width="262px"  
  38.                                         MaxLength="50" Height="34px"></asp:TextBox>  
  39.                                 </td>  
  40.                             </tr>  
  41.                             <tr>  
  42.                                 <td align="right">  
  43.                                     <span class="TextTitle">Password:</span>  
  44.                                 </td>  
  45.                                 <td align="left" style="padding-left: 10px;">  
  46.                                     <asp:TextBox ID="TextBoxPassword" runat="server" CssClass="textbox" Width="261px"  
  47.                                         MaxLength="50" Height="34px"></asp:TextBox>  
  48.                                     <br />  
  49.                                 </td>  
  50.                             </tr>  
  51.                             <tr>  
  52.                                 <td align="right">  
  53.                                     <span class="TextTitle">Country:</span>  
  54.                                 </td>  
  55.                                 <td align="left" style="padding-left: 10px;">  
  56.                                     <asp:TextBox ID="TextBoxCountry" runat="server" CssClass="textbox" Width="258px"  
  57.                                         MaxLength="50" Height="34px"></asp:TextBox>  
  58.                                     <br />  
  59.                                 </td>  
  60.                             </tr>  
  61.                             <tr>  
  62.                                 <td align="right">  
  63.                                     <span class="TextTitle">Email:</span>  
  64.                                 </td>  
  65.                                 <td align="left" style="padding-left: 10px;">  
  66.                                     <asp:TextBox ID="TextBoxEmail" runat="server" CssClass="textbox" Width="258px" MaxLength="50"  
  67.                                         Height="34px"></asp:TextBox>  
  68.                                     <br />  
  69.                                 </td>  
  70.                             </tr>  
  71.                             <tr>  
  72.                                 <td align="right">  
  73.                                 </td>  
  74.                                 <td align="left" style="padding-left: 10px;">  
  75.                                     <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" Width="87px" />  
  76.                                     <br />  
  77.                                 </td>  
  78.                             </tr>  
  79.                             <tr>  
  80.                                 <td>  
  81.                                 </td>  
  82.                                 <td>  
  83.                                     <asp:GridView ID="CategoriesGridView" runat="server" AutoGenerateColumns="False"  
  84.                                         CellPadding="4" DataKeyNames="UserID" AllowSorting="True" PagerStyle-HorizontalAlign="left"  
  85.                                         Width="100%" BackColor="#FFFFCC">  
  86.                                         <HeaderStyle HorizontalAlign="Left" />  
  87.                                         <Columns>  
  88.                                             <asp:TemplateField HeaderText="UserName ">  
  89.                                                 <ItemTemplate>  
  90.                                                     <asp:Label ID="LabelUserName" runat="server" Text='<%#Eval("UserName") %>'></asp:Label>  
  91.                                                 </ItemTemplate>  
  92.                                             </asp:TemplateField>  
  93.                                             <asp:TemplateField HeaderText="Password">  
  94.                                                 <ItemTemplate>  
  95.                                                     <asp:Label ID="LabelLPassword" runat="server" Text='<%#Eval("Password") %>'></asp:Label>  
  96.                                                 </ItemTemplate>  
  97.                                             </asp:TemplateField>  
  98.                                             <asp:TemplateField HeaderText="Country ">  
  99.                                                 <ItemTemplate>  
  100.                                                     <asp:Label ID="LabelCountry" runat="server" Text='<%#Eval("Country") %>'></asp:Label>  
  101.                                                 </ItemTemplate>  
  102.                                             </asp:TemplateField>  
  103.                                             <asp:TemplateField HeaderText="Email">  
  104.                                                 <ItemTemplate>  
  105.                                                     <asp:Label ID="LabelEmail" runat="server" Text='<%#Eval("Email") %>'></asp:Label>  
  106.                                                 </ItemTemplate>  
  107.                                             </asp:TemplateField>  
  108.                                             <asp:TemplateField HeaderText="Edit">  
  109.                                                 <ItemTemplate>  
  110.                                                     <asp:ImageButton ID="ImageButtonEdit" runat="server" CausesValidation="false" CommandArgument='<%#Eval("UserId") %>'
    OnCommand=
    "CategoryImageButtonEdit_Command" ImageUrl="~/Images/Edit.png"  
  111.                                                         ToolTip="Edit" />  
  112.                                                 </ItemTemplate>  
  113.                                                 <ItemStyle Width="100px" />  
  114.                                             </asp:TemplateField>  
  115.                                             <asp:TemplateField HeaderText="Delete">  
  116.                                                 <ItemStyle Width="100px" HorizontalAlign="Left" />  
  117.                                                 <ItemTemplate>  
  118.                                                     <asp:ImageButton ID="ImageButtonDelete" runat="server" CausesValidation="false" CommandArgument='<%#Eval("UserId") %>'  
  119.                                                         CommandName="Delete" OnCommand="CategoryButtonDelete_Command" ImageUrl="~/Images/cross.gif"  
  120.                                                         ToolTip="Delete" />  
  121.                                                 </ItemTemplate>  
  122.                                             </asp:TemplateField>  
  123.                                         </Columns>  
  124.                                         <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />  
  125.                                         <PagerStyle HorizontalAlign="Left"></PagerStyle>  
  126.                                         <RowStyle CssClass="GridItems" />  
  127.                                         <HeaderStyle CssClass="GridHeader" />  
  128.                                     </asp:GridView>  
  129.                                 </td>  
  130.                             </tr>  
  131.                         </table>  
  132.                     </td>  
  133.                 </tr>  
  134.             </table>  
  135.         </div>  
  136.     </div>  
  137.     </form>  
  138. </body>  
  139. </html>  
The designing form looks like below: 
 
img14.jpg
 
Double-click the button, and add the following code in the click event handler:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using ServiceReference1;  
  9. using System.IO;  
  10.   
  11. public partial class WcfServiceselectInsert : System.Web.UI.Page  
  12. {  
  13.     ServiceReference1.Service1Client objServiceClientobjService = new ServiceReference1.Service1Client();  
  14.     protected void Page_Load(object sender, EventArgs e)  
  15.     {  
  16.         if (!IsPostBack)  
  17.         {  
  18.             showdata();  
  19.         }  
  20.     }  
  21.     private void showdata()  
  22.     {  
  23.         DataSet ds = new DataSet();  
  24.         ds = objServiceClientobjService.SelectUserDetails();  
  25.         CategoriesGridView.DataSource = ds;  
  26.         CategoriesGridView.DataBind();  
  27.     }  
  28.     protected void Button1_Click(object sender, EventArgs e)  
  29.     {  
  30.         if (Button1.Text == "Update")  
  31.         {  
  32.             updateuserdetail();  
  33.             TextBoxUserName.Text = string.Empty;  
  34.             TextBoxPassword.Text = string.Empty;  
  35.             TextBoxCountry.Text = string.Empty;  
  36.             TextBoxEmail.Text = string.Empty;  
  37.             Button1.Text = "Submit";  
  38.         }  
  39.         else  
  40.         {  
  41.             insertuserdetail();  
  42.             showdata();  
  43.         }  
  44.     }  
  45.     private void insertuserdetail()  
  46.     {  
  47.         UserDetails userInfo = new UserDetails();  
  48.         userInfo.UserName = TextBoxUserName.Text;  
  49.         userInfo.Password = TextBoxPassword.Text;  
  50.         userInfo.Country = TextBoxCountry.Text;  
  51.         userInfo.Email = TextBoxEmail.Text;  
  52.         string result = objServiceClientobjService.InsertUserDetails(userInfo);  
  53.         LabelMessage.Text = result;  
  54.         showdata();  
  55.     }  
  56.     protected void CategoryButtonDelete_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)  
  57.     {  
  58.         UserDetails userInfo = new UserDetails();  
  59.         userInfo.UserID = int.Parse(e.CommandArgument.ToString());  
  60.         objServiceClientobjService.DeleteUserDetails(userInfo);  
  61.         showdata();  
  62.     }  
  63.     protected void CategoryImageButtonEdit_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)  
  64.     {  
  65.         UserDetails userInfo = new UserDetails();  
  66.         userInfo.UserID = int.Parse(e.CommandArgument.ToString());  
  67.         ViewState["UserId"] = userInfo.UserID;  
  68.         DataSet ds = new DataSet();  
  69.         ds = objServiceClientobjService.UpdateUserDetails(userInfo);  
  70.         if (ds.Tables[0].Rows.Count > 0)  
  71.         {  
  72.             TextBoxUserName.Text = ds.Tables[0].Rows[0]["UserName"].ToString();  
  73.             TextBoxPassword.Text = ds.Tables[0].Rows[0]["Password"].ToString();  
  74.             TextBoxCountry.Text = ds.Tables[0].Rows[0]["Country"].ToString();  
  75.             TextBoxEmail.Text = ds.Tables[0].Rows[0]["Email"].ToString();  
  76.             Button1.Text = "Update";  
  77.         }  
  78.     }  
  79.     private void updateuserdetail()  
  80.     {  
  81.         UserDetails userInfo = new UserDetails();  
  82.         userInfo.UserID = int.Parse(ViewState["UserId"].ToString());  
  83.         userInfo.UserName = TextBoxUserName.Text;  
  84.         userInfo.Password = TextBoxPassword.Text;  
  85.         userInfo.Country = TextBoxCountry.Text;  
  86.         userInfo.Email = TextBoxEmail.Text;  
  87.         objServiceClientobjService.UpdateRegistrationTable(userInfo);  
  88.         showdata();  
  89.     }  
  90. }  
Now run the application. 
 
Press CTRL+F5 to run the project:
 
img15.jpg
 
Now enter the UserName, Password, country and Email and click on the button.
 
img16.jpg 
 
Now click on the button. Data will be saved in the database table and also displayed in the GridView on the form.
 
img17.jpg 
 
We can also insert more data.
 
img18.jpg 
 
Now click on Delete Image to delete the record where the username is manish in the GridView.
 
img19.jpg 
 
Now click on the edit Image to update the record where the username is Rohatash Kumar in the GridView. The data will be displayed in the TextBoxes.
 
img20.jpg 
 
Now replace username with Name Sanjay and email id with  sanjay@gmail.com and click on the update Button. The updated data will be displayed in the GridView and also will be updated in the database table. 
 
img21.jpg