ExecuteReader, ExecuteScalar, or ExecuteNonQuery operations in Oracle E-Business Suite using the WCF service model

Article
02/02/2021

The Oracle E-Business adapter exposes generic operations such as ExecuteNonQuery, ExecuteReader, and ExecuteScalar. You can use these operations to execute any statement on Oracle E-Business Suite. These operations differ based on the kind of response you get for the statement. For more information about how the adapter supports these operations, see Support for ExecuteNonQuery, ExecuteReader, and ExecuteScalar Operations.

This topic demonstrates how to perform an ExecuteReader operation using the Oracle E-Business adapter using the WCF service model. You can follow the same set of procedures described in this topic to perform ExecuteNonQuery and ExecuteScalar operations.

About the Examples Used in this Topic

The example in this topic performs an ExecuteReader operation to perform a SELECT operation on the MS_SAMPLE_EMPLOYEE interface table. The table is created by running the script provided with the samples. For more information about samples, see Samples for the Oracle EBS adapter. A sample, ExecuteReader, which is based on this topic, is also provided with the Oracle E-Business adapter samples.

The WCF Client Class

The name of the WCF client generated for invoking generic operations (ExecuteNonQuery, ExecuteReader, or ExecuteScalar) using the Oracle E-Business adapter is listed in the following table.

Operations	WCF Client Name
ExecuteNonQuery, ExecuteReader, or ExecuteScalar	GenericOperation_Client

Method Signature for Invoking Generic Operations

The following table shows the signature for the method exposed to invoke the generic operations.

Operation	Method Signature
ExecuteNonQuery	int ExecuteNonQuery(string Query, string[] OutputRefCursorNames, out System.Data.DataSet[] OutputRefCursors)
ExecuteReader	System.Data.DataSet ExecuteReader(string Query)
ExecuteScalar	string ExecuteScalar(string Query)

As an example, the signature for the generic operation methods is shown in the following code snippet.

public partial class GenericOperation_Client : System.ServiceModel.ClientBase<GenericOperation_>, GenericOperation_ {  
  public int ExecuteNonQuery(string Query, string[] OutputRefCursorNames, out System.Data.DataSet[] OutputRefCursors);  
  public System.Data.DataSet ExecuteReader(string Query);  
  public string ExecuteScalar(string Query);  
}

In this snippet,

GenericOperation_Client is the name of the class. This class is used to create a client to invoke the generic operation, ExecuteReader.
public System.Data.DataSet ExecuteReader(string Query) is the method that you invoke to perform a SELECT statement on the MS_SAMPLE_EMPLOYEE interface table.

Creating a WCF Client to Invoke an ExecuteReader Operation

The generic set of actions required to perform an operation on Oracle E-Business Suite using a WCF client involves a set of tasks described in Overview of the WCF service model with the Oracle E-Business Suite adapter. This section describes how to create a WCF client to invoke the ExecuteReader operation.

To create a WCF client to invoke ExecuteReader operation

Create a Visual C# project in Visual Studio. For this topic, create a console application.
Generate the WCF client class for the ExecuteReader generic operation. This operation is available under the root node when you connect to the Oracle E-Business Suite using the Add Adapter Service Reference Plug-in. For more information about generating a WCF client class, see Generate a WCF client or a WCF service contract for Oracle E-Business Suite solution artifacts.

Important

Before generating the WCF client class, make sure you set the EnableBizTalkCompatibilityMode binding property to false.
In the Solution Explorer, add reference to Microsoft.Adapters.OracleEBS and Microsoft.ServiceModel.Channels.
Open the Program.cs file and add the following namespaces:
- Microsoft.Adapters.OracleEBS
- System.ServiceModel
In the Program.cs file, create a client as described in the snippet below.
```
OracleEBSBinding binding = new OracleEBSBinding();  
EndpointAddress address = new EndpointAddress("oracleebs://ebs-72-11");  
GenericOperation_Client client = new GenericOperation_Client(binding, address);  
```
In this snippet, GenericOperation_Client is the WCF client defined in OracleEBSBindingClient.cs. This file is generated by the Add Adapter Service Reference Plug-in.

Note

In this snippet, you explicitly specify the binding and endpoint address in your application code. You can use these values from the application configuration file, app.config, also generated by the Add Adapter Service Reference Plug-in. For more information about the different ways of specifying client binding, see Configure a client binding for the Oracle E-Business Suite.

Set the credentials for the client.

client.ClientCredentials.UserName.UserName = "myuser";  
client.ClientCredentials.UserName.Password = "mypassword";

Because you are performing an operation on an interface table, you must set the application context. In this example, to set the application context, you specify the OracleUserName, OraclePassword, and OracleEBSResponsibilityName binding properties. For more information about application context, see Set application context.
```
binding.OracleUserName = "myOracleEBSUserName";  
binding.OraclePassword = "myOracleEBSPassword";  
binding.OracleEBSResponsibilityName = "myOracleEBSResponsibility";  
```

Open the client as described in the snippet below:

try  
{  
   Console.WriteLine("Opening Client...");  
   client.Open();  
}  
catch (Exception ex)  
{  
   Console.WriteLine("Exception: " + ex.Message);  
   throw;  
}

Invoke the ExecuteReader operation for performing the SELECT operation on MS_SAMPLE_EMPLOYEE table. Before you invoke the ExecuteReader operation, you must add the System.Data namespace to your code.

string query = "SELECT * FROM MS_SAMPLE_EMPLOYEE";  
DataSet ds = client.ExecuteReader(query);  

Console.WriteLine("Invoking the SELECT statement using ExecuteReader");  
Console.WriteLine("*****************************************************");  
foreach (DataTable tab in ds.Tables)  
{  
   foreach (DataRow row in tab.Rows)  
   {  
      Console.WriteLine("The details of the employee are: ");  
      for (int i = 0; i < tab.Columns.Count; i++)  
      {  
         Console.WriteLine(row[i]);  
      }  
      Console.WriteLine();  
   }  
}  
Console.WriteLine("*****************************************************");  
Console.ReadLine();

Close the client as described in the snippet below:

client.Close();  
Console.WriteLine("Press any key to exit...");  
Console.ReadLine();

Build the project and then run it. All the records in the MS_SAMPLE_EMPLOYEE table are displayed on the console.

Share via