marketdata/MarketDataLib/DataAccess/HolidayDA.cs

using System;
using System.Text;
using System.Diagnostics;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using MarketData.MarketDataModel;
using MarketData.Utils;

namespace MarketData.DataAccess
{
  public class HolidayDA
  {
    private HolidayDA()
    {
    }
    public static String GetHolidayDescription(DateTime date)
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      String description="";

      try
      {
        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select description from marketholidays where date='").Append(Utils.SqlUtils.SqlDate(date)).Append("'");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        if(sqlDataReader.Read())
        {
          if(!sqlDataReader.IsDBNull(0)) description=sqlDataReader.GetString(0);
        }
        return description;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return description;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    } 
    public static DateTime GetMaxHolidayDate()
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      DateTime maxHolidayDate=Utility.Epoch;

      try
      {
        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select max(date) from marketholidays");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        if(sqlDataReader.Read())
        {
          if(!sqlDataReader.IsDBNull(0)) maxHolidayDate=sqlDataReader.GetDateTime(0);
        }
        return maxHolidayDate;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return maxHolidayDate;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }

    public static bool IsMarketHoliday(DateTime date)
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      int recordCount=0;

      try
      {
        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select count(*) from marketholidays where date='").Append(Utility.DateTimeToStringYYYYHMMHDD(date)).Append("' ");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        if(sqlDataReader.Read())
        {
          if(!sqlDataReader.IsDBNull(0)) recordCount=sqlDataReader.GetInt32(0);
        }
        return 0==recordCount?false:true;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return false;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
    public static Dictionary<DateTime,DateTime> GetHolidays()
    {
      MySqlConnection sqlConnection=null;
      MySqlDataReader sqlDataReader=null;
      MySqlCommand sqlCommand=null;
      String strQuery=null;
      Dictionary<DateTime,DateTime> holidays=new Dictionary<DateTime,DateTime>();

      try
      {
        StringBuilder sb=new StringBuilder();
        sqlConnection=SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select date from marketholidays order by date desc");
        strQuery=sb.ToString();
        sqlCommand=new MySqlCommand(strQuery,sqlConnection);
        sqlCommand.CommandTimeout=SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader=sqlCommand.ExecuteReader();
        while(sqlDataReader.Read())
        {
          DateTime holiday=sqlDataReader.GetDateTime(0);
          if(!holidays.ContainsKey(holiday))holidays.Add(holiday,holiday);
        }
        return holidays;
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return null;
      }
      finally
      {
        if(null!=sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if(null!=sqlConnection) sqlConnection.Close();
      }
    }
  }
}