Replicating C#’s using statement functionality in C++
Hi Community,
Today’s post is about a project I’ve recently started working on, it’s a C++ application for Linux built with GTK+ and SQL Server vNext for Linux. I’m also using CLion which is my favorite cross-platform IDE when doing plain C++ and Glade as my UI editor.
I chose not to use Qt, because I’d like to demonstrate how to use disparate tools and editors at our disposal to being able to put a solution together. Qt like Visual Studio provides pretty much a comprehensive and complete development environment, and Qt is awesome but it’s very similar to MFC in that aspect, once one starts building a solution with it, it has to be used all along.
I’m using g++ (compiling with –std=c++14, actually) in conjunction with UnixODBC and FreeTDS (information on how to install these two here in this post) to access SQL Server vNext running on another Linux-based PC. I have structured the solution as an MVC solution, besides having a DAL responsible to perform operations in the database.
The way ODBC works is through handles (mostly for environment, connections and statements) that are prepared prior executing any command on the database server. This is where having something similar to using statement in C# comes in handy. As you must be aware, the using statement besides being able to dispose of an object (most of the times, specially when object enclosed in it doesn’t hold any strong or pinned references) provides developers with the opportunity to execute code in an “atomic” fashion, in order to explain this better refer to code snippet below that enables and enforces a transaction when multiple threads are writing to a database, if an error condition is encountered everything is rolled back in the database, but more importantly the resources in use are disposed of, it’s similar to the principle of “unit of work” pattern.
public void CreateRecords()
{
var hasError = false;
var mutex = new Mutex();
var mutex2 = new Mutex();
var batches = new List<Guid>();
Enumerable.Range(1, 1000).Select(x => x * x).ToList().ForEach(z => batches.Add(Guid.NewGuid()));
Task.Factory.StartNew(() => {
using (var ts = new TransactionScope()) {
var rootTransaction = Transaction.Current;
using (var conn = new SqlConnection(@"Data Source=.\Central;Integrated Security=true;Initial Catalog=DemoService")) {
conn.Open();
Parallel.ForEach(batches, (item, loopState) => {
try {
var dependantTransaction = rootTransaction.DependentClone(DependentCloneOption.RollbackIfNotComplete);
mutex2.WaitOne();
using (var cmd = new SqlCommand(string.Format("Insert Into MyTestTable (InsertedBy, Value) Values('{0}', '{1}')",
new object[] { Thread.CurrentThread.ManagedThreadId, item.ToString() }), conn))
cmd.ExecuteNonQuery();
mutex2.ReleaseMutex();
if (DateTime.Now.Second % 2 != 0) throw new Exception(); // To simulate error condition
dependantTransaction.Complete();
} catch (Exception ex) {
mutex.WaitOne();
if (!hasError)
hasError = true;
mutex.ReleaseMutex();
loopState.Stop();
return;
}
});
}
if (!hasError)
ts.Complete();
}
});
}
This is one of the cool things about C#, the language takes care of resources once we have finished using them, but lazy developers (and there are quite a lot) can’t be bothered to do it right, so find themselves producing leaky code. Now, back to having something similar to using in C++, refer to SqlDal class below and see a function template called “Using” that takes a lambda as the argument. The lambda contains the code that will make use of the resources allocated and released by function template.
#include "CommonHeaders.h"
#include "ContactModel.h"
#include <sql.h>
#include <sqlext.h>
class SqlDal {
private:
std::string ConnectionString;
void ReleaseConnectionResources(ConnectionResult &conn);
std::string ExtractErrorFromDriver(SQLHANDLE handle, SQLSMALLINT type);
protected:
ConnectionResult GetConnection(std::string connString);
// Replicating C#'s using statement through a templated function and lambda
template <class Func>
void Using(Func&& dataAccessCode) {
auto conn = GetConnection(ConnectionString);
dataAccessCode(conn);
ReleaseConnectionResources(conn);
}
public:
std::string ConnectionString_get();
void ConnectionString_set(std::string connStr);
Result<std::vector<Contact>> RetrieveRecords();
};
The method that implements “Using” is RetrieveRecords depicted below.
Result<std::vector<Contact>> SqlDal::RetrieveRecords() {
Result<std::vector<Contact>> retval;
auto dataAccessCode = [&] (ConnectionResult &conn){
SQLRETURN result;
Contact newRecord;
SQLLEN lenContactId, lenFirstName, lenLastName, lenPhoneNumber, lenEmail;
retval.ReturnCode = SQLExecDirect(conn.stmt, (SQLCHAR*) "Select * from dbo.Contact", SQL_NTS);
if (!SQL_SUCCEEDED(retval.ReturnCode)) {
retval.Success = false;
retval.Exception = ExtractErrorFromDriver(conn.stmt, SQL_HANDLE_STMT);
} else {
SQLBindCol(conn.stmt, 1, SQL_INTEGER, &newRecord.ODBCFields.ContactId, ContactId_Length, &lenContactId);
SQLBindCol(conn.stmt, 2, SQL_C_CHAR, &newRecord.ODBCFields.FirstName, FirstName_Length, &lenFirstName);
SQLBindCol(conn.stmt, 3, SQL_C_CHAR, &newRecord.ODBCFields.LastName, LastName_Length, &lenLastName);
SQLBindCol(conn.stmt, 4, SQL_C_CHAR, &newRecord.ODBCFields.PhoneNumber, PhoneNumber_Length, &lenPhoneNumber);
SQLBindCol(conn.stmt, 5, SQL_C_CHAR, &newRecord.ODBCFields.Email, Email_Length, &lenEmail);
while((result = SQLFetch(conn.stmt)) != SQL_NO_DATA) {
if (result == SQL_SUCCESS || result == SQL_SUCCESS_WITH_INFO)
retval.Data.push_back(Contact(newRecord.ODBCFields));
}
retval.Success = true;
}
};
// Replicating C#'s using here. Connection's resources are allocated, connection is open and used to execute
// data access code passed in as a lambda, then connection is closed and related resources are freed.
Using(dataAccessCode);
return retval;
}
void SqlDal::ReleaseConnectionResources(ConnectionResult &conn) {
if (conn.Success())
SQLDisconnect(conn.dbc);
SQLFreeHandle(SQL_HANDLE_STMT, conn.stmt);
SQLFreeHandle(SQL_HANDLE_DBC, conn.dbc);
SQLFreeHandle(SQL_HANDLE_ENV, conn.env);
}
ConnectionResult SqlDal::GetConnection(std::string connString) {
ConnectionResult ret;
if (connString.size() > 0) {
try {
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &ret.env);
SQLSetEnvAttr(ret.env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, ret.env, &ret.dbc);
ret.retval = SQLDriverConnect(ret.dbc, NULL, (SQLCHAR*) connString.c_str(),
SQL_NTS, ret.outputBuffer, sizeof(ret.outputBuffer),
&ret.outputBufferLength, SQL_DRIVER_COMPLETE);
SQLAllocHandle(SQL_HANDLE_STMT, ret.dbc, &ret.stmt);
if (!SQL_SUCCEEDED(ret.retval)) {
ret.Exception = ExtractErrorFromDriver(ret.dbc, SQL_HANDLE_DBC);
ReleaseConnectionResources(ret);
}
} catch(std::exception &ex) {
ret.Exception = ex.what();
}
}
return ret;
}
The executing code is also shown in the image below. Please note the information returned from SQL in the form of the Contact model.
Also the following image depict SQL Server vNext running in my development Ubuntu VM
And this one depicts SQuirreL querying the demo database via SQL Server’s JDBC driver. I had to use ODBC instead of the JDBC driver, because the latter is Java based (it’s a jar file) which might’ve required me to host the JVM and use JNI and that’s not the scope or purpose of the application. I’ll publish the solution in its entirety once I’ve completed it.
Regards,
Angel
Filed under: Open-Source - @ January 19, 2017 3:56 pm
Tags: C++, CLION, GCC, Glade, GTK, Linux, Name, ODBC, SQL Server