CRUD operations using WCF Service

CRUD operations using WCF Service. Creating Insert, update, delete and select methods in web service and hosting it in IIS and using them in the Web site.

First add a WCF service application and rename the .svc file as “MyService” or with any other name which be used further as the services name.

Output 1 of crud operations in WCF

Add below code in the IMyService.cs file

[ServiceContract]
   public interface IMyService
   {
       [OperationContract]
       EmployeeData Get();

       [OperationContract]
       void Insert(string name, string country);

       [OperationContract]
       void Update(int EmpID, string name, string country);

       [OperationContract]
       void Delete(int EmpID);
   }


   [DataContract]
   public class EmployeeData
   {
       public EmployeeData()
       {
           this.EmployeeTable = new DataTable("EmployeeData");
       }

       [DataMember]
       public DataTable EmployeeTable { get; set; }
   }

DataContract class named EmployeeData which contains a DataTable Property EmployeeTable which will be used to send the data from the WCF Service to the Web Application.
The IMyService Interface has four methods decorated with OperationContract attribute to perform Select, Insert, Update and Delete operations.

Add the code for Crud operation in MyService.svc file.

public EmployeeData Get()
       {
           string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand("SELECT EmpID, Name, Country FROM Employee"))
               {
                   using (SqlDataAdapter sda = new SqlDataAdapter())
                   {
                       cmd.Connection = con;
                       sda.SelectCommand = cmd;
                       using (DataTable dt = new DataTable())
                       {
                           EmployeeData employee = new EmployeeData();
                           sda.Fill(employee.EmployeeTable);
                           return employee;
                       }
                   }
               }
           }
       }

       public void Insert(string name, string country)
       {
           string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand("INSERT INTO Employee (Name, Country) VALUES (@Name, @Country)"))
               {
                   cmd.Parameters.AddWithValue("@Name", name);
                   cmd.Parameters.AddWithValue("@Country", country);
                   cmd.Connection = con;
                   con.Open();
                   cmd.ExecuteNonQuery();
                   con.Close();
               }
           }
       }

       public void Update(int EmpID, string name, string country)
       {
           string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand("UPDATE Employee SET Name = @Name, Country = @Country WHERE EmpID = @EmpID"))
               {
                   cmd.Parameters.AddWithValue("@EmpID", EmpID);
                   cmd.Parameters.AddWithValue("@Name", name);
                   cmd.Parameters.AddWithValue("@Country", country);
                   cmd.Connection = con;
                   con.Open();
                   cmd.ExecuteNonQuery();
                   con.Close();
               }
           }
       }

       public void Delete(int EmpID)
       {
           string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand("DELETE FROM Employee WHERE EmpID = @EmpID"))
               {
                   cmd.Parameters.AddWithValue("@EmpID", EmpID);
                   cmd.Connection = con;
                   con.Open();
                   cmd.ExecuteNonQuery();
                   con.Close();
               }
           }
       }

Build the solution and host it in IIS and make use of the services from the web application by adding the service reference.

Demo Code

Download Demo