Progress

It has been a while since my last post about Project Sputnik and so a quick progress report is definitely due.

I haven't had much free time to work on the project in this last month due to work activities and personal commitments. Same old same old, right?

That said, I have coded up the DAL layer for each database table in a basic form, and tested each unit. So, essentially, I now have a complete DAL for the project that performs the basic CRUD functionality. As I code up the BL and UI layers I expect that some of the DAL functionality will need to be honed in certain areas to provide extra functionality.

From here on in it is a Business Logic focus for me. For the next few weeks at least ...

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)

 

 

 



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.

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.

DAL proof of concept

I've put together a proof of concept for the DAL that covers two related tables and then a simple test harness. Code to follow ...