Real Test Driven Development – Thinking of Money 7

 

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

Advertisements

About Duncan Butler

Trying to be a very agile software developer, working in C# with Specflow, Nunit and Machine Specifications, and in the evening having fun with Ruby and Rails
This entry was posted in Projects. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s