CodeBetter.Com
Sign in | Join | Help
in Search
CodeBetter.Com
RSS 2.0 via Feedburner
           Do you Twitter? Follow us @CodeBetter

Eric Wise

Business & .NET

November 2004 - Posts

  • Server Side Spam Filter (Windows 2003 server, pop3 service) suggestions?

    Looking for a free or inexpensive server side spam filter for my windows 2003 server that uses the built-in pop3 service.  Any suggestions?
    Posted Nov 22 2004, 08:12 AM by Eric Wise with 2 comment(s)

  • Want a Job? (Cleveland Area)

    Risetime (the company I currently work for) is hiring.

    We've been having a heck of a time finding qualified applicants in the area.  So if you've ever thought about living in the most important election state (ohio!) you should definately drop me a resume.  This is a good company to work for both salary and benefits and the management team is real good about letting you manage your own time and projects as long as you hit your deadlines.  We're currently doing work for one of the Fortune 10 and need additional staff ASAP.

    Plus you get to work with me, which is the greatest benefit anyone can offer of course.  *rolls eyes*

    Skills desired:

    VB.NET / C# .NET (we code in vb.net almost exclusively)

    SQL Server 2000

    ASP .NET specific experience is a plus.

    Solid grasp of OO concepts

     

    Email me your resume if interested: eric.wise at risetime dot com

    Posted Nov 10 2004, 10:55 AM by Eric Wise with 3 comment(s)

  • 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..

    1. 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.
    2. 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 }

     

    So now you know how all three types of table objects work in Easy Assets .NET!

    Posted Nov 06 2004, 03:48 PM by Eric Wise with no comments

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

    Continuation of part 3a

    Code sample 2: Lookup Key Table (locations)

    Lookup Key tables are very similar to Lookup Tables except that when you access them, you normally pass in a foreign key from a parent table.  That is to say the data in the table is 99.9% of the time only useful as a subset.  In the last post, I mentioned how 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.  The next level, which I will post today, is a location.  If the high school is the site, then locations within that site could be the Gymnasium, Auditorium, Administrative building, English building, etc.

    First we have to build our locations 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 Locations.

       11     /// </summary>

       12     public class Locations : CollectionBase

       13     {

       14         private string _connectstring;

       15  

       16         public Locations(string connectstring)

       17         {

       18             _connectstring = connectstring;

       19         }

       20  

       21         public int Add(Location location)

       22         {

       23             return List.Add(location);

       24         }

       25  

       26         public void Remove(Location location)

       27         {

       28             List.Remove(location);

       29         }

       30  

       31         public Location this[int index]

       32         {

       33             get

       34             {

       35                 return((Location)List[index]);

       36             }

       37             set

       38             {

       39                 List[index] = value;

       40             }

       41         }

       42  

       43         public void GetList(int siteid)

       44         {

       45             SqlParameter[] sqlparams = new SqlParameter[1];

       46  

       47             SqlParameter param = new SqlParameter("@SiteID", SqlDbType.Int);

       48             param.Value = siteid;

       49             sqlparams[0] = param;

       50  

       51             using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, "ea_location_getlist", sqlparams))

       52             {

       53                 while(dr.Read())

       54                 {

       55                     Location l = new Location();

       56                     l.LocationID = (int)dr["LocationID"];

       57                     l.LocationDescription = dr["LocationDescription"].ToString();

       58                     l.SiteID = siteid;

       59                     this.Add(l);

       60                 }

       61             }

       62         }

       63     }

       64 }

    Notice that the only real difference between this collection and the sites collection is that GetList() takes a parameter of site id.  This of course could be overloaded with a GetList() with no parameters if I ever needed to load all locations regardless of site.

    The location object should be nothing new at this point, but I'll post it anyway.

        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 Location.

       10     /// </summary>

       11     public class Location

       12     {

       13         #region private members

       14  

       15         //property accessors

       16         private int _locationid = -1;

       17         private int _siteid;

       18         private string _locationdescription;

       19  

       20         //Database key

       21         private string _connectstring;

       22  

       23         #endregion private members

       24  

       25         #region Properties

       26  

       27         public int LocationID

       28         {

       29             get

       30             {

       31                 return _locationid;

       32             }

       33             set

       34             {

       35                 _locationid = value;

       36             }

       37         }

       38  

       39         public int SiteID

       40         {

       41             get

       42             {

       43                 return _siteid;

       44             }

       45             set

       46             {

       47                 _siteid = value;

       48             }

       49         }

       50  

       51         public string LocationDescription

       52         {

       53             get

       54             {

       55                 return _locationdescription;

       56             }

       57             set

       58             {

       59                 _locationdescription = value;

       60             }

       61         }

       62  

       63         #endregion Properties

       64  

       65         public Location()

       66         {

       67  

       68         }

       69  

       70         /// <summary>

       71         /// Creates a new location

       72         /// </summary>

       73         /// <param name="connectstring">database connection string</param>

       74         public Location(string connectstring)

       75         {

       76             _connectstring = connectstring;

       77         }

       78  

       79         /// <summary>

       80         /// Loads a location by id

       81         /// </summary>

       82         /// <param name="id">pk</param>

       83         /// <param name="connectstring">database connection string</param>

       84         public Location(int id, string connectstring)

       85         {

       86             _connectstring = connectstring;

       87  

       88             SqlParameter[] sqlparams = new SqlParameter[1];

       89  

       90             SqlParameter param = new SqlParameter("@LocationID", SqlDbType.Int);

       91             param.Value = id;

       92             sqlparams[0] = param;

       93  

       94             using(SqlDataReader dr = SqlHelper.ExecuteReader(_connectstring, "ea_location_get", sqlparams))

       95             {

       96                 if(dr.Read())

       97                 {

       98                     _locationid = dr["LocationID"] == DBNull.Value ? -1 : (int)dr["LocationID"];

       99                     _siteid = dr["SiteID"] == DBNull.Value ? -1 : (int)dr["SiteID"];

      100                     _locationdescription = dr["LocationDescription"] == DBNull.Value ? "" : dr["LocationDescription"].ToString();

      101                 }

      102                 else

      103                     throw new Exception("Location ID not found!");

      104             }

      105         }

      106  

      107         /// <summary>

      108         /// Does insert if key is -1 (default)

      109         /// Updates if key is not default.

      110         /// </summary>

      111         public void Save()

      112         {

      113             if(_locationid == -1)

      114             {

      115                 //Do Insert

      116                 SqlParameter[] sqlparams = new SqlParameter[2];

      117  

      118                 SqlParameter param = new SqlParameter("@LocationDescription", SqlDbType.NVarChar);

      119                 if(_locationdescription != "") param.Value = _locationdescription.Replace("'","''");

      120                 else param.Value = DBNull.Value;

      121                 sqlparams[0] = param;

      122  

      123                 param = new SqlParameter("@SiteID", SqlDbType.Int);

      124                 if(_siteid != -1) param.Value = _siteid;

      125                 else param.Value = DBNull.Value;

      126                 sqlparams[1] = param;

      127  

      128                 if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_location_insert", sqlparams) == -1)

      129                     throw new Exception("Location create failed!");

      130             }

      131             else

      132             {

      133                 //Do Update

      134                 SqlParameter[] sqlparams = new SqlParameter[2];

      135  

      136                 SqlParameter param = new SqlParameter("@LocationID", SqlDbType.Int);

      137                 if(_locationid != -1) param.Value = _locationid;

      138                 else param.Value = DBNull.Value;

      139                 sqlparams[0] = param;

      140  

      141                 param = new SqlParameter("@LocationDescription", SqlDbType.NVarChar);

      142                 if(_locationdescription != "") param.Value = _locationdescription.Replace("'","''");

      143                 else param.Value = DBNull.Value;

      144                 sqlparams[1] = param;

      145  

      146                 if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_location_update", sqlparams) == -1)

      147                     throw new Exception("Location update failed!");

      148             }

      149         }

      150  

      151         public void Delete()

      152         {

      153             SqlParameter[] sqlparams = new SqlParameter[1];

      154  

      155             SqlParameter param = new SqlParameter("@LocationID", SqlDbType.Int);

      156             param.Value = _locationid;

      157             sqlparams[0] = param;

      158  

      159             if(SqlHelper.ExecuteNonQuery(_connectstring, "ea_location_delete", sqlparams) == -1)

      160                 throw new Exception("Location delete failed!");

      161         }

      162     }

      163 }

    In part 3c, I will introduce you to an object that can search on multiple fields.

    Posted Nov 06 2004, 02:42 PM by Eric Wise with 1 comment(s)

  • 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...

    1.  I am tying this product to SQL server only
    2. 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.
    3. 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.
    4. 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:

    1. Lookup/List tables- These tables are used to populate lists, usually drop down.  In general, all values will be read from these tables.
    2. 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)
    3. 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 collect