Solved

Thread Safe Database Access Class

Posted on 2006-10-23
3
223 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now