Biztalk and Enterprise Library Database Application Block

Hi,
We are gonna learn "How to use Enterprise Library Database Application Block with Biztalk?". Enterprise Library has a myriad of helpfull software tool. Database Application Block is using for DB Operations at .net platform. Sometimes you may need to make DB operations at biztalk. My example will show you "How to use Enterprise Library Database Application Block at your helper class."

Firstly, you have to have a config file at your helper class.



This is my project. You can see the references at picture. I've added Enterprise Library shared dll and Data access dll..You can see the basic oracle configuration at below. This is the configuration file(YourConfigurationFile.config) :


<?xml version="1.0"?>
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</configSections>
<dataConfiguration defaultDatabase="OracleDefault"/>
<connectionStrings>
<add name="OracleDefault"
connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1721))(CONNECT_DATA=(SERVICE_NAME=testdb)));USER ID=yourusername;Password=yourpassword;"
providerName="System.Data.OracleClient"/>
<add name="ODPTestOracleDBConnStr" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1721))(CONNECT_DATA=(SERVICE_NAME=testdb)));USER ID=yourusername;Password=yourpassword;"
providerName="Oracle.DataAccess.Client" />
</connectionStrings>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="System.Data.OracleClient" publicKeyToken="B77A5C561934E089" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-2.0.0.0" newVersion="2.0.0.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>

</configuration>

Basically, we're planning to read customer information from a xml file and write to DB.

public void ExecuteFile(XmlDocument doc)
{
ProcessCustomers(doc);
}


public void ProcessCustomers(XmlDocument doc)
{
try
{
XmlNode root = doc.DocumentElement;
XmlNodeList nodeList = root.SelectNodes("Customer");
YourCustomer customer = new YourCustomer();
foreach (XmlElement node in nodeList)
{
customer.number = node.SelectSingleNode("CustomerID").InnerText;
DateTime birthDate = DateTime.ParseExact(node.SelectSingleNode("CustomerBirthDate").InnerText, "yyyyMMdd", DateTimeFormatInfo.InvariantInfo);
customer.name = node.SelectSingleNode("CustomerName").InnerText;
string returnValue = WriteCustomers(customer);
}

}
catch (Exception ex)
{
throw ex;
}
}


public string WriteCustomers(YourCustomer customer)
{
try
{
IConfigurationSource source = new FileConfigurationSource("C:\\YourConfiguration.config");
DatabaseProviderFactory factory = new DatabaseProviderFactory(source);

Database db = factory.Create("OracleDefault");

DbCommand cmd = db.GetStoredProcCommand("CUSTOMER.YOUR_CUSTOMER");

db.AddInParameter(cmd, "c_member_no", DbType.AnsiString, customer.number);
db.AddInParameter(cmd, "c_birth_date", DbType.AnsiString, customer.birthDate);
db.AddInParameter(cmd, "c_name", DbType.AnsiString, customer.name);

DbParameter retparam = cmd.CreateParameter();
retparam.Direction = ParameterDirection.ReturnValue;
retparam.DbType = DbType.Int32;
retparam.ParameterName = "RETVAL";
cmd.Parameters.Add(retparam);
db.ExecuteNonQuery(cmd);
return retparam.Value.ToString();


}
catch (Exception ex)
{
throw ex;
}

}


After this helper class code, you can call ExecuteFile function from your Biztalk Expression.

Biztalk and Logging with log4net

Sometimes, You may need to log some information at your Biztalk project. For example; debugging your orchestration or just monitoring or mailing. Just Event viewer could not meet your needs. In my opinion, Logging is a mandatory practice for all software projects. Most of the time, I use log4net for .net projects.

Now, I'll show you "How can you implement log4net at your Biztalk project?" and "How can you call your logger from your orchestration?"

Firstly, you have to got log4net.dll. You have to add this dll as a reference file to your helper class.

Secondly, create a class for logger instance. You can find my logger class codes below :

using log4net;
using System.IO;

public class Logger
{

public static Logger MailLog = new Logger(LogType.Mail);
public static Logger DefaultLog = new Logger(LogType.Default);
public static Logger EventLog = new Logger(LogType.EventLogger);
public static Logger FileAppender = new Logger(LogType.FileAppender);
public static void InitializeProjectConfigurationLog4Net()
{
FileInfo configFile;
configFile = new FileInfo("C:\\yourlogger.config");
if (configFile.Exists)
{
log4net.Config.XmlConfigurator.Configure(configFile);
}
else
{
log4net.Config.XmlConfigurator.Configure();
}
}




protected ILog MyLogger
{
get { return this._logger; }
}



private ILog _logger;
private Logger(string loggerName)
{
this._logger = LogManager.GetLogger(loggerName);
}



public void Debug(object message)
{
this.MyLogger.Debug(message);
}
public void Debug(object message, System.Exception t)
{
this.MyLogger.Debug(message, t);
}
public void Error(object message)
{
this.MyLogger.Error(message);
}
public void Error(object message, System.Exception t)
{
this.MyLogger.Error(message, t);
}
public void Fatal(object message)
{
this.MyLogger.Fatal(message);
}
public void Fatal(object message, System.Exception t)
{
this.MyLogger.Fatal(message, t);
}
public void Info(object message)
{
this.MyLogger.Info(message);
}
public void Info(object message, System.Exception t)
{
this.MyLogger.Info(message, t);
}
public void Warn(object message)
{
this.MyLogger.Warn(message);
}
public void Warn(object message, System.Exception t)
{
this.MyLogger.Warn(message, t);
}

}

public class LogType
{
public const string Mail = "SmtpLogger";
public const string Default = "DefaultLogger";
public const string EventLogger = "EventLogger";
public const string FileAppender = "FileAppender";
}

There is a static method in this class which is called InitializeProjectConfigurationLog4Net. This method makes your logger configuration. In this method you can see this line :

configFile = new FileInfo("C:\\yourlogger.config")

This is your logger configuration file. You have to put your log4net config information to this file. For example(yourlogger.config);

<?xml version="1.0"?>

<configuration>

<configSections>

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>

</configSections>

<appSettings>

<add key="log4net.Internal.Debug" value="true"/>

</appSettings>

<log4net>

<appender name="SmtpLogger" type="log4net.Appender.SmtpAppender">

<to value="smbd@test.com"/>

<from value="from@test.com"/>

<subject value="test subject"/>

<smtpHost value="yoursmtpIP"/>

<bufferSize value="1024"/>

<lossy value="true"/>

<evaluator type="log4net.Core.LevelEvaluator">

<threshold value="INFO"/>

</evaluator>

<layout type="log4net.Layout.PatternLayout">

<conversionPattern value="%newline%date [%thread] %-5level %logger [%property{NDC}] - %message%newline%newline%XXX"/>

</layout>

</appender>

<appender name="FileAppender" type="log4net.Appender.FileAppender">

<file value="c:\\your_log.txt"/>

<appendToFile value="true"/>

<layout type="log4net.Layout.PatternLayout">

--><!--<conversionPattern value="%date - %-5level - %message%newline"/>--><!--

<conversionPattern value="%-5level %date{yyyy-MM-dd HH:mm:ss} : %message%newline"/>

</layout>

</appender>

<appender name="EventLogger" type="log4net.Appender.EventLogAppender">

<param name="LogName" value="yourLogName"/>

<param name="ApplicationName" value="yourApplicationName"/>

<layout type="log4net.Layout.PatternLayout">

<param name="ConversionPattern" value="%d [%t] %-5p %c [%x] - %m %n"/>

</layout>

</appender>

<logger name="EventLogger">

<appender-ref ref="EventLogger"/>

<level value="ALL"/>

</logger>

<logger name="FileAppender">

<appender-ref ref="FileAppender"/>

<level value="ALL"/>

</logger>

<logger name="SmtpLogger">

<appender-ref ref="SmtpLogger"/>

<level value="ALL"/>

</logger>

</log4net>

</configuration>

This file contains smpt logger, file logger and event viewer logger. After these definitions, you have to initalize your logger class . You can trigger this static initializer method from your orchestration.

logger = new HelperClass.Logger

logger.InitializeProjectConfigurationLog4Net()

This expression starts your log4net logger. You can use your logger easily at your biztalk expressions.

logger.FileAppender.Info ("File not found");

logger.MailLog.Info("Alert");

What is Biztalk

Biztalk is an advanced integration tool which is developed by Microsoft. There are 7000 Biztalk customers all over the world. %90 percent of Companies use Biztalk which in Fortune Global 100. Up till now Microsoft has published 5 Biztalk versions. These are 2000, 2002, 2004, 2006 and 2006 r2. You can make a lot of things with Biztalk. For example advanced file transfer, Dynamic Data integration with WCF, ERP integration, RFID communication and using other .net framework libraries.

How does it work ?
There are two different function of Biztalk. Firstly, it moves data like “conveyor band”. It accept different file format for data transfer. It can transfer all kind of data to xml. After the data operations, it can be create xml result. Secondly, Other Biztalk function is an orchestration. Orchestration is a workflow diagram. We can see the big picture and Biztalk architecture at below.




Message :
Every type of file which is sent to Biztalk is considered a “Message”. It can be xml file and text file and even an email.

Receive Port :
Messagges are received from Receive Port. Every Receive Port must have a Receive Location. Receive port is a logical item on the Biztalk Architecture. But receive location states real address which is taken data. For example when a receive location which states address of c:\test\In\*.xml connect to a Receive Port, this folder is started to listen by Biztalk. When a certain xml file copy to folder Receive Port takes it and forward an Orchestration or if available another subscription.

MessageBox :
It represents Database. All the files which is input and output are saved to MessageBox(default configuration name is BizTalkMsgBoxDb).

Send Port :
When operations are complete messages can be sent to Send port.

Orchestration :
An Orchestration is the most glamorous feature of the Biztalk. Orchestrations are programmable by graphical and they can execute a script. We can define and design all workflow at an orchestration. All these operation can operate via Microsoft Visual Studio.