Geeks With Blogs

Bill Osuch - Random geek notes

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.
 

Posted on Tuesday, October 19, 2010 1:15 PM BizTalk | Back to top


Comments on this post: BizTalk - simple WCF tutorial for using a stored procedure

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
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
Left by sathish on Dec 21, 2010 7:00 AM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
Make sure none of your other receive ports have Activate set to True; that's usually the cause of that particular error...
Left by Bill on Dec 21, 2010 8:41 AM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
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)
Left by Cary Campbell on Jan 25, 2011 9:17 AM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
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
Left by Bill on Jan 28, 2011 12:18 PM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
Worked like a charm! Thanks for sample!
Left by Sol on Aug 24, 2011 5:31 PM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
MANY Hallelujahs were shouted when I discovered and successfully ran this tutorial!!! Thank you SO MUCH!
Left by Jim on Sep 09, 2011 10:19 AM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
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
Left by Madhukar on May 22, 2012 1:02 AM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
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?
Left by silverbugg on Sep 27, 2013 6:05 PM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
Hi Bill,

I have created the same but getting below error
Microsoft.ServiceModel.Channels.Common.ConnectionException: Login failed for user System.Data.SqlClient.SqlException: Login failed for user 'kpit\nagarajk'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(Serv
Left by Nagaraja Karur on Feb 10, 2015 8:06 AM

# re: BizTalk - simple WCF tutorial for using a stored procedure
Requesting Gravatar...
Hello, I try to put testing file to input folder and I got error:


URI: "C:\Biztalk_projekty\WCF_XML2SQL\request\*.xml" Reason: Finding the document specification by message type "http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo#ADD_TESTDATA" failed. Verify the schema deployed properly.

Any idea? Thanks a lot
Left by gips on Jun 06, 2015 6:41 AM

Your comment:
 (will show your gravatar)


Copyright © Bill Osuch | Powered by: GeeksWithBlogs.net | Join free