public bool DeleteTransactionByTransactionGuid(string transactionGuid)
{
bool deleteSuccessful = false;
//int tableId;
try
{
PolarisEFContext efContext = new PolarisEFContext(connectionString);
PolarisTransaction polarisTransaction = new PolarisTransaction();
IPolarisTransaction plTrx = new PolarisTransaction();
//Get the table ID by searching with the Transaction ID
var transRows = (from plTrxs in efContext.PolarisTransactions
.Where(plTrxs => plTrxs.TransactionGuid.Equals(Guid.Parse(transactionGuid)))
select new
{
tableId = plTrxs.TransactionId
}).FirstOrDefault();
//Put the table ID into the Transaction model
polarisTransaction.TransactionId = transRows.tableId;
efContext.Entry(polarisTransaction).State = EntityState.Deleted;
efContext.Remove(polarisTransaction);
efContext.SaveChanges();
return(deleteSuccessful);
}
catch (Exception exception)
{
throw exception;
}
}
public bool DeleteAllTransactionByAccountGuid(string accountGuid)
{
bool deleteSuccessful = false;
try
{
PolarisEFContext efContext = new PolarisEFContext(connectionString);
//Get the Sql Server Table ID for each row that matches the Account Guid
var transRows = (from plTrxs in efContext.PolarisTransactions
.Where(plTrxs => plTrxs.AccountGuid.Equals(Guid.Parse(accountGuid)))
select new
{
tableId = plTrxs.TransactionId
}).ToList();
//Iterate through the list and delete the rows
foreach (var transactionRow in transRows)
{
PolarisTransaction transaction = new PolarisTransaction();
transaction.TransactionId = transactionRow.tableId;
efContext.Entry(transaction).State = EntityState.Deleted;
efContext.PolarisTransactions.RemoveRange(transaction);
efContext.SaveChanges();
}
return(deleteSuccessful);
}
catch (Exception exception)
{
throw exception;
}
}
public Task <List <IPolarisTransaction> > GetTransactionsByAccountGuidEF(string accountGuid)
{
List <IPolarisTransaction> transactionList = new List <IPolarisTransaction>();
try
{
PolarisEFContext efContext = new PolarisEFContext(connectionString);
List <IPolarisTransaction> TransactionList = new List <IPolarisTransaction>();
var transRows = (from plTrxs in efContext.PolarisTransactions
.Where(plTrxs => plTrxs.AccountGuid.Equals(Guid.Parse(accountGuid)))
select new
{
acctGuid = plTrxs.AccountGuid.ToString(),
trxGuid = plTrxs.TransactionGuid.ToString(),
trxType = plTrxs.TransactionType,
trxBegBal = plTrxs.BeginningBalance,
trxDate = plTrxs.TransactionDateTime,
trxAmt = plTrxs.TransactionAmount,
trxMemo = plTrxs.Memo,
trxEndBal = plTrxs.EndingBalance
}).ToList();
//Put the rows into the PolarisTransaction list
foreach (var trxRow in transRows)
{
IPolarisTransaction polarisTransaction = new PolarisTransaction();
polarisTransaction.AccountGuid = Guid.Parse(trxRow.acctGuid.ToString());
polarisTransaction.TransactionGuid = Guid.Parse(trxRow.trxGuid.ToString());
polarisTransaction.TransactionType = trxRow.trxType;
polarisTransaction.BeginningBalance = trxRow.trxBegBal;
polarisTransaction.TransactionDateTime = trxRow.trxDate;
polarisTransaction.TransactionAmount = trxRow.trxAmt;
polarisTransaction.Memo = trxRow.trxMemo;
polarisTransaction.EndingBalance = decimal.Parse(trxRow.trxEndBal.ToString());
polarisTransaction.EndingBalance = trxRow.trxEndBal;
TransactionList.Add(polarisTransaction);
}
//return the account
return(Task.FromResult(TransactionList));
}
catch (Exception exception)
{
throw exception;
}
}
/// <summary>
///
/// </summary>
/// <param name="connectionString"></param>
/// <param name="accountGuid"></param>
/// <returns></returns>
public Task <List <IPolarisTransaction> > GetTransactionsByAccountGuid(string sqlCommandText)
{
List <IPolarisTransaction> transactionList = new List <IPolarisTransaction>();
//open the connection
SqlConnection sqlConnection = ConnectToSqlServer(connectionString);
//string sqlCommandText = $"SELECT TransactionGuid,AccountGuid,TransactionType," +
// $"BeginningBalance,TransactionDateTime,TransactionAmount," +
// $"Memo,EndingBalance FROM PolarisTransactions WHERE AccountGuid='{accountGuid}'";
try
{
SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
//Use a DataReader
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
IPolarisTransaction transaction = new PolarisTransaction();
//Transaction transaction = new Transaction();
transaction.TransactionGuid = Guid.Parse(dataReader[0].ToString());
transaction.AccountGuid = Guid.Parse(dataReader[1].ToString());
transaction.TransactionType = dataReader[2].ToString();
transaction.BeginningBalance = decimal.Parse(dataReader[3].ToString());
transaction.TransactionDateTime = DateTime.Parse(dataReader[4].ToString());
transaction.TransactionAmount = decimal.Parse(dataReader[5].ToString());
transaction.Memo = dataReader[6].ToString();
transaction.EndingBalance = decimal.Parse(dataReader[7].ToString());
transactionList.Add(transaction);
}
//return the transactions
return(Task.FromResult(transactionList));
}
catch (Exception exception)
{
throw exception;
}
finally
{
sqlConnection.Close();
}
}
