How to poll database using WCF-SQL adapter


1. Create database and tables
2. Create stored procedure
3. Create BizTalk project
4. Create orchestration
5. Consume WCF-SQL
6. Write received data to file share



First create TESTWCFSQL database from you SQL Management studio
Run the script to create table:

USE TESTWCFSQL
GO

IF OBJECT_ID('dbo.MainData', 'U') IS NOT NULL
DROP TABLE dbo.MainData
GO

CREATE TABLE dbo.MainData
(
MainDataID int primary key identity(1,1),
FirstName nvarchar(30),
LastName nvarchar(30),
Amount int
)
GO


Run script in SQL management studio to create procedure

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'PollData'
)
DROP PROCEDURE dbo.PollData
GO

CREATE PROCEDURE dbo.PollData
AS
select
MainDataID,
FirstName,
LastName,
Amount
from dbo.MainData
delete from dbo.MainData
GO

insert some sample lines executing
use TESTWCFSQL

insert into MainData (FirstName, LastName, Amount) values
(N'Patrick', N'Cash', 1000),
(N'Ivan', N'Landle', 2000),
(N'Pitt', N'Sampras', 3000)

To test procedure run
exec dbo.PollData
but do not forget to run insert one more time

Now we have ready to run SQL table and procedure

Start Visual Studio 2008 in "Run as administrator" mode
From Visual Studio 2008 create new BizTalk project, name it
WCFSQLTest

Insert Item from "Add generated item"

From templates select "Add adapter metadata"

Then select WCF-SQL

In Binding select "sqlBindng"
Press "Configure"
for parameters select
Security
Client Credential Type: Windows
URI properties
InboundID: ID1
Server: localhost
Initial catalog: TestWCFSQL
Binding properties
Inbound operation type: TypedPolling
Poll data available statement: select top(1) * from MainData
Polling statement: exec dbo.PollData

Press OK

Press Connect

Check that you connected to DB
select Service(inbound operations)
in Select a category choose TypedPolling than Add dbo.PollData

Press OK

Schema file and orchestration file will be generated

Rename orchestration WCFSQLOrchestration1.odx
Rename schema definition file to InboundSchema.xsd
In orchestration create message, name it SQLPoll
Set message type: To multipart message type which was auto created

On PortSurface start Port Configuration Wizard
Name new port: SQLRcvPort
press Next

select Use existing Port Type
select port type created by wizard
leave default settings, press Next
leave default settings, press Next

Drag and drop Receive shape to orchestration
Select message SQLPoll for it and connect with newly created Port

Start Wizard to create output port
Name it FileSndPort leave default settings except selecting that you will use the port to send information

Create new Send shape , assign SQLPoll message to it and connect it with FileSndPort
Your orchestration should look like this


Now from project properties change Application name to TestSQLWCF and sign the DLL

Deploy the project.

To create WCF-SQL port
In BizTalk Administration Console
Select newly created Application TestSQLWCF
Create Receive Port and Location
in location set WCF-SQL adapter configure it with the same settings you setup in
wizard while creating Generated Item

Then create Send Port and assign it to a folder on your disk

Bind logical ports and physical ports as shown on the picture


Start Application

in output directory check for new files

Comments

Anonymous said…
Hi Armen,

I'm trying to setup database polling with BizTalk 2010 and SQL Server 2008 R2.

When I follow the steps in your post I'm getting stuck at the following step.

Check that you connected to DB
select Service(inbound operations)
in Select a category choose TypedPolling than Add dbo.PollData

At this stage, when I select Service(inbound operations) and category I don't see dbo.PollData anywhere. There are three items in the Available categories and operations list.
* Polling
* Typed Polling
* Notification

Any ideas?

Thanks...
Hi
You should select Typed Polling
Regis Benedito said…
Hi! Biztalk genererates three schemas. TypedPolling, ArrayOfTypedPollingResultSet0,TypedPollingResultSet0. Which one i choose?
EMNA said…
HI Armen ,
I followed these steps but at the end the output record is still empty
I did not understand what I must do

any ideas ?

thanks
Hi
just check if your proc returns records. You can also use SQL profiler to monitor returned records
Rajasekhar said…
Hi it is really useful.

Thanks
Regards,
Rajasekhar.R
Bob said…
This is a really useful post. Thanks for making it!
Welcome !
I am happy that my articles can help you.
harry said…
Very useful post, Armen !
Thanks
technololy said…
worked like a charm..lolade from nigeria

Popular posts from this blog

SQL query timeout from application but works fast from SSMS