This is a basic tutorial for using a stored procedure via a WCF-SQL adapter in BizTalk. It's similar to the examples given in the SDK, but eliminates all the extra garbage of creating custom C# assemblies, enabling the SQL FILESTREAM, etc.
You'll need to make sure both the LOB SDK and the Adapter Pack are installed before beginning.
First, create a test database call WCFSQLTest, then create a table and a stored proc, using the following scripts:
CREATE TABLE [dbo].[TestData](
[TestDataId] [int] IDENTITY(1,1) NOT NULL,
[StringColumn] [varchar](50) NOT NULL,
[IntColumn] [int] NOT NULL,
CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
CREATE PROCEDURE [dbo].[ADD_TESTDATA]
SET NOCOUNT ON;
INSERT INTO [WCFSQLTest].[dbo].[TestData]
SELECT [TestDataId] FROM [TestData] where [TestDataId] = (select IDENT_CURRENT('TestData'))
You’ll need to set up the proper permissions on the database, whether it be Windows security, a SQL account, SSO, or whatever you’re preferred method is.
Next, you’ll create a new BizTalk solution called WCF-SQLTest. Go ahead and add your strong name key, and give it an application name of WCF-SQLTest (Properties -> Deployment tab).
Right-click on the project name and select Add -> Add Generated Items… Select ConsumeAdapterService and click Add. With a little luck you’ll get the Consume Adapter Service configuration screen. Select sqlBinding from the drop-down, and enter the following for the URI: “mssql://YourServerName//WCF-SQLTest?”. (Yes, that’s a question mark at the end) Click the Connect button, and you should be connected.
Make sure Client is selected under contract type, and click on Strongly-Typed Procedures. Under “Available categories and operations” you should see your ADD_TESTDATA procedure. Select it and click Add. Assuming your screen looks like the example below, click OK.
After a moment, you should have three new items:
Expand all the nodes in TypedProcedure.dbo.xsd – you’ll see that the ADD_TESTDATA element contains the stored procedure parameters, and the ADD_TESTDATAResponse element contains the return value – in this case, the identity column of the row that was just inserted.
The file WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml contains binding information that will allow you to automatically create a port in the BizTalk Admin Console. We’re not going to use it, however, so you can delete this file.
Next, create an orchestration, calling it whatever makes you happy. Drop 4 shapes onto it – a Receive (don’t forget to activate it!), then a Send, then a Receive, then one last Send. Create two messages, one called Request (using the type WCF_SQLTest.TypedProcedure_dbo.ADD_TESTDATA) and the other called response (using the type WCF_SQLTest.TypedProcedure_dbo.ADD_TESTDATAResponse). The first and second Receive and Send shapes should use the Request message, and the last two should use the Response message.
Now create your ports:
- Name: MessageIn | Direction: Receive
- Name: WCFPort | Direction: Send-Receive
- Name: ResponseOut | Direction: Send
Your orchestration should look like the following:
Now, deploy the solution and open up the BizTalk Admin Console. You’ll need to create 3 ports, but first go ahead and create a test directory for file I/O (I suggest C:\TestLocation\Request and C:\TestLocation\Response).
Create your receive port, where you’ll drop the “trigger” message:
- Name: MessageIn
- Location Type: FILE
- Receive pipeline: XMLReceive
- URI: C:\TestLocation\Request\*.xml
Next, create the send port where you’ll drop the XML file with the response from the database:
- Name: ResponseOut
- Type: FILE
- URI: C:\TestLocation\Response\%MessageID%.xml
- Send pipeline: XML Transmit
- Transport Advanced Options -> Retry count: 0 (why wait around if there’s a problem?)
Finally, create your WCF port:
- Name: WCFPort
- Type: WCF-Custom
- Send/Receive pipelines: XMLTransmit/XMLReceive
Click the configure button, and make the following entries:
- Address (URI): mssql://D3DZ34J1//WCFSQLTest?
- Action: TypedProcedure/dbo/ADD_TESTDATA
- Either use SSO, or enter a Username and Password
Start up the application in the BizTalk Admin Console. You’ll need to create an xml file to drop in; try this:
<stringColumn>Row Number 1</stringColumn>
Drop this file into C:\TestLocation\Request, and assuming nothing goes wrong, you should get an xml file in the response directory that looks something like this:
<?xml version="1.0" encoding="utf-8" ?>
Notice that the TestDataId element is the value of the identity column of the row you just inserted.