BizTalk Server 2013: WCF-SQL Executing a Stored Procedure in Windows Azure SQL Database

아티클
1/17/2024

Introduction

The 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 querying 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 Wayne Walter Berry:
*
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.

Figure 11. Select Stored procedures.

After selecting the procedures you can click Add.

Figure 11. Adding the procedures.

The final step is to click Ok. The schemas to call the procedures and a binding file to create a Send Port will be provisioned for you. Below you see the structure of the created schema to interact with the stored procedures.

Figure 12. Stored Procedure schemas (click to enlarge).

As you can see the Consume Adapter Service Wizard is a metadata generation tool and enables you to configure security, line-of-business connection string and adapter binding properties. The Subsequent step involves selecting one or more operations based on contract type, which is either Outbound or Inbound. Finally you can generate the xml schemas and WCF-Custom Receive Port Binding file, when it concerns inbound contract type or WCF-Custom Send Port Binding file for outbound contract type.

Building a BizTalk solution

Add a BizTalk project to the solution that contains the schemas. Add an orchestration in the newly created project and build an orchestration like below:

Figure 13. Orchestration calling a stored procedure (click to enlarge).

All the logical ports in the orchestration are configured with specify later binding. Orchestration in the above example is a basic flow of receive location receiving a message, which directed to the logical send port. The received response is directed to another send port. The scope is added to have exception handling in the orchestration. The solution containing the schemas and orchestration is signed with a strong name and deployed to the BizTalk run time. The orchestration will handle the adding of an employee, while selecting an employee by name will be done through messaging (see Configuring the BizTalk solution).

Configuring the BizTalk solution

The configuration of the psychical ports can be done after the solution is deployed to the BizTalk run time. First a two ports with each a receive location are created in the BizTalk application through the BizTalk Administration Console to support the use case scenario. One of the location will be receiving request messages to add an new employee, while the other will be used to select an employee by name. Next a send port will be created by importing the provisioned binding file from the generating schema's step. The created send port will edited to be bound only to the orchestration.

Figure 14. WCF-Custom Transport Properties.

The Operation Name has to be the operation name of the bound logical send port in the orchestration. The operation for the select stored procedure will be deleted. In the Credentials tab the username and password of the Windows Azure SQL Database have to specified.

Another send port will be created to send the result of the stored procedure to file.

The orchestration will bound to a receive port, the provisioned and altered send port (communication with Windows Azure SQL Database) and a send port that send the result of the stored procedure to file.

Figure 15. Orchestration bindings.

The messaging part in the same BizTalk application is set up as follows:

The generated binding file after provisioning of the schemas is renamed and used to generate another send port;
The send port will be altered by removing the operation of adding an employee;
A filter will be added to the send port to subscribe for the message type (request employee by name message);
Another send port will be created that will subscribe to response message.

Figure 16. WCF-Custom Transport Properties

Testing the solution

The stored procedure is called indirectly through BizTalk by an application that wants to request or insert employee data from/into the employee table. In the use case scenario this is simulated by dropping a request in folder, BizTalk picks the request up and routes it to subscribing orchestration or send port that communicates with Windows Azure SQL Database. The stored procedure will be executed on the Employee table in Windows Azure SQL Database. The response will be routed to the orchestration or the send port that has a subscription on message type. This message is then send to another folder.

Add employee request:

<ns0:ADD_EMP_DETAILS xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
  <ns0:emp_name>Joe Davis</ns0:emp_name> 
  <ns0:emp_desig>Accountant</ns0:emp_desig> 
  <ns0:salary>79000</ns0:salary> 
</ns0:ADD_EMP_DETAILS>

Add employee response:

<ADD_EMP_DETAILSResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
<ADD_EMP_DETAILSResult>
<DataSet  xmlns="http://schemas.datacontract.org/2004/07/System.Data"><xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element  msdata:IsDataSet="true" name="NewDataSet">
<xs:complexType><xs:sequence><xs:element minOccurs="0" maxOccurs="unbounded" name="NewTable"><xs:complexType><xs:sequence><xs:element minOccurs="0" name="Employee_ID" type="xs:int" /></xs:sequence></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:schema><diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"><NewDataSet xmlns="">
<NewTable><Employee_ID>10006</Employee_ID>
</NewTable>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
</ADD_EMP_DETAILSResult>
<ReturnValue>0</ReturnValue></ADD_EMP_DETAILSResponse>

Get Employee by Name request:

<ns0:GET_EMP_DETAILS_BY_NAME xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
  <ns0:emp_name>Joe Davis</ns0:emp_name> 
</ns0:GET_EMP_DETAILS_BY_NAME>

Get Employee by Name response:

<GET_EMP_DETAILS_BY_NAMEResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo"><GET_EMP_DETAILS_BY_NAMEResult>
<DataSet  xmlns="http://schemas.datacontract.org/2004/07/System.Data"><xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element msdata:IsDataSet="true" name="NewDataSet"><xs:complexType><xs:sequence><xs:element minOccurs="0" maxOccurs="unbounded" name="NewTable"><xs:complexType><xs:sequence><xs:element minOccurs="0" name="Employee_ID" type="xs:int" /><xs:element minOccurs="0" name="Name" type="xs:string" /><xs:element minOccurs="0" name="DOJ" type="xs:dateTime" /><xs:element minOccurs="0" name="Designation" type="xs:string" /><xs:element minOccurs="0" name="Job_Description" type="xs:string" /><xs:element minOccurs="0" name="Photo" type="xs:base64Binary" /><xs:element minOccurs="0" name="Salary" type="xs:decimal" /><xs:element minOccurs="0" name="Last_Modified" type="xs:base64Binary" /><xs:element minOccurs="0" name="Status" type="xs:int" /><xs:element minOccurs="0" name="Address" type="xs:string" /></xs:sequence></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:schema><diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"><NewDataSet xmlns=""><NewTable><Employee_ID>10006</Employee_ID><Name>Joe Davis</Name><Designation>Accountant</Designation><Salary>79000.00</Salary><Last_Modified>AAAAAAAAAAY=</Last_Modified><Status>0</Status></NewTable></NewDataSet></diffgr:diffgram></DataSet></GET_EMP_DETAILS_BY_NAMEResult>
<ReturnValue>0</ReturnValue></GET_EMP_DETAILS_BY_NAMEResponse>

Call for action

You can download the code from the use case scenario from MSDN Code Gallery:

WCF-SQL executing a stored procedure in Windows Azure SQL Database

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). However not all the features of the WCF-SQL Adapter are supported when it comes to Windows Azure SQL Database, see SQL Server Feature Limitations (Windows Azure SQL Database).

다음을 통해 공유