BizTalk - simple WCF tutorial for using a stored procedure

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.
 

Print | posted @ Tuesday, October 19, 2010 1:15 PM

Comments on this entry:

Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by sathish at 12/21/2010 7:00 AM

Hi I have created the same but i am getting the below error
use of unconstructed message 'Response'
'an activatable receive must be the first executable statement in a service'


Thanks
Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by Bill at 12/21/2010 8:41 AM

Make sure none of your other receive ports have Activate set to True; that's usually the cause of that particular error...
Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by Cary Campbell at 1/25/2011 9:17 AM

Hi I'm getting the following message. What does it mean? Thanks

System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed. ---> System.Runtime.InteropServices.COMException: The MSDTC transaction manager was unable to push the transaction to the destination transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn't have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers. (Exception from HRESULT: 0x8004D02A) at System.Transactions.Oletx.ITransactionShim.Export(UInt32 whereaboutsSize, Byte[] whereabouts, Int32& cookieIndex, UInt32& cookieSize, CoTaskMemHandle& cookieBuffer) at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) --- End of inner exception stack trace --- Server stack trace: at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)
Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by Bill at 1/28/2011 12:18 PM

Probably one of two things:
1) Your firewall is blocking MSDTC transactions
2) MSDTC isn't configured properly on one of the two machines

Try running the DTCPing tool to test your connection:
http://blogs.msdn.com/b/distributedservices/archive/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool.aspx
Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by Sol at 8/24/2011 5:31 PM

Worked like a charm! Thanks for sample!
Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by Jim at 9/9/2011 10:19 AM

MANY Hallelujahs were shouted when I discovered and successfully ran this tutorial!!! Thank you SO MUCH!
Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by Madhukar at 5/22/2012 1:02 AM

Thank you very much, it’s very helpful, I tried many hours to figure out how we can manage request and response using WCF. Finally I got this and its working fine. Is there any difference generating database schema as "Add Adaptor" or "Consume Adaptor"?

Thanks a lot,
Madhukar
Gravatar # re: BizTalk - simple WCF tutorial for using a stored procedure
by silverbugg at 9/27/2013 6:05 PM

So do you still have to get at the contents with XPath or by using the WCF addapter is the message response any more intelligent. For instance I have my TypedProcedures Output Message as a message in my orchestration. Can I get to the Value of TestDataId by SQLResponse.parameters.TestDataId or SQLResponse.parameters.StoredProcedureResultSet0.StoredProcedureResultSet0.TestDataId? Or do I have to do the whole stinky xpath mess to get to that value?
Post A Comment
Title:
Name:
Email:
Comment:
Verification: