First we need to set the update from within the engine
[Test] public void Update() { _Engine.AddTransaction(10.10, "test category", "test description", DateTime.Now); Assert.IsTrue(_Engine.Update()); }
public bool Update() { bool transFailed = false; foreach (MoneyTransaction moneyTransaction in _History) { if (!moneyTransaction.Update()) transFailed = true; } return !transFailed; }
if a transaction was to fail, we can work through the collection checking the error strings, to discover the
reason.
we are at the point now that the only thing left is to load the transactions into the engine, we will need a data
tier to do this which I suppose will return a data set or data table, we can then itterate through this and fill
the history collection with transactions, using a simple add.
I want to do this using a simple Load command, which will clear the current list and reload it, thus when a user
presses the save button or what ever the engine will be updated automatically, removing those transactions that
have been deleted, and saving new and updated items.
I am going to need the get command and get parameter methods, so I think I will move them out to a helper class,
that can be static and called as required.
public static class DataHelpers { public static SqlParameter GetParam(string paramName, object paramValue, SqlDbType paramType,
ParameterDirection paramDirection)
{
SqlParameter param = new SqlParameter(paramName, paramType);
param.Direction = paramDirection;
param.Value = paramValue;
return param;
}
public static SqlParameter GetParam(string paramName, object paramValue, SqlDbType paramType,
int ParamSize, ParameterDirection paramDirection) { SqlParameter param = GetParam(paramName, paramValue, paramType, paramDirection); param.Size = ParamSize; return param; } public static SqlCommand GetCommand(string procedureName, string connectionString) { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand cmd = new SqlCommand(procedureName, connection); cmd.CommandType = CommandType.StoredProcedure; return cmd; } }
I have updated DBMoneyTransactions to use the above class, so next is the select function the DBengine, first
we need a test.
[Test] public void GetTransactionsInDateOrder() { DBEngine dbEngine = new DBEngine(); DataTable transactions = dbEngine.GetTransactionsInDateOrder(); Assert.IsNotNull(transactions); Assert.IsTrue(transactions.Rows.Count > 0); }
I decided that the data tier should return a data table or null, this will allow the engine to enumerate the table one row at a time and create a transaction with all the correct data.
public DataTable GetTransactionsInDateOrder(ref string helpText) { SqlCommand cmd = null; SqlDataAdapter adapter; try { cmd = DataHelpers.GetCommand("uspSelectTransactionsDateOrder", STR_Connection); DataSet data = new DataSet(); adapter = new SqlDataAdapter(cmd); adapter.Fill(data); if (data.Tables.Count > 0) return data.Tables[0]; return null; } catch (SqlException sqlEx) { helpText = sqlEx.Message; return null; } finally { if (cmd != null) { cmd.Connection.Close(); cmd.Dispose(); } } } }
so next we move up to the engine, we need a load function which will call the above routine and fill the history list with the data. so first a test.
[Test] public void Load() { Assert.IsTrue(_Engine.LoadTransactions(), _Engine.ErrorText); Assert.IsTrue(_Engine.History.Count > 0, "no records returned"); }
note I added a new property to the money engine that will return any errors from child objects, so they can be used by the UI to inform the user that there was a problem.
and then the problems started, I found that big int did not covert easily to int, and money didn’t seam to be a good database type to use. so after much changing of types I ended up with the money being changed to float, which converts in c# to a double. the transaction id is stored as a big int, this converts to an int32 in c#.
public bool LoadTransactions() { DBMoneyEngine database = new DBMoneyEngine(); DataTable transactions = database.GetTransactionsInDateOrder(ref _ErrorText); if (transactions == null) return false; if (transactions.Rows.Count > 0) { foreach (DataRow transData in transactions.Rows) { Int32 transID = transData["TransID"] is DBNull ? 0 : Convert.ToInt32(transData["TransID"]); TransactionType transType = transData["TransType"] is DBNull ?
TransactionType.AdditionTransaction : (int) transData["TransType"] == 0 ?TransactionType.AdditionTransaction : TransactionType.SubtractionTransaction; DateTime transDate = transData["TransDate"] is DBNull ?DateTime.Now : (DateTime) transData["TransDate"]; string transCategory = transData["TransCategory"] is DBNull ? string.Empty :transData["TransCategory"].ToString(); string transDescription = transData["TransDescription"] is DBNull ? string.Empty :transData["TransDescription"].ToString(); double transAmount = transData["TransAmount"] is DBNull ? 0 :(double)transData["TransAmount"]; MoneyTransaction tran = new MoneyTransaction(transID, transAmount, transCategory,transDescription, transDate, transType ); _History.Add(tran); } } return true; }For those who are new to c# and have not transferred from c or c++, the above constructs may look strangebut they are really just short hand for an if statement for example.if (transData["TransID"] is DBNull) transID = 0; else transID = Convert.ToInt32(transData["TransID"]);
can be converted toInt32 transID = transData["TransID"] is DBNull ? 0 : Convert.ToInt32(transData["TransID"]);the part from the = sign to the ? is the if statement, the part after the ? is the if true part, and afterthe : is the false part, as can be seen from the above code these can be nested as well, although forreadability it is best not to go far.
The Butler Did It 