marketdata/MarketDataLib/DataAccess/MStarSecurityDA.cs

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

namespace MarketData.DataAccess
{
  public class MStarSecurityDA
  {
    private MStarSecurityDA()
    {
    }
    public static String GetSecurityId(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      String strQuery = null;
      String securityIdentifier=null;

      try
      {
        if(!HasSecurityId(symbol))return null;
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select security_identifier from mstarsecurityid where symbol=").Append(Utility.AddQuotes(symbol));
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (sqlDataReader.Read())
        {
          securityIdentifier=sqlDataReader.GetString(0);
        }
        return securityIdentifier;
      }
      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 void PutSecurityId(String symbol,String securityId)
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction=null;

      try
      {
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        if(!HasSecurityId(symbol))InsertSecurityId(symbol,securityId,sqlConnection,sqlTransaction);
        else UpdateSecurityId(symbol,securityId,sqlConnection,sqlTransaction);
        sqlTransaction= sqlConnection.BeginTransaction();
        sqlTransaction.Commit();
      }
      catch(Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
      }
      finally
      {
        if(null!=sqlTransaction)
        {
          sqlTransaction.Dispose();
        }
        if(null!=sqlConnection)
        {
          sqlConnection.Close();
          sqlConnection.Dispose();
        }
      }
    }

    private static bool UpdateSecurityId(String symbol,String securityIdentifier, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
    {
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        if (null == securityIdentifier || null == symbol) return false;
        StringBuilder sb = new StringBuilder();
        sb.Append("update mstarsecurityid set security_identifier=").Append(Utility.AddQuotes(securityIdentifier));
        sb.Append(" where symbol=").Append(Utility.AddQuotes(symbol));
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Query was '{0}'",strQuery));
        return false;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
      }
    }

    private static bool InsertSecurityId(String symbol,String securityIdentifier, MySqlConnection sqlConnection, MySqlTransaction sqlTransaction)
    {
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        if (null == securityIdentifier || null == symbol) return false;
        StringBuilder sb = new StringBuilder();
        sb.Append("insert into mstarsecurityid(symbol,security_identifier,modified) values(");
        sb.Append(SqlUtils.AddQuotes(symbol)).Append(",");
        sb.Append(SqlUtils.AddQuotes(securityIdentifier)).Append(",");
        sb.Append(SqlUtils.AddQuotes(SqlUtils.ToSqlDateTime(DateTime.Now)));
        sb.Append(")");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection, sqlTransaction);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlCommand.ExecuteNonQuery();
        return true;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Query was '{0}'",strQuery));
        return false;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
      }
    }

    public static bool HasSecurityId(String symbol)
    {
      MySqlConnection sqlConnection = null;
      MySqlDataReader sqlDataReader = null;
      MySqlCommand sqlCommand = null;
      String strQuery = null;
      int count = 0;

      try
      {
        StringBuilder sb = new StringBuilder();
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
        sb.Append("select count(*) from mstarsecurityid where symbol=");
        sb.Append(Utility.AddQuotes(symbol));
        strQuery = sb.ToString(); ;
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        sqlDataReader.Read();
        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();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
  }
}