Eric Wise

Business & .NET

Easy Asssets .NET :: Part 3c, Table Module Code Samples

This is a continuation of Part 3b

Code sample 3: Searchable Parent Table (assets)

Searchable parent tables are tables that can be searched on many or no fields. The main object in Easy Assets .NET is the asset object, and having many fields and properties can make it the most complex to search. It is impossible for me to predict how many fields will be searched on. So how do I handle this? I expose two main functions. Pay particular attention to the second one, it's neat..

Search(string criteria, string sortexpression) - Being that I already know the structure of the data, this search function is a cheat function where I pass it in a criteria statement (WHERE blah=whatever AND blah2=whatever AND blah3 LIKE whatever). This allows me some flexibility in using = or Like or any other type of compares that are totally dependent on the style of the search form.
Search(Asset a, string sortexpression) - This is the fun one. What if I wanted to allow a programmer to populate certain properties on an asset object (currently I only support the string and integer properties) and allow them to pass me that asset to be analyzed. If any of the string or integer properties are not in the defaults (string.empty, 0, or -1) then I will append them to the WHERE clause of the SQL statement and search on them. This allows the caller to simply fill in whatever properties they are looking for and the collection populates where the passed in fields match. System.Reflection is a very powerful namespace and worth doing more research on if you haven't taken the opportunity before. Honestly, I don't use this function in the current build, but I was curious about reflection and thought it would be a neat thing to code.

Here's the code:

    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 Assets.
   11     /// </summary>
   12     public class Assets : CollectionBase
   13     {
   14         private string _connectstring;
   15  
   16         public Assets(string connectstring)
   17         {
   18             _connectstring = connectstring;
   19         }
   20  
   21         public int Add(Asset asset)
   22         {
   23             return List.Add(asset);
   24         }
   25  
   26         public void Remove(Asset asset)
   27         {
   28             List.Remove(asset);
   29         }
   30  
   31         public Asset this[int index]
   32         {
   33             get
   34             {
   35                 return((Asset)List[index]);
   36             }
   37             set
   38             {
   39                 List[index] = value;
   40             }
   41         }
   42  
   43         public void Search(Asset a, string sortexpression)
   44         {
   45             //Prepare SQL Statement
   46             System.Text.StringBuilder strSQL = new System.Text.StringBuilder();
   47  
   48             strSQL.Append("SELECT * FROM Assets ");
   49  
   50             //Use Reflection to get properties
   51             System.Reflection.PropertyInfo[] props;
   52             props = a.GetType().GetProperties();
   53  
   54             bool where = false;
   55  
   56             foreach(System.Reflection.PropertyInfo prop in props)
   57             {
   58                 switch(prop.PropertyType.Name)
   59                 {
   60                     case "String":
   61                         if(prop.GetValue(a, null).ToString() != String.Empty)
   62                         {
   63                             if(where)
   64                                 strSQL.AppendFormat(" AND {0} = '{1}'", prop.Name, prop.GetValue(a, null));
   65                             else
   66                             {
   67                                 strSQL.AppendFormat("WHERE {0} = '{1}'", prop.Name, prop.GetValue(a, null));
   68                                 where = true;
   69                             }
   70                         }
   71                         break;
   72  
   73                     case "Int32":
   74                         if((System.Int32)prop.GetValue(a, null) > 0)
   75                         {
   76                             if(where)
   77                                 strSQL.AppendFormat(" AND {0} = '{1}'", prop.Name, prop.GetValue(a, null).ToString());
   78                             else
   79                             {
   80                                 strSQL.AppendFormat("WHERE {0} = '{1}'", prop.Name, prop.GetValue(a, null).ToString());
   81                                 where = true;
   82                             }
   83                         }
   84                         break;
   85                 }
   86             }
   87  
   88             if(sortexpression.Length > 0)
   89                 strSQL.AppendFormat(" ORDER BY {0}", sortexpression);
   90  
   91             using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, CommandType.Text, strSQL.ToString()))
   92             {
   93                 while(dr.Read())
   94                 {
   95                     Asset newAsset = new Asset(_connectstring);
   96  
   97                     newAsset.AssetID = dr["AssetID"] == DBNull.Value ? -1 : (int)dr["AssetID"];
   98                     newAsset.ProductID = dr["ProductID"] == DBNull.Value ? -1 : (int)dr["ProductID"];
   99                     newAsset.AssetStatusID = dr["AssetStatusID"] == DBNull.Value ? -1 : (int)dr["AssetStatusID"];
  100                     newAsset.DepartmentID = dr["DepartmentID"] == DBNull.Value ? -1 : (int)dr["DepartmentID"];
  101                     newAsset.ResourceID = dr["ResourceID"] == DBNull.Value ? -1 : (int)dr["ResourceID"];
  102                     newAsset.OwnerCompanyID = dr["OwnerCompanyID"] == DBNull.Value ? -1 : (int)dr["OwnerCompanyID"];
  103                     newAsset.SiteID = dr["SiteID"] == DBNull.Value ? -1 : (int)dr["SiteID"];
  104                     newAsset.LocationID = dr["LocationID"] == DBNull.Value ? -1 : (int)dr["LocationID"];
  105                     newAsset.RoomID = dr["RoomID"] == DBNull.Value ? -1 : (int)dr["RoomID"];
  106                     newAsset.AcquisitionMethodID = dr["AcquisitionMethodID"] == DBNull.Value ? -1 : (int)dr["AcquisitionMethodID"];
  107                     newAsset.PurchaseOrderID = dr["PurchaseOrderID"] == DBNull.Value ? -1 : (int)dr["PurchaseOrderID"];
  108                     newAsset.PurchaseOrderLine = dr["PurchaseOrderLine"] == DBNull.Value ? -1 : (int)dr["PurchaseOrderLine"];
  109                     newAsset.PurchaseUnitPrice = dr["PurchaseUnitPrice"] == DBNull.Value ? 0 : double.Parse(dr["PurchaseUnitPrice"].ToString());
  110                     newAsset.PurchaseDate = dr["PurchaseDate"] == DBNull.Value ? string.Empty : DateTime.Parse(dr["PurchaseDate"].ToString()).ToShortDateString();
  111                     newAsset.DateReceived = dr["DateReceived"] == DBNull.Value ? string.Empty : DateTime.Parse(dr["DateReceived"].ToString()).ToShortDateString();
  112                     newAsset.SerialNumber = dr["SerialNumber"] == DBNull.Value ? "" : dr["SerialNumber"].ToString();
  113                     newAsset.AssetTag = dr["AssetTag"] == DBNull.Value ? string.Empty : dr["AssetTag"].ToString();
  114                     newAsset.AssetTag2 = dr["AssetTag2"] == DBNull.Value ? string.Empty : dr["AssetTag2"].ToString();
  115                     newAsset.MasterAssetID = dr["MasterAssetID"] == DBNull.Value ? -1 : (int)dr["MasterAssetID"];
  116                     newAsset.LifeYears = dr["LifeYears"] == DBNull.Value ? 0 : double.Parse(dr["LifeYears"].ToString());
  117                     newAsset.SalesOrder = dr["SalesOrder"] == DBNull.Value ? string.Empty : dr["SalesOrder"].ToString();
  118                     newAsset.SalvageValue = dr["SalvageValue"] == DBNull.Value ? 0 : double.Parse(dr["SalvageValue"].ToString());
  119                     newAsset.AdditionalInfo = dr["AdditionalInfo"] == DBNull.Value ? string.Empty : dr["AdditionalInfo"].ToString();
  120                     
  121                     this.Add(newAsset);
  122                 }
  123             }
  124         }
  125  
  126         public void Search(string criteria, string sortexpression)
  127         {    
  128             System.Text.StringBuilder strSQL = new System.Text.StringBuilder();
  129  
  130             strSQL.Append("SELECT * FROM Assets");
  131             strSQL.Append(" INNER JOIN Products ON Assets.ProductID = Products.ProductID ");
  132             strSQL.Append(" INNER JOIN Manufacturers ON Products.ManufacturerID = Manufacturers.ManufacturerID ");
  133  
  134             if(criteria.IndexOf("ScheduledMaintenance") > 0)
  135                 strSQL.Append(" INNER JOIN ScheduledMaintenance ON ScheduledMaintenance.AssetID = Assets.AssetID ");
  136  
  137             if(criteria.Length > 0)
  138                 strSQL.Append(criteria);
  139  
  140             if(sortexpression.Length > 0)
  141                 strSQL.AppendFormat(" ORDER BY {0}", sortexpression);
  142  
  143             using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, CommandType.Text, strSQL.ToString()))
  144             {
  145                 while(dr.Read())
  146                 {
  147                     Asset a = new Asset(_connectstring);
  148  
  149                     a.AssetID = dr["AssetID"] == DBNull.Value ? -1 : (int)dr["AssetID"];
  150                     a.ProductID = dr["ProductID"] == DBNull.Value ? -1 : (int)dr["ProductID"];
  151                     a.AssetStatusID = dr["AssetStatusID"] == DBNull.Value ? -1 : (int)dr["AssetStatusID"];
  152                     a.DepartmentID = dr["DepartmentID"] == DBNull.Value ? -1 : (int)dr["DepartmentID"];
  153                     a.ResourceID = dr["ResourceID"] == DBNull.Value ? -1 : (int)dr["ResourceID"];
  154                     a.OwnerCompanyID = dr["OwnerCompanyID"] == DBNull.Value ? -1 : (int)dr["OwnerCompanyID"];
  155                     a.SiteID = dr["SiteID"] == DBNull.Value ? -1 : (int)dr["SiteID"];
  156                     a.LocationID = dr["LocationID"] == DBNull.Value ? -1 : (int)dr["LocationID"];
  157                     a.RoomID = dr["RoomID"] == DBNull.Value ? -1 : (int)dr["RoomID"];
  158                     a.AcquisitionMethodID = dr["AcquisitionMethodID"] == DBNull.Value ? -1 : (int)dr["AcquisitionMethodID"];
  159                     a.PurchaseOrderID = dr["PurchaseOrderID"] == DBNull.Value ? -1 : (int)dr["PurchaseOrderID"];
  160                     a.PurchaseOrderLine = dr["PurchaseOrderLine"] == DBNull.Value ? -1 : (int)dr["PurchaseOrderLine"];
  161                     a.PurchaseUnitPrice = dr["PurchaseUnitPrice"] == DBNull.Value ? 0 : double.Parse(dr["PurchaseUnitPrice"].ToString());
  162                     a.PurchaseDate = dr["PurchaseDate"] == DBNull.Value ? string.Empty : DateTime.Parse(dr["PurchaseDate"].ToString()).ToShortDateString();
  163                     a.DateReceived = dr["DateReceived"] == DBNull.Value ? string.Empty : DateTime.Parse(dr["DateReceived"].ToString()).ToShortDateString();
  164                     a.SerialNumber = dr["SerialNumber"] == DBNull.Value ? "" : dr["SerialNumber"].ToString();
  165                     a.AssetTag = dr["AssetTag"] == DBNull.Value ? string.Empty : dr["AssetTag"].ToString();
  166                     a.AssetTag2 = dr["AssetTag2"] == DBNull.Value ? string.Empty : dr["AssetTag2"].ToString();
  167                     a.MasterAssetID = dr["MasterAssetID"] == DBNull.Value ? -1 : (int)dr["MasterAssetID"];
  168                     a.LifeYears = dr["LifeYears"] == DBNull.Value ? 0 : double.Parse(dr["LifeYears"].ToString());
  169                     a.SalesOrder = dr["SalesOrder"] == DBNull.Value ? string.Empty : dr["SalesOrder"].ToString();
  170                     a.SalvageValue = dr["SalvageValue"] == DBNull.Value ? 0 : double.Parse(dr["SalvageValue"].ToString());
  171                     a.AdditionalInfo = dr["AdditionalInfo"] == DBNull.Value ? string.Empty : dr["AdditionalInfo"].ToString();
  172                     
  173                     this.Add(a);
  174                 }
  175             }
  176         }
  177  
  178         public void GetChildAssets(int masterassetid)
  179         {
  180             System.Text.StringBuilder strSQL = new System.Text.StringBuilder();
  181  
  182             strSQL.AppendFormat("SELECT * FROM Assets WHERE MasterAssetID={0}", masterassetid.ToString());
  183  
  184             using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, CommandType.Text, strSQL.ToString()))
  185             {
  186                 while(dr.Read())
  187                 {
  188                     Asset a = new Asset(_connectstring);
  189  
  190                     a.AssetID = dr["AssetID"] == DBNull.Value ? -1 : (int)dr["AssetID"];
  191                     a.ProductID = dr["ProductID"] == DBNull.Value ? -1 : (int)dr["ProductID"];
  192                     a.AssetStatusID = dr["AssetStatusID"] == DBNull.Value ? -1 : (int)dr["AssetStatusID"];
  193                     a.DepartmentID = dr["DepartmentID"] == DBNull.Value ? -1 : (int)dr["DepartmentID"];
  194                     a.ResourceID = dr["ResourceID"] == DBNull.Value ? -1 : (int)dr["ResourceID"];
  195                     a.OwnerCompanyID = dr["OwnerCompanyID"] == DBNull.Value ? -1 : (int)dr["OwnerCompanyID"];
  196                     a.SiteID = dr["SiteID"] == DBNull.Value ? -1 : (int)dr["SiteID"];
  197                     a.LocationID = dr["LocationID"] == DBNull.Value ? -1 : (int)dr["LocationID"];
  198                     a.RoomID = dr["RoomID"] == DBNull.Value ? -1 : (int)dr["RoomID"];
  199                     a.AcquisitionMethodID = dr["AcquisitionMethodID"] == DBNull.Value ? -1 : (int)dr["AcquisitionMethodID"];
  200                     a.PurchaseOrderID = dr["PurchaseOrderID"] == DBNull.Value ? -1 : (int)dr["PurchaseOrderID"];
  201                     a.PurchaseOrderLine = dr["PurchaseOrderLine"] == DBNull.Value ? -1 : (int)dr["PurchaseOrderLine"];
  202                     a.PurchaseUnitPrice = dr["PurchaseUnitPrice"] == DBNull.Value ? 0 : double.Parse(dr["PurchaseUnitPrice"].ToString());
  203                     a.PurchaseDate = dr["PurchaseDate"] == DBNull.Value ? string.Empty : DateTime.Parse(dr["PurchaseDate"].ToString()).ToShortDateString();
  204                     a.DateReceived = dr["DateReceived"] == DBNull.Value ? string.Empty : DateTime.Parse(dr["DateReceived"].ToString()).ToShortDateString();
  205                     a.SerialNumber = dr["SerialNumber"] == DBNull.Value ? "" : dr["SerialNumber"].ToString();
  206                     a.AssetTag = dr["AssetTag"] == DBNull.Value ? string.Empty : dr["AssetTag"].ToString();
  207                     a.AssetTag2 = dr["AssetTag2"] == DBNull.Value ? string.Empty : dr["AssetTag2"].ToString();
  208                     a.MasterAssetID = dr["MasterAssetID"] == DBNull.Value ? -1 : (int)dr["MasterAssetID"];
  209                     a.LifeYears = dr["LifeYears"] == DBNull.Value ? 0 : double.Parse(dr["LifeYears"].ToString());
  210                     a.SalesOrder = dr["SalesOrder"] == DBNull.Value ? string.Empty : dr["SalesOrder"].ToString();
  211                     a.SalvageValue = dr["SalvageValue"] == DBNull.Value ? 0 : double.Parse(dr["SalvageValue"].ToString());
  212                     a.AdditionalInfo = dr["AdditionalInfo"] == DBNull.Value ? string.Empty : dr["AdditionalInfo"].ToString();
  213                     
  214                     this.Add(a);
  215                 }
  216             }
  217         }
  218     }
  219 }