Revision #10

You are currently reviewing an older revision of this page.
Go to current version

Introduction

WCF-SQL Adapter (binding) from the BizTalk adapter pack has the following capabilities:
  • Execute create-read-update-delete statements on tables and views; execute stored procedures and generic T-SQL statements           
  • Database polling via FOR XML    
  • Database polling via traditional tabular results    
  • Proactive database push via SQL Query Notification        
  • Expansive adapter configuration which impacts connection management and transaction behavior     
  • Support for composite transactions which allow aggregation of operations across tables or procedures into a single atomic transaction   
  • Rich metadata browsing and retrieval for finding and selecting database operations       
  • Support for the latest data types (e.g. XML) and SQL Server 2008, 2008 R2, 2012 platform, Windows Azure SQL Database
  • Reusable outside of BizTalk applications by WCF or basic HTTP clients        
  • Adapter extension and configuration through out of the box WCF components or custom WCF behaviors
  • Dynamic WSDL generation which always reflects current state of the system instead of fixed contract which always requires explicit updates
Depending on the version of BizTalk Server and its subsequent adapter pack the supported SQL Server versions range from 2005 to 2012. Beside the on premise SQL Server versions the adapter also supports connectivity with Windows Azure SQL Database. In this article the execution of a stored procedure in Windows Azure SQL Database will be discussed.

Use Case Scenario

The following use case will be used in this article:
  • A request message will be sent to add a new employee in the Human Resource (HR) database in Windows Azure SQL Database;
  • A stored procedure will based on details of the employee in the payload of the message insert a new record in Windows Azure SQL Database called employee;
  • Result of the stored procedure will be returned;
  • A request message will be sent to select an employee based on his/her name from the HR database in Windows Azure SQL Database;
  • A stored procedure will based on details of the employee name in the payload of the message select a record in Windows Azure SQL Database called employee;
  • Result of the stored procedure will be returned;
All the steps necessary to build a solution supporting the use case will be explained. These steps are:
  • Creating a database in a Windows Azure SQL Database;
  • Creating a table;
  • Creating the stored procedures;
  • Generating the schemas;
  • Building a BizTalk solution;
  • Configuring the solution;
  • Testing the solution.

Creating a database, table, and stored procedures in Windows Azure SQL Database

If you have an account with Windows Azure Portal than you can create 1 Gb database online. You can go for a free trail (1 month), or through MSDN account (you receive quite a lot of benefits with Windows Azure), or commercially (see pricing).

In case you have subscription with Windows Azure than you can sign in through Azure Management Portal and if you do not have Windows Azure SQL Database instance yet you can create one by following the steps in detailed in Getting Started with Windows Azure SQL Database (step 2 and 3).

Create a database

A database can be created through the Windows Azure Management Portal. You navigate to your Windows Azure SQL Database instance and through Data Services --> SQL Database --> Quick Create you can easily create a database.



Figure 1. Create a database through Windows Azure Management Portal.
The database will be provisioned for you.


Figure 2. Provision of a database (click to enlarge).
As soon as the database is online you manage it by clicking on it and select manage.

Figure 3. Manage the database (click to enlarge).
An overview will be presented of the provisioned database. It includes the url to access the database.


Figure 4. Manage url of the database (click to enlarge).
By using the url in a browser you will be directed to silverlight portal where you have to specify a username and password to access the database.

Figure 5. Silverlight portal of Windows Azure SQL Database

Create a table in Windows Azure SQL Database through the management portal (Silverlight)


After logging in you have access to your database. You can now look at the database design or click New Query. The latter enables you fire query statements on the database. Either quering database objects like tables or views or executing DML statements like creating a table or procedure. The following statement creates a table for you:
CREATE TABLE [dbo].[Employee](
[Employee_ID] [int] IDENTITY(10001,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[DOJ] [datetime] NULL,
[Designation] [varchar](50) NOT NULL,
[Job_Description] [varchar](max) NULL,
[Photo] [image] NULL,
[Salary] [decimal](18, 2) NOT NULL,
[Last_Modified] [timestamp] NULL,
[Status] [int] NULL CONSTRAINT [DF_Employee_Status] DEFAULT ((0)),
[Address] [xml] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Employee_ID] ASC
))
GO


Figure 6. Executing a TSQL statement in Windows Azure SQL Database Portal.
For the use case scenario the following statements are being used to fill the table with initial data.
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Jeff Price','Manager',500000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Don Hall','Accountant',40000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Keith Harris','Supervisor',300000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Jim Hance','Admin',200000)
INSERT INTO [Employee]([Name],[Designation],[Salary])VALUES('Andy Jacobs','Accountant',400000)

Create stored procedures in Windows Azure SQL Database through the management portal (Silverlight)


Creating stored procedures is similar as to creating a table. Click New Query and execute statement like below:
CREATE PROCEDURE [dbo].[ADD_EMP_DETAILS]
-- Add the parameters for the stored procedure here
@emp_name varchar(50),
@emp_desig varchar(50),
@salary decimal(18,2)
 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
-- Insert statements for procedure here
INSERT INTO [Employee]
([Name]
,[Designation]
,[Salary])
VALUES
(@emp_name
,@emp_desig
,@salary)
SELECT [Employee_ID] FROM Employee where [Employee_ID] = (select IDENT_CURRENT('Employee'))
 
END
 
GO
Above the code for inserting an employee is shown. Below you will see the code for selecting an employee based on name.
CREATE PROCEDURE [dbo].[GET_EMP_DETAILS_BY_NAME]
-- Add the parameters for the stored procedure here
@emp_name varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
-- Insert statements for procedure here
SELECT * FROM [Employee]
WHERE [Name] = @emp_name
 
END
 
GO
Creating stored procedure is straight forward and same way as in SQL Server Management Studio.

Generating schemas


First step will be that you will need to create the XML schemas for your operation(s) in Windows Azure SQL Database. With the WCF LOB Adapter SDK SP2 installed, you will have the metadata browser built into Visual Studio. NET at your disposal. You can access this from a BizTalk project by right-clicking the project, choosing Add then Generated Items and selecting Consume Adapter Service.


Figure 7. Add generated items - Consume Adapter Service
This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing you will need to do here is choose the sqlBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style. The configuration of the URI is a little different than connecting to a SQL Server on premise within your data center. The Client credential type will be username and password. These are credentials of your Windows Azure SQL Database (same as when logging into your database, see picture 5).


Picture 8. Configure adapter - Security.
URI properties you specify for server property is Windows Azure SQL Database name.


Picture 9. Configure adapter - URI Properties.
In the final tab the binding properties can be specified. Important to note is that EnableBizTalkCompatibilityMode is true and AmbientTransaction setting is false. The latter needs to be false as with true the local transaction will be escalated to Microsoft Distributed Coordinator (MSDTC).  According Handling Transactions in Windows Azure SQL Database written by :

Windows Azure SQL Database SQL Database does not support distributed transactions, which are transactions that multiple transaction managers (multiple resources). This means that Windows Azure SQL Database SQL doesn’t allow Microsoft Distributed Transaction Coordinator (MSDTC) to delegate distributed transaction handling. Because of this you can’t use ADO.NET or MSDTC to commit or rollback a single transaction that spans across multiple Windows Azure SQL Databases or a combination of Windows Azure SQL Database and an on-premise SQL Server.


Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories. The contract type can be Client (Outbound operations) as seen below in the screenshot to perform operations on SQL objects or Service (Inbound operations) to perform polling operations.


Figure 10. Connect to Windows Azure SQL Database.
On the root "/" character a range of untyped generic functions are displayed, which can be exploited. Click on Procedures and you will see a number of Procedures, select the required Stored Procedure. Note that other SQL objects can be accessed like tables and views.


Wrap up

This article demonstrated how to execute a stored procedure on Windows Azure SQL Database using the WCF-SQL adapter from the BizTalk Adapter Pack. Either through messaging or using an orchestration a stored procedure on Windows Azure SQL Database has been discussed. Calling a stored procedure is not the only capability of the WCF-SQL Adapter (see Introduction). You can also use the WCF-SQL Adapter to perform operations on SQL Views, Tables, Polling and so on (See Also).


See Also

Read suggested related topics:

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki. 

Revert to this revision