ARM64/MarketData/MarketDataLib/DataAccess/HistoricalDA.cs

using System.Text;
using MySql.Data.MySqlClient;
using MarketData.MarketDataModel;
using MarketData.Utils;

namespace MarketData.DataAccess
{
  public class HistoricalDA
  {
    private HistoricalDA()
    {
    }
    public static TimeSeriesCollection GetTimeSeriesMaxAsOf(String symbol,DateTime asOf,TimeSeriesElement.ElementType elementType)
    {
      TimeSeriesCollection timeSeriesCollection =new TimeSeriesCollection();
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,asof,value,modified from historical where symbol=");
        sb.Append("'").Append(symbol).Append("'");
        sb.Append(" and type='").Append(TimeSeriesElement.StringForType(elementType)).Append("'");
        sb.Append(" and asof<='").Append(SqlUtils.FormatDate(asOf)).Append("'");
        sb.Append(" order by asof desc");
        sb.Append(";");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          TimeSeriesElement timeSeriesElement = new TimeSeriesElement();
          timeSeriesElement.Symbol = sqlDataReader.GetString(0);
          timeSeriesElement.AsOf = sqlDataReader.GetDateTime(1);
          timeSeriesElement.Type = elementType;
          if (!sqlDataReader.IsDBNull(2)) timeSeriesElement.Value = sqlDataReader.GetDouble(2);
          if (!sqlDataReader.IsDBNull(3)) timeSeriesElement.Modified = sqlDataReader.GetDateTime(3);
          else timeSeriesElement.Modified=Utility.Epoch;
          timeSeriesCollection.Add(timeSeriesElement);
        }
        return timeSeriesCollection;
      }
      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();
      }
    }
    public static TimeSeriesCollection GetTimeSeries(String symbol,TimeSeriesElement.ElementType elementType)
    {
      TimeSeriesCollection timeSeriesCollection =new TimeSeriesCollection();
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select symbol,asof,value,modified from historical where symbol=");
        sb.Append("'").Append(symbol).Append("'");
        sb.Append(" and type='").Append(TimeSeriesElement.StringForType(elementType)).Append("'").Append(" order by asof desc");
        sb.Append(";");
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          TimeSeriesElement timeSeriesElement = new TimeSeriesElement();
          timeSeriesElement.Symbol = sqlDataReader.GetString(0);
          timeSeriesElement.AsOf = sqlDataReader.GetDateTime(1);
          timeSeriesElement.Type = elementType;
          if (!sqlDataReader.IsDBNull(2)) timeSeriesElement.Value = sqlDataReader.GetDouble(2);
          if (!sqlDataReader.IsDBNull(3)) timeSeriesElement.Modified = sqlDataReader.GetDateTime(3);
          else timeSeriesElement.Modified=Utility.Epoch;
          timeSeriesCollection.Add(timeSeriesElement);
        }
        return timeSeriesCollection;
      }
      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();
      }
    }
    public static bool InsertTimeSeries(TimeSeriesCollection timeSeriesCollection)
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
      String strQuery = null;
      DateTime modified=DateTime.Now;

      try
      {
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        DeleteTimeSeries(timeSeriesCollection, sqlConnection, sqlTransaction);
        for (int index = 0; index < timeSeriesCollection.Count; index++)
        {
          TimeSeriesElement timeSeriesElement = timeSeriesCollection[index];
          StringBuilder sb = new StringBuilder();
          sb.Append("insert into historical (symbol,asof,type,value,modified) ");
          sb.Append("values(");
          sb.Append("'").Append(timeSeriesElement.Symbol).Append("'").Append(",");
          sb.Append("'").Append(Utility.DateTimeToStringYYYYHMMHDD(timeSeriesElement.AsOf)).Append("'").Append(",");
          sb.Append("'").Append(TimeSeriesElement.StringForType(timeSeriesElement.Type)).Append("'").Append(",");
          sb.Append(timeSeriesElement.Value).Append(",");
          sb.Append("'").Append(SqlUtils.ToSqlDateTime(modified)).Append("'");
          sb.Append(");");
          strQuery = sb.ToString();
          MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
          sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
          sqlCommand.ExecuteNonQuery();
					sqlCommand.Dispose();
        }
        sqlTransaction.Commit();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        MDTrace.WriteLine(LogLevel.DEBUG,"Query was '"+strQuery+"'");
        return false;
      }
      finally
      {
        if (null != sqlTransaction) sqlTransaction.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
    private static bool DeleteTimeSeries(TimeSeriesCollection timeSeriesCollection, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
    {
      for (int index = 0; index < timeSeriesCollection.Count; index++)
      {
        DeleteTimeSeries(timeSeriesCollection[index], sqlConnection, sqlTransaction);
      }
      return true;
    }
    private static bool DeleteTimeSeries(TimeSeriesElement timeSeriesElement, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
    {
      StringBuilder sb = new StringBuilder();
      String strQuery = null;

      try
      {
        sb.Append("delete from historical where ");
        sb.Append("symbol='").Append(timeSeriesElement.Symbol).Append("'");
        sb.Append(" and ");
        sb.Append("asof='").Append(Utility.DateTimeToStringYYYYHMMHDD(timeSeriesElement.AsOf)).Append("'");
        sb.Append(" and type='").Append(TimeSeriesElement.StringForType(timeSeriesElement.Type)).Append("'");
        strQuery = sb.ToString();
        MySqlCommand sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        return true;
      }
      catch (Exception exception)
      {
        sqlTransaction.Rollback();
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        if (null != strQuery) MDTrace.WriteLine(LogLevel.DEBUG,"Query was " + strQuery);
        return false;
      }
      finally
      {
      }
    }
  }
}