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

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

Step 2: Creating WCF Service

Now you have to create a WCF Service:

• Go to Visual Studio 2010
• New -> Select a project

Now click on the project and select WCF Service Application and provide a name for the service:

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:

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:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.ServiceModel.Web;

using System.Text;

using System.Data.SqlClient;

using System.Data;

 

namespace WCFServiceForInsert

{

    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.

    [ServiceContract]

    public interface IService1

    {

        [OperationContract]

        string InsertUserDetails(UserDetails userInfo);

 

        [OperationContract]

        DataSet  SelectUserDetails();

 

        [OperationContract]

        bool DeleteUserDetails(UserDetails userInfo);

 

        [OperationContract]

        void UpdateRegistrationTable(UserDetails userInfo);

    }

 

    // Use a data contract as illustrated in the sample below to add composite types to service operations.

    [DataContract]

    public class UserDetails

    {

        int userid;

        string username;

        string password;

        string country;

        string email;

 

        [DataMember]

        public int UserID

        {

            get { return userid; }

            set { userid = value; }

        }

 

        [DataMember]

        public string UserName

        {

            get { return username; }

            set { username = value; }

        }

        [DataMember]

        public string Password

        {

            get { return password; }

            set { password = value; }

        }

        [DataMember]

        public string Country

        {

            get { return country; }

            set { country = value; }

        }

        [DataMember]

        public string Email

        {

            get { return email; }

            set { email = value; }

        }

    }

}

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:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.ServiceModel.Web;

using System.Text;

using System.Data.SqlClient;

using System.Data;

 

namespace WCFServiceForInsert

{

    public class Service1 : IService1

    {

        public DataSet SelectUserDetails()

        {

            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");

            con.Open();

            SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);

            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();

            sda.Fill(ds);

            cmd.ExecuteNonQuery();

            con.Close();

            return ds;

        }

 

        public void UpdateRegistrationTable(UserDetails userInfo)

        {

            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");

            con.Open();

            SqlCommand cmd = new SqlCommand("update RegistrationTable set UserName=@UserName,Password=@Password,Country=@Country, Email=@Email where UserID=@UserID", con);

            cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);

            cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);

            cmd.Parameters.AddWithValue("@Password", userInfo.Password);

            cmd.Parameters.AddWithValue("@Country", userInfo.Country);

            cmd.Parameters.AddWithValue("@Email", userInfo.Email);

            cmd.ExecuteNonQuery();

            con.Close();

        }

 

        public bool DeleteUserDetails(UserDetails userInfo)

        {

            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");

                con.Open();

                SqlCommand cmd = new SqlCommand("delete from RegistrationTable where UserID=@UserID", con);

                cmd.Parameters.AddWithValue("@UserID", userInfo.UserID);

                cmd.ExecuteNonQuery();

                con.Close();

                return true;

        }

 

        public string InsertUserDetails(UserDetails userInfo)

        {

            string Message;

            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");

            con.Open();

            SqlCommand cmd = new SqlCommand("insert into RegistrationTable(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)", con);

            cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);

            cmd.Parameters.AddWithValue("@Password", userInfo.Password);

            cmd.Parameters.AddWithValue("@Country", userInfo.Country);

            cmd.Parameters.AddWithValue("@Email", userInfo.Email);

            int result = cmd.ExecuteNonQuery();

            if (result == 1)

            {

                Message = userInfo.UserName + " Details inserted successfully";

            }

            else

            {

                Message = userInfo.UserName + " Details not inserted successfully";

            }

            con.Close();

            return Message;

        }

    }

}

  

Testing the Service

Press F5 to run the service. A WCF Test Client form will be displayed and it will load the service.

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:

Now copy the URL:

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

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

Now again go to the Solution Explorer and click on the add the service reference:

The following window will be opened:

Now paste the above URL in the address and click on the go Button:

Click on the Ok Button. Now the reference has been added in the Solution Explorer.

Now create a new Windows Form and drag and drop controls onto the Windows Form. The designing form looks like below:

Double-click on the every Button, and add the following code with the click event handler:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.ServiceModel;

 

namespace WindowsFormsApplication1

{

    public partial class Registration : Form

    {

        ServiceReference1.Service1Client objService = new ServiceReference1.Service1Client(); // Add service reference

 

        public Registration()

        {

            InitializeComponent();

            showdata();

        }

 

        private void showdata()  // To show the data in the DataGridView

        {

            DataSet ds = new DataSet();

            ds = objService.SelectUserDetails();

            dataGridView1.DataSource = ds.Tables[0];

            dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            ServiceReference1.UserDetails objuserdetail = new ServiceReference1.UserDetails(); // Add type reference

            objuserdetail.UserName = textBoxUserName.Text;

            objuserdetail.Password = textBoxPassword.Text;

            objuserdetail.Country = textBoxCountry.Text;

            objuserdetail.Email = textBoxEmail.Text;

            objService.InsertUserDetails(objuserdetail); // To insert the data

            showdata();

        }

 

        private void button2_Click(object sender, EventArgs e)

        {

            ServiceReference1.UserDetails objuserdetail = new ServiceReference1.UserDetails();

            if (dataGridView1.Rows.Count > 1)

            {

                DataTable dt = new DataTable();

                objuserdetail.UserID = (int)dataGridView1.CurrentRow.Cells[0].Value;

                objService.DeleteUserDetails(objuserdetail); // To Delete the data

                showdata();

            }

        }

 

        private void Registration_Load(object sender, EventArgs e)

        {

        }

 

        private void button3_Click(object sender, EventArgs e)

        {

            int i = dataGridView1.SelectedCells[0].RowIndex;

            textBoxUserName.Text = dataGridView1.Rows[i].Cells[1].Value.ToString();

            textBoxPassword.Text = dataGridView1.Rows[i].Cells[2].Value.ToString();

            textBoxCountry.Text = dataGridView1.Rows[i].Cells[3].Value.ToString();

            textBoxEmail.Text = dataGridView1.Rows[i].Cells[4].Value.ToString();

        }

 

        private void button4_Click(object sender, EventArgs e)

        {

            ServiceReference1.UserDetails objuserdetail = new ServiceReference1.UserDetails();

            objuserdetail.UserID = (int)dataGridView1.CurrentRow.Cells[0].Value;

            objuserdetail.UserName = textBoxUserName.Text;

            objuserdetail.Password = textBoxPassword.Text;

            objuserdetail.Country = textBoxCountry.Text;

            objuserdetail.Email = textBoxEmail.Text;

            objService.UpdateRegistrationTable(objuserdetail); // To Update the Data

            showdata();

            textBoxUserName.Text = "";

            textBoxPassword.Text = "";

            textBoxCountry.Text = "";

            textBoxEmail.Text = "";

        }

    }

}

   

Now run the application

 

Press CTRL+F5 to run the application:

 

 

Now enter the UserName, Password, Country and Email and click on the save Button:

 

 

Now click on the save Button. Data will be saved in the database table and also displayed in the DataGridView on the form.

 

 

Now select a row from the DataGridView. Suppose we selected a row which has the UserName monu and the userID 38.

 

 

Now click on the Delete Button to delete the row from the DataGridView and database:

 

 

Now select a row from the DataGridView. Suppose we selected a row which has the UserName Rohatash:

 

Now click on the edit Button to display row data in the TextBoxes to update:

 

 

Now replace UserName Rohatash with Rohatash kumar and change the country India to SriLanka:

 

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:

 

The data has been inserted into the SQL Server database table; check it: