Export (0) Print
Expand All

Performing Operations on Tables with Large Object Types by Using the WCF Service Model

This section contains information about how to invoke the ReadLOB and UpdateLOB operations from the WCF service model. The ReadLOB and UpdateLOB operations are surfaced for tables and views that contain LOB columns; that is columns that are used to store Oracle large object (LOB) data. For an overview of the Oracle LOB data types supported by the Microsoft BizTalk Adapter for Oracle Database and of the ReadLOB and UpdateLOB operations, see Operations on Tables and Views That Contain LOB Data.

Dd787986.Important(en-us,BTS.70).gifImportant
LOB data columns can contain large amounts of data—up to 4 gigabytes (GB). A significant limitation of using a WCF client to operate on LOB columns is that the WCF service model only supports data streaming on the ReadLOB operation, not on the UpdateLOB operation. This is because WCF requires that for streaming to work from service model, the parameter to be streamed must be the only parameter in its direction. The UpdateLOB operation has two other IN parameters (a column name and row filter) in addition to the LOB data; for this reason, streaming is not supported on it in the WCF service model. Therefore, if you are updating a LOB column with a large amount of data, you might want to use the WCF channel model. For more information on how to use the WCF channel model to stream LOB data using the UpdateLOB operation, see Streaming Oracle LOB Data Types by Using the WCF Channel Model.

The examples in this topic use the /SCOTT/CUSTOMER table. This table contains a BLOB column named PHOTO.A script to generate this table is supplied with the SDK samples. For more information about the SDK samples, see Samples.

The following example shows the method signatures for a WCF client class generated for the ReadLOB and UpdateLOB operations on the /SCOTT/CUSTOMER table.

public partial class SCOTTTableCUSTOMERClient : System.ServiceModel.ClientBase<SCOTTTableCUSTOMER>, 
                                                SCOTTTableCUSTOMER 
{
    public System.IO.Stream ReadLOB(string LOB_COLUMN, string FILTER); 
    
    public void UpdateLOB(string LOB_COLUMN, string FILTER, byte[] Stream);
}
Dd787986.note(en-us,BTS.70).gifNote
Note that ReadLOB returns a data stream, but that UpdateLOB does not operate on a stream.

Both the ReadLOB and UpdateLOB methods can operate only on a single LOB column in a single database row. You set the following parameters to identify the target column/row.

ParameterDefinition

LOB_COLUMN

The name of the target column within the row identified by the FILTER parameter; for example, "PHOTO".

FILTER

The contents of a SQL SELECT statement WHERE clause that specifies the target row; for example, "NAME='Kim Ralls'". The filter must specify one and only one row. If the filter matches more than one row:

  • ReadLOB returns LOB data for the first matching row.

  • UpdateLOB throws an exception.

Dd787986.note(en-us,BTS.70).gifNote
The stream returned by ReadLOB does not support Seek. This means that properties such as Length are not supported, either.

Dd787986.Caution(en-us,BTS.70).gifCaution
The UpdateLOB operation must be performed within a transaction scope. Also, the UseAmbientTransaction binding property must be set to true before performing the UpdateLOB operation.

The following code shows how to use a WCF client to update the BLOB PHOTO column in the /SCOTT/CUSTOMER table from a file and read the new column data back to a file. You can find a full sample in the SDK samples. For more information about the SDK samples, see Samples.

using System;
using System.Collections.Generic;
using System.Text;
using System.Transaction;

// Include for file streaming
using System.IO;

// Add WCF, WCF LOB Adapter SDK, and Oracle Database adapter namepaces
using System.ServiceModel;
using Microsoft.ServiceModel.Channels;
using Microsoft.Adapters.OracleDB;

// Include this namespace for the WCF channel model
using System.ServiceModel.Channels;

// Include this namespace for the WCF LOB Adapter SDK and Oracle Database adapter exceptions
using Microsoft.ServiceModel.Channels.Common;

using CustomerTablens = microsoft.lobservices.oracledb._2007._03;



namespace OracleLobOpsSnippetSM
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                OracleDBBinding binding = new OracleDBBinding();
                binding.UseAmbientTransaction = true; //set this to true for UpdateLOB operation

                EndpointAddress endpointAddress = new EndpointAddress("oracleDB://ADAPTER");
               
                using (SCOTTTableCUSTOMERClient customerTableClient =
                    new SCOTTTableCUSTOMERClient(binding, endpointAddress))
                {
                    customerTableClient.ClientCredentials.UserName.UserName = "SCOTT";
                    customerTableClient.ClientCredentials.UserName.Password = "TIGER";

                    // Open the client to read the LOB data back
                    customerTableClient.Open();

                    
                    // Add a photo to the customer record  
                    using (FileStream fs = new FileStream("SamplePhoto.jpg", FileMode.Open))
                    {
                        Console.WriteLine("Updating photo");
                        int count = 0;
                        byte[] photo = new byte[fs.Length];
                        while ((count += fs.Read(photo, count, (int) (((fs.Length-count)>4096)? 4096:fs.Length-count))) < fs.Length) ;

                        // UpdateLOB operation must be performed within a transaction scope
                        using(TransactionScope tx = new TransactionScope())
                        {
                           customerTableClient.UpdateLOB("PHOTO", "NAME='Kim Ralls'", photo);
                           Console.WriteLine("Photo updated");
                           tx.Complete();
                        }
                    }

                    
                    // Read the data back and store it to another file
                    using (FileStream fs = new FileStream("PhotoCopy.jpg", FileMode.Create))
                    {
                        Console.WriteLine("Reading photo data");
                        Stream photoStream = customerTableClient.ReadLOB("PHOTO", "NAME='Kim Ralls'");
                        Console.WriteLine("Photo data read -- writing to PhotoCopy.jpg");

                        int count;
                        int length = 0;
                        byte[] buffer = new byte[4096];
                        while ((count = photoStream.Read(buffer, 0, 4096)) > 0)
                        {
                            fs.Write(buffer, 0, count);
                            length+=count;
                        }
                        Console.WriteLine("{0} bytes written to PhotoCopy.jpg", length);
                    }
                    
                }

                Console.WriteLine("Photo updated and read back -- Hit <RETURN> to end");
                Console.ReadLine();

            }
            catch (Exception ex)
            {
                // handle exception
                Console.WriteLine("Exception caught: " + ex.Message);
            }
        }
    }
}
Show:
© 2014 Microsoft