Polling SQL Azure Database Table with WCF-SQL Adapter

Posted: October 2, 2011  |  Categories: BizTalk Server 2010 SQL Azure Uncategorized
Tags:

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.

You can download the entire article as a PDF document.
Polling SQL Azure Database Table with WCF-SQL Adapter

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.

image

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

image

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
)
)

GO

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

You can download the entire article as a PDF document.
Polling SQL Azure Database Table with WCF-SQL Adapter

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

image

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.

image

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;

image

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.

image

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

image

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!

You can download the entire article as a PDF document.
Polling SQL Azure Database Table with WCF-SQL Adapter
Author: Steef-Jan Wiggers

Steef-Jan Wiggers is all in on Microsoft Azure, Integration, and Data Science. He has over 15 years’ experience in a wide variety of scenarios such as custom .NET solution development, overseeing large enterprise integrations, building web services, managing projects, designing web services, experimenting with data, SQL Server database administration, and consulting. Steef-Jan loves challenges in the Microsoft playing field combining it with his domain knowledge in energy, utility, banking, insurance, healthcare, agriculture, (local) government, bio-sciences, retail, travel, and logistics. He is very active in the community as a blogger, TechNet Wiki author, book author, and global public speaker. For these efforts, Microsoft has recognized him a Microsoft MVP for the past 8 years.

  • Nice post on the subject.
    I was just wondering; does SQL Azure support notifications? They would be a better choice as a poll counts as a transaction even if not data is available.

    Then Azure can notify BizTalk of data and then receive it.

  • A concise article. Please do pass by my new SQL Geek site–its a bit wild!
    ashane
    http://www.thesqlgeek.blogspot.com/

  • This works perfectly on any non-Azure database but didn't get it working on the same DB in Azure. Without the 'ambient transaction option' it does nothing at all, but with the option it at least does the update specified in the polledDataAvailableStatement.

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top