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.
|
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\").
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).
|
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.
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.
3. Click the plus sign (+) beside the newly created system.
Right-click the New Provider option.
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).
5. Select the Connection details tab and click New Assembly.
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.
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.
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.
9. Expand the new provider element, select the Virtual Entities element and create a New Dat Virtualization Class.
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.
Click New Class and complete the necessary fields in the new window.
Click OK to complete the Entity Class Configuration.
11. Right-click the new entity class element and select the Add Entity to Virtual option.
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.
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.
In the Edit Attribute window, enter the external attribute name and click OK.
|
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.