Eric Wise

Business & .NET

Easy Assets .NET :: Part 3A, Table Module Code Samples

This is a continuation of part 1 and part 2.

Well folks, it's time to spew some code. As I mentioned in part 2, the table module pattern allows me to create objects that model the underlying database. Now many architecture types will say that this type of model isn't very flexible as far as changing out the underlying database, but...

I am tying this product to SQL server only
By making the objects model the tables and embedding the data I/O in them, it should be very easy for even beginning coders to follow the code.
Note that all object will inherit collectionbase as I prefer to bind collections rather than datareaders or datasets because of the added flexibility of using my own objects in a collection.
Also note that every object contains a connection string parameter, this is because by parsing the domain of the logged in user I know which database they belong to. Each object needs to be aware of what customer they serve.

That being said, all tables can fall into three types:

Lookup/List tables- These tables are used to populate lists, usually drop down. In general, all values will be read from these tables.
Lookup Key Tables- These tables have a greater variety of information than lookup and list tables, but generally will have a lookup key parameter passed in. (For example, an employee list will usually specify a department id)
Searchable parent tables- These tables have no specific lookup key, and searches may be from a variety of fields.

Code sample 1: Lookup Table (sites)

Lookup tables are the simplest of the objects. In Easy Assets .NET users can define three levels of information about the physical location of an asset. The top level is referred to as a site. If you were managing a school district, a site would be the most general location, in this case I'll use the High School as the site. Now because Site is the top level, there is no lookup key, and when requesting a list of sites user will see all sites ordered alphabetically by description.

First we have to build our site collection:

    1 using System;
    2 using System.Collections;
    3 using System.Data;
    4 using System.Data.SqlClient;
    5 using Microsoft.ApplicationBlocks.Data;
    6  
    7 namespace EasyAssets.DAC
    8 {
    9     /// <summary>
   10     /// Summary description for Sites.
   11     /// </summary>
   12     public class Sites : CollectionBase
   13     {
   14         private string _connectstring;
   15  
   16         public Sites(string connectstring)
   17         {
   18             _connectstring = connectstring;
   19         }
   20  
   21         public int Add(Site site)
   22         {
   23             return List.Add(site);
   24         }
   25  
   26         public void Remove(Site site)
   27         {
   28             List.Remove(site);
   29         }
   30  
   31         public Site this[int index]
   32         {
   33             get
   34             {
   35                 return((Site)List[index]);
   36             }
   37             set
   38             {
   39                 List[index] = value;
   40             }
   41         }
   42  
   43         public void GetList()
   44         {
   45             using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, "ea_site_getlist"))
   46             {
   47                 while(dr.Read())
   48                 {
   49                     Site s = new Site();
   50                     s.SiteID = (int)dr["SiteID"];
   51                     s.SiteDescription = dr["SiteDescription"].ToString();
   52                     this.Add(s);
   53                 }
   54             }
   55         }
   56     }
   57 }

And our actual site object:

    1 using System;
    2 using System.Data;
    3 using System.Data.SqlClient;
    4 using Microsoft.ApplicationBlocks.Data;
    5  
    6 namespace EasyAssets.DAC
    7 {
    8     /// <summary>
    9     /// Summary description for Site.
   10     /// </summary>
   11     public class Site
   12     {
   13         #region private members
   14  
   15         //property accessors
   16         private int _siteid = -1;
   17         private string _sitedescription;
   18  
   19         //Database key
   20         private string _connectstring;
   21  
   22         #endregion private members
   23  
   24         #region Properties
   25  
   26         public int SiteID
   27         {
   28             get
   29             {
   30                 return _siteid;
   31             }
   32             set
   33             {
   34                 _siteid = value;
   35             }
   36         }
   37  
   38         public string SiteDescription
   39         {
   40             get
   41             {
   42                 return _sitedescription;
   43             }
   44             set
   45             {
   46                 _sitedescription = value;
   47             }
   48         }
   49  
   50         #endregion Properties
   51  
   52  
   53         public Site()
   54         {
   55  
   56         }
   57  
   58         public Site(string connectstring)
   59         {
   60             _connectstring = connectstring;
   61         }
   62  
   63         /// <summary>
   64         /// Loads a site by id
   65         /// </summary>
   66         /// <param name="id">pk</param>
   67         /// <param name="connectstring">database connection string</param>
   68         public Site(int id, string connectstring)
   69         {
   70             _connectstring = connectstring;
   71  
   72             SqlParameter[] sqlparams = new SqlParameter[1];
   73  
   74             SqlParameter param = new SqlParameter("@SiteID", SqlDbType.Int);
   75             param.Value = id;
   76             sqlparams[0] = param;
   77  
   78             using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, "ea_site_get", sqlparams))
   79             {
   80                 if(dr.Read())
   81                 {
   82                     _siteid = dr["SiteID"] == DBNull.Value ? -1 : (int)dr["SiteID"];
   83                     _sitedescription = dr["SiteDescription"] == DBNull.Value ? "" : dr["SiteDescription"].ToString();
   84                 }
   85                 else
   86                     throw new Exception("Site ID not found!");
   87             }
   88         }
   89  
   90         /// <summary>
   91         /// Does insert if key is -1 (default)
   92         /// Updates if key is not default.
   93         /// </summary>
   94         public void Save()
   95         {
   96             if(_siteid == -1)
   97             {
   98                 //Do Insert
   99                 SqlParameter[] sqlparams = new SqlParameter[1];
  100  
  101                 SqlParameter param = new SqlParameter("@SiteDescription", SqlDbType.NVarChar);
  102                 if(_sitedescription != "") param.Value = _sitedescription.Replace("'","''");
  103                 else param.Value = DBNull.Value;
  104                 sqlparams[0] = param;
  105  
  106                 if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_site_insert", sqlparams) == -1)
  107                     throw new Exception("Site create failed!");
  108             }
  109             else
  110             {
  111                 //Do Update
  112                 SqlParameter[] sqlparams = new SqlParameter[2];
  113                 
  114                 SqlParameter param = new SqlParameter("@SiteID", SqlDbType.Int);
  115                 if(_siteid != -1) param.Value = _siteid;
  116                 else param.Value = DBNull.Value;
  117                 sqlparams[0] = param;
  118  
  119                 param = new SqlParameter("@SiteDescription", SqlDbType.NVarChar);
  120                 if(_sitedescription != "") param.Value = _sitedescription.Replace("'","''");
  121                 else param.Value = DBNull.Value;
  122                 sqlparams[1] = param;
  123  
  124  
  125  
  126                 if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_site_update", sqlparams) == -1)
  127                     throw new Exception("Site update failed!");
  128             }
  129         }
  130  
  131         public void Delete()
  132         {
  133             SqlParameter[] sqlparams = new SqlParameter[1];
  134  
  135             SqlParameter param = new SqlParameter("@SiteID", SqlDbType.Int);
  136             param.Value = _siteid;
  137             sqlparams[0] = param;
  138  
  139             if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_site_delete", sqlparams) == -1)
  140                 throw new Exception("Site delete failed!");
  141         }
  142     }
  143 }