Create Simple WCF Service Project using C# and SQL

In this blog you will learn how to Create Simple WCF Service Project using C# and SQL.

Create Table in Sql

Intially, database can be created with table as u like (i.e) say here i have create employee details. StoreProcedure to be create for Employee Details.

1. Create database EmpDetails
2. Create table "Employee"

Create table Employee
(EmployeeID numeric(18,0),FirstName varchar(25),LastName varchar(25),BirthDate datetime,City varchar(20), Country varchar(30))

Insert the values to the table "Employee",

insert into Employee values (1126146,'Rajivi','Lathika','5/29/1995','Chennai','India');
insert into Employee values(1126147,'Keerthi','Venkatesan','3/27/1993','Chennai','India');etc;,

The Store Procedure for employee details is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Alter PROCEDURE GetEmpDetails
    -- Add the parameters for the stored procedure here
          @EmployeeID int=0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
         SELECT FirstName, LastName, convert(char(10),BirthDate,101)BirthDate, City, Country
      FROM Employee WHERE EmployeeID=@EmployeeID


    -- Insert statements for procedure here
    --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Note: Here the birthdate to be converted to date alone which seems to be in date and time.

Now we successfully created the database.

Create Service

1. Open Microsoft Visual Studio and follow these steps.
   
   File->New->Project->WCF->WCF Service Application->(rename the form if u want) OK.
   
   
2. Create a class file by add->class file.
   

   namespace WcfService1
   {
       public class EmpNDet
       {
           public string GetEmpDet(string strSQLCon, string strEmpID)
           {
               string strXMLResult = string.Empty;
               DataSet DTEmpDet = new DataSet();
               try
               {
                   var arrParameters = new SqlParameter[1];
                   arrParameters[0] = new SqlParameter("@EmployeeID", SqlDbType.VarChar, 18);
                   arrParameters[0].Value = strEmpID;
                   DTEmpDet = SqlHelper.ExecuteDataset(strSQLCon, CommandType.StoredProcedure, "GetEmpDetails", arrParameters);
                   DTEmpDet.Tables[0].TableName = "EmpDet";
                   if (DTEmpDet != null)
                   {
                       strXMLResult = DTEmpDet.GetXml();
                   }
                   else
                   {
                       strXMLResult = "<EStatus>NORECORD</EStatus>";
                   }
               }
               catch (Exception ex)
               {
                   strXMLResult = "<EStatus>ERROR</EStatus><EMsg>" + ex.Message.ToString() + "</EMsg>";
               }
               return strXMLResult;
           }
       }
   }

   Note: Here GetEmpDet,were you can create of ur own choice.Parameter "@EmployeeID"
   we are going to pass to retrieve the employee details.
   
   
3. In Solution Explorer,IService1.cs file which had been created to be clicked and start to code.
   
   Purpose:
   

   namespace WcfService1
   {
       // 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 GetEmpDet(string strSQLCon, string strEmpID);
           // TODO: Add your service operations here
       }
   }

4. Next step to code Service1.svc.
   
   purpose:
   

   namespace WcfService1
   {
       //     NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
       //     NOTE: In order to launch WCF Test Client for testing this service, please select Service1.svc or Service1.svc.cs at the Solution Explorer and start debugging.
       public class Service1: IService1
       {
           string strResult = string.Empty;
           EmpNDet objEmpNDet = new EmpNDet();
           public string GetEmpDet(string strSQLCon, string strEmpID)
           {
               strResult = objEmpNDet.GetEmpDet(strSQLCon, strEmpID);
               return strResult;
           }
       }
   }

   Note: strResult to get the result from database in XML format.

C# Windows form application

1. Design the form.
   
   
   
   
2. Code the form to get employee details.
   

   using System;
   using System.Collections.Generic;
   using System.ComponentModel;
   using System.Data;
   using System.Drawing;
   using System.Linq;
   using System.Text;
   using System.Threading.Tasks;
   using System.Windows.Forms;
   using System.Xml;
   
   namespace WindowsFormsApplication30
   {
       public partial class EmployeeDetails : Form
       {
           public string strErrorMsg;
           public string strXMLResult;
           public string strXML;
           public string IDText = "";
   
           XmlDocument objXMLDoc = new XmlDocument();
           DataSet objUWDataSet = new DataSet();
           WindowsFormsApplication30.WcfService1.Service1Client mywcfservice = new WindowsFormsApplication30.WcfService1.Service1Client();
           public EmployeeDetails()
           {
               InitializeComponent();
               modInsGlobal.strSQLCon = "Data Source = 192.168.2.105\\SQL2008; Initial Catalog = SWIC20140108; User ID = sasiemp; Password =1CjlEmtCDEk";
           }
   
           private void EmployeeDetails_Load(object sender, EventArgs e)
           {
               GetDetails();
   
           }
   
           private void GetDetails()
           {
               DataSet objDataSet = new DataSet();
               string strEmpID = IDText;
   
               try
               {
                   Cursor.Current = Cursors.WaitCursor;
                   strXML = mywcfservice.GetEmpDet(modInsGlobal.strSQLCon, strEmpID);
                   Cursor.Current = Cursors.Default;
   
                   if (strXMLResult == "NORECORD")
                   {
                       txtEmpID.Text = "";
                       txtFirstNam.Text = "";
                       txtSecondName.Text = "";
                       txtDOB.Text = "";
                       txtCity.Text = "";
                       txtCountry.Text = "";
                   }
                   else
                   {
                       objXMLDoc.LoadXml(strXML);
                       XmlReader objXMLReader = new XmlNodeReader(objXMLDoc);
                       objDataSet.ReadXml(objXMLReader);
                       txtEmpID.Text =IDText;
                       txtFirstNam.Text = objDataSet.Tables[0].Rows[0]["FirstName"].ToString();
                      txtSecondName.Text = objDataSet.Tables[0].Rows[0]["LastName"].ToString();
                       txtDOB.Text = objDataSet.Tables[0].Rows[0]["BirthDate"].ToString();
                        txtCity.Text  = objDataSet.Tables[0].Rows[0]["City"].ToString();
                       txtCountry.Text  = objDataSet.Tables[0].Rows[0]["Country"].ToString();
                   }
               }
               catch (Exception ex)
               {
                   return;
               }
           }
   
           private void btnDet_Click(object sender, EventArgs e)
           {
               IDText = txtEmpID.Text;
               GetDetails();
           }
   
           private void button1_Click(object sender, EventArgs e)
           {
               txtEmpID.Text = "";
               txtFirstNam.Text = "";
               txtSecondName.Text = "";
               txtDOB.Text = "";
               txtCity.Text = "";
               txtCountry.Text = "";
           }
       }
   }

3. Then we have to add the service.

   -Right click on the project Add->Service Reference.
   
   -Run the Web service project by choosing any browser say Internet Explorer.
   
   -You can see the local host.Click the Service1.svc and copy the url.
   
   svcutil.exe http://localhost:59765/Service1.svc?wsdl
   
   -Rigth click on the Service Reference and paste the url. Click Go And click the tree view to choose the method and click OK.
   

4. Finally u can run the project. Give the EmpID and click Details button and you can see the employee details of that employee ID.