This eighth article in a series about BizTalk Server 2006 R2 and Windows Communication Foundation continues a focus on the new BizTalk Adapter Pack. Specifically, we will look at how to consume the Microsoft BizTalk Adapter 3.0 for Oracle Database by using the WCF service model.
This article highlights four scenarios:
- Inserting multiple records at once into an Oracle table
- Calling an Oracle stored procedure using both weak and strong ref cursors
- Creating a WCF polling host that pulls data from an Oracle table at regular intervals
- Wrapping the Oracle adapter’s operations in a RESTful HTTP interface
Scenario #1 – Inserting Multiple Oracle Table Records
This first scenario looks at how to insert an array of records into an Oracle database. A standard .NET class library was created to hold all of the type classes generated by the BizTalk Adapter Pack Oracle adapter. Next, the Oracle database was browsed by right-clicking the Visual Studio.NET project, and choosing Add Adapter Service Reference. This opened the BizTalk Adapter Pack browsing wizard. Once valid credentials were entered, and the correct data source was chosen (using a value from the Oracle tnsnames.ora file), a connection to the Oracle database was established. In the screenshot below, notice that “Insert” operations were selected for both the “Orders” and “OrderItems” table.
When the wizard was closed, two new files were added to the Visual Studio.NET project. First, a class was generated which contained the type definitions and proxy classes used to call the adapter service. Secondly, an application configuration file containing the necessary Oracle adapter binding information was generated.
This scenario was tested using a C# Windows Form application. After adding a reference to System.ServiceModel in the Visual Studio.NET project, the auto-generated application configuration file from the class library project was copied into this WinForm project.
Next, the necessary code was added to insert a record into the “Orders” table, and insert multiple corresponding records into the “OrderItems” table.
//INSERT ORDER //binding name from auto-generated configuration using (BIZTALK_DEVTableORDERSClient orderClient = new BIZTALK_DEVTableORDERSClient("OracleDBBinding_BIZTALK_DEV.Table.ORDERS")) { //set credentials orderClient.ClientCredentials.UserName.UserName = "BIZTALK_DEV"; orderClient.ClientCredentials.UserName.Password = "xxxxx"; //create order object array ORDERSRECORDINSERT[] orders = new ORDERSRECORDINSERT[1]; ORDERSRECORDINSERT order = new ORDERSRECORDINSERT(); order.CUSTOMERID = "2"; order.ORDERDATE = "2008-05-02"; order.ORDERID = "34"; order.ORDERSTATUS = "SUBMITTED"; order.ORDERSUM = "59.99"; orders[0] = order; //second and third parameters are for bulk inserts, not multiple record inserts orderClient.Insert(orders, null, null); } //INSERT ORDER ITEMS //use order item binding configuration using (BIZTALK_DEVTableORDERITEMSClient itemClient = new BIZTALK_DEVTableORDERITEMSClient("OracleDBBinding_BIZTALK_DEV.Table.ORDERITEMS")) { //set credentials itemClient.ClientCredentials.UserName.UserName = "BIZTALK_DEV"; itemClient.ClientCredentials.UserName.Password = "xxxxx"; //create multiple order item objects ORDERITEMSRECORDINSERT[] items = new ORDERITEMSRECORDINSERT[2]; ORDERITEMSRECORDINSERT item1 = new ORDERITEMSRECORDINSERT(); ORDERITEMSRECORDINSERT item2 = new ORDERITEMSRECORDINSERT(); //item1 item1.ORDERID = "34"; item1.ORDERITEMID = "55"; item1.ITEMCODE = "129"; item1.QUANTITY = "15"; item1.PURCHASEPRICE = "9.99"; //item2 item2.ORDERID = "34"; item2.ORDERITEMID = "56"; item2.ITEMCODE = "131"; item2.QUANTITY = "8"; item2.PURCHASEPRICE = "25.99"; items[0] = item1; items[1] = item2; //call Oracle adapter service itemClient.Insert(items, null, null); }
Once compiled and tested, the expected order and order items successfully appeared in the Oracle database tables. Note that there is a difference between using the “Insert” operation to do a multiple record insert or a bulk record insert. The multiple record insert, seem in the examples above, takes the array of objects and adds the records to the database table. For a bulk record insert, the first parameter of the “Insert” operation is null and the second and third parameters are used to identify which table to pull bulk records from.
Scenario #2 – Consuming Oracle Stored Procedures
For this scenario, two stored procedure were created to demonstrate using a weak ref cursor and a strong ref cursor and how the Microsoft BizTalk Adapter 3.0 for Oracle Database consumes each procedure differently. The goal of these procedures was to return a list of all order items that a particular customer had bought. Because the “Order Items” table does not have a foreign key on the “Customers” table (only the “Orders” table), a table join operation is necessary.
The first stored procedure returned a SYS_REFCURSOR as an output parameter. This was how the resulting recordset is returned from the procedure.
--returns weak ref cursor PROCEDURE "BIZTALK_DEV".GETORDERSFORCUSTOMER ( CUSTID IN NUMBER, O_CURSOR OUT SYS_REFCURSOR) IS BEGIN OPEN O_CURSOR FOR --join tables to get list of all items that a customer ordered SELECT oi.ITEMCODE, oi.QUANTITY, oi.PURCHASEPRICE FROM ORDERITEMS oi JOIN ORDERS o ON o.ORDERID = oi.ORDERID WHERE o.CUSTOMERID = CUSTID; END GETORDERSFORCUSTOMER;
The second procedure needed to return a strongly typed result set. To do that, a cursor “type” was defined in an Oracle package. This type references a database view (“ORDERORDERITEMS”) which joined the “ORDERITEMS” and “ORDERS” table. So, the cursor should have the structure defined in that view.
PACKAGE "BIZTALK_DEV"."ORDERITEMCUR_PKG" IS TYPE OITEM_CURTYPE IS REF CURSOR RETURN ORDERORDERITEMS%ROWTYPE; END "ORDERITEMCUR_PKG";
Now, the procedure using the strong ref cursor looked like this:
PROCEDURE "BIZTALK_DEV".GETORDERSFORCUSTOMERSTRONG ( CUSTID IN NUMBER, OITEM OUT ORDERITEMCUR_PKG.OITEM_CURTYPE) IS --returns strongly typed cursor BEGIN OPEN OITEM FOR --queries view that joined the tables SELECT * FROM ORDERORDERITEMS WHERE CUSTOMERID = CUSTID; END GETORDERSFORCUSTOMERSTRONG;
Now that the appropriate Oracle database artifacts were created, the Add Adapter Service Reference Visual Studio.NET command was selected so that the procedure metadata could be generated. As shown in the screenshot below, both stored procedures were selected.
In the Windows Form project created for Scenario #1, a new bit of code was added to call each of the procedures. After ensuring that the WinForm project had the latest edition of the auto-generated application configuration file (which now held the Oracle binding configuration for the procedures), the following code was added:
//uses binding from configuration file using (BIZTALK_DEVProcedureClient procClient = new BIZTALK_DEVProcedureClient("OracleDBBinding_BIZTALK_DEV.Procedure")) { //set credentials procClient.ClientCredentials.UserName.UserName = "BIZTALK_DEV"; procClient.ClientCredentials.UserName.Password = "xxxxx"; //return generic row object from adapter namespace GenRecordRow[] weakResult = procClient.GETORDERSFORCUSTOMER("2"); //pull out ItemCode value using column index string itemCode = weakResult[0].GenRecordColumn[0].ColumnValue; }
Executing this procedure and viewing the Visual Studio.NET debugger showed the value residing in the “GenRecordColumn[0]” position.
How about executing the stored procedure that utilized a strongly typed ref cursor? In that case, the procedure returns a typed “OITEMRECORD” array which is aware of the columns in the database view. In the screenshot below, notice the well defined properties of the result set.
Scenario #3 – Working with Polling-based Solutions
One powerful feature of the BizTalk adapters is the ability to poll a database source and trigger events based off of the new data found. Now, this capability is part of the BizTalk Adapter Pack. This scenario shows how one can perform polling operations using the Microsoft BizTalk Adapter 3.0 for Oracle Database.
Once again, the Visual Studio.NET class library project (which holds all of the generated types) was right-clicked and the Add Adapter Service Reference option was chosen. This time, instead of only entering the database credentials and data source on the connection setup, the Polling Statement value was populated. In this scenario, all of the columns from the “Orders” table were retrieved. Note that there is a PostPollStatement value which executes after the polling statement completes. In a real-world scenario, the PostPollStatement would set a flag on the orders that were retrieved so that they were not retrieved AGAIN during the next polling action.
Optionally, one can also set the “PollingId” which simply adds an extension to the default namespace used for polling.
Once the connection details were defined, and a connection achieved, the “contract type” was switched from “Client (outbound operation)” to “Service (inbound operation).” This is because instead of building a client for the Oracle adapter, the polling solution requires the developer to build a host service. Once the contract type is changed, a single operation, POLLIGNSTMT is available for selection.
After the wizard is completed, three files were generated. This includes two class files (containing the service contract and service implementation stub) and an updated application configuration that included new binding configurations and service endpoints. Note that the generated “type” classes are strongly typed. That is, the polling statement entered in the connection details was used to build a typed class that matched the column structure returned.
Next, a new WCF service class was added to the previously built (client) Windows Form project. Now, the WinForm application will act as a service host instead of a service client.
A new service was defined, which inherited the auto-generated OracleDBBindingService stub. The service must be defined as a singleton using the InstanceContextMode.Single service attribute. The operation within this service will be called whenever new records are found during polling. So, the virtual operation in the base service was overridden so that custom logic could be applied for new polled orders.
In this scenario, when new orders are polled, the new orders are written to a log file. As mentioned above, the “post poll” logic was not applied, so each polling interval will retrieve ALL records in the “Order” table. The operation code is as follows:
[ServiceBehavior(InstanceContextMode=InstanceContextMode.Single)] public class OrderPollingService : OracleDBBindingService { //override virtual operation in base public override POLLINGSTMTResponse POLLINGSTMT(POLLINGSTMTRequest request) { //did any records come in from the polling statement? if(request.POLLINGSTMTRECORD.Length > 0) { //open up file for writing StreamWriter sw = File.AppendText(@"C:\WCFTrace.txt"); sw.WriteLine("--------- new orders found ----------"); //loop through (strongly typed) order records foreach (POLLINGSTMTRECORD order in request.POLLINGSTMTRECORD) { sw.WriteLine("customer: " + order.CUSTOMERID + "order ID: " + order.ORDERID + "order status: " + order.ORDERSTATUS); } sw.Close(); } //return response indicating that process succeeded POLLINGSTMTResponse response = new POLLINGSTMTResponse(); return response; } }
Next, the service host (in the Winform application) needed to be configured. The wizard-generated bits contained a service host stub that could be modified for this particular service. In this case, the “StartService” operation was modified to create an instance of the service, set the base URI using the appropriate data source, and the host object instantiated and started.
Once the wizard-generated application configuration was copied to the WinForm project, a couple changes were necessary. First, the Oracle adapter binding configuration needed to have its AcceptCredentialsInURI setting changed to “true.” Next, the service name had to be changed to reflect the fact that a new WCF service class has overridden the base service class. The modified service configuration looked like this:
After starting the service host, the first polling execution retrieved all of the Order data and wrote it to a text file. A new row was added to the source table, so upon the next poll, an additional record was part of the block written to the text file.
Scenario #4 – Building a RESTful HTTP Service for an Oracle Table
This final scenario takes operations exposed by the Oracle adapter, and wraps a RESTful interface (.NET Framework 3.5 only) around them so that querying or updating data is as easy as manipulating the HTTP URI.
First, a new WCF service library was needed. The Add Adapter Service Reference wizard was used to generate the basic CRUD (create/read/update/delete) operations for the “Customers” table in the Oracle database.
Prior to completing the wizard, it was necessary to select the “Advanced Options” button. Here, the “Mark classes serializable” and “XMLSerializer” serializer were chosen so that the objects could properly translate from XML and back across the wire. If this step was NOT done, one would encounter “HTTP 400 Bad Request” messages.
After the wizard was completed, and generated types/proxies were added to the project, a new class was created that would hold the RESTful interface and service definition. To do this, a reference to the System.ServiceModel.Web assembly was added to the project. Next, an interface (marked for serialization as well) was created. The WebGet attribute was added to the interface operation and the UriTemplate value was set to “EnterpriseOrderSystem/Customers/{customerId}“. The goals is that at runtime, a user could request http://server/EnterpriseOrderSystem/Customers/1234 and see the customer record.
Once the service interface contract was done, the actual service implementation was created. In this case, the Oracle adapter proxy class made executing the database query a straightforward task. Besides setting up the service credentials, only a single line of code was needed to execute the request on the “Customers” table.
Next, a new C# Windows Form application was built to host the HTTP endpoint. IIS 6.0 could have been used here, but the developer has more control over the URI (i.e. no required *.svc entry) using a custom host than IIS 6.0. If IIS 7.0 is available, then workaround exist for cleaning up the URI.
A new configuration file was then added to the WinForms application. The Oracle binding and (client) node containing the “Customers” table endpoint from the auto-generated application configuration were copied into this configuration. A base address was added to the host. The service type was selected from the new WCF service library built above. Next, a WebHttp endpoint behavior (new in .NET Framework 3.5) was created (required). Finally, a new endpoint was added to the service. No address was put on the endpoint, but the WebHttp behavior, webHttpBinding and appropriate service contract were all added to the endpoint.
Now the application was ready to go. Once the new WCF host was started, a browser request (e.g. “GET”) was made for a specific customer.
Sure enough, a valid “Customer” object was returned to the caller. Switching the customer ID in the URI yielded the corresponding Customer record.
What if additional flexibility was needed to retrieve ALL customers via a “GET” operation? A new operation was added to the service interface contract (“GetAllCustomers”) with the correct UriTemplate, and the implemented operation simply used the Customer proxy class to retrieve all customer objects.
No changes to the WinForm WCF host application were needed. After the host was restarted, the browser request for all customers returned multiple customer objects.
Executing an “update” on the Customers table involves a different medium than the web browser. A RESTful update command typically uses the HTTP PUT command, which a browser doesn’t generate.
First, a new operation was added to the WCF service interface. Notice that the WebInvoke attribute is appended to the operation. This attribute is used for all HTTP verbs besides “GET”. In this case, the UriTemplate is identical to the individual customer query, but if one sends a “CUSTOMERRECORDUPDATE” payload to this URI using the HTTP PUT command, then the customer record would be updated.
Notice that the payload parameter (“changedCustomer”) is the last parameter of the operation. The URI parameters need to go ahead of the payload parameter.
After that interface operation was implemented (using the Oracle adapter proxy object), a client application was built to send HTTP PUT commands to this service.
The code needed in the WinForm client application was:
//set URI to specific customer being updated HttpWebRequest req = (HttpWebRequest)WebRequest.Create("http://server:8087/EnterpriseOrderSystem/Customers/4"); //set the HTTP verb req.Method = "PUT"; //create customer object (that reflects existing customer) CUSTOMERSRECORDUPDATE updatedCustomer = new CUSTOMERSRECORDUPDATE(); updatedCustomer.CUSTOMERID = "4"; updatedCustomer.CUSTOMERNAME = "Adam West"; updatedCustomer.CUSTOMERADDRESS = "1200 Mayor Lane"; updatedCustomer.CUSTOMERCITY = "Quahog"; updatedCustomer.CUSTOMERZIP = "10091"; //changed from original database value updatedCustomer.MEMBERSHIPSTATUS = "Expired"; //serialize this object into XML MemoryStream customerStream = new MemoryStream(); XmlSerializer customerSerializer = new XmlSerializer(typeof(CUSTOMERSRECORDUPDATE)); customerSerializer.Serialize(customerStream, updatedCustomer); req.ContentType = "text/xml"; req.ContentLength = customerStream.Length; //write the XML customer to the request stream Stream requestStream = req.GetRequestStream(); requestStream.Write(customerStream.ToArray(), 0, (int)customerStream.Length); WebResponse resp = req.GetResponse();
When the client application was called, then the customer record with the ID specified was updated in the database.
What if system consumers wanted to query orders for a specific customer? A more nested URI can be created. Notice below that the WebGet attribute’s UriTemplate now contains parameters for the “customerId” AND the “orderId”.
Once the 7 lines of code are added to implement this service, a browser request can be made and the following result achieved …
A URI request to “http://server/EnterpriseOrderSystem/Customers/2/Orders” would return all orders for a given customer.
By using the Microsoft BizTalk Adapter 3.0 for Oracle Database to generate the database abstraction and connectivity services, it becomes quite simple to apply additional wrappers around the database interactions. Adding new RESTful database operations is borderline trivial using the combination of the .NET Framework 3.5 and the Oracle adapter.
Summary
This article highlighted four key scenarios for using the Microsoft BizTalk Adapter 3.0 for Oracle Database. We saw the value of strongly typed cursors vs. weakly typed cursors when consuming stored procedures. Using the Oracle adapter for polling also introduces a wide range of opportunities to build eventing based systems that are triggered by database updates. Finally, we investigated the RESTful capabilities of the .NET Framework 3.5 and how the application of a few metadata attributes, combined with the generated code of the BizTalk Adapter Pack, can open up new ways to query and interact with LOB system data.
Once this entire BizTalk+WCF series is complete, I will make the entire source code available.
Questions, comments or corrections? Go ahead and leave a comment on my blog post about this article.
You can read more about BizTalk, SOA and enterprise architecture on my blog at https://seroter.wordpress.com.
Richard Seroter's Architecture Musings 