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(); } }