Project Sputnik Solution Structure

A couple of posts ago I mentioned I would upload some screen shots of the Solution. Well here they are. Finally.

This shows the projects with the solution. You can see all the elements required, DAL Interface, the DAL, BLL, Concrete Classes and finally the Thick Client (WinForms app).

 

Focussing on the DAL, here are the DAL classes (one for each table in the DB)

 

 

And finally, the concrete classes (one for each entity)

 

 

 



Compacting a VHD

This is something I refer to from time to time - I'm posting here so I can find the link again more quickly in the future...

http://helpdeskgeek.com/how-to/compact-virtual-pc-disk/

Progress Update 2

The thick-client test harness I mentioned in the previous post was actually a "copy / paste" of the main Assets Form from the existing WinForms app into the new solution. The purpose of this was to prove the BL and DA layers actually provide the services that the final application(s) will need.

One thing that came out of this test was the fact that the old app has all the BL within the forms, so this logic had to be stripped out of the UI and then the (somewhat thinner) form coupled up to the BL layer. As I said this is just a test harness so this UI is not production code whereas the BL and DAL are.

I'm happy to report that the test form behaves as it should. However, I now need to do the same work with the exiting Asset Maintenance form so that I can test the BL and DA layers support creating new and updating existing Asset entities.

Progress update

Work commitments have hampered my work on Project Sputnik recently but I am happy to say that I have made progress.

I've created the official solution and added the child projects. I have created the DAL interface class, Entity classes and DAL classes for each table in the database, created the BL layer for the Assets that enforces the BL for the two asset classes, and finally thrown a WinForms thick-client test-harness together to display the items in the DB. This isn't demo or POC code but production code.

I'll post again soon with details and screen shots.

Rich Client UI

Moving on to the UI the final step in proving the traditional n-tier and DAL architecture is to have a WinForms application that references only the Business Logic layer. Throughout this p.o.c. I have only coded up the reading part of the DAL CRUD operation, the reason for that being one of brevity. I could of course go back and code up the full operation if I so desired, but in this case the p.o.c will remain just that.

I created a WinForms app and added a ListView control to Form1, setting the properties to detailed view and adding two columns. I then added a handler for the Load method of the form as shown below and added the code to read the assets from the database and display them on the “grid”.

using System.Collections.Generic;
using System.Windows.Forms;
using BL;

namespace UIAssetTestHarness

{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, System.EventArgs e)

        {
            // Load the grid with the asset data
            BLAssets assets = new BLAssets();
            List<UIAsset> assetsList = assets.GetAssets();
            foreach (UIAsset asset in assetsList)
            {
                ListViewItem item = new ListViewItem(asset.AssetTag);
                item.SubItems.Add(asset.Description);
                lstAssets.Items.Add(item);
            }
        }
    }
}

And that’s it. If I were to pursue this p.o.c any further it would be to code the BL layer as web services and have the UI consume those. I’ll be doing that later on.

The Business Logic

Carrying on with the proof of concept I coded up some BL classes and a test harness for them. The Asset entity has a numeric Type property which is the ID of an AssetType entity. The BL needs to handle this relationship so that the user will work with friendly names / artefacts on the UI. The BL is therefore the translator between the DAL and the UI and so needs to change the Type property from a number to a friendly string. To this end I created a class that deals with a single Asset entity, converting it to a UI version.

using System;
using DAL;
using EntityClasses;

namespace BL

{
    public class UIAsset : Asset
    {
        // Inherits all the members of Asset and hides the base class Type (int) to expose the UI name (string)
        new public string Type { get; set; }
    }

    public class BLAsset

    {
        private Asset _asset { get; set; }
        private AssetType _assetType { get; set; }
        private UIAsset _uiAsset { get; set; }

        // Constructors

        public BLAsset()
        {
        }

        public BLAsset(Int32 id)

        {
            LoadAsset(id);
        }

        // Destructor

        ~BLAsset()
        {
        }

        // Build a UIAsset class from the Asset and AssetType entities

        public bool LoadAsset(Int32 id)
        {
            Boolean returnVal = false;

            try

            {
                DAL_Asset dal_Asset = new DAL_Asset();
                _asset = dal_Asset.Retrieve(id);
            }

            catch (Exception e)

            {
                _asset = null;
                throw e;
            }

            if (_asset.Type != 0)

            {
                try
                {
                    DAL_AssetType dal_AssetType = new DAL_AssetType();
                    _assetType = dal_AssetType.Retrieve(_asset.Type);
                }

                catch (Exception e)

                {
                    _asset = null;
                    throw e;
                }
            }
            else
            {
                _asset = null;
                throw new Exception(string.Format("Invalid Asset type associated with Asset ID {0}", id.ToString()));
            }

            _uiAsset = new UIAsset();

            _uiAsset.AssetTag = _asset.AssetTag;
            _uiAsset.Description = _asset.Description;
            _uiAsset.ID = _asset.ID;
            …
            // Get the Asset Type friendly name
            _uiAsset.Type = _assetType.sAssetType;

            returnVal = true;

            return returnVal;
        }

        public UIAsset GetUIAsset()

        {
            return _uiAsset;
        }
    }
}

This is all well and good, but the UI will need to work with more than just one asset. For instance, at some point the UI will need to show a list of assets in the system so that a user can select one for editing, say. To accommodate this requirement I coded another class to handle multiple assets and to offer a list of Asset Types (on creating a new Asset entity the user will need to select an Asset Type, probably from a dropdown list).

using System;
using System.Collections.Generic;
using DAL;
using EntityClasses;

namespace BL

{
    public class BLAssets
    {
        private List<UIAsset> _assets { get; set; }
        private List<AssetType> _assetTypes { get; set; }

        // Constructor
        // Get a list of all the assets in the system
        public BLAssets()
        {
            try
            {
                // Get a list of the Asset IDs in the system
                DAL_Asset dal_Asset = new DAL_Asset();
                List<Int32> IDs = dal_Asset.RetrieveIDs();

                _assets = new List<UIAsset>();

                BLAsset _asset = new BLAsset();

                // Iterate through the list of IDs adding each one to the list

                foreach (Int32 id in IDs)
                {
                    UIAsset newAsset = new UIAsset();
                    if (_asset.LoadAsset(id))
                    {
                        newAsset = _asset.GetUIAsset();
                        _assets.Add(newAsset);
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            try

            {
                DAL_AssetType dal_AssetType = new DAL_AssetType();
                _assetTypes = dal_AssetType.RetrieveAll();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<UIAsset> GetAssets()

        {
            return _assets;
        }

        public List<AssetType> GetAssetTypes()

        {
            return _assetTypes;
        }
    }
}

To test this functionality a very simple Console application was needed.

using System;
using System.Collections.Generic;

namespace BL

{
    class Program
    {
        static void Main(string[] args)
        {
            ReadAssets();
            ReadAssetTypes();

            Console.WriteLine("Press Any Key to continue....");

            Console.ReadKey(true);
        }

        static void ReadAssets()

        {
            Console.WriteLine("ReadAssets() test");
            BLAssets x = new BLAssets();
            List<UIAsset> assets = x.GetAssets();
            if (assets.Count > 0)
            {
                foreach (UIAsset asset in assets)
                {
                    Console.WriteLine("Asset details:");
                    Console.WriteLine("ID: {0}, Tag: {1}, Type {2}", asset.ID, asset.AssetTag, asset.Type);
                }
            }
            Console.WriteLine("");
        }

        static void ReadAssetTypes()

        {
            Console.WriteLine("ReadAssetTypes() test");
            BLAssets x = new BLAssets();
            List<EntityClasses.AssetType> assetTypes = x.GetAssetTypes();
            if (assetTypes.Count > 0)
            {
                foreach (EntityClasses.AssetType assetType in assetTypes)
                {
                    Console.WriteLine("AssetType details:");
                    Console.WriteLine("ID: {0}, AssetType: {1}", assetType.ID, assetType.sAssetType);
                }
            }

            Console.WriteLine("");

        }
    }
}

So there it is. I needed to add the App.Config and ConnectionStrings.config files to the test harness (see previous post).

The last step in the P.O.C was to build a simple thick-client UI.

Connection Strings

Referring back to my initial attempt to port the existing VB6 application from MS Access to SQL Server by changing the connection string, one of the aims of Project Sputnik is to try to make it DB independent. Before my recent trip abroad my techie nerd/guru friend and former colleague put me on to a method which I believe will help me achieve this. The application will have a separate configuration file for the connection string (than App.Config) and the DAL will have a class that reads the connection string from this file. By writing ANSI compliant SQL in the DAL concrete classes I hope to achieve the goal to be DB impartial.

The App.Config file in the recent DAL Test Harness looks like this:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>
       <connectionStrings configSource="connectionstrings.config" />
</configuration>

I added a configuration file called ConnectionStrings.Config and set the file property ‘Copy to Output Directory’ to ‘Copy Always’. The content of the file looks like this:

<?xml version="1.0" encoding="utf-8" ?>

<connectionStrings>
       <add name="sql" connectionString="Server=<server>;Database=OfficeManager;User Id=<uid>;Password=<pwd>;" providerName="System.Data.SqlClient"/>
</connectionStrings>

The User ID and password have been hidden of course… The DAL has an additional class called AppSettingsReader.cs which looks like this:

using System;
using System.Configuration;

namespace DAL

{
    sealed class AppSettingsReader
    {
        public static ConnectionStringSettings GetConnectionSettings(String key)
        {
            return ConfigurationManager.ConnectionStrings[key];
        }
    }
}

For this to work there needs to be a reference to System.configuration in the project.

DAL proof of concept pt2

Firstly, I created an interface class for the DAL that each concrete class in the DAL will implement. Here's the entire code:

using System;
using System.Collections.Generic;

namespace DALInterface
{
  public interface IDAL <T>
  {
    void Create(T obj);
    T Retrieve( Int32 key);
    void Update(T obj);
    bool Delete( Int32 key);
    List <T> RetrieveAll();
    List < Int32 > RetrieveIDs();
  }
}

I then chose two tables that have a simple relationship for the Business Logic to enforce, namely Asset and AssetTypes. I then created concrete classes for each entity as these will be needed across multiple layers in my architecture.

using System;

namespace EntityClasses
{
  public class Asset
  {
    public Int32 ID { get; set; }
    public String AssetTag { get; set; }
    public Int32 Type { get; set; }
    public String Description { get; set; }
    ...
  }
}

Here is the AssetType class

using System;

namespace EntityClasses
{
  public class AssetType
  {
    public Int32 ID { get; set; }
    public string sAssetType { get; set; }
  }
}

Now that I had a definition for an Asset and an Asset Type, I could create concrete DAL classes for them.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using DALInterface;
using EntityClasses;

namespace DAL
{
  public class DAL_Asset : IDAL<Asset>
  {
    private DbConnection connection;
    private DbProviderFactory factory;

    // Constructor
    public DAL_Asset()
    {
      ConnectionStringSettings csc = AppSettingsReader.GetConnectionSettings("sql");

      factory = DbProviderFactories.GetFactory(csc.ProviderName);

      connection = factory.CreateConnection();
      connection.ConnectionString = csc.ConnectionString;
    }

    // Destructor
    ~DAL_Asset()
    {
      connection.Dispose();
      // there is no factory.Dispose();
    }

    public void Create(Asset asset)
    {
      throw new NotImplementedException();
    }

    public Asset Retrieve(Int32 id)
    {
      Asset asset = new Asset();
      connection.Open();
      DbCommand command = factory.CreateCommand();
      command.CommandText = "SELECT * FROM Assets WHERE ID = @id";
      DbParameter param = factory.CreateParameter();
      param.DbType = DbType.Int32;
      param.ParameterName = "@id";
      param.Value = id;

      command.Connection = connection;
      command.Parameters.Add(param);

      DbDataReader reader = command.ExecuteReader();

      if (reader.HasRows)
      {
        while (reader.Read())
        {
          asset.AssetTag = reader["AssetTag"] == DBNull.Value ? String.Empty : reader["AssetTag"].ToString();
          asset.Description = reader["Description"] == DBNull.Value ? String.Empty : reader["Description"].ToString();
          asset.ID = reader["ID"] == DBNull.Value ? 0 : (Int32)reader["ID"];
          asset.Type = reader["Type"] == DBNull.Value ? 0 : (Int32)reader["Type"];
        }
      }
      else
      {
        throw new RowNotInTableException();
      }

      connection.Close();
      return asset;
    }

    public void Update(Asset asset)
    {
      throw new NotImplementedException();
    }

    public bool Delete(Int32 id)
    {
      throw new NotImplementedException();
    }

    public List<Asset> RetrieveAll()
    {
      throw new NotImplementedException();
    }

    public List<Int32> RetrieveIDs()
    {
      List<Int32> IDs = new List<Int32>();
      connection.Open();
      DbCommand command = factory.CreateCommand();
      command.CommandText = "SELECT ID FROM Assets";
      command.Connection = connection;
      DbDataReader reader = command.ExecuteReader();

      if (reader.HasRows)
      {
        while (reader.Read())
        {
          IDs.Add(reader["ID"] == DBNull.Value ? 0 : (Int32)reader["ID"]);
      }
    }
    else
    {
      throw new Exception("No IDs found in Assets table");
    }

    connection.Close();
    return IDs;
    }
  }
}

Here is the AssetType class

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using DALInterface;
using EntityClasses;

namespace DAL
{
  public class DAL_AssetType : IDAL<AssetType>
  {
    private DbConnection connection;
    private DbProviderFactory factory;

    public DAL_AssetType()
    {
      ConnectionStringSettings csc = AppSettingsReader.GetConnectionSettings("sql");
      factory = DbProviderFactories.GetFactory(csc.ProviderName);
      connection = factory.CreateConnection();
      connection.ConnectionString = csc.ConnectionString;
    }

    // Destructor
    ~DAL_AssetType()
    {
      connection.Dispose();
      // there is no factory.Dispose();
    }

    public void Create(AssetType assetType)
    {
      throw newNotImplementedException();
    }

    public AssetType Retrieve(Int32 id)
    {
      AssetType assetType = new AssetType();

      connection.Open();
      DbCommand command = factory.CreateCommand();
      command.CommandText = "SELECT * FROM AssetTypes WHERE ID = @id";
      DbParameter param = factory.CreateParameter();
      param.DbType = DbType.Int32;
      param.ParameterName = "@id";
      param.Value = id;

      command.Connection = connection;
      command.Parameters.Add(param);

      DbDataReader reader = command.ExecuteReader();
      if (reader.HasRows)
      {
        while (reader.Read())
        {
          assetType.ID = reader["ID"] == DBNull.Value ? 0 : (Int32)reader["ID"];
          assetType.sAssetType = reader["AssetType"] == DBNull.Value ? String.Empty : reader["AssetType"].ToString();
        }
      }
      else
      {
        throw new RowNotInTableException();
      }

      connection.Close();
      return assetType;
    }

    public void Update(AssetType assetType)
    {
      throw new NotImplementedException();
    }

    public bool Delete(Int32 id)
    {
      throw new NotImplementedException();
    }

    public List<AssetType> RetrieveAll()
    {
      List<AssetType> assetTypes = new List<AssetType>();
      List<Int32> IDs = RetrieveIDs();
      foreach (Int32 id in IDs)
      {
        assetTypes.Add(Retrieve(id));
      }
      return assetTypes;
    }

    public List<Int32> RetrieveIDs()
    {
      List<Int32> IDs = new List<Int32>();
      connection.Open();
      DbCommand command = factory.CreateCommand();
      command.CommandText = "SELECT ID FROM AssetTypes";
      command.Connection = connection;
      DbDataReader reader = command.ExecuteReader();
      if (reader.HasRows)
      {
        while (reader.Read())
        {
          IDs.Add(reader["ID"] == DBNull.Value ? 0 : (Int32)reader["ID"]);
        }
      }
      else
      {
        throw new Exception("No IDs found in AssetTypes table");
      }
      connection.Close();
      return IDs;
    }
  }
}

Now I had everything I needed, I could create a test harness. For that a simple Console Application was all that was needed.

using System;
using System.Collections.Generic;

namespace DAL
{
  class Program
  {
    static void Main(string[] args)
    {
      GetListOfAssets();
      ReadAsset(57);
      ReadAssetType(1);
      Console.WriteLine("Press Any Key to continue....");
      Console.ReadKey(true);
    }

    static void GetListOfAssets()
    {
      Console.WriteLine("GetListOfAssets() test");
      DAL_Asset instance = new DAL_Asset();
      try
      {
        List<Int32> IDs = new List<Int32>();
        IDs = instance.RetrieveIDs();
        if (IDs.Count == 0)
        {
          Console.WriteLine("Asset count is zero");
        }
        else
        {
          foreach (Int32 id in IDs)
          {
            Console.Write(string.Format("{0}, ", id.ToString()));
          }
        }
      }
      catch (Exception e)
      {
        throw e;
      }
      Console.WriteLine("");
      Console.WriteLine("");
    }

    static void ReadAsset(Int32 id)
    {
      Console.WriteLine("ReadAsset() Test");
      DAL_Asset instance = new DAL_Asset();
      try
      {
        EntityClasses.Asset asset = instance.Retrieve(id);
        if (asset == null)
        {
          Console.WriteLine("Asset not found - it is null");
        }
        else
        {
          Console.WriteLine(string.Format("Asset ID: {0}", asset.ID.ToString()));
          Console.WriteLine(string.Format("Asset AssetTag: {0}", asset.AssetTag.ToString()));
          Console.WriteLine( string.Format("Asset Description: {0}", asset.Description.ToString()));
          Console.WriteLine(string.Format("Asset PurchaseDate: {0}" , asset.PurchaseDate.ToString()));
        }
      }
      catch (Exception e)
      {
        throw e;
      }
      Console.WriteLine("");
    }

    static void ReadAssetType(Int32 id)
    {
      Console.WriteLine("ReadAssetType() test");
      DAL_AssetType instance = new DAL_AssetType();
      try
      {
        EntityClasses.AssetType at = instance.Retrieve(id);
        if (at == null)
        {
          Console.WriteLine("AssetType not found - it is null");
        }
        else
        {
          Console.WriteLine(string.Format("AssetType ID: {0}", at.ID.ToString()));
          Console.WriteLine(string.Format("AssetType AssetType: {0}" , at.sAssetType));
        }
      }
      catch (Exception e)
      {
        throw e;
      }
      Console.WriteLine("");
    }
  }
}

And that is it. A simple test harness that uses the DAL classes to read data from the database. The next step would be to code up a simple Business Logic layer to consume the DAL and another test harness at that point too.