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.