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.
Anjali ChavanPosted Aug 17, 2022, 7:18 AM
Can I connect a WCF service to Oracle 10g database?
Kumaresh RajalingamPosted Mar 24, 2016, 9:59 AM
Nice one sir
anil babuPosted Jun 17, 2013, 6:10 AM
Hi, This is my table CustId(PK) ID Email PhoneNO 1 101 [email protected] 22222222 2 201 [email protected] 33333333 3 301 [email protected] 44444444 I want Create wcf service to take tag ID email/phone number and send back CustId How can i do this? Pls give the code for sample for this
MichellePosted Jul 13, 2011, 3:49 PM
Hi Vijai, My web config file has ------------------------------------ <?xml version="1.0"?> <configuration> <system.web> <compilation debug="true" targetFramework="4.0" /> </system.web> <system.serviceModel> <behaviors> <serviceBehaviors> <behavior> <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment --> <serviceMetadata httpGetEnabled="true"/> <!-- To receive exception details in faults for debugging purposes, set the value below to true. Set to false before deployment to avoid disclosing exception information --> <serviceDebug includeExceptionDetailInFaults="false"/> </behavior> </serviceBehaviors> </behaviors> <serviceHostingEnvironment multipleSiteBindingsEnabled="true" /> </system.serviceModel> <system.webServer> <modules runAllManagedModulesForAllRequests="true"/> </system.webServer> </configuration> ------------------------------------- where in there would I put your web config code as shown in this blog on WCF?
aatka aliPosted Apr 12, 2011, 7:08 AM
I want to create a WCF Service that saves data into the Table of Database