Virtualization Classes
From Business Process Management, BPM and Workflow Automation Wiki | BizAgi BPMS
<keywords content="keywords"> virtualization, data mapping, virtual entities, data level integration, mySQL, custom virtualization </keywords>
|
Applies to ENTERPRISE EDITIONS |
Contents |
Virtualization Classes
Introduction
This article presents an example of the virtualization classes that implement the "IProviderVirtualDA" and "IEntityVirtualDA" interfaces, which are required for a custom virtualization (when the data source is in a database engine different from Oracle or Microsoft SQL Server).
The example provided illustrates a custom virtualization using a MySQL data source.
|
Alert: Take into account that the following code provides an illustration of the "AddEntity()" method implementation to serve as a guide, but other methods are not included and need to be completed. |
Prerequisites
To implement these classes, it is required to download and install the MySQL Connector/Net component for the connection to a MySQL instance.
For this example, these classes were worked with Microsoft Visual Studio. Within the .Net project worked with Visual Studio, the "MySql.Data.dll" assembly installed by the MySQL Connector/Net component needs to be referenced by the .Net project.
It is also necessary to reference the "Bizagi.EntityManager.Interfaces.IEntityDA.dll" assembly, which is found in the bin folder of the project's web application (by default at "C:\Bizagi\Enterprise\Projects\[PROJECT_NAME]\WebApplication\bin\".
Classes Implementation Example
Class Interface IProviderVirtualDA
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Collections.Specialized;
using System.Text;
using MySql.Data.MySqlClient;
using Bizagi.EntityManager.Interfaces;
namespace BizagiMySQL
{
public class CMySQLProvider : IProviderVirtualDA
{
protected HybridDictionary m_htMetadata;
protected MySqlConnection MySqlconn;
protected bool bDisposed;
public CMySQLProvider()
{
bDisposed = false;
MySqlconn = null;
}
#region IProviderVirtualDA Members
public void BeginTransaction()
{
// TODO: Add CMySQLProvider.BeginTransaction implementation
}
public void Rollback()
{
// TODO: Add CMySQLProvider.Rollback implementation
}
public void Commit()
{
// TODO: Add CMySQLProvider.Commit implementation
}
public void OpenConnection()
{
if (MySqlconn == null)
{
// Verify that the metadata fields are correct
if (!m_htMetadata.Contains("Server") || !m_htMetadata.Contains("Database") ||
!m_htMetadata.Contains("Username") || !m_htMetadata.Contains("Password"))
{
throw new CEntityClassInterfaceException("Incomplete metadata:"
+ "connection parameters missing");
}
else
{
// build the connection string
string sConn;
string sServer = m_htMetadata["Server"].ToString();
string sDatabase = m_htMetadata["Database"].ToString();
string sUsername = m_htMetadata["Username"].ToString();
string sPassword = m_htMetadata["Password"].ToString();
sConn = "Server=" + sServer + ";Database=" + sDatabase
+ ";Uid=" + sUsername + ";Pwd=" + sPassword + ";";
MySqlconn = new MySqlConnection(sConn);
MySqlconn.Open();
}
}
}
public void Init(HybridDictionary htMetadata)
{
m_htMetadata = htMetadata;
}
public void CloseConnection()
{
if (MySqlconn != null)
MySqlconn.Close();
MySqlconn = null;
}
#endregion
#region IDisposable Members
public void Dispose()
{
if (!bDisposed)
{
// never close the connection in this method!!
bDisposed = true;
}
}
#endregion
public MySqlConnection getConnection()
{
return this.MySqlconn;
}
public DataSet executeMySQLQuery(string sSQL)
{
MySqlCommand command;
MySqlDataAdapter adapter;
DataSet ds = new DataSet();
command = new MySqlCommand(sSQL, this.getConnection());
adapter = new MySqlDataAdapter(command);
adapter.Fill(ds);
return ds;
}
public void executeNonQueryMySQL(string sSQL)
{
MySqlCommand command;
command = new MySqlCommand(sSQL, this.getConnection());
command.ExecuteNonQuery();
}
}
}
Class Interface IEntityVirtualDA
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Collections.Specialized;
using System.Text;
using System.Xml;
using MySql.Data.MySqlClient;
using Bizagi.EntityManager.Interfaces;
using Bizagi.Defs;
namespace BizagiMySQL
{
public class CMySQLEntity : IEntityVirtualDA
{
/// <summary>
/// Flag to show if object has been disposed
/// </summary>
protected bool m_bDisposed;
/// <summary>
/// Metadata used to initialize object, as collection of name-value pairs
/// </summary>
protected HybridDictionary m_htMetadata;
/// <summary>
/// Connection with the virtual System
/// </summary>
protected CMySQLProvider m_objSystem;
public CMySQLEntity()
{
m_bDisposed = false;
}
#region IEntityVirtualDA Members
public Hashtable AddEntity(string sEntSource, Hashtable htAddCols,
string[] arrsKeyColumns, object[] arroKeyValues, string[] arrsAutoColumns)
{
Hashtable htResult = new Hashtable();
DataTable dtAutoAttribValues = new DataTable();
try
{
StringBuilder sbIntoList = new StringBuilder(htAddCols.Count * 10);
StringBuilder sbValuesList = new StringBuilder(htAddCols.Count * 10);
// Columns to add
foreach (DictionaryEntry oEntry in htAddCols)
{
// Builds INTO clause and VALUES clauses separately
sbIntoList.Append(oEntry.Key);
sbIntoList.Append(",");
// Adds values as parameters
if (oEntry.Value.GetType() == typeof(string))
sbValuesList.Append("'"+oEntry.Value+"'");
else
if(oEntry.Value.GetType() == typeof(DateTime))
{
string sFecha = ((DateTime)oEntry.Value).
ToString("dd/MM/yyyy hh:mm:ss tt");
sbValuesList.Append("STR_TO_DATE('"
+ sFecha + "','%d/%m/%Y %h:%i:%s %p')");
}
else
sbValuesList.Append(oEntry.Value);
sbValuesList.Append(",");
}
if (sbIntoList.Length == 0 || sbValuesList.Length == 0)
{
throw new CEntityClassInterfaceException("Nothing to insert.");
}
StringBuilder sbSQL = new StringBuilder();
StringBuilder sbSQLSelectCols = new StringBuilder();
// Inserts values in entity table
sbSQL.Append("INSERT INTO ");
sbSQL.Append(sEntSource);
sbSQL.Append(" ( ");
sbSQL.Append(sbIntoList.Remove(sbIntoList.Length - 1, 1).ToString());
sbSQL.Append(" ) VALUES ( ");
sbSQL.Append(sbValuesList.Remove(sbValuesList.Length - 1, 1).ToString());
sbSQL.Append(" ) ");
m_objSystem.executeNonQueryMySQL(sbSQL.ToString());
return htResult;
}
catch (Exception e)
{
throw new ApplicationException(e.Message);
}
}
public void Init(IProviderVirtualDA objProvider, HybridDictionary htMetadata)
{
m_objSystem = (CMySQLProvider)objProvider;
m_htMetadata = htMetadata;
}
public bool DeleteEntity(string sEntSource, string[] arrsKeyColumns, object[] arroKeyValues)
{
// TODO: Add CMySQLEntity.DeleteEntity implementation
return false;
}
public int ExistsEntityInstance(string sEntSource, string sColumn,
object oValue, string[] arrsKeyColumns, object[] arroKeyValues)
{
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" SELECT ");
sbSQL.Append(string.Join(",", arrsKeyColumns));
sbSQL.Append(" FROM ");
sbSQL.Append(sEntSource);
sbSQL.Append(" WHERE ");
sbSQL.Append(buildWhereClause(arrsKeyColumns, arroKeyValues));
DataSet ds = m_objSystem.executeMySQLQuery(sbSQL.ToString());
return ds.Tables[0].Rows.Count;
}
catch (Exception e)
{
throw new ApplicationException(e.Message);
}
}
bool Bizagi.EntityManager.Interfaces.IEntityVirtualDA.ExistsEntityInstance(string sEntSource,
string[] arrsKeyColumns, object[] arroKeyValues)
{
// TODO: Add ExistsEntityInstance implementation
return true;
}
public Hashtable UpdateEntity(string sEntSource, string[] arrsKeyColumns,
object[] arroKeyValues, Hashtable htUpdateCols, string[] arrsAutoColumns)
{
// TODO: Add UpdateEntity implementation
return null;
}
public DataSet GetEntityInstance(string sEntSource, string[] arrsKeyColumns,
object[] arroKeyValues, string[] arrsColList)
{
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" SELECT ");
sbSQL.Append(string.Join(",", arrsColList));
sbSQL.Append(" FROM ");
sbSQL.Append(sEntSource);
sbSQL.Append(" WHERE ");
sbSQL.Append(buildWhereClause(arrsKeyColumns, arroKeyValues));
DataSet ds = m_objSystem.executeMySQLQuery(sbSQL.ToString());
return ds;
}
catch (Exception e)
{
throw new ApplicationException(e.Message);
}
}
public object GetAttributeValue(string sEntSource, string[] arrsKeyColumns,
object[] arroKeyValues, string sAttrSource)
{
try
{
StringBuilder sbSQL = new StringBuilder();
// Column to be retrieved
sbSQL.Append("SELECT ");
sbSQL.Append(sAttrSource);
// FROM clause
sbSQL.Append(" FROM ");
sbSQL.Append(sEntSource);
// Uses surrogate key value to find instance
sbSQL.Append(" WHERE ");
sbSQL.Append(buildWhereClause(arrsKeyColumns, arroKeyValues));
DataSet ds = m_objSystem.executeMySQLQuery(sbSQL.ToString());
DataTable dtAttribValues = ds.Tables[0];
return dtAttribValues;
}
catch (Exception e)
{
throw new ApplicationException(e.Message);
}
}
public DataSet GetEntityInstancesTable(string sEntSource, string[] arrsKeyColumns,
string[] arrsColList, string sFilterText, bool bFillSchema, int iTopReturnRows)
{
try
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" SELECT ");
sbSQL.Append(string.Join(",", arrsColList));
// FROM clause
sbSQL.Append(" FROM ");
sbSQL.Append(sEntSource);
// WHERE clause
if (sFilterText.Length > 0)
{
// Filter text must be formatted in OleDb syntax
sbSQL.Append(" WHERE ");
sbSQL.Append(sFilterText);
}
sbSQL.Append(" ORDER BY ");
sbSQL.Append(string.Join(",", arrsKeyColumns));
DataSet ds = m_objSystem.executeMySQLQuery(sbSQL.ToString());
return ds;
}
catch (Exception e)
{
throw new ApplicationException(e.Message);
}
}
#endregion
#region IDisposable Members
public void Dispose()
{
if (!m_bDisposed)
{
m_bDisposed = true;
}
}
#endregion
public string buildWhereClause(string[] keyColumns, object[] keyValues)
{
StringBuilder sbSQL = new StringBuilder();
string[] arrsKeyValues = new string[keyValues.Length];
string sAnd = " AND ";
for (int iKeyIndex = 0; iKeyIndex < keyColumns.Length; iKeyIndex++)
{
// associate columns with values....
sbSQL.Append(keyColumns[iKeyIndex]);
sbSQL.Append(" = ");
sbSQL.Append(keyValues[iKeyIndex].ToString());
sbSQL.Append(sAnd);
}
return sbSQL.Remove(sbSQL.Length - 4, 3).ToString();
}
}
}
Related Articles
- How to integrate Bizagi with an external data source.
- Custom Virtualization.
- MySQL Replication Classes Example.
<comments />
