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"

  1. Create table Employee  
  2. (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",
  1. insert into Employee values (1126146,'Rajivi','Lathika','5/29/1995','Chennai','India');  
  2. insert into Employee values(1126147,'Keerthi','Venkatesan','3/27/1993','Chennai','India');etc;,  
The Store Procedure for employee details is:
  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. -- =============================================  
  6. -- Author:      <Author,,Name>  
  7. -- Create date: <Create Date,,>  
  8. -- Description: <Description,,>  
  9. -- =============================================  
  10. Alter PROCEDURE GetEmpDetails   
  11.     -- Add the parameters for the stored procedure here  
  12.           @EmployeeID int=0  
  13. AS  
  14. BEGIN  
  15.     -- SET NOCOUNT ON added to prevent extra result sets from  
  16.     -- interfering with SELECT statements.  
  17.     SET NOCOUNT ON;  
  18.          SELECT FirstName, LastName, convert(char(10),BirthDate,101)BirthDate, City, Country  
  19.       FROM Employee WHERE EmployeeID=@EmployeeID  
  20.   
  21.   
  22.     -- Insert statements for procedure here  
  23.     --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>  
  24. END  
  25. 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.
    1. namespace WcfService1  
    2. {  
    3.     public class EmpNDet  
    4.     {  
    5.         public string GetEmpDet(string strSQLCon, string strEmpID)  
    6.         {  
    7.             string strXMLResult = string.Empty;  
    8.             DataSet DTEmpDet = new DataSet();  
    9.             try  
    10.             {  
    11.                 var arrParameters = new SqlParameter[1];  
    12.                 arrParameters[0] = new SqlParameter("@EmployeeID", SqlDbType.VarChar, 18);  
    13.                 arrParameters[0].Value = strEmpID;  
    14.                 DTEmpDet = SqlHelper.ExecuteDataset(strSQLCon, CommandType.StoredProcedure, "GetEmpDetails", arrParameters);  
    15.                 DTEmpDet.Tables[0].TableName = "EmpDet";  
    16.                 if (DTEmpDet != null)  
    17.                 {  
    18.                     strXMLResult = DTEmpDet.GetXml();  
    19.                 }  
    20.                 else  
    21.                 {  
    22.                     strXMLResult = "<EStatus>NORECORD</EStatus>";  
    23.                 }  
    24.             }  
    25.             catch (Exception ex)  
    26.             {  
    27.                 strXMLResult = "<EStatus>ERROR</EStatus><EMsg>" + ex.Message.ToString() + "</EMsg>";  
    28.             }  
    29.             return strXMLResult;  
    30.         }  
    31.     }  
    32. }  
    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:

    1. namespace WcfService1  
    2. {  
    3.     // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.  
    4.     [ServiceContract]  
    5.     public interface IService1  
    6.     {  
    7.         [OperationContract]  
    8.         string GetEmpDet(string strSQLCon, string strEmpID);  
    9.         // TODO: Add your service operations here  
    10.     }  
    11. }  
  4. Next step to code Service1.svc.

    purpose:

    1. namespace WcfService1  
    2. {  
    3.     //     NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.  
    4.     //     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.  
    5.     public class Service1: IService1  
    6.     {  
    7.         string strResult = string.Empty;  
    8.         EmpNDet objEmpNDet = new EmpNDet();  
    9.         public string GetEmpDet(string strSQLCon, string strEmpID)  
    10.         {  
    11.             strResult = objEmpNDet.GetEmpDet(strSQLCon, strEmpID);  
    12.             return strResult;  
    13.         }  
    14.     }  
    15. }  
    Note: strResult to get the result from database in XML format.

C# Windows form application

  1. Design the form.

    form

  2. Code the form to get employee details.
    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.Threading.Tasks;  
    9. using System.Windows.Forms;  
    10. using System.Xml;  
    11.   
    12. namespace WindowsFormsApplication30  
    13. {  
    14.     public partial class EmployeeDetails : Form  
    15.     {  
    16.         public string strErrorMsg;  
    17.         public string strXMLResult;  
    18.         public string strXML;  
    19.         public string IDText = "";  
    20.   
    21.         XmlDocument objXMLDoc = new XmlDocument();  
    22.         DataSet objUWDataSet = new DataSet();  
    23.         WindowsFormsApplication30.WcfService1.Service1Client mywcfservice = new WindowsFormsApplication30.WcfService1.Service1Client();  
    24.         public EmployeeDetails()  
    25.         {  
    26.             InitializeComponent();  
    27.             modInsGlobal.strSQLCon = "Data Source = 192.168.2.105\\SQL2008; Initial Catalog = SWIC20140108; User ID = sasiemp; Password =1CjlEmtCDEk";  
    28.         }  
    29.   
    30.         private void EmployeeDetails_Load(object sender, EventArgs e)  
    31.         {  
    32.             GetDetails();  
    33.   
    34.         }  
    35.   
    36.         private void GetDetails()  
    37.         {  
    38.             DataSet objDataSet = new DataSet();  
    39.             string strEmpID = IDText;  
    40.   
    41.             try  
    42.             {  
    43.                 Cursor.Current = Cursors.WaitCursor;  
    44.                 strXML = mywcfservice.GetEmpDet(modInsGlobal.strSQLCon, strEmpID);  
    45.                 Cursor.Current = Cursors.Default;  
    46.   
    47.                 if (strXMLResult == "NORECORD")  
    48.                 {  
    49.                     txtEmpID.Text = "";  
    50.                     txtFirstNam.Text = "";  
    51.                     txtSecondName.Text = "";  
    52.                     txtDOB.Text = "";  
    53.                     txtCity.Text = "";  
    54.                     txtCountry.Text = "";  
    55.                 }  
    56.                 else  
    57.                 {  
    58.                     objXMLDoc.LoadXml(strXML);  
    59.                     XmlReader objXMLReader = new XmlNodeReader(objXMLDoc);  
    60.                     objDataSet.ReadXml(objXMLReader);  
    61.                     txtEmpID.Text =IDText;  
    62.                     txtFirstNam.Text = objDataSet.Tables[0].Rows[0]["FirstName"].ToString();  
    63.                    txtSecondName.Text = objDataSet.Tables[0].Rows[0]["LastName"].ToString();  
    64.                     txtDOB.Text = objDataSet.Tables[0].Rows[0]["BirthDate"].ToString();  
    65.                      txtCity.Text  = objDataSet.Tables[0].Rows[0]["City"].ToString();  
    66.                     txtCountry.Text  = objDataSet.Tables[0].Rows[0]["Country"].ToString();     
    67.                 }  
    68.             }  
    69.             catch (Exception ex)  
    70.             {  
    71.                 return;  
    72.             }  
    73.         }  
    74.   
    75.         private void btnDet_Click(object sender, EventArgs e)  
    76.         {  
    77.             IDText = txtEmpID.Text;  
    78.             GetDetails();  
    79.         }  
    80.   
    81.         private void button1_Click(object sender, EventArgs e)  
    82.         {  
    83.             txtEmpID.Text = "";  
    84.             txtFirstNam.Text = "";  
    85.             txtSecondName.Text = "";  
    86.             txtDOB.Text = "";  
    87.             txtCity.Text = "";  
    88.             txtCountry.Text = "";  
    89.         }  
    90.     }  
    91. }  
  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.