Create a linked Server to Oracle View in MS SQL Server

Install the Oracle Client 

  1. Download the Oracle client 11gR2 (32/64 bit depends on OS) (Download)

  2. Install Oracle Client

    - Run the Setup.exe as Administrator, it will open the command prompt, wait until it finishes, (Normally it takes long time to finish)

            

    - Select Installation Type - Choose Administrator

- Select Product Languages 

- Specify Installation Location - leave it as default and note down the "Software location" - need to   refer that location later

- Check the summary and click "Finish".

  - Check the Installation Status

- Close the installation wizard once the installation is successful.

  3.  Modify the "tnsnames.oRA" file

      

    - Go the that Installation path (C:\app\User\product\11.1.0\client_4\network\admin\sample) 

    -  Copy (tnsnames.oRA )file in to the AdminFolder (C:\app\User\product\11.1.0\client_2 \network\admin)  

    -  Open "tnsnames.oRA" file , take the backup of that file before edit.

    -  Delete all except comments, and paste below entry

          

      VIRVHRMS =(DESCRIPTION =(ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = ###)(PORT = ###))) (CONNECT_DATA =(SERVICE_NAME = ###)))

•  VIRVHRMS - Data source Name (Give any name - you have to give this name in Oracle data connection)  
• You have to change some parameters according to your environment
• Host - Database Name
• PORT - Port Number
• SERVICE_NAME - SID 

- Finally Save that file 

  Create Linked Server with Oracle View in SQL Server 

  before create the Linked Server, enable the "Allow inprocess".

   Go to OraOLEDB.Oracle 's properties

  Enable "Allow in process"

   

OK, Now we can create a link server to Oracle View,

   1. Create New Linked Server

       

   2.Type the Name for Linked Server (PRODVIEW) and select "Other data source" for Server Type.

   

• Linked Server: “PRODVIEW”.  – Just a name 
• Provider: "Oracle Provider for OLE DB" 
• Product Name: “Oracle” 
• Data Source: “VIRVHRMS”   - The same entry in tnsname.ora file

3. Go to Security tab , and Select "Be made using this security context" and give the remote login  and password and click "OK".

Simple Steps to create the WCF Service

  Before creating WCF Service, you should know the basic understanding about following things,
   1.  ServiceContract 
   2. OperationContract
   3. DataContract
  Creating WCF Service consists 5 main parts, such as,

  a. Create an interface (ServiceContract) with all abstract class(OperationContract)
   b. Create Data Contract with Data Member

   c.  Implement that interface by concrete class

   d. Hosting part of WCF Services
   e. Test the WCF Service with WCF Test Client tool.

   

   Ok, We will look how to do above steps using Visual Studio 2012.
   
  1. Open Visual Studio 2012 as Administrator. and Create new project

  

  2. Select the WCF Service Library as Project template and name it as "CoreService". change the Location if you want. then click "OK".
      

    

  3.   Rename the IService1.cs to IEmployeeService.cs. delete all the code, and paste below code and add the required dlls.
      using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace CoreService
{
   [ServiceContract]
    public interface IEmployeeService
    {
        [OperationContract]
        Employee GetEmployeeByLoginName(string LoginName);

        [OperationContract]
        List<Employee> GetEmployees();   
    }



       [DataContract]
    public class Employee     
       {
           private string employeeId;
        [DataMember]
        public string EmployeeId        
           {
            get { return employeeId; }
            set { employeeId= value; }
        }

           private string employeeName;
        [DataMember]
        public string EmployeeName       
           {
            get { return employeeName; }
                        set { employeeName= value; }
                }

           private string department;
        [DataMember]
        public string Department
           {
            get { return department;}
                        set   { department= value; }
                }


    }
 } 
 here Service contract and data contract has been added.  

 4. Implement above interface, Rename "Service1.cs" to EmployeeService.cs.
       delete all code and paste below code,

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

namespace CoreService
{
     

//Applied to a service to indicate whether that service can be run in ASP.NET compatibility code.
    

      [AspNetCompatibilityRequirements(RequirementsMode =  AspNetCompatibilityRequirementsMode.Allowed)]
   
    public class EmployeeService : IEmployeeService
    {
        public Employee GetEmployeeByLoginName(string loginName)
        {
            //write your logic here , return the Employee Object
        }

        public List<Employee> GetEmployees()
        {
            //write your logic here , return the Employee Object List      
        }
    }
}
  
 5. Now we have created service and it is the time to host that service in IIS. first create WCF Service Application using Visual studio 2012. (New Project --> WCF --> WCF Service Application)
     

    
    Name it as "EmployeeServiceHost" and change the Location if you really want.
  
  6. Add the service project(CoreService) as reference to Hosting Project (EmployeeServiceHost) 

  6. Delete "IService1.cs"  and "Service1.svc.cs" files , those are unnecessary files for hosting.
     

   7. Open "Service1.svc" and delete Code behind part (CodeBehind="Service1.svc.cs").
       Service part has to be changed correctly, first part is namespace of Concrete class and last part is concrete class name (Service="CoreService.EmployeeService" )

  8. Modify the web.config  file of Hosting Project (EmployeeServiceHost ). before that we have to understand about following things,

      Every services can have more than one end points  so that different connection types will be supported for that service. every service must have a name , which is the concrete class name of WCF Service (CoreService.EmployeeService). if it is required , add behaviorConfiguration property for that service.

      Every end point has some important parameters, like "address" , "binding" and "contract". so change it based on your settings.

•      address - optional - can keep it as empty

•       binding - binding method - don't change the default  value  if the WCF is not JSON or JSNOP ("webHttpBinding" for JSON),

•       Contract - refer the Service Contract ("CoreService.IEmployeeService" )

     other than these 3 main parameters , there are 2 more optional parameter, like behaviorConfiguration and bindingConfiguration. when you want to return as JSON or JSONP you will have to specify those values. (Read here for JSON enabled WCF)

   Sample Web.config (for very simple WCF Service)

 

 <services>

    <service name="CoreService.EmployeeService">

      <endpoint address ="" binding="wsHttpBinding" contract="CoreService.IEmployeeService"/>   

      // other end points      

    </service>

   // other services

  </services>

  before publish the service into IIS , we have to make sure that Our Service works properly. so view the Service1.svc in browser . 

 if the service works properly , brower look like,

  if any errors occurs , solve it before publish into IIS.

  9. Now we are ready to publish the Service into IIS. right click in the Hosting Project and click "publish".

     Select the Profile . (Create new profile , if you don't have any),

    
    click Next...

    

    Select the "File System" as Publish Method. then Select the Target Location. Destination URL is optional. click Next...

  
    Select "Debug" as Configuration mode, then click Next...

  

     Check the parameters again before publish into IIS. once you check it, click "Publish".

  10. Open IIS Manager and Create new site for our WCF Service (Click here if you want to know how to create web site in IIS).

  11. Check whether the WCF Service is working or not by using WCF Test Client tool (How to Use WCF Test Client)