BizTalk 2006: Default SQL Adapter – Custom SQL Adapter (PART 3)

Hi Folks!

This article pertains to BizTalk 2006 and not BizTalk 2006 R2, since R2 has WCF which is a better alternative to the default BizTalk Adapters. I have shown this example for those of us that do not have R2.

The SQL Send Adapter that ships with BizTalk have some limitations, what I wanted was a SQL adapter that can send a message to a store procedure, thus storing XML data directory to the SQL Server 2005 table. We all know the SQL Receive Adapter can call a stored procedure, but surely a send adapter should have the same features without using Updategram etc, see Part 1 and Part 2 of this blog series.

So imagine a SQL Send Port where you can give:

  1. SQL Server Connection String
  2. Stored Procedure name
  3. BizTalk message
  4. Optional Stored Procedure parameters and even promoted properties

So the UI for the default adapter is like this:

As you can see this is no good for meeting our requirements, here is a quick sneak preview of the Custom SQL Adapter Properties Page:

So basically, what I did was get the sample Transaction Adapter from the BizTalk SDK folder and did some enhancements to it. Excuse my link, since my machine is 64 Bit, so your location of program files will be without the (x86).

C:\Program Files (x86)\Microsoft BizTalk Server 2006\SDK\Samples\AdaptersDevelopment\TransactionalAdapter

This Adapter solution provided with the SDK has all the tools we need to get started. I have provided the source code for the modified version of this SDK adapter for you to download.

What is important to realize when creating a Custom SQL Send Adapter is this:

  1. GUID for the Receive Handler
  2. GUID for the Send Handler
  3. GUID for the AppID
  4. Guide for the CLSID
  5. SQLTransmitLocation.xsd – Modify the Component to show a dialog window when setting the properties in the Custom Adapter
  6. SQLAsyncBatch.cs file contains the calling code to the procedure and passes the Message as a stream and other parameters you specify in the dialog window.
  7. Registry file to register the components.

 

Note: The GUID’S I have put in the sample code will work out the box, however, if you want to customize yours further and reuse the code and have multiple custom adapters, then ensure you change the GUID’s.

To install this Adapter you will need to download the source code then:

  1. Open the TransactionalAdmin.reg file and modify the GUID’s (OPTIONAL)
  2. There are two GUID’s that you modify in the TransactionalAdmin.reg file that will need to be updated in the Source Code, in the Registration folder (OPTIONAL)
    1. "OutboundEngineCLSID"="{D2697EC5-414F-40DD-90F5-904EB5A54674}" – Also update your GUID in the code file: SQLTransmitter.cs
    2. "InboundEngineCLSID"="{c319016b-25bd-4162-935a-694081e146c7}" – Also update your GUID in the code file: SQLReceiver.cs
  3. There is two GUID’s in the TransactionalAdmin.reg file that needs to be updated to be unique: (OPTIONAL)

[HKEY_CLASSES_ROOT\CLSID\{5794bde9-e834-4e11-b33c-db7b8eff8bf4}]

@="SQL Adapter Class" "AppID"="{6e33a268-b437-4c66-9d5c-4a7a5169877a}"

 

[HKEY_CLASSES_ROOT\CLSID\{5794bde9-e834-4e11-b33c-db7b8eff8bf4}\Implemented Categories]

[HKEY_CLASSES_ROOT\CLSID\{5794bde9-e834-4e11-b33c-db7b8eff8bf4}\Implemented Categories\{7F46FC3E-3C2C-405B-A47F-8D17942BA8F9}]

 

Do not update the GUID in Red.

Tip: Use this site to make GUID’s:

http://www.guidgenerator.com/online-guid-generator.aspx

Cool, now that we got the nasty GUID’s out of the way, let’s get going. Build the application to generate the dll’s. You will have the following dll’s:

  1. SQLAdapter\Admin\bin\Debug\MMIT.BizTalk.Adapter.SQLAdmin.dll (This is used at Design Time)
  2. SQLAdapter\Runtime\bin\Debug\MMIT.BizTalk.Adapter.SQL.dll
  3. BaseAdapter\bin\Debug\MMIT.BizTalk.Adapter.Common.dll

Drag the above DLL’S into the GAC. I think the only one needed in the GAC is MMIT.BizTalk.Adapter.Common.dll, but I put all three.

Ensure the path in the registry file points to the location of these dll’s

"InboundAssemblyPath"="C:\\ProtoTypes\\CustomSQLAdapter\\SQLAdapter\\Runtime\\bin\\Debug\\MMIT.BizTalk.Adapter.SQL.dll"

"OutboundAssemblyPath"="C:\\ProtoTypes\\CustomSQLAdapter\\SQLAdapter\\Runtime\\bin\\Debug\\MMIT.BizTalk.Adapter.SQL.dll"

"AdapterMgmtAssemblyPath"="C:\\ProtoTypes\\CustomSQLAdapter\\SQLAdapter\\Admin\\bin\\Debug\\MMIT.BizTalk.Adapter.SQLAdmin.dll"

 

Of course you can create release versions of these dll’s if you like as well.

One this is done, you then need to:

  1. Locate the TransactionalAdmin.reg file and double click it to register the adapter in the registry
  2. Open the BizTalk Server Admin Console and add the adapter:

Once done you will get a send and receive handler for it:

Now, you are ready to use the Adapter in a send port.

Notice this custom SEND SQL Adapter has parameters and a store procedure!

Fill in the procedure name.

Click on parameters and enter them in:

 

The above dialog box is not so smart, I have not had time to perfect it, but you can see that if the PropertyType is a message, you do not need to fill in the others and the same is true for other types respectively. See the code below to see how it is read so you know what values to fill for what types:

The code comes from:

SQLAsyncBatch.cs

private void SendMessage(IBaseMessage message, SQLTransmitProperties properties)

{

TextReader reader = new StreamReader(message.BodyPart.Data, Encoding.UTF8);

string storedProcedureName = properties.CmdText;

string connectionString = properties.ConnectionString;

 

using (SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

 

SqlCommand command = new SqlCommand(storedProcedureName, connection);

command.CommandType = CommandType.StoredProcedure;

//TODO: Clean up this mess.

foreach(string parameter in properties.Parameters.Split(Environment.NewLine.ToCharArray()))

{

if (parameter != "")

{

string[] paramFields = parameter.Split(‘;’);

string name = paramFields[0];

string type = paramFields[1];

string value = paramFields[2];

string _namespace = paramFields[3];

switch (type)

{

case "Message":

command.Parameters.Add(new SqlParameter("@" + name, reader.ReadToEnd()));

break;

case "Constant":

command.Parameters.Add(new SqlParameter("@" + name, value));

break;

case "Promoted":

command.Parameters.Add(

new SqlParameter("@" + name, message.Context.Read(value, _namespace)));

break;

}

}

}

command.ExecuteScalar();

}

}

 

So the beauty of this is now a BizTalk message can be sent directly to SQL table with XML Data Type:

USE [Test]

GO

/****** Object: Table [dbo].[cs] Script Date: 04/28/2008 15:49:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[cs](

    [cs_id] [bigint] IDENTITY(1,1) NOT NULL,

    [xml_data] [xml] NULL,

    [source_name] [nvarchar](50) NULL,

    [date_inserted] [smalldatetime] NULL DEFAULT (getdate()),

    [stage] [nvarchar](20) NULL,

    [status] [nvarchar](20) NULL

) ON [PRIMARY]

 

Here is a test stored procedure to use:

USE [Test]

GO

/****** Object: StoredProcedure [dbo].[set_records] Script Date: 04/28/2008 15:50:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[set_records] @Data XML–, @status NVARCHAR(20)

AS

Declare @status nvarchar(20)

set @status = ‘Processed’

    IF @Data IS NULL OR @status IS NULL

        RETURN

 

    –move to next record if status = success

    IF @status = ‘Processed’

    BEGIN

        UPDATE dbo.cs

        SET xml_data = @Data,

            stage = dbo.myroute(cs.source_name, cs.stage, cs.status),

            status = case dbo.myroute(cs.source_name, cs.stage, cs.status) WHEN cs.stage THEN @status ELSE N‘Unprocessed’ END

        FROM dbo.cs cs

            JOIN @Data.nodes(‘declare default element namespace "http://SQLAdapterGetRecords.Record";Record/Row/Metadata’) as tab(col)

        ON tab.col.value(‘text()[1]’, ‘BIGINT’) = cs.cs_id

    END

    ELSE

    BEGIN

        UPDATE dbo.cs

        SET status = @status

        FROM dbo.cs cs

            JOIN @Data.nodes(‘declare default element namespace "http://SQLAdapterGetRecords.Record";Record/Row/Metadata’) as tab(col)

        ON tab.col.value(‘text()[1]’, ‘BIGINT’) = cs.cs_id

    END

 

 

 

To use the above store procedure the only parameters to pass is the message, since the code does this:

case "Message":

command.Parameters.Add(new SqlParameter("@" + name, reader.ReadToEnd()));

So the result looks like this:

 

Now, you can spend time customising the form and make it more intelligent of course, and grey out the areas that are not needed when selecting a Type.

The Form is located in the ComponentModel folder.

 

The SQLTransmitLocation.xsd is used to hook up the Design Time event to the custom form:

– <xs:element minOccurs="1" default="" name="parameters" type="xs:string">

– <xs:annotation>

– <xs:appinfo>

– <baf:designer>

  <baf:displayname _locID="">Parameters</baf:displayname>

  <baf:description _locID="">The stored procedure parameters.</baf:description>

  <baf:editor>MMIT.BizTalk.Adapter.SQLAdmin.ComponentModel.ParameterUITypeEditor, MMIT.BizTalk.Adapter.SQLAdmin, Version=1.0.0.0, Culture=neutral, PublicKeyToken=37c5f231b3e0efe3</baf:editor>

  </baf:designer>

  </xs:appinfo>

  </xs:annotation>

  </xs:element>

  </xs:sequence>

  </xs:complexType>

  </xs:element>

  </xs:schema>

 

This all looks a bit confusing, but the best approach is to download the code and get it working and play with it!

When you download the code, go to the project properties for each project and point the code to a strong assembly key, use this command to create one in the command prompt window (Visual Studio Command Prompt):

Sn –k mykey.snk

 

The program has many improvements that can be made, but I feel there is more than enough information in here to get you going! I look forward to your feedback or ideas.

 

Hopefully many of you can get to BizTalk R2, and use WCF instead.

Special thanks to my colleague Chris Hagens for creating the Form properties (UITypeEditor)!

Good Luck.

Download Source Code

NOTE: USE WCF-CUSTOM SQL now, these blogs are from a couple of years ago 🙂 I lost my old blog server 😦

Advertisement
  • Uncategorized

Leave a Reply

Please log in using one of these methods to post your comment:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s