ARM64/MarketData/MarketDataLib/DataAccess/WatchListDA.cs

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

namespace MarketData.DataAccess
{
  public class WatchListDA
  {
    private WatchListDA()
    {
    }
    
    /// <summary>
    /// AddToWatchList - Add list of symbols to specified watch list
    /// </summary>
    /// <param name="symbols"></param>
    /// <param name="watchListName"></param>
    /// <returns></returns>
    public static bool AddToWatchList(List<string> symbols,String watchListName = "Valuations")
    {
      if(null == symbols || 0==symbols.Count || String.IsNullOrEmpty(watchListName))return false;
      foreach(string symbol in symbols)
      {
        AddToWatchList(symbol, watchListName);
      }
      return true;
    } 

    /// <summary>
    /// AddToWatchList - This will ignore the insert if the record already exists
    /// </summary>
    /// <param name="symbol"></param>
    /// <param name="watchListName"></param>
    /// <returns></returns>
    public static bool AddToWatchList(String symbol, String watchListName = "Valuations")
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
      MySqlCommand sqlCommand = null;
      String strQuery = null;

      try
      {
        if (null == symbol) return false;
        symbol = symbol.ToUpper();
        WatchListItem watchListItem = GetWatchListItem(watchListName);
        if (null == watchListItem) return false;
//        if (IsInWatchList(symbol, watchListName)) return true;
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("portfolio_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        StringBuilder sb = new StringBuilder();
        sb.Append("insert ignore into watchlist(watch_list_id,symbol)values(");
        sb.Append(watchListItem.WatchListId).Append(",");
        sb.Append(SqlUtils.AddQuotes(symbol));
        sb.Append(")");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        sqlTransaction.Commit();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG, exception);
        return false;
      }
      finally
      {
        if (null != sqlCommand) sqlCommand.Dispose();
        if (null != sqlTransaction) sqlTransaction.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static bool RemoveFromWatchList(String symbol, String watchListName = "Valuations")
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
      MySqlCommand sqlCommand = null;
      String strQuery = null;

      try
      {
        if (null == symbol) return false;
        symbol = symbol.ToUpper();
        WatchListItem watchListItem = GetWatchListItem(watchListName);
        if (null == watchListItem) return false;
        List<String> watchListSymbols = GetWatchList(watchListName);
        if (null == watchListSymbols.Find(x => x.Equals(symbol))) return true;
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("portfolio_data"));
        sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        StringBuilder sb = new StringBuilder();
        sb.Append("delete from watchlist where watch_list_id=").Append(watchListItem.WatchListId).Append(" and ").Append("symbol='").Append(symbol).Append("'");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        sqlTransaction.Commit();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG, exception);
        return false;
      }
      finally
      {
        if (null != sqlCommand) sqlCommand.Dispose();
        if (null != sqlTransaction) sqlTransaction.Dispose();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static bool IsInWatchList(String symbol, String watchListName = "Valuations")
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("portfolio_data"));
        sb.Append("select count(*) from watchlist wl, watchlists wls where wl.watch_list_id=wls.watch_list_id and wls.watch_list_name=");
        sb.Append("'").Append(watchListName).Append("'").Append(" ");
        sb.Append("and wl.symbol=").Append("'").Append(symbol).Append("'").Append(" ");
        sb.Append(" order by 1 asc");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (!sqlDataReader.Read()) return false;
        int count = sqlDataReader.GetInt32(0);
        return count > 0 ? true : false;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG, exception);
        return false;
      }
      finally
      {
        if (null != sqlCommand) sqlCommand.Dispose();
        if (null != sqlDataReader) sqlDataReader.Close();
        if (null != sqlConnection) sqlConnection.Close();
      }
    }

    public static List<String> GetWatchList(String watchListName)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      List<String> watchList = new List<String>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("portfolio_data"));
        sb.Append("select wl.symbol from watchlist wl, watchlists wls where wl.watch_list_id=wls.watch_list_id and wls.watch_list_name=");
        sb.Append("'").Append(watchListName).Append("'").Append(" order by 1 asc");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          String symbol = sqlDataReader.GetString(0);
          watchList.Add(symbol);
        }
        return watchList;
      }
      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 WatchListItem GetWatchListItem(String watchListName)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      WatchListItem watchListItem = null;
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("portfolio_data"));
        sb.Append("select watch_list_name,watch_list_id from watchlists where watch_list_name='").Append(watchListName).Append("'");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (sqlDataReader.Read())
        {
          watchListItem = new WatchListItem();
          watchListItem.WatchListName = sqlDataReader.GetString(0);
          watchListItem.WatchListId = sqlDataReader.GetInt32(1);
        }
        return watchListItem;
      }
      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 List<String> GetWatchLists()
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      List<String> watchList = new List<String>();
      String strQuery = null;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("portfolio_data"));
        sb.Append("select watch_list_name from watchlists");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        while (sqlDataReader.Read())
        {
          String symbol = sqlDataReader.GetString(0);
          watchList.Add(symbol);
        }
        return watchList;
      }
      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();
      }
    }
  }
}