asked on

Thread Safe Database Access Class

HI,

I wish to get people's advice on creating a Thread Safe Database Access Class.
WHat i am experiencing is that two threads are trying to connect to the database(firebird) and they both stop at the connect() command.
I am using managed c++ but this could be changed if need be. I have tried mutex locking the connection/disconnect/query segments but the locking still occurs. Can Anyone help?

I have provided the class below if you wish to look at it.

///---------------------------------- .H FILE -------------------------------------------------
#ifndef __XXXX_WIN32_DB_H__
#define __XXXX_WIN32_DB_H__

#ifdef __XXXX_DLL__ // Need for building DLLs
#using <mscorlib.dll> //
#using <System.dll>
#using <System.Data.dll>
#endif

using namespace System; // Standard Net Stuff including Console
using namespace System::Data; // Used for Database
using namespace System::Data::Odbc; // Used for Database
using namespace System::Threading; // Used in Threading

#define __XXXX_WIN32_DB_DEBUG__ 1
//#define __XXXX_WIN32_DB_USEMUTEX__ 1

ref class XXXX_WIN32_DB
{
public:

XXXX_WIN32_DB();
~XXXX_WIN32_DB();

int Initialise();
int ConnectToDatabase();
int DisconnectFromDatabase();

int Set_ConnectionString(System::String^ sConnectionString);

int ExecuteQuery(System::String^ sSQL, System::Data::DataSet^ oDS);
int ExecuteNonQuery(System::String^ sSQL, int &NumberOfRowsAffected);

int Show_State();

static Mutex^ DB_mut = gcnew Mutex;
String^ DB_mutloc;
protected:
private:
//-----------------------------------------------------------------------------
// Database Memeber Varaibles
//-----------------------------------------------------------------------------
Odbc::OdbcCommand^ m_pCommand;
bool m_bCommand;
Odbc::OdbcConnection^ m_pConnection;
bool m_bConnection;
System::String^ m_sConnectionString;

};

#endif // __XXXX_WIN32_DB_H__

///------------------------------------ .CPP FILE -----------------------------------------------

#include "stdafx.h"
#include "XXXX_WIN32_DB.h"

XXXX_WIN32_DB::XXXX_WIN32_DB()
{
Initialise();
}
XXXX_WIN32_DB::~XXXX_WIN32_DB()
{
}

int XXXX_WIN32_DB::Initialise()
{
//--------------------------------------------------------------------------------
// Set the connection string
//--------------------------------------------------------------------------------
// FIREBIRD CONNECTION STRING
m_sConnectionString = "DRIVER=Firebird/InterBase(r) driver;UID=XXXX;PWD=XXXX;DBNAME=C:\\Databases\\DBXXXX.FDB";

m_bCommand = false;
m_bConnection = false;

try
{
m_pConnection = gcnew Odbc::OdbcConnection();
m_bConnection = true;
}
catch (OdbcException^ e)
{
String^ errorMessages = L"OdbcException\n";
for (int i=0; i < e->Errors->Count; i++)
{
errorMessages = String::Concat(
L"Message: ", e->Errors[i]->Message, L"\n",
L"NativeError: ", e->Errors[i]->NativeError, L"\n",
L"Source: ", e->Errors[i]->Source, L"\n",
L"SQL: ", e->Errors[i]->SQLState, L"\n");
}

Console::WriteLine(errorMessages);

return -3;
}

DB_mutloc = "NONE";

return 0;
}
//-----------------------------------------------------------------------
// Return Values
//-----------------------------------------------------------------------
// 0. OK
// -1. No Connection Object
// -2. Openning Connection Error
//-----------------------------------------------------------------------
int XXXX_WIN32_DB::ConnectToDatabase()
{
if(m_bConnection == false)
{
// Need to create a connection
return -1;
}
#ifdef __XXXX_WIN32_DB_DEBUG__
Console::WriteLine("XXXX_WIN32_DB::ConnectToDatabase() - START");
#endif
try
{
m_pConnection->ConnectionString = m_sConnectionString;

#ifdef __XXXX_WIN32_DB_USEMUTEX__
//Wait until it is OK to enter.
Console::WriteLine(L" Connect - DB Mutex wait, time = [{0}], last Location = [{1}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"), DB_mutloc);
DB_mut->WaitOne();
DB_mutloc = String::Concat("ConnectToDatabase()");
#endif

// Open Connection
m_pConnection->Open();

#ifdef __XXXX_WIN32_DB_USEMUTEX__
// Release the Mutex.
Console::WriteLine(L" Connect - DB Mutex release, time = [{0}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"));
DB_mut->ReleaseMutex();
#endif

}catch (OdbcException^ e)
{

String^ errorMessages = L"";

for (int i=0; i < e->Errors->Count; i++)
{
errorMessages = String::Concat(
L"Error Opening Connection to Database\n",
L"Message: ", e->Errors[i]->Message, L"\n",
L"NativeError: ", e->Errors[i]->NativeError, L"\n",
L"Source: ", e->Errors[i]->Source, L"\n",
L"SQL: ", e->Errors[i]->SQLState, L"\n");
}

Console::WriteLine(errorMessages);
return -2;
}
catch ( Exception^ e )
{
Console::WriteLine(L"Error Opening Connection to Database\n{0}",e);
return -2;
}
#ifdef __XXXX_WIN32_DB_DEBUG__
Console::WriteLine("XXXX_WIN32_DB::ConnectToDatabase() - FINISH");
#endif
return 0;
}
//-----------------------------------------------------------------------
// Return Values
//-----------------------------------------------------------------------
// 0. OK
// -1. No Connection Object
// -2. Closing Connection Error
//-----------------------------------------------------------------------
int XXXX_WIN32_DB::DisconnectFromDatabase()
{
if(m_bConnection == false)
{
// Need to create a connection
return -1;
}
#ifdef __XXXX_WIN32_DB_DEBUG__
Console::WriteLine("XXXX_WIN32_DB::DisconnectFromDatabase() - START");
#endif
try
{

#ifdef __XXXX_WIN32_DB_USEMUTEX__
//Wait until it is OK to enter.
Console::WriteLine(L" Disconnect - DB Mutex wait, time = [{0}], last Location = [{1}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"), DB_mutloc);
DB_mut->WaitOne();
DB_mutloc = String::Concat("DisconnectFromDatabase()");
#endif

m_pConnection->Close();

#ifdef __XXXX_WIN32_DB_USEMUTEX__
// Release the Mutex.
Console::WriteLine(L" Disconnect - DB Mutex release, time = [{0}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"));
DB_mut->ReleaseMutex();
#endif

}catch (OdbcException^ e)
{
String^ errorMessages = L"";

for (int i=0; i < e->Errors->Count; i++)
{
errorMessages = String::Concat(
L"Error Closing Connection to Database\n",
L"Message: ", e->Errors[i]->Message, L"\n",
L"NativeError: ", e->Errors[i]->NativeError, L"\n",
L"Source: ", e->Errors[i]->Source, L"\n",
L"SQL: ", e->Errors[i]->SQLState, L"\n");
}

Console::WriteLine(errorMessages);
return -2;
}
catch ( Exception^ e )
{
Console::WriteLine(L"Error Closing Connection to Database\n{0}",e);
return -2;
}
#ifdef __XXXX_WIN32_DB_DEBUG__
Console::WriteLine("XXXX_WIN32_DB::DisconnectFromDatabase() - FINISH");
#endif
return 0;
}

int XXXX_WIN32_DB::Show_State()
{
Console::WriteLine("Connection Object = [{0}]",m_bConnection);
return 0;
}
int XXXX_WIN32_DB::Set_ConnectionString(System::String^ sConnectionString)
{
m_sConnectionString = sConnectionString;
return 0;
}
//-----------------------------------------------------------------------
// Return Values
//-----------------------------------------------------------------------
// 0. OK
// -1. Null Referrence Exception
// -2. Invalid Operation Exception
// -3. ODBC Exception
// -4. General Exception
//-----------------------------------------------------------------------
int XXXX_WIN32_DB::ExecuteQuery(System::String^ sSQL, System::Data::DataSet^ oDS)
{
#ifdef __XXXX_WIN32_DB_DEBUG__
Console::WriteLine(L"XXXX_WIN32_DB::ExecuteQuery() - START");
#endif
try
{
m_pCommand = gcnew Odbc::OdbcCommand();
m_pCommand->Connection = m_pConnection;
m_pCommand->CommandText = sSQL;
m_pCommand->CommandType = System::Data::CommandType::Text;

Odbc::OdbcDataAdapter^ pAdapter = gcnew Odbc::OdbcDataAdapter();
pAdapter->SelectCommand = m_pCommand;

#ifdef __XXXX_WIN32_DB_USEMUTEX__
//Wait until it is OK to enter.
Console::WriteLine(L" ExecuteQuery - DB Mutex wait, time = [{0}], last Location = [{1}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"), DB_mutloc);
DB_mut->WaitOne();
DB_mutloc = String::Concat("ExecuteQuery()");
#endif

//Console::WriteLine(L" ExecuteQuery - Query = [{0}]",sSQL);
pAdapter->Fill(oDS);

//Console::WriteLine(L"XXXX_WIN32_DB::ExecuteQuery() - Fill Completed, rows = [{0}]", oDS->Tables[0]->Rows->Count );

#ifdef __XXXX_WIN32_DB_USEMUTEX__
// Release the Mutex.
DB_mut->ReleaseMutex();
Console::WriteLine(L" ExecuteQuery - DB Mutex release, time = [{0}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"));
#endif

// Return Successful
return 0;

}
catch( NullReferenceException^ nre)
{
String^ dbgmsg = String::Concat(L"XXXX_WIN32_DB::ExecuteQuery()\n",L"Null Reference Exception: ", nre->Message);
Console::WriteLine( dbgmsg);
return -1;
}
catch( InvalidOperationException^ ioe)
{
String^ dbgmsg = String::Concat(L"XXXX_WIN32_DB::ExecuteQuery()\n",L"Invalid Operation: ", ioe->Message);
Console::WriteLine( dbgmsg);
return -2;
}
catch (OdbcException^ e)
{
String^ errorMessages = L"";

for (int i=0; i < e->Errors->Count; i++)
{
errorMessages = String::Concat(
L"XXXX_WIN32_DB::ExecuteQuery()\n",
L"Error in ExecuteQuery\n",
L"Message: ", e->Errors[i]->Message, L"\n",
L"NativeError: ", e->Errors[i]->NativeError, L"\n",
L"Source: ", e->Errors[i]->Source, L"\n",
L"SQL: ", e->Errors[i]->SQLState, L"\n");
}

Console::WriteLine(errorMessages);
return -3;
}
catch ( Exception^ e )
{
Console::WriteLine(L"XXXX_WIN32_DB::ExecuteQuery()\n", L"Error in ExecuteQuery \n{0}", e );
return -4;
}
#ifdef __XXXX_WIN32_DB_DEBUG__
Console::WriteLine(L"XXXX_WIN32_DB::ExecuteQuery() - FINISH");
#endif

return 0;
}

//-----------------------------------------------------------------------
// Return Values
//-----------------------------------------------------------------------
// 0. OK
// -1. Null Referrence Exception
// -2. Invalid Operation Exception
// -3. ODBC Exception
// -4. General Exception
//-----------------------------------------------------------------------
int XXXX_WIN32_DB::ExecuteNonQuery(System::String^ sSQL, int &NumberOfRowsAffected)
{
#ifdef __XXXX_WIN32_DB_DEBUG__
Console::WriteLine(L"XXXX_WIN32_DB::ExecuteNonQuery()");
#endif
try
{
m_pCommand = gcnew Odbc::OdbcCommand();
m_pCommand->Connection = m_pConnection;
m_pCommand->CommandText = sSQL;
m_pCommand->CommandType = System::Data::CommandType::Text;

#ifdef __XXXX_WIN32_DB_USEMUTEX__
//Wait until it is OK to enter.
Console::WriteLine(L" ExecuteNonQuery - DB Mutex wait, time = [{0}], last Location = [{1}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"), DB_mutloc);
DB_mut->WaitOne();
DB_mutloc = String::Concat("ExecuteNonQuery()");
#endif

NumberOfRowsAffected = m_pCommand->ExecuteNonQuery();

#ifdef __XXXX_WIN32_DB_USEMUTEX__
// Release the Mutex.
Console::WriteLine(L" ExecuteNonQuery - DB Mutex release, time = [{0}]", System::DateTime::Now.ToString("yyyyMMdd HH:mm:ss.fff"));
DB_mut->ReleaseMutex();
#endif

return 0;
}
catch( NullReferenceException^ nre)
{
String^ dbgmsg = String::Concat(L"Error in ExecuteNonQuery\n",L"Null Reference Exception: ", nre->Message);
Console::WriteLine( dbgmsg);
return -2;
}
catch( InvalidOperationException^ ioe)
{
String^ dbgmsg = String::Concat(L"Error in ExecuteNonQuery\n", L"Invalid Operation: ", ioe->Message);
Console::WriteLine( dbgmsg);
return -2;
}
catch (OdbcException^ e)
{
String^ errorMessages = L"";

for (int i=0; i < e->Errors->Count; i++)
{
errorMessages = String::Concat(
L"Error in ExecuteNonQuery\n",
L"Message: ", e->Errors[i]->Message, L"\n",
L"NativeError: ", e->Errors[i]->NativeError, L"\n",
L"Source: ", e->Errors[i]->Source, L"\n",
L"SQL: ", e->Errors[i]->SQLState, L"\n");
}

Console::WriteLine(errorMessages);
return -3;
}
catch ( Exception^ e )
{
String^ dbgmsg = L"Error in ExecuteNonQuery";
Console::WriteLine( dbgmsg, e );
return -1;
}
}

AlexFM

The simplest way to ensure thread safety for any method is the following:

#include <msclr\lock.h>
...

void XXXX_WIN32_DB::ThreadSafeMethod()
{
lock(this);

// any other code
}

Add this line to the beginning of any method which can be executed by thread-safe way from different threads - assuming that all threads use the same class instance.

cossy74

ASKER

AlexFM: I included the <msclr\lock.h> in the header file and it complied. BUT when i make the int ConnectToDatabase(); function to be int ConnectToDatabase::ThreadSafeMethod(); i get errors saying that the function is not a class or namespace.

ASKER CERTIFIED SOLUTION

AlexFM

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial