Custom Virtualization

<< Click to Display Table of Contents >>

Navigation:  Bizagi Studio > Process wizard > Data Modeling > Connecting to external data sources > Data Virtualization > Using advanced Virtualization configuration >

Custom Virtualization

Overview

Bizagi provides a wizard to assist in connecting to an external sources such as SQL Server and Oracle (for data-level integration).

 

For any data source other then SQL Server and Oracle, there is the possibility to override the methods and classes used by Bizagi in its Virtualization feature.

 

 

What you need to do

In order to use Bizagi's Virtualization for an external data source other than SQL Server or Oracle, the following steps are carried out:

 

1. Creating a custom Virtualization library.

Within this custom library, you will need to override Bizagi Virtualization's classes and methods (as described in the next section: Classes and methods to override).

 

2. Configuring the data provider in Bizagi to use the custom Virtualization library.

This configuration uses the Advanced Virtualization configuration in Bizagi.

 

 

Classes and methods to override

To customize Bizagi's Virtualization, it is necessary to develop an assembly that implements the following interfaces:

ICustomProviderDA

ICustomQueryDA

ICustomVirtualizationDA

IDatabaseProperties

 

 

Bizagi separates the operations for connection, disconnection and transactional management from the operations to query data from the external sources in the Provider and Entity interfaces.

Therefore, when creating a custom Virtualization library, Bizagi follows best practices for this management and implementation's design.

 

 

Provider Interface ICustomProviderDA

The class that invokes this interface must contain the following methods:

 

void Init (Dictionary<string, string> metadata): In charge of taking the data supplied in the system configuration, such as server name, database name, user, password, etc. It receives a Dictionary<string, string> map that contains the data configured as system metadata.

void OpenConnection(): This method is used to make the connection with the external system. In the case of a database, this is where you build the connection links and open a connection with the repository.

void CloseConnection(): In charge of closing the connection with the external system.

void BeginTransaction(): In charge of initiating a new transaction for the current operation in the system.

void Commit(): Commits the current transaction in the reference system.

void Rollback(): Carries out rollback of the current transaction in the reference system.

 

Provider Interface ICustomQueryDA

The class that invokes this interface must contain the following methods:

 

void AddParameter (string parameterName, BizagiDataType dataType, int size, byte precision, byte scale, object value, ParameterDirection parameterDirection): In charge of adding a parameter to the query to be executed.

void ClearParameters(): Removes the existing parameters in the query.

DataSet RunQuery(string query, string tableName): used to query the values of the external data source. The logic is generally equivalent to making a query on the fields of the data object.

 

 

Entity Interface: ICustomVirtualizationDA

The class that implements this interface must contain the logic necessary to manipulate the virtualized data, such as inserting a new record, consulting within a group of records and updating and deleting a registry.

The methods that the class must contain are listed below:

 

void Init(ICustomProviderDA provider): Initializes the connection with the system.

 

Parameter

Description

provider

Allows the conversion towards the object that implements the ICustomProviderDA interface.

 

DataSet GetEntityInstance(string entitySource, KeyColumn[] keyColumns, string[] columnList): Takes data from entity instances (values) from the corresponding table for a specific ID.

 

Parameter

Description

entitySource

Table where the entity is implemented.

keyColumns

Array that contains the names of the columns used to filter the record.

columnList

Array that contains the name of the columns to be consulted.

RETURNED DataSet

A DataSet with the values for that entity.

 

DataSet GetEntityInstancesTable(string entitySource, string[] keyColumns, string[] outputColumnList, string filterText, bool fillSchema, int topReturnRows)

 

Parameter

Description

entitySource

Table where the entity is located.

keyColumns

Array of columns used as keys.

outputColumnList

Columns to be included on the list.

filterText

Filter to be applied in search of entities in the WHERE clause.

fillSchema

True if the information from the schema is to be returned from the database, False otherwise.

topReturnRows

Number of registers to be returned.

RETURNED DataSet

A DataSet with the values for that entity.

 

Object GetAttributeValue(string entitySource, KeyColumn[] keyColumns, string attributeSource)

 

Parameter

Description

entitySource

Table where the entity is implemented.

keyColumns

Array of columns used as keys.

attributeSource

Column of values to return.

RETURNED Object

The value of the attribute.

 

bool ExistsEntityInstance(string entitySource, KeyColumn[] keyColumns)

 

Parameter

Description

entitySource

Table where the entity is implemented.

keyColumns

Array of columns used as key.

RETURNED bool

True if the instance was found, False otherwise.

 

int ExistsEntityInstance(string entitySource, EntityColumn columnToFind, KeyColumn[] keyColumns)

 

Parameter

Description

entitySource

Table where the entity is implemented.

columnToFind

Column used to find the instance.

keyColumns

Array of columns used as key.

RETURNED int

Should return "1" if the instance was found. Returns "0" if not.

 

EntityOutputColumn[] AddEntity(string entitySource, EntityColumn[] addColumns, KeyColumn[] keyColumns, EntityAutoColumn[] autoColumns): Adds a new instance (values) to the database

 

Parameter

Description

entitySource

Table where the entity is implemented.

addColumns

List of columns to add to values.

keyColumns

Array of columns used as keys.

autoColumns

Array of auto-numeric columns.

RETURNED EntityOutputColumn[]

Should return a List of EntityOutputColumn that contains the name of the column paired with the value that was inserted.

 

EntityOutputColumn[] UpdateEntity(string entSource, EntityColumn[] updateColumns, KeyColumn[] keyColumns, EntityAutoColumn[] autoColumns): Updates the data of an instance of the entity (values) in the database and records the update.

 

Parameter

Description

entSource

Table where the entity is implemented.

updateColumns

Array of columns to update.

keyColumns

Array of columns used as key.

autoColumns

Array of auto-numeric columns.

RETURNED EntityOutputColumn[]

Should return an Array of EntityOutputColumn that contains the name of the column and the value that was inserted.

 

bool DeleteEntity(string entSource, KeyColumn[] keyColumns): Deletes a record from the database.

 

Parameter

Description

entSource

Table where the entity is implemented.

keyColumns

Array of columns used as key.

RETURNED bool

Should return True if the record was deleted, False otherwise.

 

 

Constants Interfaces: IDatabaseProperties

Keep in mind Bizagi will build the query and its syntax.

According to this idea, make sure you define all the proper constants to be used in queries in the integrated Database engine.

 

 

Example

In this example, we will configure custom Virtualization for a MySQL database engine.

 

We will present an example of the Virtualization classes that implement the "ICustomProviderDA", "ICustomQueryDA" and "IEntityVirtualDA" interfaces.

 

note_pin

Take into account that the following code provides an illustration for the "AddEntity()" method implementation (to serve as a guide), but other methods are not included and would need further development.

 

 

Creating a custom Virtualization library

In this specific example, in order to implement Virtualization classes that connect to a MySQL database, it is required to first download and install the MySQL Connector/NET component for that connection.

 

The following classes were developed with Microsoft Visual Studio. Within this .NET project, referencing the "MySql.Data.dll" assembly as installed by the MySQL Connector/Net component is required.

 

It is also necessary to reference the library BizAgi.EntityManager.Interfaces.CustomSource.dll of Bizagi.

 

This is found at the bin of the project's web application folder (by default at "C:\Bizagi\Projects\[PROJECT_NAME]\WebApplication\bin\").

 

CustomRefs

 

 

Class Interfaces ICustomProviderDA and ICustomQueryDA

using System;

using System.Data;

using BizAgi.EntityManager.Interfaces.CustomSource;

using MySql.Data.MySqlClient;

using System.Collections.Generic;

using System.Collections;

using System.Text;

 

namespace BizagiMySQL

{

 

   public class CMySQLProvider : ICustomProviderDA, ICustomQueryDA

   {

 

       private Dictionary<string, string> _metadata;

       private MySqlConnection _mySqlconn;

       private bool _disposed;

       //--- The array        used to        store the parameters to        a stored procedure

       private readonly List<CMySqlParameter> _params;

 

       public CMySQLProvider()

       {

           _disposed = false;

           _mySqlconn = null;

           _params = new List<CMySqlParameter>();

       }

     

       #region IProviderVirtualDA2 Members

 

       public void AddParameter(string parameterName, BizagiDataType dataType, int size, byte precision, byte scale, object value, ParameterDirection parameterDirection)

       {

           _params.Add(new CMySqlParameter(parameterName, dataType, size, precision, scale, value, parameterDirection));

       }

 

       public void ClearParameters()

       {

           _params.Clear();

       }

 

       public DataSet RunQuery(string query, string tableName)

       {

           MySqlCommand command = new MySqlCommand();

           MySqlDataAdapter adapter = new MySqlDataAdapter();

           DataSet ds = new DataSet();

           command.Connection = this.GetConnection();

           command.CommandText = ReplaceQueryParameters(query, _params);

           command.CommandType = CommandType.Text;

 

           GetParameters(command);

           adapter.SelectCommand = command;

           adapter.Fill(ds, tableName);

         

           return ds;

       }

 

       public IDatabaseProperties Properties

       {

           get { return CMySqlDatabaseConstants.Instance; }

       }

 

       private void GetParameters(MySqlCommand oCmd)

       {

           //--- Get an enumerator        for        the        parameter array        list

           IEnumerator oEnumerator = _params.GetEnumerator();

 

           //--- Loop through the Parameters in the ArrayList

           while (oEnumerator.MoveNext())

           {

 

               //--- Add the SQLParameter object to the SQLCommand        object

               oCmd.Parameters.Add(ConvertParameterToMySqlParameter((CMySqlParameter)oEnumerator.Current));

           }

       }

 

       private MySqlParameter ConvertParameterToMySqlParameter(CMySqlParameter oP)

       {

           MySqlDbType dbType = oP.DataType;

 

           //--- Instantiate a        SqlParameter object

           MySqlParameter mySqlParameter = new MySqlParameter(oP.ParameterName, dbType, oP.Size);

 

           mySqlParameter.Value = oP.Value;

           mySqlParameter.Direction = (ParameterDirection)oP.Direction;

           mySqlParameter.Precision = oP.Precision;

           mySqlParameter.Scale = oP.Scale;

           mySqlParameter.SourceColumn = oP.SourceColumn;

           mySqlParameter.SourceVersion = oP.UseCurrentSourceColumnValue ? DataRowVersion.Current : DataRowVersion.Original;

 

           return mySqlParameter;

       }

 

       /// <summary>

       /// Replace parameter in the form '?', by parametes in the form ':ParamName'

       /// </summary>

       /// <param name="sQuery"></param>

       /// <returns></returns>

       private static string ReplaceQueryParameters(string sQuery, List<CMySqlParameter> alParams)

       {

           if (alParams != null && alParams.Count > 0)

           {

               char cInter = '?', cQuote = '\'', cAt = '@', cColon = ':';

               string sParamToken = ":";

               bool bInQuote = false; // indicates if current character is in ''

               StringBuilder sbResp = new StringBuilder();

               int _i = 0, _n = sQuery.Length, nNumParam = 0, nLastStrIndex = 0;

               while (_i < _n)

               {

                   if (bInQuote)

                   {

                       if (sQuery[_i] == cQuote)

                       {

                           if ((_i + 1) < _n && sQuery[_i + 1] == cQuote)

                           {

                               // "...''..." case, already in quote

                               _i += 2;

                           }

                           else

                           {

                               // "...'" case

                               bInQuote = false;

                               _i++;

                           }

                       }

                       else

                       {

                           _i++;

                       }

                   }

                   else

                   {

                       if (sQuery[_i] == cInter)

                       {

                           // "...?..." case

                           if (nNumParam >= alParams.Count)

                           {

                               // parameter doesn't exists

                               throw new Exception(string.Format("Parameter {0} doesn't exists in command: '{1}'", nNumParam, sQuery));

                           }

                           else

                           {

                               // get valid param name

                               string sValidParamName = GetValidParamName(((CMySqlParameter)alParams[nNumParam]).ParameterName, alParams, nNumParam - 1);

                               (alParams[nNumParam]).ParameterName = sValidParamName;

 

                               // replace parameter by name

                               sbResp.Append(sQuery.Substring(nLastStrIndex, _i - nLastStrIndex));

                               sbResp.Append(sParamToken + sValidParamName);

 

                               nLastStrIndex = _i + 1;

                               nNumParam++;

                           }

                       }

                       else if (sQuery[_i] == cAt)

                       {

                           // replace parameter by name

                           sbResp.Append(sQuery.Substring(nLastStrIndex, _i - nLastStrIndex));

                           sbResp.Append(cInter);

 

                           nLastStrIndex = _i + 1;

                           nNumParam++;

                       }

                       else if (sQuery[_i] == cQuote)

                       {

                           // "'..." case

                           bInQuote = true;

                       }

                       _i++;

                   }

               }

               sbResp.Append(sQuery.Substring(nLastStrIndex, _n - nLastStrIndex));

               return sbResp.ToString();

           }

           return sQuery;

       }

 

       /// <summary>

       /// Gets a valid parameter name, unique for first iMaxParamIndex parameters

       /// </summary>

       /// <param name="sParamName"></param>

       /// <param name="alParams"></param>

       /// <param name="iMaxParamIndex"></param>

       /// <returns></returns>

       private static string GetValidParamName(string sParamName, List<CMySqlParameter> alParams, int iMaxParamIndex)

       {

           int iMaxDbIdLength = CMySqlDatabaseConstants.Instance.MaxDbIdLength;

           string sResp = (iMaxDbIdLength < sParamName.Length) ? sParamName.Substring(0, iMaxDbIdLength) : sParamName;

           int iSuffix = 1;

           while (IndexOfParam(sResp, alParams, iMaxParamIndex) != -1)

           {

               // find an unique name

               string sSuffix = iSuffix.ToString();

               sResp = (iMaxDbIdLength - sSuffix.Length < sParamName.Length) ? sParamName.Substring(0, iMaxDbIdLength - sSuffix.Length) : sParamName;

               sResp += sSuffix;

               iSuffix++;

           }

           return sResp;

       }

 

       private static int IndexOfParam(string sParamName, List<CMySqlParameter> alParams, int iMaxParamIndex)

       {

           int i = 0, n = iMaxParamIndex;

           while (i < n)

           {

               if ((alParams[i]).ParameterName == sParamName)

               {

                   n = i;

               }

               else

               {

                   i++;

               }

           }

           return (n == iMaxParamIndex) ? -1 : n;

       }

 

       #endregion

 

       #region IProviderVirtualDA Members

 

       public void Init(Dictionary<string, string> metadata)

       {

           this._metadata = metadata;

       }

 

       public void BeginTransaction()

       {

       }

 

       public void Commit()

       {

       }

 

       public void Rollback()

       {

       }

 

       public void OpenConnection()

       {

           if (_mySqlconn == null)

           {

               //Verify all parameters are filled

               if (!_metadata.ContainsKey("Server") || !_metadata.ContainsKey("Database") ||

               !_metadata.ContainsKey("Username") || !_metadata.ContainsKey("Password"))

               {

                   throw new CustomVirtualizationException("Incomplete metadata: connection parameters missing");

               }

               else

               {

                   //Build the connection string with the parameters received from Bizagi Server

                   string sServer = _metadata["Server"];

                   string sDatabase = _metadata["Database"];

                   string sUsername = _metadata["Username"];

                   string sPassword = _metadata["Password"];

                   string sConn = "Server=" + sServer + ";Database=" + sDatabase + ";Uid=" + sUsername + ";Pwd=" + sPassword + ";";

 

                   _mySqlconn = new MySqlConnection(sConn);

                   _mySqlconn.Open();

               }

           }

       }

 

       public void CloseConnection()

       {

           if (_mySqlconn != null)

               _mySqlconn.Close();

           _mySqlconn = null;

       }

 

       #endregion

 

       #region IDisposable Members

 

       void  IDisposable.Dispose()

       {

           if (!_disposed)

           {

               _disposed = true;

           }

       }

 

       #endregion

 

       #region internal methods

 

       internal MySqlConnection GetConnection()

       {

           return this._mySqlconn;

       }

 

       internal DataSet ExecuteMySQLQuery(string sSQL)

       {

           DataSet ds = new DataSet();

 

           MySqlCommand command = new MySqlCommand(sSQL, this.GetConnection());

           MySqlDataAdapter adapter = new MySqlDataAdapter(command);

           adapter.Fill(ds);

 

           return ds;

       }

 

       internal DataSet ExecuteMySQLQuery(MySqlCommand command)

       {

           DataSet ds = new DataSet();

 

           MySqlDataAdapter adapter = new MySqlDataAdapter(command);

           adapter.Fill(ds);

 

           return ds;

       }

 

       internal void ExecuteNonQueryMySQL(string sSQL)

       {

           MySqlCommand command = new MySqlCommand(sSQL, this.GetConnection());

           command.ExecuteNonQuery();

       }

 

       /// <summary>

       /// Initializes command with current connection and transaction

       /// </summary>

       internal MySqlCommand GetCommand()

       {

 

           MySqlCommand m_oledbCmd = new MySqlCommand();

           m_oledbCmd.Connection = GetConnection();

           m_oledbCmd.CommandType = CommandType.Text;

           m_oledbCmd.Parameters.Clear();

 

           return m_oledbCmd;

       }

 

       #endregion

   }

 

}

 

 

 

Class Interface ICustomVirtualizationDA

using System;

using System.Collections.Generic;

using System.Data;

using System.Collections;

using System.Collections.Specialized;

using System.Text;

using BizAgi.EntityManager.Interfaces.CustomSource;

using MySql.Data.MySqlClient;

 

namespace BizagiMySQL

{

 

   public class CMySQLEntity : ICustomVirtualizationDA

   {

       /// <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 ICustomVirtualizationDA Members

 

       public void Init(ICustomProviderDA objProvider)

       {

           m_objSystem = (CMySQLProvider)objProvider;

       }

 

       public EntityOutputColumn[] AddEntity(string entitySource, EntityColumn[] addColumns, KeyColumn[] keyColumns, EntityAutoColumn[] autoColumns)

       {

           List<EntityOutputColumn> htResult = new List<EntityOutputColumn>();

 

           try

           {

               StringBuilder sbIntoList = new StringBuilder(addColumns.Length * 10);

               StringBuilder sbValuesList = new StringBuilder(addColumns.Length * 10);

 

               // Columns to add

               MySqlCommand command = this.m_objSystem.GetCommand();

 

               int pos = 0;

               foreach (EntityColumn oEntry in addColumns)

               {

                   if (pos++ > 0)

                   {

                       sbIntoList.Append(",");

                       sbValuesList.Append(",");

                   }

 

                   // Builds INTO clause and VALUES clauses separately

                   sbIntoList.Append(oEntry.Name);

 

                   // Adds values as parameters

                   sbValuesList.Append("@param"+pos);

                   command.Parameters.AddWithValue("@param"+pos, oEntry.Value);

               }

 

               if (sbIntoList.Length == 0 || sbValuesList.Length == 0)

               {

                   throw new CustomVirtualizationException("Nothing to insert.");

               }

 

               StringBuilder sbSQL = new StringBuilder();

 

               // Inserts values in entity table

               sbSQL.Append("INSERT INTO ");

               sbSQL.Append(entitySource);

               sbSQL.Append(" ( ");

               sbSQL.Append(sbIntoList);

               sbSQL.Append(" ) VALUES ( ");

               sbSQL.Append(sbValuesList);

               sbSQL.Append(" ) ");

 

               command.CommandText = sbSQL.ToString();

               command.ExecuteNonQuery();

 

               //Check if some of the Column is autoFilled... (it should be part of the key, but this is not really neccesary)

               //It can only be ONE autoFilledColumn

               if (autoColumns.Length > 0)

               {

                   command = m_objSystem.GetCommand();

                   command.CommandText = "SELECT last_insert_id()";

                   object ret = command.ExecuteScalar();

 

                   htResult.Add(new EntityOutputColumn { Name = autoColumns[0].Name, DataType = autoColumns[0].DataType, Value = ret });

               }

 

               return htResult.ToArray();

           }

           catch (Exception e)

           {

               throw new ApplicationException(e.Message);

           }

       }

 

       public EntityOutputColumn[] UpdateEntity(string entSource, EntityColumn[] updateColumns, KeyColumn[] keyColumns, EntityAutoColumn[] autoColumns)

       {

           MySqlCommand command = null;

           List<EntityOutputColumn> htResult = new List<EntityOutputColumn>();

 

           try

           {

               StringBuilder sbColsList = new StringBuilder(updateColumns.Length * 10);

 

               command = m_objSystem.GetCommand();

 

               // Loop thru attributes to update, to build SET clause

               int pos = 0;

               foreach (EntityColumn oEntry in updateColumns)

               {

                   if (pos++ > 0)

                   {

                       sbColsList.Append(",");

                   }

 

                   sbColsList.Append(oEntry.Name);

 

                   // Adds values as parameters

                   sbColsList.Append("=@param" + pos);

                   command.Parameters.AddWithValue("@param" + pos, oEntry.Value);

               }

 

               // Builds SQL update query

               if (updateColumns.Length > 0 && sbColsList.Length > 0)

               {

                   StringBuilder sbSQL = new StringBuilder();

 

                   sbSQL.Append(" UPDATE ");

                   sbSQL.Append(entSource);

                   sbSQL.Append(" SET ");

                   sbSQL.Append(sbColsList);

 

                   sbSQL.Append(" WHERE ");

                   sbSQL.Append(BuildWhereClause(command, keyColumns));

 

                   // Performs update!

                   command.CommandText = sbSQL.ToString();

                   command.ExecuteNonQuery();

                   command.Parameters.Clear();

               }

 

               return htResult.ToArray();

           }

           finally

           {

               // Dispose the SqlCommand

               if (command != null)

               {

                   command.Dispose();

               }

           }

       }

 

       public bool DeleteEntity(string entSource, KeyColumn[] keyColumns)

       {

           MySqlCommand command = null;

 

           try

           {

               command = m_objSystem.GetCommand();

 

               StringBuilder sbSQL = new StringBuilder();

 

               // Delete from entity table

               sbSQL.Append("DELETE FROM ");

               sbSQL.Append(entSource);

 

               sbSQL.Append(" WHERE ");

               sbSQL.Append(BuildWhereClause(command, keyColumns));

 

               // Performs delete!

               command.CommandText = sbSQL.ToString();

               return command.ExecuteNonQuery() > 0;

           }

           finally

           {

               // Dispose the SqlCommand

               if (command != null)

               {

                   command.Dispose();

               }

           }

       }

 

       public DataSet GetEntityInstance(string entitySource, KeyColumn[] keyColumns, string[] columnList)

       {

           try

           {

               MySqlCommand command = m_objSystem.GetCommand();

 

               StringBuilder sbSQL = new StringBuilder();

 

               sbSQL.Append(" SELECT ");

               sbSQL.Append(string.Join(",", columnList));

               sbSQL.Append(" FROM ");

               sbSQL.Append(entitySource);

               sbSQL.Append(" WHERE ");

               sbSQL.Append(BuildWhereClause(command, keyColumns));

 

               command.CommandText = sbSQL.ToString();

               DataSet ds = m_objSystem.ExecuteMySQLQuery(command);

 

               return ds;

           }

           catch (Exception e)

           {

               throw new ApplicationException(e.Message);

           }

       }

 

 

       public object GetAttributeValue(string entitySource, KeyColumn[] keyColumns, string attributeSource)

       {

           try

           {

               MySqlCommand command = m_objSystem.GetCommand();

 

               StringBuilder sbSQL = new StringBuilder();

 

               // Column to be retrieved

               sbSQL.Append("SELECT ");

               sbSQL.Append(attributeSource);

 

               // FROM clause

               sbSQL.Append(" FROM ");

               sbSQL.Append(entitySource);

 

               // Uses surrogate key value to find instance

               sbSQL.Append(" WHERE ");

               sbSQL.Append(BuildWhereClause(command, keyColumns));

 

               command.CommandText = sbSQL.ToString();

               DataSet ds = m_objSystem.ExecuteMySQLQuery(command);

               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());

               ds.Tables[0].TableName = "ENTITYINSTANCES";

               // Data table with values

               //DataTable dtAttribValues = ds.Tables[0];

             

               /*XmlDataDocument xmlDoc = new XmlDataDocument(ds);

               string sAux = xmlDoc.InnerXml;

               sAux = sAux.Replace("Table", "ENTITYINSTANCES");

               return sAux;*/

               return ds;

           }

           catch (Exception e)

           {

               throw new ApplicationException(e.Message);

           }

       }

 

       public bool ExistsEntityInstance(string entitySource, KeyColumn[] keyColumns)

       {

           try

           {

               MySqlCommand command = m_objSystem.GetCommand();

 

               StringBuilder sbSQL = new StringBuilder();

 

               sbSQL.Append(" SELECT ");

               sbSQL.Append(" count(1) ");

               sbSQL.Append(" FROM ");

               sbSQL.Append(entitySource);

               sbSQL.Append(" WHERE ");

               sbSQL.Append(BuildWhereClause(command, keyColumns));

 

               command.CommandText = sbSQL.ToString();

               object ret = command.ExecuteScalar();

 

               int result;

               if (int.TryParse(ret.ToString(), out result))

                   return result > 0;

               else

                   return false;

           }

           catch (Exception e)

           {

               throw new ApplicationException(e.Message);

           }

       }

 

       public int ExistsEntityInstance(string entitySource, EntityColumn columnToFind, KeyColumn[] keyColumns)

       {

           try

           {

               MySqlCommand command = m_objSystem.GetCommand();

 

               StringBuilder sbSQL = new StringBuilder();

 

               sbSQL.Append(" SELECT ");

               sbSQL.Append(BuildKeySelectClause(keyColumns));

               sbSQL.Append(" FROM ");

               sbSQL.Append(entitySource);

               sbSQL.Append(" WHERE ");

               sbSQL.Append(columnToFind);

               sbSQL.Append(" = ");

               sbSQL.Append("@keyParam");

 

               command.Parameters.AddWithValue("@keyParam", columnToFind.Value);

 

               command.CommandText = sbSQL.ToString();

               object ret = command.ExecuteScalar();

 

               int result;

               if (int.TryParse(ret.ToString(), out result))

                   return result;

               else

                   return 0;

 

           }

           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(MySqlCommand command, KeyColumn[] keyColumns)

       {

           StringBuilder sbSQL = new StringBuilder();

 

           for (int iKeyIndex = 0; iKeyIndex < keyColumns.Length; iKeyIndex++)

           {

               if (iKeyIndex > 0)

                   sbSQL.Append(" AND ");

 

               // Associate columns with values....

               sbSQL.Append(keyColumns[iKeyIndex].Name);

               sbSQL.Append(" = ");

               sbSQL.Append("@keyParam"+iKeyIndex);

 

               command.Parameters.AddWithValue("@keyParam"+iKeyIndex, keyColumns[iKeyIndex].Value);

           }

 

           return sbSQL.ToString();

       }

 

       public string BuildKeySelectClause(KeyColumn[] keyColumns)

       {

           StringBuilder builder = new StringBuilder();

 

           for (int i = 0; i < keyColumns.Length; i++)

           {

               if (i > 0) builder.Append(", ");

 

               var keyColumn = keyColumns[i];

               builder.Append(keyColumn.Name);

           }

 

           return builder.ToString();

       }

   }

}

 

 

 

Parameters Class

using BizAgi.EntityManager.Interfaces;

using BizAgi.EntityManager.Interfaces.CustomSource;

using MySql.Data.MySqlClient;

 

namespace BizagiMySQL {

 

 using System.Data;

 

 public class CMySqlParameter

 {

         private BizagiDataType                dataType;                                                //--- Provider-independent data type                        

       private MySqlDbType         dbDataType;                                                //--- Provider-specific datatype of the parameter

     private string                                paramName;                                                //--- The Internal name of the parameter

     private string                                originalParamName;                                //--- The Original o External name of the parameter

 

         //-------------------------------------------------------------------------------------------------------

         //--- Class Constructors

         //--- Overloaded:        Yes

         //-------------------------------------------------------------------------------------------------------

         public CMySqlParameter(string sParameterName, BizagiDataType BizagiDataType, int iSize, object oValue) {

                 ParameterName = sParameterName;

                 dataType = BizagiDataType;

                 dbDataType = getSqlDataType(BizagiDataType);

                 Size = iSize;

                 Value = oValue;

                 Direction = ParameterDirection.Input;

         }

 

         public CMySqlParameter (string sParameterName, BizagiDataType BizagiDataType, int iSize, object oValue, ParameterDirection eDirection) {

                 ParameterName = sParameterName;

                 dataType = BizagiDataType;

                 dbDataType = getSqlDataType(BizagiDataType);

                 Size = iSize;

                 Value = oValue;

                 Direction = eDirection;

         }

 

         public CMySqlParameter (string sParameterName, BizagiDataType BizagiDataType, int iSize, byte iPrecision, byte iScale, object oValue, ParameterDirection eDirection)

         {

                 ParameterName = sParameterName;

                 dataType = BizagiDataType;

                 dbDataType = getSqlDataType(BizagiDataType);

                 Size = iSize;

                 Precision = iPrecision;

                 Scale = iScale;

                 Value = oValue;

                 Direction = eDirection;

         }

 

         public CMySqlParameter (string sParameterName, BizagiDataType BizagiDataType, int iSize, byte iPrecision, byte iScale, object oValue, ParameterDirection eDirection, string sSourceColumn, bool bUseCurrentSourceColumnValue)

         {

                 ParameterName = sParameterName;

                 dataType = BizagiDataType;

                 dbDataType = getSqlDataType(BizagiDataType);

                 Size = iSize;

                 Precision = iPrecision;

                 Scale = iScale;

                 Value = oValue;

                 Direction = eDirection;

                 SourceColumn = sSourceColumn;

                 UseCurrentSourceColumnValue = bUseCurrentSourceColumnValue;

         }

 

       public ParameterDirection Direction { get; private set; }

 

       public object Value { get; private set; }

 

       public string SourceColumn { get; private set; }

 

       public string ParameterName

         {

           get { return paramName; }

             set

                 {

                         string sResult = value;

                         originalParamName = value;

 

                         paramName = sResult.Replace ("@","?");

                 }

         }

 

         //--

         // Only for this IParameter implementation

         // ReadOnly -- Return the original name of the parameter

         //--

         public string OriginalName

         {

           get { return originalParamName; }

         }

 

         public BizagiDataType Type

         {

           get { return dataType; }

           set

           {

               dataType = value;

               dbDataType = getSqlDataType(dataType);

           }

         }

 

     public int Size { get; set; }

 

     public byte Precision { get; set; }

 

     public byte Scale { get; set; }

 

     public bool UseCurrentSourceColumnValue { get; set; }

 

     public MySqlDbType DataType

         {

           get { return dbDataType; }

         }

 

         private static MySqlDbType getSqlDataType(BizagiDataType BizagiDataType) {

           MySqlDbType oDbType = MySqlDbType.Int32;

 

                 switch (BizagiDataType) {

 

                         case BizagiDataType.BigInt:

                   oDbType = MySqlDbType.Int64;

                                 break;

 

                         case BizagiDataType.Int:

                   oDbType = MySqlDbType.Int32;

                                 break;

 

                         case BizagiDataType.SmallInt:

                   oDbType = MySqlDbType.Int16;

                                 break;

 

                         case BizagiDataType.TinyInt:

                   oDbType = MySqlDbType.Byte;

                                 break;

 

                         case BizagiDataType.Boolean:

                   oDbType = MySqlDbType.Bit;

                                 break;

 

                         case BizagiDataType.Decimal:

                   oDbType = MySqlDbType.Decimal;

                                 break;

 

                         case BizagiDataType.Numeric:

                   oDbType = MySqlDbType.Float;

                                 break;

 

                         case BizagiDataType.Money:

                   oDbType = MySqlDbType.Decimal;

                                 break;

 

                         case BizagiDataType.Float:

                   oDbType = MySqlDbType.Float;

                                 break;

 

                         case BizagiDataType.Real:

                   oDbType = MySqlDbType.Float;

                                 break;

 

                         case BizagiDataType.DateTime:

                   oDbType = MySqlDbType.DateTime;

                                 break;

 

                         case BizagiDataType.SmallDateTime:

                   oDbType = MySqlDbType.DateTime;

                                 break;

 

                         case BizagiDataType.Char:

                   oDbType = MySqlDbType.VarChar;

                                 break;

 

                         case BizagiDataType.VarChar:

                   oDbType = MySqlDbType.VarChar;

                                 break;

                 

                         case BizagiDataType.Text:

                   oDbType = MySqlDbType.Text;

                                 break;

                 

                         case BizagiDataType.Binary:

                   oDbType = MySqlDbType.Binary;

                                 break;

 

                         case BizagiDataType.VarBinary:

                   oDbType = MySqlDbType.VarBinary;

                                 break;

 

                         case BizagiDataType.Image:

                   oDbType = MySqlDbType.VarBinary;

                                 break;

 

                         case BizagiDataType.Guid:

                   oDbType = MySqlDbType.Guid;

                                 break;

 

               case BizagiDataType.NChar:

                   oDbType = MySqlDbType.VarChar;

                   break;

 

               case BizagiDataType.NVarChar:

                   oDbType = MySqlDbType.VarChar;

                   break;

 

               case BizagiDataType.NText:

                   oDbType = MySqlDbType.Text;

                   break;

 

                         default:

                                 break;

                 }

 

                 return oDbType;

 

         }

 }

}

 

 

Class Interface IDatabaseProperties

using BizAgi.EntityManager.Interfaces.CustomSource;

 

namespace BizagiMySQL

{

   /// <summary>

   /// Specific database constants

   /// </summary>

   public class CMySqlDatabaseConstants : IDatabaseProperties

   {

       #region Singleton

 

       private static readonly CMySqlDatabaseConstants MySqlInstance = new CMySqlDatabaseConstants();

 

       public static CMySqlDatabaseConstants Instance

       {

           get { return MySqlInstance; }

       }

 

       protected CMySqlDatabaseConstants() {}

 

       #endregion

 

       #region Fields / Properties

 

       /// <summary>

       /// Open character for enclose a name that is reserved word or has special characters

       /// </summary>

       private readonly string m_sQuotedNameOpenChar = "'";

       public string QuotedNameOpenChar { get { return m_sQuotedNameOpenChar; } }

 

       /// <summary>

       /// Close character for enclose a name that is reserved word or has special characters

       /// </summary>

       private readonly string m_sQuotedNameCloseChar = "'";

       public string QuotedNameCloseChar { get { return m_sQuotedNameCloseChar; } }

 

       /// <summary>

       /// Indicates if by default comparisons between strings are case sensitive

       /// </summary>

       private readonly bool m_bCaseSensitive = false;

       public bool CaseSensitive { get { return m_bCaseSensitive; } }

 

       internal int MaxDbIdLength

       {

           get { return 128; }

       }

 

       #endregion

   }

}

 

Once the implementation for these classes is done, build your component as a class library (for this example, as a dll assembly).

 

 

Configuring the data provider

In this step, configure the System and data provider for Virtualization; by specifying which library will be used (the custom component we created previously).

 

note_pin

Take into account that this configuration is done through the Advanced Virtualization method, which requires that the entities to virtualize are already created in Bizagi's data model.

 

1. Click External Systems in the Expert View of Bizagi.

Then either right-click the Systems element and select New System from the drop-down menu, or click directly on New System in the standard menu bar.

 

VR_Both02_NewSystem

 

2. Enter a Name, Display name and Description for the new system and tick the Enable Virtual Entities and Replication for this System checkbox.

Click Ok to save changes.

 

 

CustomVirtualization_Image002

 

 

3. Click the plus sign (+) beside the newly created system.

Right-click the New Provider option.

 

CustomVirtualization_Image003

 

4. In the New Provider window enter a Name, Display name and Description in the Basic configuration tab.

Check the Enable Virtual Entities for this Provider option. Also check Enable Replication for this Provider (if it applies).

 

CustomVirtualization_Image004

 

5. Select the Connection details tab and click New Assembly.

 

CustomVirtualization_Image005

 

6. Enter a Name, Display name, Namespace and Description.

Then click Select Assembly to select the .dll of the custom virtual library previously built.

Click Ok in the New Assembly window.

 

CustomVirtualization_Image006

 

 

7. Click the New Class button and in the New Class for Assembly window, enter the name of the class that implements the IProviderVirtualDA interface.

Click Ok.

 

CustomVirtualization_Image007

 

8. Now in the Development Properties tab, include the connection properties by clicking the Metadata button to create a new Property-Value paired row.

Click Ok.

 

CustomVirtualization_Image008

 

9. Expand the new provider element, select the Virtual Entities element and create a New Dat Virtualization Class.

 

CustomVirtualization_Image009

 

 

10. Select the assembly from the drop-down list in the New Class for assembly window (if the Entity class is in the same assembly as the Provider class) or include a new one by clicking on New Assembly.

 

 

CustomVirtualization_Image010

 

Click New Class and complete the necessary fields in the new window.

Click Ok to complete the Entity Class Configuration.

 

CustomVirtualization_Image011

 

 

11. Right-click the new entity class element and select the Add Entity to Virtual option.

 

CustomVirtualization_Image012

 

 

12. In the Add Entity to System window, first select from the Master entities drop-down list the desired entity.

Then, in the External Source field, enter the name for the source's table or view.

Next, select one (or more) from the list of attributes of the Bizagi entity, to be the business key (an attribute that will have a unique value so that it can be used to differentiate one record from another).

 

Click Ok to save this changes.

 

 

CustomVirtualization_Image013

 

13. Select the virtual entity and for each attribute select the corresponding external attribute. This is done by right-clicking the attribute and selecting Properties.

 

CustomVirtualization_Image014

 

 

In the Edit Attribute window, enter the external attribute name and click OK.

 

CustomVirtualization_Image015

 

 

note_pin

If your custom Virtualization library has references to other assemblies it is necessary to copy these assemblies: to the Bizagi Studio folder, (by default at: "C:\Program Files\Bizagi\Bizagi Standard\Studio"), and into the bin of the web application folder (by default: at "C:\Bizagi\Projects\[PROJECT_NAME]\WebApplication\bin").

The latter is done so that Virtualization works in run-time.

 

 

Verifying Virtualization (Checkpoint)

Once Virtualization is set, you may choose to verify that it properly synchronizes the Master Entities with the external tables.

 

Take into account that with Virtualization, Processes in Bizagi access information stored across multiple data sources in execution time (run-time).

This functionality is transparent to end users in the Work Portal; therefore you need to verify that Virtualization is working correctly.  This is done by checking that the Activity's form in your Processes use information from virtualized entities.

 

In addition to this, explicitly check that the values are being synchronized with the source, you may choose to see these values displayed in specific entities through the Entities module (available for the Development environment).

 

To view how to verify that the Virtualization was set up correctly or is running properly, refer to Verifying Virtualization.