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.

Create database EmpDetails
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

Open Microsoft Visual Studio and follow these steps.

File->New->Project->WCF->WCF Service Application->(rename the form if u want) OK.
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.
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. }
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

Design the form.
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. }
Then we have to add the service.
Finally u can run the project. Give the EmpID and click Details button and you can see the employee details of that employee ID.

6 Comments

Join the conversation! Your thoughts help the community grow.

Subash10y ago
Nice
Keerthi Venkatesan10y ago
yes, your right. This is one way where WCF services allow to access .
Ammar Shaukat10y ago
what are you trying to do here i could't get you ?
Ammar Shaukat10y ago
good job. @keerthi why do not use Entity framework ? it is very easy .
Thiruppathi R10y ago
great job
Sonu Chaudhary10y ago
good one