Configure a Client Binding for the SQL Adapter

After you have generated the WCF client class, you can create a WCF client (instance) and invoke its methods to consume the Microsoft BizTalk Adapter for SQL Server. For information about how to generate the WCF client class and helper code for operations that the SQL adapter exposes, see Generate a WCF Client or WCF Service Contract for SQL Server Artifacts.

To create the WCF client, you must specify an endpoint address and a binding. The endpoint address must contain a valid SQL connection URI, and the binding must be an instance of a SQL Binding (sqlBinding). For more information about the SQL connection URI, see Create the SQL Server connection URI. You must specify the user credentials as part of the connection URI. You may use the ClientCredentials property of the WCF client, as explained in this topic.

You can specify the SQL binding and the endpoint address in your code or in a configuration file. When you use the Add Adapter Service Reference Visual Studio Plug-in to generate the WCF client class, a configuration file (app.config) is also created for your project. This file contains configuration settings that reflect the binding properties and connection information (except credentials) that you specified when you connected to the SQL database with the Add Adapter Service Reference Plug-in.

Specifying the Binding and Endpoint Address in Code

The following code shows how to create a WCF client by specifying the binding and endpoint address in code by using the ClientCredentials property of the WCF client.

SqlAdapterBinding binding = new SqlAdapterBinding();  
EndpointAddress sqlAddress = new EndpointAddress("mssql://<sql_server_name>//<database_name>?");  
  
TableOp_dbo_CustomerClient client = new TableOp_dbo_CustomerClient (binding, sqlAddress);  
  
client.ClientCredentials.UserName.UserName = "USER";  
client.ClientCredentials.UserName.Password = "PASSWORD";  
  
client.Open();  

Specifying the Binding and Endpoint Address in a Configuration File

The following code shows how to create a WCF client by specifying the binding and endpoint address in an app.config file.

TableOp_dbo_CustomerClient client = new TableOp_dbo_CustomerClient("SqlAdapterBinding_TableOp_dbo_Customer");  
  
client.ClientCredentials.UserName.UserName = "USER";  
client.ClientCredentials.UserName.Password = "PASSWORD";  
  
client.Open();  

The following XML shows the configuration file created for the Customer table by the Add Adapter Service Reference Plug-in. This file contains the client endpoint configuration referenced in the preceding example.

<?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://<sql_server_name>//<database_name>?" binding="sqlBinding"  
                bindingConfiguration="SqlAdapterBinding" contract="TableOp_dbo_Customer"  
                name="SqlAdapterBinding_TableOp_dbo_Customer" />  
        </client>  
    </system.serviceModel>  
</configuration>  

If a project has more than one WCF client, there will be multiple client endpoint entries defined in the configuration file. Each WCF client entry will have a unique name based on its binding configuration and target SQL Server artifact; for example, "SqlAdapterBinding_TableOp_dbo_Customer". If you connect multiple times to create the WCF clients in your project, multiple binding configuration entries will be created, one for each connection. These binding configuration entries will be named in the following manner: SqlAdapterBinding, SqlAdapterBinding1, and so on. Each client endpoint entry created during a specific connection will reference the binding entry created during that connection.

See Also

Develop SQL applications using the WCF Service model
Generate a WCF Client or WCF Service Contract for SQL Server Artifacts
Create the SQL Server connection URI