Connect to SQL Server in Visual Studio using the Consume Adapter Service Add-in
The Consume Adapter Service BizTalk Project Add-in is installed when you install WCF LOB Adapter SDK. The Consume Adapter Service Add-in loads all the WCF-Custom bindings installed on the computer. To connect to SQL Server using the WCF-based SQL adapter in a BizTalk project, you must use the sqlbinding.
This topic provides instructions on how to use the Consume Adapter Service Add-in.
Connecting to SQL Server Using Consume Adapter Service Add-in
Perform the following steps to connect to SQL Server using the Consume Adapter Service Add-in.
To connect to SQL Server
To connect using the Consume Adapter Service Add-in in a BizTalk solution:
Create a BizTalk project using Visual Studio.
Right-click the project name in Solution Explorer, point to Add, and then click Add Generated Items.
In the Add Generated Items dialog box, do the following:
Use this To do this Categories Click Consume Adapter Service. Templates Click Consume Adapter Service.
Click Add. The Consume Adapter Service Add-in opens.
From the Select a binding drop-down list, select sqlBinding, and then click Configure.
In the Configure Adapter dialog box, click the Security tab, and from the Client credential type drop-down list, do one of the following:
If you are connecting to SQL Server using Windows Authentication, the Windows user with which you are logged in must be added to SQL Server as described in Connect to SQL Server using Windows Authentication with the SQL adapter.
Click this To do this None Connect to SQL Server by using Windows authentication. Windows Connect to SQL Server by using Windows authentication. Username Specify the user name and password to connect to SQL Server by specifying credentials for a user defined in SQL Server database. Note that the user name and password are case-sensitive. Note: If you leave the User name and Password fields as blank, the adapter connects to SQL Server using Windows authentication.
Click the URI Properties tab, and then specify values for the connection parameters. For more information about the connection URI for the SQL adapter, see Create the SQL Server connection URI.
If the connection parameters contain any reserved characters, you must specify them as-is in the URI Properties tab, that is, without using any escape characters. However, if you specify the URI directly in the Configure a URI field and the connection parameters contain reserved characters, you must specify the connection parameters using proper escape characters.
If you do not specify any values in the URI property tab, the Consume Adapter Service Add-in puts the URI as
mssql://.//. In such a case, the adapter connects to the default database and the default database instance on the local computer.
Click the Binding Properties tab, and then specify values for the binding properties, if any, required by the operations you want to target.
Click Connect. After the connection is established, the connection status is shown as Connected.
The following figure shows the Consume Adapter Service Add-in immediately after the connection is established.
The Consume Adapter Service Add-in displays different nodes containing various operations that can be performed on SQL Server. For example, the Procedures node contains all the procedures available for the database you connected to. Similarly, the Tables node contains all the tables in the database you connected to, and the operations that can be performed on a table. For more information about these nodes, see Metadata Node IDs.