Develop your SQL applications

Overview

The Microsoft BizTalk Adapter for SQL Server is a Windows Communication Foundation (WCF) binding. Client applications can consume the SQL adapter to invoke operations on SQL Server artifacts. The SQL adapter can be consumed:

• Through a physical port binding in a BizTalk Server solution.

• By invoking methods on an instance of a client proxy.

• As a hosted WCF service.

• By sending SOAP messages over a channel instance in code that uses the WCF channel model.

BizTalk vs WCF service vs WCF channel

The following table:

• Lists the different operations that can be performed on SQL Server using the SQL adapter.

• Provides links to the topics containing information about performing the task using the chosen approach (BizTalk Server, WCF service model, WCF channel model).

Task	BizTalk Server	WCF Service Model	WCF Channel Model
Performing basic Insert, Update, Delete, and Select operations on tables and views	Insert, update, delete, or select operations using BizTalk Server with the SQL adapter	Insert, update, delete, or select operations on interface tables and views using the WCF service model	Run an Insert Operation on a Table in SQL using the WCF Channel Model
Performing operations on tables and views with large data type columns (Also includes information about FILESTREAM operations using the adapter)	Operations on tables and views that contain large data types using the SQL adapter	Run Operations on Tables and Views with Large Data Types in SQL using the WCF Service Model	-
Executing stored procedures	Execute stored procedures in SQL Server using BizTalk Server	Invoke Stored Procedures in SQL using the WCF Service Model	-
Executing stored procedures with single parameters without using a BizTalk orchestration	Execute stored procedures with a single XML parameter in SQL Server using BizTalk Server	-	-
Executing stored procedures that contain a FOR XML clause in the definition	Execute stored procedures having a FOR XML clause in SQL Server using BizTalk Server	-	-
Performing composite operations on SQL Server	Run composite operations on SQL Server using BizTalk Server	-	-
Invoking scalar functions in SQL Server	Invoke Scalar Functions in SQL Server using BizTalk Server	Invoke Scalar Functions in SQL Server by Using the WCF Service Model	-
Invoking table-valued functions in SQL Server	Invoke Table-Valued Functions in SQL Server using BizTalk Server	Invoke Table-Valued Functions in SQL Server by Using the WCF Service Model	-
Performing ExecuteReader, ExecuteScalar, or ExecuteNonQuery operations	ExecuteReader, ExecuteScalar, or ExecuteNonQuery Operations in SQL using BizTalk Server	ExecuteReader, ExecuteScalar, or ExecuteNonQuery operations in SQL using WCF Service Model	-
Receiving polling-based data-change messages	Poll SQL Server using the SQL Adapter with BizTalk Server	Poll SQL Server using the SQL Adapter with WCF Service Model	Receive Polling-based Data-changed Messages from SQL Server by Using the WCF Channel Model
Receiving SQL Server notifications	Receive SQL Query Notifications using BizTalk Server	Receive Query Notifications from SQL using the WCF Service Model	-

Next steps

The topics in this section provide information, procedures, and examples to help you develop applications that consume the SQL adapter in both BizTalk Server and .NET programming solutions.

• Create a connection to SQL Server
• Get metadata for SQL Server operations in Visual Studio
• Metadata Node IDs
• Working with the binding properties
• Prerequisite: Configure MSDTC
• Develop BizTalk applications using the SQL adapter
• Develop applications using the WCF Service model
• Develop applications using the WCF Channel model
• Samples
• Configure Transaction Isolation Level and Transaction Timeout