Transactional Adapter (BizTalk Server Sample)
The Transactional Adapter sample demonstrates how to create and use an explicit Microsoft Distributed Transaction Coordinator (MSDTC) transaction against a database during processing of a BizTalk Server message.
This sample contains a receive adapter which runs a SQL statement at a user-specified interval to obtain data from a SQL Server database using an MSDTC transaction. The data is then submitted to the BizTalk Server MessageBox database in the form of a message under the context of the same transaction.
The corresponding send adapter runs a user-specified SQL stored procedure using input from a BizTalk message in the context of a transaction. It uses specific data from that message to locate and delete the corresponding message from the Message Box database under that same transaction.
This sample has two projects in its solution. The first is the administrative project (Admin) which is used prior to runtime to allow a user to configure the receive locations and send ports that use this adapter. The second is the runtime project (Runtime) that runs when the send and receive adapters are executing.
The sample is located in the following SDK location:
<Samples Path>\Samples\AdaptersDevelopment\TransactionalAdapter. The administrative configuration project is located in the \Admin folder, while the runtime project exists in the \Runtime folder.
The following table shows the files in this sample and describes their purpose.
|Admin Project Filename||Admin Project File Description|
|TransactionalAdmin.csproj||Adapter administrative project file used for pre-runtime configuration|
|TransactionalReceiveHandler.xsd||XSD for receive handler properties|
|TransactionalReceiveLocation.xsd||XSD for receive location properties|
|TransactionalTransmitLocation.xsd||XSD for transmit location properties|
|TransactionalTransmitHandler.xsd||XSD for transmit handler properties|
|TransactionalAdapterManagement.cs||Adapter configuration management. Contains GetConfigSchema which is invoked by the BizTalk Adapter Framework to return an XSD configuration schema for each of the (four) possible configuration types it supports.|
|Runtime Project Filename||Runtime Project File Description|
|Transactional.csproj||Adapter runtime project file|
|TransactionalAsyncBatch.cs||Asynchronous implementation of the send part of the adapter|
|TransactionalDeleteBatch.cs||Deletes a batch of messages and votes to commit or abort a transaction|
|TransactionalProperties.cs||Extracts and sets configuration properties|
|TransactionalReceiver.cs||Creates and manages receive endpoints|
|TransactionalReceiverEndpoint.cs||Actual listening or polling for each receive location|
|TransactionalTransmitter.cs||Accepts a batch of messages from the Messaging Engine to be transmitted|
This sample is meant as a framework for you to use in creating custom send and receive adapters using explicit transactions.
If the BizTalk installation is 64-bit or the location of installation is modified, OutboundAssemblyPath, InboundAssemblyPath, AdapterMgmtAssemblyPath would need to be changed accordingly.
Start Visual Studio Command Prompt.
At the command prompt, type the following and then press enter:
cd \Program Files\Microsoft BizTalk Server <version>\SDK\Samples\AdaptersDevelopment\TransactionalAdapter\Runtime
At the command prompt, type the following and then press enter:
sn –k TransactionalAdapter.snk
At the command prompt, type exit, and then press enter to close the command prompt window.
Click Start, point to All Programs, point to Accessories, and then click Windows Explorer.
Browse to drive:\Program Files\Microsoft BizTalk Server 2xxxSDK\Samples\AdaptersDevelopment\TransactionalAdapter, and double-click TransactionalAdapter.sln to open this solution in Visual Studio.
To build both of the Transactional Adapter projects (Admin and Runtime) in Solution Explorer, right-click Solution TransactionalAdapter, and then click Rebuild.
In Windows Explorer, navigate to drive:\Program Files\Microsoft BizTalk Server 2xxxSDK\Samples\AdaptersDevelopment\TransactionalAdapter\Admin.
To add the transactional adapter data to the registry, double-click TransactionalAdmin.reg.
TransactionalAdmin.reg includes hard-coded paths to C:\Program Files\Microsoft BizTalk Server\. If you did not install BizTalk Server in the default location or if you upgraded your BizTalk Server installation from a previous version, you must modify the file TransactionalAdmin.reg with the appropriate paths. Update the paths associated with the "InboundAssemblyPath", "OutboundAssemblyPath", and "AdapterMgmtAssemblyPath" values to point to the correct location of the specified files.
If you install BizTalk on a 64 bit machine, change all instances of the HKEY_CLASSES_ROOT\CLSID\ registry entry to HKEY_CLASSES_ROOT\Wow6432Node\CLSID\ in the TransactionalAdmin.reg registry file.
In the Registry Editor dialog box, click Yes to add the sample adapter to the registry, and then click OK.
To close Windows Explorer, click File, and then click Close.
Click the Start menu, select All Programs, select Microsoft BizTalk Server 20xx, and then select BizTalk Server Administration.
In the BizTalk Server Administration, expand the BizTalk Server Administration tree, expand the BizTalk Group tree, and then expand the Platform Settings tree.
Right-click Adapters, click New, and then click Adapter.
In the Adapter Properties dialog box, do the following.
Use this To do this Name Type TransactionalAdapter. Adapter Select Txn from the drop-down list. This entry appears as a result of running the TransactionalAdmin.reg file previously. Description Type Sample Transactional Adapter.
Click OK. The adapter now appears in the list of adapters in the right window of the BizTalk Administration console.
Expand the BizTalk Group[server name] node in BizTalk Server Administration, expand Applications node, expand BizTalk Application 1 node.
Right-click Receive Ports, and then click New, select One-Way Receive Port.
For Name, enter TxnReceivePort1, and then click OK.
Right-click the Receive Locations node, click New, and then select One-Way Receive Location.
In theSelect a Receive Port dialog box, select TxnReceivePort1, and then click OK.
In the Receive Location Properties dialog box, under the General tab, enter TxnReceiveLocation1 for Name. Ensure the Receive Port label displays TxnReceivePort1.
In theType dropdown list box, in the Transport frame, select TransactionalAdapter.
In the Receive****Pipeline box, ensure PassThruReceive is selected. Leave the rest of the properties with their default settings.
Click the Configure button next to the Type drop down box. This displays a dialog specific to this adapter. Specify the following as you see appropriate, then click OK.
Property Setting Connection String The SQL database connection string used to connect and authenticate with the Northwind database. We will later run a SQL script that will use this database. Command Text The SQL statements that are executed against the Northwind database to obtain data to put into a BizTalk Message. Cookie Comprises part of the URI so enter a unique value, such as the name of the receive location, for instance: TxnReceiveLocation1. Polling Interval Unit The number of units of time measure for the polling of the data. Set this to seconds. Polling Interval The unit of time measure for the polling of the data. Set this to 15 seconds.
Click OK to close the Configure dialog box, then OK again to close the Receive Location Properties dialog box to return to the BizTalk Server Administration.
With the BizTalk Application 1 node still expanded, right-click Send Ports, and then click New, and select Static One-Way Send Port.
In the Name field, enter TxnSendPort1.
In the Transport frame, in the Type drop-down list, selectTransactionalAdapter
In the Send Pipeline box, ensure PassThruTransmit is selected.
Click the Configure button next to the transport drop-down list.In the dialog that appears specify the following as you see appropriate, then click OK.
Property Setting Cookie Comprises part of the URI - Enter a unique value here such as the name of the receive location, for instance: TxnSendPort1. Connection String The SQL database connection string used to connect and authenticate with the Northwind database. It will most likely be the same one used to configure the TxnReceiveLocation1 receive location. Stored Procedure The stored procedure name that gets executed to poll the database - sp_txnProc. The body of the BizTalk message is given to that stored procedure as a string parameter called @Data. For example, the user will in this case later configure the stored procedure with the name sp_txnProc. The runtime of the adapter would execute the equivalent of this call to the database.
exec sp_txnProc @Data = “the contents of the BizTalk message”
In the left navigation pane, click Filter.
In the filter expression editor, enter the following expression to set up a subscription for this send port to receive any messages received by the TxnReceivePort1 receive port.
Enter these values:BTS.ReceivePortName== TxnReceivePort1
Use the default values for the remainder of the adapter properties and select OK.
Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, select SQL Server Management Studio.
In the Connect to Server dialog box, make sure Server Type is set to Database Engine, and enter credentials to authenticate to the database server, then select Connect.
Select the New Query toolbar button and paste the following into the new query window to insert a test table, test data, and a test stored procedure into the Northwind database. Select the Execute toolbar button.
use [Northwind] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[scratch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[scratch] GO CREATE TABLE [dbo].[scratch] ( [id] [int] IDENTITY (1, 1) NOT NULL , [msg] [nvarchar] (4000) NOT NULL ) ON [PRIMARY] GO GRANT SELECT , UPDATE , INSERT ON [dbo].[scratch] TO [public] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_txnProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_txnProc] GO CREATE PROCEDURE [dbo].[sp_txnProc] @Data nvarchar (4000) AS INSERT scratch ( msg ) values ( @Data ) GO GRANT EXECUTE ON [dbo].[sp_txnProc] TO [public] GO
In BizTalk Server Administration, expand the Send Ports node, select the TxnSendPort1 send port, and select Start.
In BizTalk Server Administration, expand the ReceiveLocations node, select the TxnRecieveLocation1 receive location, and then select Enable.
Once the receive location is enabled, it will automatically poll the database at the designated intervals for data.