Alternatives to BizTalk SQL Adapters

06.15.11

  Everyone who has done BizTalk development is familiar with the SQL Adapter options for reading and writing information to a database.  While there are some great benefits to using the SQL Adapter in certain instances (such as reading data from a stored procedure on a timed bases), but for most solutions that have database interactions SQL Adapters (even in BizTalk 2010) are not the most appropriate option.  They are time consuming to work with, require a number of weighty schemas to be created, and are difficult to keep in sync as the data model changes.  In reality, reading and writing to a database occurs very frequently, and should be quick and easy to implement.

This post will look at an alternative approach to reading & writing data to SQL Server.  Once you have the pattern in place, you should be able to develop additional reads/writes, with all of the components, in a matter of a few minutes.

Assume that you have an XML document in a BizTalk orchestration that you want to write to a SQL database table for archival purposes, and that the target table looks like the following:

In order to write the document to this table from the orchestration, the following items will be created:

  1. A C# Class Library containing a method that creates a connection and writes to the database.
  2. A stored procedure on SQL (to keep inline SQL out of the C# Class Library, and to aid in long term maintenance without the need to recompile code)
  3. An expression shape in the orchestration that will call the C# Class Library method, passing in the appropriate parameters.

First, look at the stored procedure that would be required.  This stored procedure needs one input parameter – the XML to be written – and can be easily developed and tested from Query Analyzer.  An example of this is as follows:

Executing this stored procedure with sample XML results in the following entry in the table:

Second, now that the stored procedure is in place, the C# Class Library can be created.  Take the following steps:

  1. Create a new C# (or VB.NET) Class Library project in Visual Studio
  2. Add a strong name key (so it can be installed in the GAC – required by BizTalk)
  3. Add in an appropriate namespace and give it a good class name
  4. Mark the class as Serializable (this is required in order to be able to call the object from an expression shape that is not inside of an Atomic scope)
  5. Create the method

A sample version of this looks as follows:

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
 
namespace XXXX.BizTalk.Helper
{
    [Serializable]
    public class DataAccess
    {
        
        public void ArchiveData(XmlDocument xmlSource, string strConnectionString)
        {
            SqlConnection sqlConnection = new SqlConnection(strConnectionString);
            string strStoredProcedure = "spInsertArchive";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = strStoredProcedure;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlConnection.Open();
 
            sqlParameter = new SqlParameter();
            sqlParameter.ParameterName = "@xmlSourceData";
            sqlParameter.SqlDbType = SqlDbType.Xml;
            sqlParameter.Direction = ParameterDirection.Input;
            sqlParameter.Value = new XmlNodeReader(xmlSource);
            sqlCommand.Parameters.Add(sqlParameter);
              
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }
       }
}

Once coded, compile the class and get the resulting .NET assembly (DLL).  This will be referenced in the BizTalk solution.

Finally, in the BizTalk solution, take these steps:

  1. Reference the new Class Library assembly file (the DLL) in the BizTalk solution.
  2. Create a new orchestration variable and set it to the .NET type in your class library (it will appear as an option in the list). 

In this case, the variable is defined as follows:

Then, you will:

  1. Create a new orchestration variable of type System.Xml.XmlDocument to hold the message you want to archive.
  2. In an expression shape within the orchestration, set the value of the new XML parameter and call the class (as follows):
// the following is a connection string, which can be defined in a configuration file for ease of modification after orchestration is deployed
strConnectionString = “Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;”
 
// this assumes that you have a message in the orchestration called msgBizTalkMessage that contains the data you want to archive
xmlDoc = (System.Xml.XmlDocument)msgBizTalkMessage; 
 
objHelper.ArchiveData(xmlDoc, strConnectionString);

« Back to index