From Business Process Management, BPM and Workflow Automation Wiki | BizAgi BPMS

<keywords content="keywords"> stored procedures, oracle integration, sql server integration, data level integration, data level, sp, procedures, select, insert, update </keywords>

Contents 1 How to execute stored procedures from Bizagi 1.1 Developing the component in .Net which connects to the external databases 1.2 Registering the component in Bizagi's Component Library 1.3 Including the stored procedures invocation from a Bizagi business rule 1.4 Related Articles

How to execute stored procedures from Bizagi

In this article we will describe how to execute stored procedures in external databases from a Bizagi process.

This integration is possible by developing a component in .Net which: manages a connection to the external database and provides the methods to execute the stored procedures.
By registering this component through Bizagi's Component library feature (available in the Enterprise edition), it will be possible to execute the stored procedures from a Bizagi business rule.

Take into account that there are other possibilities for integration with Bizagi processes and external systems.
This specific example uses the Component Library integration feature for a broker-based integration with the remote databases.
This approach is common for integration scenarios involving legacy systems or any other system that has no service oriented architecture design.

In this example, we will illustrate specifically the invocation of two stored procedures (one in a Microsoft SQL Server database, and the other one in an Oracle database) from two points in the "Personal Loan Request" process in Bizagi.

To illustrate this, the following diagram represents the "how-to" architecture and workings:

The Personal Loan Request process has an "Applicant Analysis" subprocess.
The Applicant Analysis subprocess goes through an "Import Previous Products" automatic task, in which Bizagi will lookup if the current applicant has any product history with the SQL Server external system and load the information into the process. Afterwards, in another automatic task called "Calculate Payment Capacity", Bizagi will use existing business rules in an Oracle external system, to obtain a calculated payment capacity amount, according to some of the applicant's information (income, expenditure, total value of equities, etc.).

The model for this example has been extracted and simplified from the Free Process Templates available in Bizagi: 

In summary, the steps involved to cover this integration presented are:
1. Developing the .Net component which connects to the external systems.
2. Registering the component in Bizagi's Component Library.
3. Including the stored procedures invocation by calling the component's methods from a Bizagi business rule.

Developing the component in .Net which connects to the external databases

For this example, we will create a new "Class Library" project using Visual Studio 2008 and include as references the necessary connection drivers for our external database connections.
The connection to the Oracle database, in this example, is made using Oracle Data Provider for .Net (ODP.net), and therefore we will include the "Oracle.DataAccess" assembly.

Our example project is called "SPProvider" and consists of 2 main classes: "Controller.cs" and "ImplementationProvider.cs".
ImplementationProvider.cs handles the connection and methods to run stored procedures. It is coded as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace SPProvider
{
    internal class ImplementationProvider
    {

        protected string _connectionString;
        protected SqlConnection conn = null;
        protected Oracle.DataAccess.Client.OracleConnection connO = null; 
        protected System.Collections.ArrayList parameters = new System.Collections.ArrayList();
  

        internal string ConnectionString
        {
            set { _connectionString = value; }
            get { return _connectionString; }
        }

        internal ImplementationProvider(string sKey, string engine)
        {
            //ConnectionString = ConfigurationManager.AppSettings[sKey].ToString();
            
            if (sKey.Equals("SYS2") && engine.Equals("ORACLE"))
            {
                ConnectionString = 
                    "Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = myPort)))" +
                    "(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = myOracleServiceName))); User Id = myUserDb; Password = myPassword;";
                connO = new Oracle.DataAccess.Client.OracleConnection(this.ConnectionString);
            }
            else
            {
                ConnectionString = 
                    "Persist Security Info=True;Current Language=us_english;" +
                    "Data Source=myDatabaseInstance;Initial Catalog=myDatabase;User ID=myLogin;Password=myPassword";
                conn = new SqlConnection(this.ConnectionString);
            }
        }

        internal void AddParameter_SQLServer(string sParamName, string sParamValue, ParameterDirection dir)
        {
            SqlParameter param;

            // Requiered SP development team change: pass NULL values not empty strings
            if (sParamValue == "")
                param = new SqlParameter(sParamName, DBNull.Value);
            else
                param = new SqlParameter(sParamName, sParamValue);
            parameters.Add(param);
            param.Direction = dir;
        }

        internal void AddParameter_Oracle(string sParamName, int sParamValue, ParameterDirection dir)
        {
            Oracle.DataAccess.Client.OracleParameter param;

            param = new Oracle.DataAccess.Client.OracleParameter(sParamName, sParamValue);
            
            parameters.Add(param);
            param.Direction = dir;
        }


        internal DataSet RunStoredProcedureSQLServer(string storeprocedure, string tableName)
        {
            SqlCommand comm = new SqlCommand(storeprocedure, conn);
            for (int i = 0; i < parameters.Count; i++)
            {
                comm.Parameters.Add((SqlParameter)parameters[i]);

            }
            comm.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(comm);

            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables.Count > 0)
                ds.Tables[0].TableName = tableName;

            parameters = new System.Collections.ArrayList();
            return ds;
        }

        internal int RunStoredProcedureOracle(string storeprocedure, string tableName)
        {
            int resp = 0;


            Oracle.DataAccess.Client.OracleCommand commO = 
                new Oracle.DataAccess.Client.OracleCommand(storeprocedure, connO);
            for (int i = 0; i < parameters.Count; i++)
            {
                commO.Parameters.Add((Oracle.DataAccess.Client.OracleParameter)parameters[i]);

            }
            commO.CommandType = CommandType.StoredProcedure;

            try
            {
                commO.Connection.Open();
                resp = commO.ExecuteNonQuery();
                Oracle.DataAccess.Client.OracleParameter tempParam = commO.Parameters[3];
                resp = Convert.ToInt32(tempParam.Value);
            }
            finally
            {
                commO.Connection.Close();
            }

            parameters = new System.Collections.ArrayList();
            return resp;
        }


    }
}

The 2 stored procedures used for this example have the following inputs and outputs:
+ The one in SQL Server's database:

CREATE PROCEDURE sp_GetCustomerProducts @AppId INTEGER AS select * from Products p  ... 

+ The one in Oracle's database:

CREATE OR REPLACE PROCEDURE SP_CALCULATE_PAYMENTCAPACITY 
(Incomes IN NUMBER, Expenses IN NUMBER, Equity IN OUT NUMBER, Capacity OUT NUMBER) AS ... 

Controller.cs is our main entry point and has the static methods for the invocation at the process' business rules. It is coded as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;

namespace SPProvider
{
    public class Controller
    {
        public static DataSet GetProductsList(int appId, string storedPr)
        {
            ImplementationProvider myProvider;
            DataSet ds = null;

            try
            {
                //instance provider
                myProvider = new ImplementationProvider("SYS1", "SQLSERVER");
                myProvider.AddParameter_SQLServer("AppId", appId.ToString(), ParameterDirection.Input);

                ds = myProvider.RunStoredProcedureSQLServer(storedPr, "NA");
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Source + ":" + ex.Message);
            }
        }

        public static int CalculateCapacity(Hashtable inputs, Hashtable inouts, Hashtable outputs, string storedPr)
        {
            ImplementationProvider myProvider;
            int resp = 0;

            try
            {
                //instance provider
                myProvider = new ImplementationProvider("SYS2", "ORACLE");

                foreach (string iname in inputs.Keys)
                {
                    myProvider.AddParameter_Oracle(iname, Convert.ToInt32(inputs[iname]), ParameterDirection.Input);
                }
                foreach (string ioname in inouts.Keys)
                {
                    myProvider.AddParameter_Oracle(ioname, Convert.ToInt32(inouts[ioname]), ParameterDirection.InputOutput);
                }
                foreach (string oname in outputs.Keys)
                {
                    myProvider.AddParameter_Oracle(oname, Convert.ToInt32(outputs[oname]), ParameterDirection.Output);
                }

                resp = myProvider.RunStoredProcedureOracle(storedPr, "NA");
                
                return resp;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Source + ":" + ex.Message);
            }
        }


    }
}

Notice that the namespace for our Controller.cs is called "SPProvider".
Finally, we will build the project and produce our "SPProvider.dll" assembly output.
You may download here this complete Visual Studio 2008 project (remember that the connection already configured in this example requires the Oracle Data Provider for .Net installation).

Registering the component in Bizagi's Component Library

We will now go to Bizagi Studio's Component Library feature and register the developed "SPProvider" component by adding a new component as shown:

After we specify a name, display name and description, select the assembly for upload (browse the output dll file).
Ensure you specify that the Namespace corresponds to the one defined in your Visual Studio project, as mentioned in the previous step (for our example, the Namespace is "SPProvider").



Finally, our registered component libraries view should look like:

Including the stored procedures invocation from a Bizagi business rule

Methods in the registered component may now be used from within processes in our Bizagi project, by calling them from a Bizagi rule.
In this example, two rules are created to make use of our component (one for each integration point in our "Applicant Analysis" process).

The first rule is called "getPreviousProducts", and is set in the service task "Import Previous Products" at the onExit activity action.
The second rule is called "calculatePaymentCapacity", and is set in the service task "Calculate Payment Capacity" at the onExit activity action.

Creating these rules is done in the second option in the Process Wizard's step 4 "Business Rules: Activity Actions (Events)". To include the lines which call our component's methods, we include an expression module in the rule and call the component's methods as Namespace.Class.Method();.

Our "getPreviousProducts" rule will look like:

The expression module contains the following:

var ApplicantId = <ApplicantsAnalysis.Applicant.Identification>;
var MyResult = SPProvider.Controller.GetProductsList(ApplicantId, "sp_GetCustomerProducts");
for(var i = 0; i < MyResult.Tables[0].Rows.Count; i++)
{
	var tempRow = MyResult.Tables[0].Rows[i];
	var newrecord = Me.addRelation("ApplicantsAnalysis.PreviousProducts");
	newrecord.setXPath("Name", tempRow["Product"]);
	newrecord.setXPath("Observations", tempRow["Observations"]);
}

Take into account that our method's invocation will return a DataSet for the SQL Server stored procedure.
We take that result dataset and create the records in our previous products collection. This information will be shown in an "Applicant's Products" grid, in the next activity automatically.

Our "calculatePaymentCapacity" rule will look like this:

The expression module contains the following:

var inputsH = new Hashtable();
inputsH.Add("Incomes", <ApplicantsAnalysis.Applicant.AnnualIncome>);
inputsH.Add("Expenses", <ApplicantsAnalysis.Applicant.AnnualExpenses>);
var inoutsH = new Hashtable();
inoutsH.Add("Equity", <ApplicantsAnalysis.Applicant.EquititesTotal>);
var outputsH = new Hashtable();
outputsH.Add("Capacity", 0);
var v_resp = SPProvider.Controller.CalculateCapacity(
inputsH, inoutsH, outputsH, "SP_CALCULATE_PAYMENTCAPACITY");
<ApplicantsAnalysis.PaymentCapacity> = v_resp;

Notice that this stored procedure's method invocation receives hashtable-structure parameters (one for each of the parameter types in the stored procedure), mainly to group and support the "n" parameters expected by the stored procedure.
Since it returns a calculated value (an integer), we just need to assign that value to our data model attribute "PaymentCapacity".

At this point, integration through the component library is set for our Bizagi process to execute the stored procedures in the external systems. 
By clicking Next in the "Include Initial Information", Bizagi will execute the stored procedures and update its response information:

Related Articles

• Integration with Bizagi processes.
• Using the component library feature in Bizagi.

<comments />