The business object manager is responsible for managing the relationship of data between the business objects and the underlying database. This includes filling the business objects from records within the database as well as inserting, updating, and deleting database records based on values within the business objects.
Creating a business object manager can significantly accelerate application development. Rather than implementing specialized code for every business object that selects, inserts , updates, deletes, and validates data, you can create one utility that manages all of these interactions consistently. The tradeoff , however, is flexibility. For a business object to be properly managed, it must adhere to specific naming requirements that allow the reflection code to function properly.
The SelectOne method loads a single identified business object from the database using a dynamically specified stored procedure. Listing 2-7 implements the SelectOne method, which populates a single business object with values stored in a specific database record.
public bool SelectOne( BusinessObject objSource, int intObjectID ) { bool boolStatus = false; string strObject; string strStoredProc; SqlParameter parameter; SqlCommand command; try { //get the object name Type objType = objSource.GetType(); strObject = objType.FullName.Substring( objType.FullName.IndexOf(".")+1); //get the stored procedure name strStoredProc = "app_"; strStoredProc += strObject; strStoredProc += "Select"; //initialize the command command = new SqlCommand( strStoredProc, dataComponent.Connection ); command.CommandType = CommandType.StoredProcedure; //add the ID parameter parameter = new SqlParameter( "@" + strObject + "ID", SqlDbType.Int ); parameter.Direction = ParameterDirection.Input; parameter.Value = intObjectID; command.Parameters.Add( parameter ); //open the connection and execute query dataComponent.Connection.Open(); SqlDataReader reader = command.ExecuteReader(); if( reader.Read() ) { //examine results and set business object, set return code to true for( int intIndex = 0; intIndex < reader.FieldCount; intIndex++ ) { string strColName = reader.GetName( intIndex ); PropertyInfo field = objType.GetProperty( strColName ); field.SetValue( objSource, reader.GetValue( intIndex ), null ); } boolStatus = true; } } catch( Exception exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } finally { dataComponent.Connection.Close(); } return boolStatus; }
This method populates a single business object that must derive from the BusinessObject abstract class. Let's assume that this method populates an Issue business object. The method begins by receiving an Issue object and an Issue ID that identifies the specific row in the Dat_Issue table. The Type object captures the named object type, which is trimmed down to the specific class name of Issue . Next, the method dynamically creates the stored procedure name to look like app_IssueSelect . Framework standards dictate that a stored procedure with a matching name must exist. Next, a local SqlCommand object initializes with the name of the stored procedure and a valid SqlConnection. The CommandType is set to expect a stored procedure. Next, because only a single Issue object is expected, a SqlParameter object is defined. Again, standards require the parameter be named @IssueID and be of type Int. The parameter is set to the ID originally supplied to the method. The connection opens, the query executes, and a SqlDataReader receives the results. Next, each column of the resulting row is examined with the GetName method. The reflection FieldInfo object peeks into the passed Issue business object to seek out a property that matches the column name. Again, standards dictate that the business object has data properties that match the corresponding table's columns . Next, the FieldInfo object's SetValue method sets the appropriate business object property to the corresponding column data. This process repeats for each column of the resulting row, returning a success or fail status.
The SelectAll method loads all database records that apply to a specified business object type. This is typically for list pages within the application. The stored procedure looks nearly identical. When creating the script using the Data Adapter Configuration Wizard, all criteria fields are left empty and the new SELECT stored procedure is app_IssueSelectAll. Listing 2-8 implements the SelectAll method to populate a collection of business objects with values stored in the database.
public bool SelectAll( BusinessObjectCollection objSource ) { bool boolStatus = false; string strObject; string strStoredProc; SqlParameter parameter; SqlCommand command; try { //get the object name Type objType = objSource.GetType(); strObject = objType.FullName.Substring( objType.FullName.IndexOf(".")+1); strObject = strObject.Replace( "Collection", "" ); //get the stored procedure name strStoredProc = "app_"; strStoredProc += strObject; strStoredProc += "SelectAll"; //initialize the command command = new SqlCommand( strStoredProc, dataComponent.Connection ); command.CommandType = CommandType.StoredProcedure; //open the connection and execute query dataComponent.Connection.Open(); SqlDataReader reader = command.ExecuteReader(); while( reader.Read() ) { BusinessObject newObject = objSource.New(); objType = newObject.GetType(); for( int intIndex = 0; intIndex < reader.FieldCount; intIndex++ ) { string strColName = reader.GetName( intIndex ); PropertyInfo field = objType.GetProperty( strColName ); field.SetValue( objSource, reader.GetValue( intIndex ), null ); } objSource.Add( newObject ); } boolStatus = true; } catch( Exception exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } finally { dataComponent.Connection.Close(); } return boolStatus; }
The SelectAll method functions nearly identically to the SelectOne method. There are only a few subtle differences. The object being passed into the method is an empty business object collection. The stored procedure requires a different name and no parameters. After the query executes, the SqlDataReader object expects multiple rows of data. For each row, the business object collection needs to provide a new business object appropriate to its container type by invoking its New method. The remaining code functions almost the same way as the SelectOne method by applying reflection to determine and fill the business object properties.
The Insert method performs the opposite task from the SelectOne method. Its purpose is to take a filled business object and insert a new row into the database. Listing 2-9 shows how to use reflection to take values stored in a business object, map them into stored procedure parameters, and insert those values into the database.
public bool Insert( BusinessObject objSource ) { bool boolStatus = false; string strObject; string strStoredProc; SqlParameter parameter; SqlCommand command; try { //get the object name Type objType = objSource.GetType(); strObject = objType.FullName.Substring( objType.FullName.IndexOf(".")+1); //get the stored procedure name strStoredProc = "app_"; strStoredProc += strObject; strStoredProc += "Insert"; //initialize the command command = new SqlCommand( strStoredProc, dataComponent.Connection ); command.CommandType = CommandType.StoredProcedure; //add the parameters parameter = new SqlParameter( "@RETURN_VALUE", SqlDbType.Int ); parameter.Direction = ParameterDirection.ReturnValue; command.Parameters.Add( parameter ); PropertyInfo[] fields = objType.GetProperties(); for( int intIndex = 0; intIndex < fields.Length; intIndex++ ) { parameter = new SqlParameter( "@" + fields[intIndex].Name, fields[intIndex].PropertyType ); parameter.Direction = ParameterDirection.Input; parameter.Value = fields[intIndex].GetValue( objSource, null ); command.Parameters.Add( parameter ); } //open the connection and execute query dataComponent.Connection.Open(); command.ExecuteNonQuery(); //return the results of the procedure if( (Int32)command.Parameters["@RETURN_VALUE"].Value == 0 ) boolStatus = true; } catch( SqlException exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } catch( Exception exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } finally { dataComponent.Connection.Close(); } return boolStatus; }
The method begins by determining the incoming object type and creating an appropriate stored procedure name. Next, the method creates a stored procedure parameter named @RETURN_VALUE and adds it to the SqlCommand object. Then, a series of dynamically generated SqlParameter objects follow. You create each SqlParameter object by dissecting the passed business object using the reflection services. First, the business object's GetFields method returns an array of FieldInfo objects that represent all of the business object properties. Second, that array of fields iterates to dynamically create stored procedure parameters based on the property name, data type, and value. This adds all the created SqlParameter objects to the SqlCommand object. The query executes, and the resulting value evaluates to determine if the operation was successful.
The Update method also receives a filled business object and attempts to update its corresponding row in the database (see Listing 2-10).
public bool Update( BusinessObject objSource ) { bool boolStatus = false; string strObject; string strStoredProc; SqlParameter parameter; SqlCommand command; try { //get the object name Type objType = objSource.GetType(); strObject = objType.FullName.Substring( objType.FullName.IndexOf(".")+1 ); //get the stored procedure name strStoredProc = "app_"; strStoredProc += strObject; strStoredProc += "Update"; //initialize the command command = new SqlCommand( strStoredProc, dataComponent.Connection ); command.CommandType = CommandType.StoredProcedure; //add the parameters parameter = new SqlParameter( "@RETURN_VALUE", SqlDbType.Int ); parameter.Direction = ParameterDirection.ReturnValue; command.Parameters.Add( parameter ); //add the original id parameter parameter = new SqlParameter( "@Original_" + strObject + "ID", SqlDbType.Int ); parameter.Direction = ParameterDirection.Input; FieldInfo field = objType.GetField( strObject + "ID" ); parameter.Value = (int)field.GetValue( objSource ); command.Parameters.Add( parameter ); //original modified date parameter for concurrency check parameter = new SqlParameter( "@Original_ModifiedDate", SqlDbType.DateTime ); parameter.Direction = ParameterDirection.Input; field = objType.GetField( "RowModified" ); parameter.Value = (int)field.GetValue( objSource ); command.Parameters.Add( parameter ); //update the modified date objSource.RowModified = DateTime.Now; PropertyInfo[] fields = objType.GetProperties(); for( int intIndex = 0; intIndex < fields.Length; intIndex++ ) { parameter = new SqlParameter( "@" + fields[intIndex].Name, fields[intIndex].PropertyType ); parameter.Direction = ParameterDirection.Input; parameter.Value = fields[intIndex].GetValue( objSource, null ); command.Parameters.Add( parameter ); } //open the connection and execute query dataComponent.Connection.Open(); command.ExecuteNonQuery(); //return the results of the procedure if( (Int32)command.Parameters["@RETURN_VALUE"].Value == 0 ) boolStatus = true; } catch( SqlException exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } catch( Exception exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } finally { dataComponent.Connection.Close(); } return boolStatus; }
The Update method functions almost exactly as the Insert method. Aside from a different stored procedure name, the only other difference is an additional SqlParameter that supplies the original business object identifier. The update method also checks for data concurrency. Each row in the database is time stamped. Before updating any row in the database, the time stamp is checked to see if the values within the User object are out of date. If the object data is out of date, an exception is thrown and the object data is not committed.
The Delete method accepts a filled business object and removes its corresponding row from the database. It is common for enterprise applications to not literally delete rows from a database because there are typically many crossdependencies between tables. Rather, it is common for each entity data table to include an IsDeleted column or something similar. When an entity is marked for deletion, a stored procedure that looks more like app_IssueUpdate updates this value from false to true. Listing 2-11 shows how to delete a database record based on values stored in a business object.
public bool Delete( BusinessObject objSource ) { bool boolStatus = false; string strObject; string strStoredProc; SqlParameter parameter; SqlCommand command; try { //get the object name Type objType = objSource.GetType(); strObject = objType.FullName.Substring( objType.FullName.IndexOf(".")+1); //get the stored procedure name strStoredProc = "app_"; strStoredProc += strObject; strStoredProc += "Delete"; //initialize the command command = new SqlCommand( strStoredProc, dataComponent.Connection ); command.CommandType = CommandType.StoredProcedure; //add the parameters parameter = new SqlParameter( "@RETURN_VALUE", SqlDbType.Int ); parameter.Direction = ParameterDirection.ReturnValue; command.Parameters.Add( parameter ); //add the ID parameter parameter = new SqlParameter( "@Original_" + strObject + "ID", SqlDbType.Int ); parameter.Direction = ParameterDirection.Input; PropertyInfo field = objType.GetProperty( strObject + "ID" ); parameter.Value = (int)field.GetValue( objSource, null ); command.Parameters.Add( parameter ); //original modified date parameter for concurrency check sqlParameter = new SqlParameter( "@Original_ModifiedDate", SqlDbType.DateTime ); sqlParameter.Direction = ParameterDirection.Input; field = objType.GetField( "RowModified" ); sqlParameter.Value = (int)field.GetValue( objSource ); sqlCommand.Parameters.Add( sqlParameter ); //update the modified date objSource.RowModified = DateTime.Now; //open the connection and execute query dataComponent.Connection.Open(); command.ExecuteNonQuery(); //return the results of the procedure if( (Int32)command.Parameters["@RETURN_VALUE"].Value == 0 ) boolStatus = true; } catch( SqlException exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } catch( Exception exception ) { EventLog systemLog = new EventLog(); systemLog.Source = "IssueTracker"; systemLog.WriteEntry( exception.Message, EventLogEntryType.Error, 0 ); } finally { dataComponent.Connection.Close(); } return boolStatus; }
The Delete method functions almost exactly like the Update method does. Instead of the full parameter list, it requires only the return value and unique row identifier. The query executes and the resulting parameter determines whether the operation was successful. Concurrency rules are applied to the Delete method just in case a user decides to delete a record based on stale data.