Executing Direct SQL Statements in a Process or Configuration Web Service in Creatio (formerly bpm’online)

Creatio does have a lot of options for working with objects/data with an Entity object model, EntitySchemaQuery, Select/Update/Insert objects (I’ll get around to writing articles on these topics as well). You can also execute a SQL statement directly when needed as well.

Before we get into the code, it’s worth mentioning, executing a SQL statement directly in the database won’t fire signals for processes listening for various events, it won’t consider any user security or restrictions, and it won’t fire any entity object events. Still, it’s a valid tool to use in Creatio when the situation requires a direct SQL update for performance reasons.

The code is pretty simple, it uses a CustomQuery object, which takes a UserConnection. Let’s take a look at using it to execute a SQL statement that don’t return any results, like an INSERT/UPDATE/DELETE statement.

var sql = "update mytable set myfield = 'my value' where somecondition = true";

var query = new CustomQuery(UserConnection, sql);
using (var db = UserConnection.EnsureDBConnection())
{
    query.Execute(db);
}

You can set parameters with CustomQuery as well:

var sql = "update mytable set myfield = @myvalue where somecondition = @mycondition";

var query = new CustomQuery(UserConnection, sql);
query.Parameters.Add("@myvalue", "some value");
query.Parameters.Add("@mycondition", "some condition");

using (var db = UserConnection.EnsureDBConnection())
{
    // you can also set a timeout if needed
    db.CommandTimeout = 300;

    query.Execute(db);
}

If you need a value returned, you can get that as well:

var sql = "select count(*) from mytable where somecondition = true";

var query = new CustomQuery(UserConnection, sql);
using (var db = UserConnection.EnsureDBConnection())
{
    var count = query.ExecuteScalar<int>(db);
}

Or even get a DataReader with the results:

var sql = "select myfield1, myfield2 from mytable where somecondition = true";

var query = new CustomQuery(UserConnection, sql);
using (var db = UserConnection.EnsureDBConnection())
{
    using (var reader = query.ExecuteReader(db))
    {
        while (reader.Read())
        {
            var myfield1 = reader.GetString(0);
            // do something...
        }
    }
}

These methods will work in ScriptTasks in a process or in a configuration web service as well. You can even use this method to fill the DataSet for a DevExpress report.