Solved

Thread Safe Database Access Class

Posted on 2006-10-23
3
225 Views
Last Modified: 2007-12-19
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;
  }
}


0
Comment
Question by:cossy74
  • 2
3 Comments
 
LVL 48

Expert Comment

by:AlexFM
ID: 17793975
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.
0
 

Author Comment

by:cossy74
ID: 17800067
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.
0
 
LVL 48

Accepted Solution

by:
AlexFM earned 500 total points
ID: 17801385
ThreadSafeMethod - I mean, every method in your class which should be thread safe. Replace this name with actual method names.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The following diagram presents a diamond class hierarchy: As depicted, diamond inheritance denotes when two classes (e.g., CDerived1 and CDerived2), separately extending a common base class (e.g., CBase), are sub classed simultaneously by a fourt…
In Easy String Encryption Using CryptoAPI in C++ (http://www.experts-exchange.com/viewArticle.jsp?aid=1193) I described how to encrypt text and recommended that the encrypted text be stored as a series of hexadecimal digits -- because cyphertext may…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question