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.