//---------------------------------------------------------
//
//     FILE : SQLDatabase.cs
//  CLASSES : SQLDatabase
//
//    USAGE : myDB = new SQLDatabase (myConnectionString);
//            if (myDB.Connected)
//            {
//              string query = "SELECT * FROM Customers";
//              DataTable customers = myDB.RunQueryForTable (query);
//              ...
//            }
//
//   AUTHOR : Bill Daniels
//            Copyright (c) 2007-2011,
//            D+S Tech Labs, Inc.
//            All Rights Reserved
//
//---------------------------------------------------------

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

//---------------------------------------------------------
//   CLASS: SQLDatabase
//  PARENT:
//---------------------------------------------------------

public class SQLDatabase
{
  //--- Data ----------------------------------------------

  private Object  lockObject = new Object ();
  
  protected SqlConnection   sqlConnection;
  protected SqlCommand      sqlCommand;
  protected SqlDataAdapter  sqlAdapter;

  //--- Properties ----------------------------------------

  public bool Connected
  {
    get
    {
      try
      {
        if (sqlConnection != null)
          return (sqlConnection.State == ConnectionState.Open);
      }
      catch (Exception) { }

      return false;
    }
  }

  //--- Constructor ---------------------------------------

  public SQLDatabase (string connectionString)
  {
    lock (lockObject)
    {
      try
      {
        // Establish a new database connection
        // Setup command and adapter objects
        sqlConnection                  = new SqlConnection ();
        sqlConnection.ConnectionString = connectionString;
        sqlConnection.Open ();

        sqlCommand                = new SqlCommand ();
        sqlCommand.Connection     = sqlConnection;
        sqlCommand.CommandTimeout = 600;  // Wait 10 minutes for execution

        sqlAdapter               = new SqlDataAdapter ();
        sqlAdapter.SelectCommand = sqlCommand;
        sqlAdapter.InsertCommand = sqlCommand;
        sqlAdapter.UpdateCommand = sqlCommand;
        sqlAdapter.DeleteCommand = sqlCommand;
        
        MessageLog.PutMessage ((Connected ? "" : "Not ") + "Connected to " +
                               sqlConnection.Database + " on " + sqlConnection.DataSource,
                               MessageLog.Severity.INFO, !Connected);
      }
      catch (Exception ex)
      {
        if (sqlConnection != null) sqlConnection.Close ();
        
        string msg = string.Format ("Error connecting to an SQL Database.  Connection String: \"{0}\"  Detail: {1}",
                                    connectionString, ex.Message);
        MessageLog.PutMessage (msg, MessageLog.Severity.ERROR, true);
      }
    }
  }

  //--- Destructor ----------------------------------------

  ~SQLDatabase ()
  {
    lock (lockObject)
    {
      if (Connected)
      {
        try
        {
          MessageLog.PutMessage ("Disconnecting", MessageLog.Severity.INFO, false);
          sqlConnection.Close ();
        }
        catch (Exception) { }
      }
    }
  }

  //--- RunQuery ------------------------------------------

  public int RunQuery (string query)
  {
    lock (lockObject)
    {
      if (Connected)
      {
        try
        {
          // Run the specified query and return the number of rows affected
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = query;

          return sqlCommand.ExecuteNonQuery ();
        }
        catch (Exception ex)
        {
          string msg = string.Format ("{0}: Query={1}", ex.Message, query);
          MessageLog.PutMessage (msg, MessageLog.Severity.ERROR, false);
        }
      }
      
      return 0;
    }
  }

  //--- RunQueryForValue ----------------------------------

  public string RunQueryForValue (string query)
  {
    lock (lockObject)
    {
      object value=null;

      if (Connected)
      {
        try
        {
          // Run the specified query and return a value as a string
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = query;
          value = sqlCommand.ExecuteScalar();
        }
        catch (Exception ex)
        {
          string msg = string.Format ("{0}: Query={1}", ex.Message, query);
          MessageLog.PutMessage (msg, MessageLog.Severity.ERROR, false);
        }
      }

      if (value == null) return null;
      return value.ToString();
    }
  }

  //--- RunQueryForTable ----------------------------------

  public DataTable RunQueryForTable (string query)
  {
    lock (lockObject)
    {
      if (Connected)
      {
        try
        {
          DataSet ds = new DataSet ();

          // Run the specified query and return the first table of the DataSet
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = query;
          sqlAdapter.Fill (ds);
          
          return ds.Tables[0];
        }
        catch (Exception ex)
        {
          string msg = string.Format ("{0}: Query={1}", ex.Message, query);
          MessageLog.PutMessage (msg, MessageLog.Severity.ERROR, false);
        }
      }
      
      return null;
    }
  }

  //--- RunQueryForDataSet --------------------------------

  public DataSet RunQueryForDataSet (string query)
  {
    lock (lockObject)
    {
      if (Connected)
      {
        try
        {
          DataSet ds = new DataSet ();

          // Run the specified query and return the first table of the DataSet
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = query;
          sqlAdapter.Fill (ds);
            
          return ds;
        }
        catch (Exception ex)
        {
          string msg = string.Format ("{0}: Query={1}", ex.Message, query);
          MessageLog.PutMessage (msg, MessageLog.Severity.ERROR, false);
        }
      }
      
      return null;
    }
  }

  //--- RunQueryForReader ---------------------------------

  public SqlDataReader RunQueryForReader (string query)
  {
    lock (lockObject)
    {
      if (Connected)
      {
        try
        {
          // Run the specified query and return an SQLDataReader
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = query;
          
          return sqlCommand.ExecuteReader();
        }
        catch (Exception ex)
        {
          string msg = string.Format ("{0}: Query={1}", ex.Message, query);
          MessageLog.PutMessage (msg, MessageLog.Severity.ERROR, false);
        }
      }
      
      return null;
    }
  }
}