How to Create WCF Service to Retrieve Data From the Oracle Database

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

namespace COreDBWCFService
{

     [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        List<Employee> getEmployees();

        [OperationContract]
        Employee getEmployeesbyID(Int32 empId);
    }
    [DataContract]
    public class Employee
    {
        public Int32 _employee_ID;
        public string _firstName;
        public string _lastName;

        [DataMember]
        public Int32 Employee_ID
        {
            get { return _employee_ID; }
            set { _employee_ID = value; }
        }

        [DataMember]
        public string FirstName
        {
            get { return _firstName; }
            set { _firstName = value; }
        }
        [DataMember]
        public string LastName
        {
            get { return _lastName; }
            set { _lastName = value; }
        }
    }

}

vi. Open Service1.svc.cs and replace the code with the following.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.OracleClient;

namespace COreDBWCFService
{
    public class Service1 : IService1
    {
        public List<Employee> getEmployees()
        {
            List<Employee> employees = new List<Employee>();
            string connectionString = "Data Source=orcl;Persist Security Info=True;" +
                   "User ID=system;Password=password-1;Unicode=True";
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                OracleCommand command = connection.CreateCommand();
                string sql;
                sql = "SELECT * FROM employeedetails where rownum<=100000";
                command.CommandText = sql;
                OracleDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Employee employee = new Employee();
                    employee.Employee_ID = Convert.ToInt32(reader["Employee_ID"]);
                    employee.FirstName = Convert.ToString(reader["FirstName"]);
                    employee.LastName = Convert.ToString(reader["LastName"]);
                    employees.Add(employee);
                }
                return employees.ToList();
            }
        }
        public Employee getEmployeesbyID(Int32 empId)
        {
            Employee employees = new Employee();
            string connectionString = "Data Source=orcl;Persist Security Info=True;" +
                   "User ID=system;Password=password-1;Unicode=True";
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                OracleCommand command = connection.CreateCommand();
                string sql = "SELECT * FROM employeedetails where employee_id=" + empId;
                command.CommandText = sql;
                OracleDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    employees.Employee_ID = empId;
                    employees.FirstName = Convert.ToString(reader["FirstName"]);
                    employees.LastName = Convert.ToString(reader["LastName"]);
                }
            }
            return employees;
        }
    }
}

vii. In the web.config change the binding to basicHttpBinding.

<services>
      <service name="COreDBWCFService.Service1" behaviorConfiguration="COreDBWCFService.Service1Behavior">
        <endpoint address="" binding="basicHttpBinding" contract="COreDBWCFService.IService1">
          <identity>
            <dns value="localhost"/>
          </identity>
        </endpoint>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
      </service>
</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.