Create a channel using the SQL adapter

Article
02/02/2021

In the WCF channel model, you invoke operations on the SQL Server database and receive the results by exchanging SOAP messages with the Microsoft BizTalk Adapter for SQL Server over a WCF channel.

You invoke outbound operations by using either an IRequestChannel or an IOutputChannel to send messages to the adapter.
You receive messages for inbound operations by receiving messages over an IInputChannel for Polling, TypedPolling, or Notification operations.

The procedures in this topic provide information about how to create and configure channel shapes that are used for inbound and outbound operations.

Creating Outbound (Client) Channels

You can use either an IRequestChannel or an IOutputChannel to invoke operations on the SQL Server database. In either case, you first create a System.ServiceModel.ChannelFactory using the appropriate interface. You then use the factory to create the channel. After you have created the channel you can use it to invoke operations on the adapter.

To create and open an outbound channel

Create and initialize an instance of ChannelFactory for the desired channel shape by using an endpoint and a binding. The endpoint specifies a SQL Server connection URI and the binding is an instance of sqlBinding.
Provide SQL Server credentials for the channel factory by using the Credentials property.
Open the channel factory.
Get an instance of the channel by invoking the CreateChannel method on the channel factory.
Open the channel.

You can specify the binding and endpoint address in your code or from configuration.

Specifying the Binding and Endpoint Address in Code

The following code example shows how to create an IRequestChannel by specifying the binding and endpoint address in code. The code to create an IOutputChannel is the same except that you must specify an IOutputChannel interface for the ChannelFactory and channel type.

// Create binding -- set binding properties before you open the factory.  
SqlAdapterBinding sdbBinding = new SqlAdapterBinding();  
  
// Create address.  
EndpointAddress sdbAddress = new EndpointAddress("mssql://<sql_server_name>//<database_name>?");  
  
// Create channel factory from binding and address.  
ChannelFactory<IRequestChannel> factory =   
    new ChannelFactory<IRequestChannel>(sdbBinding, sdbAddress);  
  
// Specify credentials.   
factory.Credentials.UserName.UserName = "myuser";  
factory.Credentials.UserName.Password = "mypassword";  
  
// Open factory  
factory.Open();  
  
// Get channel and open it.  
IRequestChannel channel = factory.CreateChannel();  
channel.Open();

Specifying the Binding and Endpoint Address in Configuration

The following code example shows how to create a channel factory from a client endpoint specified in configuration.

// Create channel factory from configuration.  
ChannelFactory<IRequestChannel> factory =  
new ChannelFactory<IRequestChannel>("MyRequestChannel");  
  
// Specify credentials.  
factory.Credentials.UserName.UserName = "myuser";  
factory.Credentials.UserName.Password = "mypassword";  
  
// Open the factory.  
factory.Open();  
  
// Get a channel and open it.  
IRequestChannel channel = factory.CreateChannel();  
channel.Open();

The Configuration Settings

The following code shows the configuration settings used for the preceding example. The contract for the client endpoint must be "System.ServiceModel.Channels.IRequestChannel" or "System.ServiceModel.Channels.IOutputChannel" depending on the kind of channel shape that you want to create.

<?xml version="1.0" encoding="utf-8"?>  
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">  
    <system.serviceModel>  
        <bindings>  
            <sqlBinding>  
                <binding name="SqlAdapterBinding" closeTimeout="00:01:00" openTimeout="00:01:00"  
                    receiveTimeout="00:10:00" sendTimeout="00:01:00" maxConnectionPoolSize="100"  
                    encrypt="false" useAmbientTransaction="true" batchSize="20"  
                    polledDataAvailableStatement="" pollingStatement="" pollingIntervalInSeconds="30"  
                    pollWhileDataFound="false" notificationStatement="" notifyOnListenerStart="true"  
                    enableBizTalkCompatibilityMode="true" chunkSize="4194304"  
                    inboundOperationType="Polling" useDatabaseNameInXsdNamespace="false"  
                    allowIdentityInsert="false" enablePerformanceCounters="false"  
                    xmlStoredProcedureRootNodeName="" xmlStoredProcedureRootNodeNamespace="" />  
            </sqlBinding>  
        </bindings>  
        <client>  
            <endpoint address="mssql://mysqlserver//mydatabase?" binding="sqlBinding"  
                bindingConfiguration="SqlAdapterBinding" contract="System.ServiceModel.Channels.IRequestChannel"  
                name="MyRequestChannel" />  
        </client>  
    </system.serviceModel>  
</configuration>

Creating Inbound (Service) Channels

You configure the SQL adapter to poll the SQL Server database tables and views by setting binding properties on an instance of sqlBinding. You then use this binding to build a channel listener from which you can get an IInputChannel channel to receive the Polling, TypedPolling, or Notification operation from the adapter.

To create and open an IInputChannel to receive inbound operations

Create an instance of SQLBinding.
Set the binding properties required for inbound operation. For example, for a Polling operation, at a minimum you must set the InboundOperationType, PolledDataAvailableStatement, and PollingStatement binding properties to configure the SQL adapter to poll the SQL Server database.
Create a channel listener by invoking BuildChannelListener<IInputChannel> method on the SQLBinding. You specify the SQL Server connection URI as one of the parameters to this method.
Open the listener.
Get an IInputChannel channel by invoking the AcceptChannel method on listener.
Open the channel.

The following code shows how to create a channel listener and get an IInputChannel to receive data-changed messages from the adapter.

Important

The SQL adapter only supports one-way receive. So, you must use IInputChannel to receive messages for inbound operations from SQL Server.

// Create a binding: specify the InboundOperationType, the PolledDataAvailableStatement, and   
// the PollingStatement binding properties.  
SqlAdapterBinding binding = new SqlAdapterBinding();  
binding.InboundOperationType = InboundOperation.Polling;  
binding.PolledDataAvailableStatement = "SELECT COUNT (*) FROM EMPLOYEE";  
binding.PollingStatement = "SELECT * FROM Employee;EXEC MOVE_EMP_DATA;EXEC ADD_EMP_DETAILS John, Tester, 100000";  
  
// Create a binding parameter collection and set the credentials  
ClientCredentials credentials = new ClientCredentials();  
credentials.UserName.UserName = "myuser";  
credentials.UserName.Password = "mypassword";  
  
BindingParameterCollection bindingParams = new BindingParameterCollection();  
bindingParams.Add(credentials);  
  
// Get a listener from the binding and open it.  
Uri connectionUri = new Uri("mssql://mysqlserver//mydatabase?");  
IChannelListener<IInputChannel> listener = binding.BuildChannelListener<IInputChannel>(connectionUri, bindingParams);  
listener.Open();  
  
// Get a channel from the listener and open it.  
IInputChannel channel = listener.AcceptChannel();  
channel.Open();