Miscellaneous C# Details

Free Memory Threshold

An update operation on a transient database can run out of memory in the middle of a transaction, and in this case it is quite possible that the transaction won’t be able to be rolled back. To address this, the McoSqlEngine::setFreeMemoryThreshold() method is used in the C++ API. However, in the C# API this memory limit is managed by setting the Database::Parameter sqlWorkspaceLimit passed to the Database::open() call. For example:

    parameters.sqlWorkspaceLimit = 2 * 1024*1024;
    ...
    db.open("quotadb", parameters, devs);
     

(Note that the default value for sqlWorkspaceLimit is 0 which means unlimited memory.)

Managing Transactions

Whereas the C++ API uses implicit transactions by default, where each call of executeStatement() or executeQuery() internally performs a transaction start and commit, C# applications need to perform explicit transaction blocking. This is done by explicitly starting and committing (or rolling back) the transaction as demonstrated in the code snippet below:

    SqlLocalConnection con = db.ConnectSql();
    con.StartTransaction(Database.TransactionType.ReadWrite);
    con.ExecuteStatement("insert into MyTable (pk,value) values (?,?)", 2012, "Good bye");
    con.ExecuteStatement("insert into MyTable (pk,value) values (?,?)", 2013, "Hello");
    con.CommitTransaction();
     

(Note also that you can use the Connection::CheckpointTransaction() method to update indexes and make objects visible to queries while the transaction is still open.)

Using the C# SqlAggregator

The SqlAggregator class acts like a distributed connection, but within a single process. In order to use the SqlAggregator the C# application creates an array of SqlLocalConnection objects to run as shards. Then it starts all of the SqlLocalConnection objects and passes the array to the SqlAggregator constructor. For example:

    SqlLocalConnection [] shards = new SqlLocalConnection[NSHARDS];
    for (int i = 0; i < NSHARDS; i++)
    {
        shards[i] = create_database("aggg" + Convert.ToString(i), config, is_dptr);
    }
    SqlAggregator agg = db.ConnectSqlAggregator(shards);
     

Once the aggregator has been created and initialized it can be used exactly as the SqlLocalConnection. For example to perform queries:

    agg.executeStatement("2:insert into MyTable (pk,value) values (?,?)", 2013, "Hello"));
 

When no longer required, the aggregator must be closed through the SqlAgregator.Disconnect() method, and after that close the SqlLocalConnection objects:

    agg.Disconnect();
    for (int i = 0; i < NSHARDS; i++)
    {
        shards[i].Disconnect();
    }
     

SQL String Length

Whereas for the ExecuteStatement() and the ExecuteQuery() methods of the SqlLocalConnection class, there is no limit to the length of the statement string argument, for the SqlRemoteConnectionclass the statement string length is limited by the bufferSize argument passed to the constructor. The default value is 64 Kb. (This applies as well to the SqlAggregator class). Please refer to the SQL Statement Strings page for further details.