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"

Insert the values to the table "Employee",
The Store Procedure for employee details is:
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.
   
   
   
   
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;
   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 = "";
   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. }
   30. private void EmployeeDetails_Load(object sender, EventArgs e)
   31. {
   32. GetDetails();
   34. }
   36. private void GetDetails()
   37. {
   38. DataSet objDataSet = new DataSet();
   39. string strEmpID = IDText;
   41. try
   42. {
   43. Cursor.Current = Cursors.WaitCursor;
   44. strXML = mywcfservice.GetEmpDet(modInsGlobal.strSQLCon, strEmpID);
   45. Cursor.Current = Cursors.Default;
   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. }
   75. private void btnDet_Click(object sender, EventArgs e)
   76. {
   77. IDText = txtEmpID.Text;
   78. GetDetails();
   79. }
   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.