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.
