Polling SQL Azure Database Table with WCF-SQL Adapter

I have seen polling related questions sometimes on the BizTalk forums and I myself occasionally have come across a scenario’s where I had to use polling. In past I have read a couple of posts on polling like the one from Richard (TypedPolling) and Armen (Polling). Now with SQL Azure I wondered if it is possible to poll a SQL Azure Table.

If you have an account with Windows Azure Portal than you can create 1 Gb database online. You can go for a free trail (90 days), or through MSDN account (you receive quite a lot of benefits with Windows Azure), or commercially (see pricing). In a previous post I showed some steps how to create a new SQL Azure database.

You can create a database through Windows Azure Portal or SQL Management Studio. If you connect to SQL Azure through Management Studio you need to use SQL Server Authentication, fill in the server name, and credentials.

Once connected you will in the left pane see Azure database you connect to.

When you click New Query a query windows will appear on the right. Here I executed the following statement:

USE [master]
GO

CREATE DATABASE [CustomerOnlineDb]
GO

In left pane you will see new database in databases tree. Select that database and click New Query again, a new query pane will appear. Here I executed the following statement:

CREATE TABLE [dbo].[CustomerAddress](
    [CustomerAddressID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [MiddleName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [FullName] [nvarchar](50) NOT NULL,
    [CompanyName] [nvarchar](50) NOT NULL,
    [FullAddress] [nvarchar](50) NOT NULL,
    [PostalCode] [nvarchar](50) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [StateProvince] [nvarchar](50) NOT NULL,
    [Country] [nvarchar](50) NOT NULL,
    [Polled] [bit] NOT NULL
CONSTRAINT [PK_CustomerAddress] PRIMARY KEY CLUSTERED
(
    [CustomerAddressID] ASC
)
)

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Catherine','R.','Abel','Catherine R. Abel', 'Professional Sales and Service','57251 Serene Blvd','91411','Van Nuys','California','United States',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Frances','B.','Smith','Frances B. Smith', 'Area Bike Accessories','6900 Sisk Road','95354','Modesto','California','United States',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Margaret','J.','Adams','Magaret J. Adams', 'Bicycle Accessories and Kits','Lewiston Mall','83501','Lewiston','Idaho','United States',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Samuel','N.','Agcaoili','Samuel N. Agcaoili', 'Vinyl and Plastic Goods Corporation',' 25800-130 King Street West','M4B 1V5','Toronto','Ontario','Canada',0)
GO

INSERT INTO dbo.CustomerAddress(FirstName, MiddleName, LastName, FullName, CompanyName, FullAddress, PostalCode, City, StateProvince, Country, Polled)
VALUES('Robert','E.','Ahlering','Robert E. Ahlering', 'Fun Toys and Bikes','6500 East Grant Road','85701','Tucson','Arizona','United States',0)
GO

I then executed SELECT * FROM dbo.CustomerAddress TSQL Statement and check if there is data.

I have set up now a database in SQL Azure with one table containing five records. The next step is to create a BizTalk project and having a xml schema and WCF-Custom Receive Port Binding generated for table in my SQL Azure database I wish to poll. I configured the URI to connect to SQL Azure (security, connection string and adapter binding properties), choose inbound operations and from available categories Polling. When I click Ok a DataSetSchema.xsd and Polling.xsd is generated together with WcfReceivePort_SqlAdapterBinding_Custom.bindinginfo.xml file. I deployed project after signing and giving appropriate name for BizTalk application.

Notice that with the Consume Adapter Service Wizard you have ability to choose between three type of inbound operations: Polling, TypedPolling and Notification. This means you can with:

Polling operation a data set as part of the polling message returned.
TypedPolling operation a strongly-typed polling message returned.
Notification operation a notification message returned.

Important step after deployment of schema’s is importing the custom binding file and setting some properties (i.e. pollDataAvailableStatement, pollingStatement). Importing a binding is a straightforward operation inside BizTalk Administation Console. After the receive port and location is created you can double click receive location and then Configure Type (WCF-Custom). URI is present in General Tab and can be left as is. In next tab you see binding information of sqlBinding. In polledDataAvailableStatement I have the following statement: SELECT COUNT(*) FROM dbo.CustomerAddress and for pollingStatement: SELECT * FROM dbo.CustomerAddress WHERE Polled = 0; UPDATE dbo.CustomerAddress SET Polled=1 WHERE Polled=0;

I also changed useAmbientTransaction property to False (for operations where the adapter client does not write any data to the SQL Server database, such as a Select operation, you might not want the additional overhead of performing the operations inside a transaction. In such cases, you can configure the SQL adapter to perform operations without a transactional context by setting the UseAmbientTransaction binding property to false.)

In the tab called Other I configured the credentials filling in user name and password for the account to access database on SQL Azure. After that you are ready for receive side. As I just wanted to poll data and send it to file, I also created a Send Port that uses FILE Adapter. I configured the adapter to send data to folder with a filter that subscribes to message type http://schemas.microsoft.com/Sql/2008/05/Polling/#Polling.

As you can I basically have messaging solution now that polls data from SQL Azure database table and routes the polled data as a single message to a folder on-premise (i.e. my virtual machine). Once I start the application I see after a few seconds a xml file containing data.

If I perform a query inside my SQL Management Studio I noticed the column Polled is 1 for every record.

With WCF-SQL Adapter I am able to poll a SQL Azure table and not just polling, but calling a Stored-Procedure or table operations are also possible using WCF-SQL adapter, available through BizTalk Adapter Pack 2010. I conclude that you can use WCF-SQL adapter for on-premise SQL Server and its cloud variant SQL Azure!