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