In this article, we will be seeing how to create WCF service to retrieve data from the Oracle database.
In the Oracle database I have a table employeedetails which has three columns Employee_ID, FirstName and LastName. In this we are going to create two methods getEmployees() and getEmployeesbyID(). The getEmployees() method is used to retrieve all the employees from employeedetails table and getEmployeesbyID() is used to retrieve the employees based on the Employee_ID.
Creating WCF service using Visual Studio 2010
I. Open Visual Studio 2010.
ii. Go to File => New => Project.
iii. Select WCF Service Application from the installed templates WCF.
iv. Enter the Name and click OK.
v. Open IService.cs file and replace the code with the following.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.ServiceModel.Web;
  7. using System.Text;
  9. namespace COreDBWCFService
  10. {
  12. [ServiceContract]
  13. public interface IService1
  14. {
  15. [OperationContract]
  16. List<Employee> getEmployees();
  18. [OperationContract]
  19. Employee getEmployeesbyID(Int32 empId);
  20. }
  21. [DataContract]
  22. public class Employee
  23. {
  24. public Int32 _employee_ID;
  25. public string _firstName;
  26. public string _lastName;
  28. [DataMember]
  29. public Int32 Employee_ID
  30. {
  31. get { return _employee_ID; }
  32. set { _employee_ID = value; }
  33. }
  35. [DataMember]
  36. public string FirstName
  37. {
  38. get { return _firstName; }
  39. set { _firstName = value; }
  40. }
  41. [DataMember]
  42. public string LastName
  43. {
  44. get { return _lastName; }
  45. set { _lastName = value; }
  46. }
  47. }
  49. }
vi. Open Service1.svc.cs and replace the code with the following.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.ServiceModel.Web;
  7. using System.Text;
  8. using System.Data.OracleClient;
  10. namespace COreDBWCFService
  11. {
  12. public class Service1 : IService1
  13. {
  14. public List<Employee> getEmployees()
  15. {
  16. List<Employee> employees = new List<Employee>();
  17. string connectionString = "Data Source=orcl;Persist Security Info=True;" +
  18. "User ID=system;Password=password-1;Unicode=True";
  19. using (OracleConnection connection = new OracleConnection())
  20. {
  21. connection.ConnectionString = connectionString;
  22. connection.Open();
  23. OracleCommand command = connection.CreateCommand();
  24. string sql;
  25. sql = "SELECT * FROM employeedetails where rownum<=100000";
  26. command.CommandText = sql;
  27. OracleDataReader reader = command.ExecuteReader();
  28. while (reader.Read())
  29. {
  30. Employee employee = new Employee();
  31. employee.Employee_ID = Convert.ToInt32(reader["Employee_ID"]);
  32. employee.FirstName = Convert.ToString(reader["FirstName"]);
  33. employee.LastName = Convert.ToString(reader["LastName"]);
  34. employees.Add(employee);
  35. }
  36. return employees.ToList();
  37. }
  38. }
  39. public Employee getEmployeesbyID(Int32 empId)
  40. {
  41. Employee employees = new Employee();
  42. string connectionString = "Data Source=orcl;Persist Security Info=True;" +
  43. "User ID=system;Password=password-1;Unicode=True";
  44. using (OracleConnection connection = new OracleConnection())
  45. {
  46. connection.ConnectionString = connectionString;
  47. connection.Open();
  48. OracleCommand command = connection.CreateCommand();
  49. string sql = "SELECT * FROM employeedetails where employee_id=" + empId;
  50. command.CommandText = sql;
  51. OracleDataReader reader = command.ExecuteReader();
  52. while (reader.Read())
  53. {
  54. employees.Employee_ID = empId;
  55. employees.FirstName = Convert.ToString(reader["FirstName"]);
  56. employees.LastName = Convert.ToString(reader["LastName"]);
  57. }
  58. }
  59. return employees;
  60. }
  61. }
  62. }
vii. In the web.config change the binding to basicHttpBinding.
  1. <services>
  2. <service name="COreDBWCFService.Service1" behaviorConfiguration="COreDBWCFService.Service1Behavior">
  3. <endpoint address="" binding="basicHttpBinding" contract="COreDBWCFService.IService1">
  4. <identity>
  5. <dns value="localhost"/>
  6. </identity>
  7. </endpoint>
  8. <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
  9. </service>
  10. </services>
viii. Build and save the solution.
Summary
Thus the WCF service has been created to retrieve the data from the Oracle database. For hosting and testing the WCF Service check the following link.