DataLayer

I l @ ve RuBoard

Because the goal is to poke around APS.NET, the data model is short and simple. Two tables will be constructed ”Department and Employee. The table layouts appear in Figure 4.2.1.

Figure 4.2.1. The Stingray database tables.

Custom attributes were briefly discussed earlier, but now is a good time to make full use of them. The data layer will define two attributes, a DBTableAttribute and a DBFieldAttribute . These attributes will contain the necessary information to invoke stored procedures in a fairly generic manner. The Reflection API can be used to locate attributes and properties of an object. Thus, our custom attributes can be located and the necessary information acquired .

A class will model every entity in the database. These classes will derive from a common abstract base class ” DBEntity . DBEntity defines two properties ” IsDirty and IsNew ”along with the abstract method FromDataRow . The IsDirty property will be used to determine if the DBEntity needs to be updated in the database. The IsNew property is used to determine whether to use an update or insert. The abstract method FromDataRow is used to construct the DBEntity from a DataRow element from a DataSet object. Listing 4.2.1 contains the DBEntity class.

Listing 4.2.1 The DBEntity Abstract Base Class

 1: ///<summary>  2: ///DBEntity defines a basic database entity and serves as the  3: ///base class of all entities with a database  4: ///</summary>  5:  6:  7: namespace Stingray.Data  8: {  9:     using System; 10:     using System.Data; 11: 12: 13: 14:     /// <summary> 15:     /// The DBEntity abstract base class 16:     /// </summary> 17:     public abstract class DBEntity { 18: 19:         private bool m_bIsDirty = false; 20:         private bool m_bIsNew = true; 21: 22:             public virtual bool IsDirty { 23:                 get {  return m_bIsDirty; } 24:                 set {  m_bIsDirty = value;} 25:             } 26: 27:             public virtual bool IsNew { 28:                 get {  return m_bIsNew; } 29:                 set {  m_bIsNew = value; } 30:             } 31: 32:             public abstract bool FromDataRow( System.Data.DataRow data ); 33:     } 34: 35: } 

DBEntity provides common implementation for the IsNew and IsDirty properties. For the purposes of this example, these default implementations will suffice.

The next order of business is to create the custom attribute classes DBTableAttribute and DBFieldAttribute . DBTableAttribute will contain the table name, the insert stored procedure name, and the update stored procedure name . DBFieldAttribute defines the parameter name, data type, and length for the data if necessary. These attributes will be applied to each object that represents an entity within the database. Making use of the Reflection API, these attributes will be used to handle tasks , such as inserting and updating these entities. Listing 4.2.2 provides the implementation of both DBTableAttribute and DBFieldAttribute classes.

Listing 4.2.2 Custom Attribute Classes

 1: namespace Stingray.Data  2: {  3:     using System;  4:     using System.Data;  5:     using System.Data.SqlClient;  6:     using System.Data.SqlTypes;  7:  8:  9: 10:     ///<summary> 11:     ///The class DBTableAttribute defines the table within which 12:     ///a particular enity resides.  In addition, this attribute 13:     ///also contains properties for the SQL Insert and Update stored 14:     ///procedures used by the enity 15:     ///</summary> 16:     [System.AttributeUsage(AttributeTargets.Class)] 17:     public class DBTableAttribute : System.Attribute { 18: 19:         /*****[Fields ]*****/ 20: 21:         private string    m_TableName; 22:         private string    m_SPInsertCommand; 23:         private string    m_SPUpdateCommand; 24: 25: 26:         /*****[Properties ]*****/ 27: 28:         public string TableName { 29:             get {  return m_TableName; } 30:             set {  m_TableName = value; } 31:         } 32: 33:         public string InsertCommand { 34:             get {  return m_SPInsertCommand; } 35:             set {  m_SPInsertCommand = value; } 36:         } 37: 38:         public string UpdateCommand { 39:             get {  return m_SPUpdateCommand; } 40:             set {  m_SPUpdateCommand = value; } 41:         } 42: 43:         /*****[Constructor(s)]*****/ 44:         public DBTableAttribute( string TableName ) { 45:             m_TableName = TableName; 46:         } 47:     } 48: 49: 50: 51:     ///<summary> 52:     ///The DBFieldAttribute is used to map class properties 53:     ///onto SQL Stored procedure parameters. 54:     ///</summary> 55:     [System.AttributeUsage(AttributeTargets.Property)] 56:     public class DBFieldAttribute : System.Attribute { 57: 58:         /*****[Fields]*****/ 59: 60:         private string        m_ParamName; 61:         private SqlDbType     m_DataType; 62:         private int        m_Length = 0; 63: 64: 65:         /*****[Properties]*****/ 66:         public string ParameterName { 67:             get {  return m_ParamName; } 68:             set {  m_ParamName = value; } 69:         } 70: 71:         public SqlDbType DataType { 72:             get {  return m_DataType; } 73:             set {  m_DataType = value; } 74:         } 75: 76:         public int Length { 77:             get {  return m_Length; } 78:             set {  m_Length = value; } 79:         } 80: 81:         /*****[Constructor(s)]*****/ 82:         public DBFieldAttribute( string ParameterName ) { 83:              m_ParamName = ParameterName; 84:         } 85:     } 86: 87: } 

The first thing to notice about each attribute is the attribute to which it is applied. The DBTableAttribute is attributed with the AttributeUsage attribute. Because the DBTableAttribute should only be used on a class, the AttributeTargets enum is used to indicate that. The AttributeUsage allows control over how attributes are used and applied. The AttributeTargets enum contains the following defined values:

 public enum AttributeTargets {     All,     Assembly,     Class,     Constructor, Delegate, Enum, Event, Field, Interface, Method, Module, Parameter, Property, ReturnValue, Struct } 

As far as the implementation of an attribute, there really is not much to it. The System.Attribute serves as the base class from which a user -defined attribute class can be derived. As with any user-defined class, the details of its usage and its implementation will depend on the purpose the new attribute should serve.

With DBEntity , DBTableAttribute , and DBFieldAttribute in place, the generic DBAccess class can be constructed. DBAccess will make use of the Reflection API to insert or update any DBEntity -derived class. DBAccess contains three static methods . The AcquireConnection is hard-coded to return a connection to the localhost database, along with necessary login information. Because this object is used on a constant basis, it could actually get the connection string from some configuration file and then be cached for later access. However, sometimes simple is better.

The DBAccess class also provides a public Save method. As its name suggests, this method is used to save any DBEntity -derived class. Listing 4.2.3 contains the source listing for the DBEntity class.

Listing 4.2.3 DBAccess

 1: namespace Stingray.Data   2: {   3:     using System;   4:     using System.Reflection;   5:     using System.Data;   6:     using System.Data.SqlClient;   7:   8:   9:     public class DBAccess  10:     {  11:  12:         /// <summary>  13:         /// Get an active connection to the stingray database  14:         /// </summary>  15:         public static SqlConnection AcquireConnection( ) {  16:             SqlConnection dbCon =  17:                    new SqlConnection(  18:                      "user id=sa;password=;initial catalog=Stingray; data  source=.;Connect Timeout=30"  19:                                     );  20:             try {  21:                 dbCon.Open( );  22:             }  catch( Exception ) {  23:                 dbCon.Dispose( );  24:                 dbCon = null;  25:             }  26:  27:             return dbCon;  28:         }  29:  30:  31:         /// <summary>  32:         /// Save a DBEntity into the Stingray Database  33:         /// </summary>  34:         /// <param name="ActiveConnection"> Active Database Connection</param>  35:         /// <param name="entity"> The Entity to be saved</param>  36:         public static bool Save( SqlConnection ActiveConnection, DBEntity entity ) {  37:  38:             //Is there anyting to save?  39:             if( !entity.IsDirty )  40:                 return true;  41:  42:             object[] Attributes = entity.GetType( ).GetCustomAttributes(  43:                             typeof(Stingray.Data.DBTableAttribute),  44:                                       false  45:                                                                        );  46:  47:             if(Attributes.Length != 1 )  48:                 return false;  49:  50:             System.Data.SqlClient.SqlCommand SqlCmd = new SqlCommand( );  51:             SqlCmd.Connection = ActiveConnection;  52:             SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;  53:  54:             ///  55:             ///Do we insert or Update?  56:             ///  57:             DBTableAttribute TableAttribute = (DBTableAttribute)Attributes[0];  58:  59:             if( entity.IsNew ) {  60:                 SqlCmd.CommandText = TableAttribute.InsertCommand;  61:             }  else {  62:                 SqlCmd.CommandText = TableAttribute.UpdateCommand;  63:             }  64:  65:             AddCmdParam( SqlCmd, entity );  66:  67:             try {  68:  69:                 SqlCmd.ExecuteNonQuery( );  70:                 return true;  71:  72:             }  catch( Exception ) {  73:                 //Do something for Heavens sake!!  74:             }  75:  76:             //Should not make it to here unless it go BOOM!  77:             return false;  78:         }  79:  80:         /// <summary>  81:         /// Create the SQLParameter(s) for the DBEnity object  82:         /// </summary>  83:         /// <param name="cmd"> The SqlCommand object to  add the Prameters to</param>  84:         /// <param name="entity">The DBEnity to scrape </param>  85:         protected static void AddCmdParam( SqlCommand SqlCmd, DBEntity entity ) {  86:  87:             ///  88:             ///Get the Public properties and create the SQL Parameters  89:             ///  90:             Type T = entity.GetType( );  91:             PropertyInfo[] Properties =  92:                     T.GetProperties(  93:                        BindingFlags.DeclaredOnly  BindingFlags.Instance  BindingFlags.Public  94:                                    );  95:  96:              foreach( PropertyInfo pi in Properties) {  97:                  object[] Attributes =  98:                             pi.GetCustomAttributes(  99:                                  typeof(Stingray.Data.DBFieldAttribute), false 100:                                               ); 101:                  if(Attributes.Length == 1) { 102:                      DBFieldAttribute Field = (DBFieldAttribute)Attributes[0]; 103:                      MethodInfo mi = pi.GetGetMethod( false ); 104: 105:                      object result = mi.Invoke( entity, null ); 106: 107:                      SqlCmd.Parameters.Add( Field.ParameterName,  Field.DataType  ); 108:                      SqlCmd.Parameters[ Field.ParameterName ].Value = result; 109: 110:                      if( Field.Length > 0 ) 111:                          SqlCmd.Parameters[ Field.ParameterName ].Size = Field.Length;  112:                  } 113: 114:             } 115: 116:         } 117:     } 118: } 

DBAccess deserves some explanation as to what it is doing. It begins by looking for the custom attribute DBTableAttribute . If found, the process of accessing the necessary properties for serialization begins. The method AddCmdParam grabs the current instance properties and looks for the custom DBFieldAttribute . Remember that the DBFieldAttribute contains properties for the parameter name, data type, and length of the parameter if applicable .

The reason for designing the data layer in such a way is merely to experiment with custom attributes and the Reflection API. I'm not suggesting this as a model for all future database development, but I'm sure you'll find a use for this type of implementation.

I l @ ve RuBoard