Building a BizTalk Pipeline Content Enricher with SQL Server 2005

By Jeffrey Juday

Pipeline Overview

I leveraged code from my Pipeline Channel Stack applications. For more details on the Pipeline Channel Stack, check out the article "BizTalk Pipeline Dreams Become Reality."

I changed the Channel assembly from the article above and added the following classes to the assembly:

ContentEnricherMessageReader handles reading the XML passed to the Pipeline.
StandardFileStreamBodyHandler is a new BodyHandler for the Channel Stack Pipeline classes.
StreamFromSQLQueryBuilder works with the StreamWritingHandler class to query the SQL Server database and write the data to a Stream.
StreamWritingHandler handles writing the Stream to a temporary file.

The entire Stream creation process is captured in the RetrieveMessage function of the TestPipelineChannel class. Below is the RetrieveMessage function with some declarations and debug code removed.

ContentEnricherMessageReader cr =
   new ContentEnricherMessageReader(_currentMessage);

StreamFromSQLQueryBuilder qry;
StandardFileStreamBodyHandler streamBH;
Stream stream;
string pathToFile = "";
PipelineMessage msg;
string fileDir = "";

cr.Read();

Directory.CreateDirectory(fileDir);

qry = new StreamFromSQLQueryBuilder(cr.SQLConnectString,
   cr.SQLQuery, pathToFile);

streamBH = new StandardFileStreamBodyHandler();

stream = qry.MakeStream();
streamBH.AcceptMessageBody(stream);

msg = _currentMessage.BizTalkContext.CreateMessage(streamBH);

return(msg);

ContentEnricherMessageReader wraps access to the incoming XML message Stream. Using the .NET XmlDocument class, this class exposes a set of properties for use by other classes hiding the messy details of accessing the underlying XML.

Most of the work is done in the StreamFromSQLQueryBuilder, so take a closer look at this class.

StreamFromSQLQueryBuilder

The MakeStream function in StreamFromSQLQueryBuilder creates the Stream consumed later by BizTalk. The body of the function appears below.

SqlDataReader dr;
SqlCommand cmd;
SqlConnection conn = new SqlConnection(_sqlConnectString);

conn.Open();

cmd = new SqlCommand(_sqlQuery, conn);

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

_writeHandler.Open();

while (dr.Read())
{
   _writeHandler.WriteRecord(dr[0].ToString());
}

dr.Close();

_writeHandler.Finish();

return (_writeHandler.TheStream);

SqlDataReader is the recommended way to return and iterate through a group of SQL records quickly. As stated earlier, StreamWritingHandler wraps access to the underlying FileStream.

With the Stream created, the Pipeline returns the Stream to BizTalk.

Sample Caveats

The included sample code is a prototype I created to test the architectural ideas.

Earlier, I mentioned PGP encryptions requirements. In my actual solution, I had a separate encryption Channel that encrypted the file and returned the encrypted Stream back to BizTalk.

Conclusion

Allocating resources just before you use them often results in better application performance. In the messaging and business process management world, the Content Enricher Design Pattern epitomizes the just-in-time allocation philosophy. Along with SQL Server 2005 and the BizTalk FTP Adapter, I showed you how to implement the Content Enricher in a BizTalk Pipeline.

Resources

"Data Points: Data Access Strategies Using ADO.NET and SQL"
http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/
BizTalk 2006 online help

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.

Page 2 of 2