marketdata/MarketDataLib/DataAccess/CashDA.cs

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

namespace MarketData.DataAccess
{
  public class CashDA
  {
    private CashDA()
    {
    }
    public static bool AddTransaction(CashTransaction cashTransaction)
    {
      MySqlConnection sqlConnection = null;
      MySqlTransaction sqlTransaction = null;
			MySqlCommand sqlCommand=null;
      String strQuery = null;

      try
      {
        if (null == cashTransaction) return false;
        double accountBalance=GetBalance(cashTransaction.Account);
        sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("portfolio_data"));
        sqlTransaction = sqlConnection.BeginTransaction();
        StringBuilder sb = new StringBuilder();
        cashTransaction.Balance=accountBalance+(cashTransaction.Credit-cashTransaction.Debit);
        sb.Append("insert into cash(account,date,description,credit,debit,balance) values(");
        sb.Append(SqlUtils.AddQuotes(cashTransaction.Account)).Append(",");
        sb.Append(SqlUtils.AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(cashTransaction.TransactionDate))).Append(",");
        sb.Append(SqlUtils.AddQuotes(cashTransaction.Description)).Append(",");
        sb.Append(cashTransaction.Credit).Append(",");
        sb.Append(cashTransaction.Debit).Append(",");
        sb.Append(accountBalance+cashTransaction.Credit-cashTransaction.Debit).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 != sqlConnection) sqlConnection.Close();
        if (null != sqlTransaction) sqlTransaction.Dispose();
      }
    }
// get balance for specific account
    public static double GetBalance(String account)
    {
      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 balance from cash where account='").Append(account).Append("' and transaction_id=(select max(transaction_id) from cash where account='").Append(account).Append("')");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (!sqlDataReader.Read()) return double.NaN;
        double accountBalance = sqlDataReader.GetDouble(0);
        return accountBalance;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return double.NaN;
      }
      finally
      {
				if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
// get latest balance for all accounts
    public static double GetBalance()
    {
      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 sum(balance) from cash");
        strQuery = sb.ToString();
        sqlCommand = new MySqlCommand(strQuery, sqlConnection);
        sqlCommand.CommandTimeout = SqlUtils.COMMAND_TIMEOUT;
        sqlDataReader = sqlCommand.ExecuteReader();
        if (!sqlDataReader.Read()) return double.NaN;
        double accountBalance = sqlDataReader.GetDouble(0);
        return accountBalance;
      }
      catch (Exception exception)
      {
        MDTrace.WriteLine(LogLevel.DEBUG,exception);
        return double.NaN;
      }
      finally
      {
        if(null!=sqlCommand)sqlCommand.Dispose();
        if (null != sqlDataReader) {sqlDataReader.Close();sqlDataReader.Dispose();}
        if (null != sqlConnection) sqlConnection.Close();
      }
    }
  }
}