Real Test Driven Development – Thinking of Money 7

Posted on January 20, 2007 by Duncan Butler

 

The data layer I have thought and done some coding without tests, and have decided that I am going to put the code in a separate class, this will make it easier to refractor, and may be then I will combine with the main class, usually though I like to keep the data access separate, as it allows me to change the database without effecting the business layer. (though in all honestly I have never had to yet!)

 ok so we start with the standard two tests, hook and create.

using NUnit.Framework;

namespace MoneyEngine.UnitTests
{
    [TestFixture]
    public class Tests
    {
        [Test]
        public void Hook()
        {
            Assert.IsTrue(true);
        }

        [Test]
        public void Create()
        {
            Assert.IsNotNull(new DBMoneyTransaction);
        }
    }
}

and then a test to insert a row into the database.

        [Test]
        public void AddTransaction()
        {
            DBMoneyTransaction transaction = new DBMoneyTransaction();

            string helpText = String.Empty;
            int transID = transaction.InsertTransaction(0, System.DateTime.Now, "Test", "Test Entry", 0.0, 
       ref helpText);

            Assert.IsTrue(transID > 0, helpText);
  }
and the code within the class
using System;
using System.Data;
using System.Data.SqlClient;

namespace MoneyEngine
{
    class DBMoneyTransaction
    {
        public int InsertTransaction(int transType, DateTime transDate, string transCategory, string transDescription, double transAmount, ref string helpText)
        {
            SqlCommand cmd = null;

            try
            {
                SqlConnection connection = new SqlConnection(@"Data Source=DSLAPTOP\SQLEXPRESS;Initial Catalog=MoneyEngine;Integrated Security=True");
                connection.Open();

                cmd = new SqlCommand("uspInsertTransaction", connection);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param = new SqlParameter("@TransType", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = transType;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TransDate", SqlDbType.DateTime);
                param.Direction = ParameterDirection.Input;
                param.Value = transDate;
                cmd.Parameters.Add(param);
                
                param = new SqlParameter("@TransCategory", SqlDbType.NVarChar, -1);
                param.Direction = ParameterDirection.Input;
                param.Value = transCategory;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TransDescription", SqlDbType.NVarChar, -1);
                param.Direction = ParameterDirection.Input;
                param.Value = transDescription;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@transAmount", SqlDbType.Money);
                param.Direction = ParameterDirection.Input;
                param.Value = transAmount;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TransID", SqlDbType.Int);
                param.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

                return (int) cmd.Parameters["@TransID"].Value;
            }
            catch (SqlException sqlEx)
            {
                helpText = sqlEx.Message;
                return 0;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
        }
    }
}

The test passes, but the above code needs some serious refractoring, like a lot of data access code, it is long and very repetivate, I know that there are products that will generate this code automatically, but I am old fashioned and like to write it myself.

OK to the refractoring, I am going to extract the parameter construction into a separate methods.

        private SqlParameter GetParam(string paramName, object paramValue, SqlDbType paramType, ParameterDirection paramDirection)
        {
            SqlParameter param = new SqlParameter(paramName, paramType);
            param.Direction = paramDirection;
            param.Value = paramValue;
            return param;
        }

        private 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 int InsertTransaction(int transType, DateTime transDate, string transCategory, string transDescription, double transAmount, ref string helpText)
        {
            SqlCommand cmd = null;

            try
            {
                SqlConnection connection = new SqlConnection(@"Data Source=DSLAPTOP\SQLEXPRESS;Initial Catalog=MoneyEngine;Integrated Security=True");
                connection.Open();

                cmd = new SqlCommand("uspInsertTransaction", connection);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(GetParam("@TransType", transType, SqlDbType.Int, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDate", transDate, SqlDbType.DateTime, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransCategory", transCategory, SqlDbType.NVarChar, -1, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDescription", transDescription, SqlDbType.NVarChar, -1, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransAmount", transAmount, SqlDbType.Money, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransID", 0, SqlDbType.Int, ParameterDirection.Output));

                cmd.ExecuteNonQuery();

                return (int) cmd.Parameters["@TransID"].Value;
            }
            catch (SqlException sqlEx)
            {
                helpText = sqlEx.Message;
                return 0;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
        }

after much testing, I managed to get the parameters out fo the main function,  and its looking a bit smaller.  now we need an update routine now so first the test

 

        [Test]
        public void UpdateTransaction()
        {
            DBMoneyTransaction transaction = new DBMoneyTransaction();

            string helpText = string.Empty;

            Assert.IsTrue(transaction.UpdateTransaction(1, 0, System.DateTime.Now, "Test", "Test Entry", 0.0, 
ref helpText));
        }

and the routine

public bool UpdateTransaction(int transID, int transType, DateTime transDate, string transCategory, string transDescription, double transAmount, ref string helpText)
        {
            SqlCommand cmd = null;

            try
            {
                SqlConnection connection = new SqlConnection(@"Data Source=DSLAPTOP\SQLEXPRESS;Initial Catalog=MoneyEngine;Integrated Security=True");
                connection.Open();

                cmd = new SqlCommand("uspInsertTransaction", connection);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(GetParam("@TransID", transID, SqlDbType.Int, ParameterDirection.Output));
                cmd.Parameters.Add(GetParam("@TransType", transType, SqlDbType.Int, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDate", transDate, SqlDbType.DateTime, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransCategory", transCategory, SqlDbType.NVarChar, -1, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDescription", transDescription, SqlDbType.NVarChar, -1, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransAmount", transAmount, SqlDbType.Money, ParameterDirection.Input));
               
                cmd.ExecuteNonQuery();

                return true;
            }
            catch (SqlException sqlEx)
            {
                helpText = sqlEx.Message;
                return false;
            }
            finall
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
        }

so new we need some refractoring, as there is a lot of duplication in the code.  after a bit of refractoring here is the new functions

 

        public 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;
        }

        public int InsertTransaction(int transType, DateTime transDate, 
string transCategory, string transDescription, double transAmount, ref string helpText)
        {
            SqlCommand cmd = null;

            try
            {
                cmd = GetCommand("uspInsertTransaction", STR_Connection);

                cmd.Parameters.Add(GetParam("@TransType", transType, SqlDbType.Int, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDate", transDate, SqlDbType.DateTime, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransCategory", transCategory, 
SqlDbType.NVarChar, -1, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDescription", transDescription, 
SqlDbType.NVarChar, -1, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransAmount", transAmount, 
SqlDbType.Money, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransID", 0, SqlDbType.Int, ParameterDirection.Output));

                cmd.ExecuteNonQuery();

                return (int) cmd.Parameters["@TransID"].Value;
            }
            catch (SqlException sqlEx)
            {
                helpText = sqlEx.Message;
                return 0;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
        }

        public bool UpdateTransaction(int transID, int transType, DateTime transDate, string transCategory, 
string transDescription, double transAmount, ref string helpText)
        {
            SqlCommand cmd = null;

            try
            {
                cmd = GetCommand("uspInsertTransaction", STR_Connection);

                cmd.Parameters.Add(GetParam("@TransID", transID, SqlDbType.Int, ParameterDirection.Output));
                cmd.Parameters.Add(GetParam("@TransType", transType, SqlDbType.Int, ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDate", transDate, SqlDbType.DateTime, 
ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransCategory", transCategory, SqlDbType.NVarChar, -1, 
ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransDescription", transDescription, SqlDbType.NVarChar, -1, 
ParameterDirection.Input));
                cmd.Parameters.Add(GetParam("@TransAmount", transAmount, SqlDbType.Money, 
ParameterDirection.Input));
                
                cmd.ExecuteNonQuery();

                return true;
            }
            catch (SqlException sqlEx)
            {
                helpText = sqlEx.Message;
                return false;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
        }

Unknown's avatar

About Duncan Butler

I’m Duncan, an entrepreneur and co-founder behind nth.digital and The Puzzle Board, where I combine heritage‑focused creativity with modern technology. My work spans AI development, C# engineering, and collaborative problem‑solving with Squad, building smarter tools, streamlined workflows, and engaging digital experiences. Whether refining puzzle production systems or exploring new AI capabilities, I’m driven by creating practical, intuitive solutions that make technology feel effortless.
This entry was posted in Projects. Bookmark the permalink.

Leave a comment