Insert, Update and Delete Data With DataGridView in Windows Form Using WCF Service

In this article, I am showing you how to Insert, Edit, Update and Delete Data with DataGridView in Windows Form Using WCF Service from C# code.

Today, I have provided an article showing you how to Insert, Edit, Update and Delete Data with DataGridView in Windows Form Using WCF Service from C# code. To Insert, Edit, Update and Delete Data with DataGridView, we must do the following 3 things: 
  1. Create Database Table 
  2. Create WCF Service
  3. Create Windows Forms Application
In the first step, we will create a table in SQL Server; after that we create a simple function to insert, update and delete data in a DataGridView control using a WCF service. In a web application, add a reference of the service to do the insert, update and delete in the DataGridView 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
 
image1.jpg
 

Step 2: Creating WCF Service

 
Now you have to create a WCF Service:
  • Go to Visual Studio 2010
  • New -> Select a project
image2.jpg
 
Now click on the project and select WCF Service Application and provide a name for the service:
 
image3.jpg
 
Now click on the Ok Button. Then you will get the following 3 files in Solution Explorer:
  1. IService.cs
  2. Service.svc
  3. Service.svc.cs
The following image shows the following files:
 
image4.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. namespace WCFServiceForInsert  
  11. {  
  12.     // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.  
  13.     [ServiceContract]  
  14.     public interface IService1  
  15.     {  
  16.         [OperationContract]  
  17.         string InsertUserDetails(UserDetails userInfo);  
  18.         [OperationContract]  
  19.         DataSet  SelectUserDetails();  
  20.         [OperationContract]  
  21.         bool DeleteUserDetails(UserDetails userInfo);  
  22.         [OperationContract]  
  23.         void UpdateRegistrationTable(UserDetails userInfo);  
  24.     }  
  25.     // Use a data contract as illustrated in the sample below to add composite types to service operations.  
  26.     [DataContract]  
  27.     public class UserDetails  
  28.     {  
  29.         int userid;  
  30.         string username;  
  31.         string password;  
  32.         string country;  
  33.         string email;  
  34.         [DataMember]  
  35.         public int UserID  
  36.         {  
  37.             get { return userid; }  
  38.             set { userid = value; }  
  39.         }  
  40.         [DataMember]  
  41.         public string UserName  
  42.         {  
  43.             get { return username; }  
  44.             set { username = value; }  
  45.         }  
  46.         [DataMember]  
  47.         public string Password  
  48.         {  
  49.             get { return password; }  
  50.             set { password = value; }  
  51.         }  
  52.         [DataMember]  
  53.         public string Country  
  54.         {  
  55.             get { return country; }  
  56.             set { country = value; }  
  57.         }  
  58.         [DataMember]  
  59.         public string Email  
  60.         {  
  61.             get { return email; }  
  62.             set { email = value; }  
  63.         }  
  64.     }  
  65. }  
Service.svc.cs File
 
In this file we define the definition of the functions for insert, update and delete.
 
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. namespace WCFServiceForInsert  
  11. {  
  12.     public class Service1 : IService1  
  13.     {  
  14.         public DataSet SelectUserDetails()  
  15.         {  
  16.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  17.             con.Open();  
  18.             SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);  
  19.             SqlDataAdapter sda = new SqlDataAdapter(cmd);  
  20.             DataSet ds = new DataSet();  
  21.             sda.Fill(ds);  
  22.             cmd.ExecuteNonQuery();  
  23.             con.Close();  
  24.             return ds;  
  25.         }  
  26.         public void UpdateRegistrationTable(UserDetails userInfo)  
  27.         {  
  28.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  29.             con.Open();  
  30.             SqlCommand cmd = new SqlCommand("update RegistrationTable set UserName=@UserName,Password=@Password,Country=@Country, Email=@Email where UserID=@UserID", con);  
  31.             cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);  
  32.             cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);  
  33.             cmd.Parameters.AddWithValue("@Password", userInfo.Password);  
  34.             cmd.Parameters.AddWithValue("@Country", userInfo.Country);  
  35.             cmd.Parameters.AddWithValue("@Email", userInfo.Email);  
  36.             cmd.ExecuteNonQuery();  
  37.             con.Close();  
  38.         }  
  39.         public bool DeleteUserDetails(UserDetails userInfo)  
  40.         {  
  41.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  42.                 con.Open();  
  43.                 SqlCommand cmd = new SqlCommand("delete from RegistrationTable where UserID=@UserID", con);  
  44.                 cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);  
  45.                 cmd.ExecuteNonQuery();  
  46.                 con.Close();  
  47.                 return true;  
  48.         }  
  49.         public string InsertUserDetails(UserDetails userInfo)  
  50.         {  
  51.             string Message;  
  52.             SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");  
  53.             con.Open();  
  54.             SqlCommand cmd = new SqlCommand("insert into RegistrationTable(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)", con);  
  55.             cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);  
  56.             cmd.Parameters.AddWithValue("@Password", userInfo.Password);  
  57.             cmd.Parameters.AddWithValue("@Country", userInfo.Country);  
  58.             cmd.Parameters.AddWithValue("@Email", userInfo.Email);  
  59.             int result = cmd.ExecuteNonQuery();  
  60.             if (result == 1)  
  61.             {  
  62.                 Message = userInfo.UserName + " Details inserted successfully";  
  63.             }  
  64.             else  
  65.             {  
  66.                 Message = userInfo.UserName + " Details not inserted successfully";  
  67.             }  
  68.             con.Close();  
  69.             return Message;  
  70.         }  
  71.     }  
  72. }  
Testing the Service
 
Press F5 to run the service. A WCF Test Client form will be displayed and it will load the service.
 
image5.jpg
 
Now double-click the InserUserDetails() method under IService1. The InserUserDetails tab will be displayed.
 
The service was added successfully. Now open the service in the browser.
 
Now right-click on the service1.vcs -> open in the browser:
  
image6.jpg
 
Now copy the URL:
 
image8.jpg
 
URL
 
http://localhost:2268/Service1.svc
 

Step 3: Create Windows Forms Application (Accessing the Service)

 
Now, you have to create a Windows Forms Application.
  • Go to Visual Studio 2010
  • New-> Select a project-> Windows Forms Application
  • Click OK
img27.jpg
 
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 windows form and give it a name
  • Click OK
img28.jpg
 
Now again go to the Solution Explorer and click on the add the service reference:
 
img29.jpg
 
The following window will be opened:
 
image11.jpg
 
Now paste the above URL in the address and click on the go Button:
 
image12.jpg
 
Click on the Ok Button. Now the reference has been added in the Solution Explorer.
 
img6.jpg
 
Now create a new Windows Form and drag and drop controls onto the Windows Form. The designing form looks like below:
 
imge7.jpg
 
Double-click on every Button, and add the following code with the click event handler:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Windows.Forms;  
  9. using System.ServiceModel;  
  10. namespace WindowsFormsApplication1  
  11. {  
  12.     public partial class Registration : Form  
  13.     {  
  14.         ServiceReference1.Service1Client objService = new ServiceReference1.Service1Client(); // Add service reference  
  15.         public Registration()  
  16.         {  
  17.             InitializeComponent();  
  18.             showdata();  
  19.         }  
  20.         private void showdata()  // To show the data in the DataGridView  
  21.         {  
  22.             DataSet ds = new DataSet();  
  23.             ds = objService.SelectUserDetails();  
  24.             dataGridView1.DataSource = ds.Tables[0];           dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);  
  25.         }  
  26.         private void button1_Click(object sender, EventArgs e)  
  27.         {  
  28.             ServiceReference1.UserDetails objuserdetail = new ServiceReference1.UserDetails(); // Add type reference  
  29.             objuserdetail.UserName = textBoxUserName.Text;  
  30.             objuserdetail.Password = textBoxPassword.Text;  
  31.             objuserdetail.Country = textBoxCountry.Text;  
  32.             objuserdetail.Email = textBoxEmail.Text;  
  33.             objService.InsertUserDetails(objuserdetail); // To insert the data  
  34.             showdata();  
  35.         }  
  36.         private void button2_Click(object sender, EventArgs e)  
  37.         {  
  38.             ServiceReference1.UserDetails objuserdetail = new ServiceReference1.UserDetails();  
  39.             if (dataGridView1.Rows.Count > 1)  
  40.             {  
  41.                 DataTable dt = new DataTable();  
  42.                 objuserdetail.UserID = (int)dataGridView1.CurrentRow.Cells[0].Value;  
  43.                 objService.DeleteUserDetails(objuserdetail); // To Delete the data  
  44.                 showdata();  
  45.             }  
  46.         }  
  47.         private void Registration_Load(object sender, EventArgs e)  
  48.         {  
  49.         }  
  50.         private void button3_Click(object sender, EventArgs e)  
  51.         {  
  52.             int i = dataGridView1.SelectedCells[0].RowIndex;  
  53.             textBoxUserName.Text = dataGridView1.Rows[i].Cells[1].Value.ToString();  
  54.             textBoxPassword.Text = dataGridView1.Rows[i].Cells[2].Value.ToString();  
  55.             textBoxCountry.Text = dataGridView1.Rows[i].Cells[3].Value.ToString();  
  56.             textBoxEmail.Text = dataGridView1.Rows[i].Cells[4].Value.ToString();  
  57.         }  
  58.         private void button4_Click(object sender, EventArgs e)  
  59.         {  
  60.             ServiceReference1.UserDetails objuserdetail = new ServiceReference1.UserDetails();  
  61.             objuserdetail.UserID = (int)dataGridView1.CurrentRow.Cells[0].Value;  
  62.             objuserdetail.UserName = textBoxUserName.Text;  
  63.             objuserdetail.Password = textBoxPassword.Text;  
  64.             objuserdetail.Country = textBoxCountry.Text;  
  65.             objuserdetail.Email = textBoxEmail.Text;  
  66.             objService.UpdateRegistrationTable(objuserdetail); // To Update the Data  
  67.             showdata();  
  68.             textBoxUserName.Text = "";  
  69.             textBoxPassword.Text = "";  
  70.             textBoxCountry.Text = "";  
  71.             textBoxEmail.Text = "";  
  72.         }  
  73.     }  
  74. }  
Now run the application
 
Press CTRL+F5 to run the application: 
 
img8.jpg 
 
Now enter the UserName, Password, Country and Email and click on the save Button:
 
img9.jpg
 
Now click on the save Button. Data will be saved in the database table and also displayed in the DataGridView on the form.
 
img10.jpg
 
Now select a row from the DataGridView. Suppose we selected a row which has the UserName menu and the userID 38.
 
img12.jpg
 
Now click on the Delete Button to delete the row from the DataGridView and database:
 
img13.jpg
 
Now select a row from the DataGridView. Suppose we selected a row which has the UserName Rohatash:
 
img14.jpg
 
Now click on the edit Button to display row data in the TextBoxes to update:
 
img15.jpg
 
Now replace UserName Rohatash with Rohatash kumar and change the country India to SriLanka:
 
img16.jpg
 
Now click on the Update Button to update the data in the DataGridView and the Database table. The updated row looks as in the following image:
 
img17.jpg
 
The data has been inserted into the SQL Server database table; check it:
 
img18.jpg