Polling using WCF-SQL Part 1

1)Add a Table to a database on your local SQL Server installation. I call the table 'Test' and the database, 'CPNI'.

2)Create a new project in VS 2010, an empty biztalk project is best. I called mine 'BlogStoredProc'. 

3)Then go to 'Add Generated Items' and then 'Add Adapter Metadata'. I am using Biztalk 2010 and I choose to use the WCF-SQL adapter. If you are using an earlier Biztalk and see another option that sounds similar (something like 'Consume Adapter Service' instead of 'Add Adapter Metadata') then you may choose that and end up in the same place.  In the 'Consume Adapter Service' window I select sqlBinding. From there I see a button to 'Configure URI'. Click it.

4)Configure URI as such:

If your instance of SQL Server has the default name 'MSSQLSERVER' then you can leave 'InstanceName' blank as I have. This is common. The other fields have a good explanation of what their value should be so I wont detail them.

In the Binding Properties tab, you will need to change 3 things. The first is InboundOperation Type, which will need to be Typed Polling.'Typed Polling' means we have regular sql procedures that are expected to 'poll' data from a database and we need our procedure to have schemas that are known ahead of time(also known as a 'Strongly Typed' procedure). Regular polling is for when the schema comes along with the polling message. For more information on the binding properties see this article:
http://social.technet.microsoft.com/wiki/contents/articles/3470.typed-polling-with-wcf-sql-adapter-best-practices-and-troubleshooting-tips.aspx

The other two things you will need to fill in here are the 'Polled DataAvailable Statement' and 'Polling Statement' which sound very similar but from the above article you can tell they are not. One needs to return a nonegative integer only (the SQL code in the 'Polled Data Available Statement' field) and the other returns the data(the SQL code in the 'Polling Statement' field). When the SQL in 'Polled Data Available Statement' executes and returns a positive integer, then the 'Polling Statement' executes. If 'Polled Data Available Statement' returns 0 then the 'Polling Statement' doesn't execute. I have done these three things in the example window to the left. 

When you are done with these two tabs click OK. 


5) In the Consume Adapter Service click Connect and then select contract type for Inbound Operations(since polling is inbound). Under category I select Typed Polling, and then you should notice that Typed Polling shows up in the 'Added categories and operations' in the box below. Click OK

At this point an orchestration and a schema have been created for me. The Table 'Test' that I created has 4 fields. You can see them below since the select statement in the binding tab that I gave it was used to generate these fields;