BizTalk - simple WCF tutorial for using a stored procedure

Comments (12) | Share

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
(
 [TestDataId] ASC
)
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[ADD_TESTDATA]
 @stringColumn varchar(50),
 @intColumn int
AS
BEGIN
 SET NOCOUNT ON;

 INSERT INTO [WCFSQLTest].[dbo].[TestData]
           ([StringColumn]
           ,[IntColumn])
     VALUES
           (@stringColumn
           ,@intColumn)
 SELECT [TestDataId] FROM [TestData] where [TestDataId] = (select IDENT_CURRENT('TestData'))
END

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:

• TypedProcedure.dbo.xsd
• ProcedureResultSet.dbo.ADD_TESTDATA.xsd
• WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml

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:

1. Name: MessageIn | Direction: Receive
2. Name: WCFPort | Direction: Send-Receive
3. 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:

General tab:

• Address (URI): mssql://D3DZ34J1//WCFSQLTest?
• Action: TypedProcedure/dbo/ADD_TESTDATA

Binding tab:

• Binding Type: sqlBinding

Credentials tab:

• 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:

<ADD_TESTDATA xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
     <stringColumn>Row Number 1</stringColumn>
     <intColumn>111</intColumn> 
</ADD_TESTDATA>

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" ?>
<ADD_TESTDATAResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
     <StoredProcedureResultSet0>
          <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/ADD_TESTDATA">
               <TestDataId>1</TestDataId> 
          </StoredProcedureResultSet0>
     </StoredProcedureResultSet0>
     <ReturnValue>0</ReturnValue> 
</ADD_TESTDATAResponse>

Notice that the TestDataId element is the value of the identity column of the row you just inserted.