BizTalk Server 2010: How to Insert Image In SQL Through Orchestration

Article
1/17/2024

Introduction

In an Integration scenario when dealing with files of type image, pdf, audio, video and other multimedia type it is preferred to use File systems and have reference of it stored, but there are requirements where this file need to be stored in a database as BLOB (Binary Large Object) type.

Note: BLOBs (Image files, Video files etc.) smaller than 256KB are more efficiently handled by a database, while a filesystem is most efficient for those greater than 1MB. Of course, this will vary between different databases and file systems.

This article takes an Image file as an example and intends to walk you through the steps required to insert Image files in SQL database through Orchestration using WCF- SQL adapter.

Scenario

The Scenario used in this article is as follows :

Item message is received in an XML file and along with the Item image at a file location.
Both the item info and image needs to be stored in a database using BizTalk.

Solution

Steps to implement the above requirement.

Step 1: Create a database along with Table to store the item information and Image.
Step 2: Generate the schema.
Step 3: Create a Helper Class to convert the file into bytes.
Step 4: Build the BizTalk Solution.
Step 5: Deploy and Configure the Solution.
Step 6: Testing the Solution.

Step 1: Create a database along with Table to store the item information and Image

Open the SQL Server Management Studio, right-click the Databases and add new database. Next is to add a new Table to the database with the required columns and respective datatypes.

Note : The column dedicated to store the image is set to type "varbinary(Max)".

Step 2: Generate the schema

Right-click the project and select Add --> Add Generated Items --> Consume Adapter Service.

Select the binding and click on the configure button. On security type select from drop down list the type of credential used to connect to the database.

On the URI properties tab, provide the Database name against InitialCatalog and against server provide the server name ("." is used as its local).

Select Contract type as Client (Outbound operations), from a category, select the table and from Available Categories and operations select Insert.

Provide filename Prefix in case you want to have a prefix added to the schemas generated (Not mandatory - but is useful to differentiate between the schemas when same table is used multiple times).

Click ok, and you will see four new items (three xsd's and one binding file) are added to the project.

Note: XSD's are prefixed with DbItemInfo as it was added while generating schema, if it was not added then xsd would have been SimpleTypeArray.xsd,
Table.dbo.xsd and TableOperation.dbo.ItemInfo.xsd.

Below is the structure of the destination.

Step 3: Create a Helper Class to convert the file into bytes

Right click the solution and select Add--> New Project and select Visual C# from Installed Templates and choose a C# class library from middle pane.

Step 4: Build the BizTalk Solution

Add a schema defining the structure of the Item message to be received. (For article the structure is limited to three elements, but can have more elements)

Add a schema defining the structure of Image content, and set the datatype as "xs:base64Binary"

Add Reference of the Helper class to the project and add Orchestration which starts as soon as Item Message is received. After the message is received which has all information along with the image name, image path is build using the receive location and image name which is later passed to the helper class which returns the content of the image file as bytes which is used to create a message which has the image content.

Then both the messages, initially received message and the message created are used to construct the message which conforms to the database.

The Expression Shape GetImageFilePath below the Receive_Item shape is used to get the file path of the Image and assigned to string variable varImgName which is used in ** ConstructImageContentMsg** followed after it.

varImgName = @"D:\Biztalk\MyProjects\DemoImageProcess\DemoImageProcess\DropLocation\ItemIn\" + msgItem.ImageName;

Note: For this article absolute path is used, ideally to store and access such information config files are used.

The construct shape has two shapes, AssignImageContent (MessageAssignment shape) and TransformtoDB (Transform shape). It is in the MessageAssignment where the ImageContentmsg is constructed with the help of Helper class, below is the code:


varXmlDoc.LoadXml("<ns0:ImageDetails xmlns:ns0='http://demoimageprocess.image/'><ImageContent>" +System.Convert.ToBase64String(FileReader.Binary.Read(varImgName))+ "</ImageContent></ns0:ImageDetails>");
msgImageContent = varXmlDoc;

Where varXmlDoc is of type System.Xml.XmlDocument used to load the structure specified by ImageContent schema along with the value returned from the helper class (System.Convert.ToBase64String(FileReader.Binary.Read(varImgName))).

Which is assigned to msgImageContent (message variable of type ImageContent schema).

Transform shape now uses both the messages, Item message which is received and ImageContent message which is constructed

Mapping from two sources to the single destination schema

Step 5: Deploy and Configure the Solution

After done with the development, sign the project and assign an Application Name and Deploy. You will need to create a Receive port with a receive location for receiving Item message and a Send port (Request Response) which will insert data into Database.

Static Solicit-Response Send Port using WCF-SQL adapter to communicate with the database. You can manually configure or you can import the binding file which was created while Generating Schemas in step 2.

Configure the Orchestration with the created receive and send port

Step 6: Testing the Solution

To test the solution drop Item message and Image file at receive location and check the Database. All the information from XML is inserted along with the image, stored as BLOB.

Download Sample

You can find the source code belonging to this article on MSDN code Gallery:

Author

Maheshkumar S Tiwari | User **Page
**http://tech-findings.blogspot.com/

Share via