Iron Speed Designer creates standard .NET web pages and code-behind files containing all of the code necessary to retrieve the data from the database, display, validate and save the data.  Your application code is easily customizable as often as you like.  You can either augment the code to perform additional actions, or even replace sections of the code with your own custom code.  Applications use a sub-classing technique that guarantees your custom code will never be overwritten if placed in the appropriate region within the code file.

Before you begin customizing an application, please review this section in detail including a description of the various classes in the code-behind files, the concept of a page lifecycle for ASP.NET web pages, the control hierarchy created by Iron Speed Designer, and the key methods that you can look for and override to change functionality of your application.

N-Tier Architecture Overview

Iron Speed Designer produces fully working, web-based applications, following the N-tier architecture favored by most enterprise-class application developers:

Iron Speed Designer creates ASPX web pages and ASCX controls.  The pages and controls use server-side code-behinds, which separates the Presentation Layer from the server-side application code.  The Business Layer contains table and record classes.  The Data Access Layer contains a variety of classes used to connect to the database.  The Database Layer contains all the SQL statements and queries needed for storing, retrieving, and filtering data from the database.  Specifically, SQL queries are created for selecting, adding, updating and deleting records into designated tables as well as to provide tabular reports.  Iron Speed Designer will even create complex queries including multi-table joins with one-to-many and many-to-many relationships.  Most of the SQL is packaged as a set of database stored procedures in order to provide the best execution performance with the fewest round-trips to the database.

In addition to the source code and components, Iron Speed Designer also creates various project files useful for customizing your application using Visual Studio .NET, and files to compile, configure and run your application.  These files include:

• A Web.config file with parameters adjusted for your application, including database and other server connection parameters.

Application Pages and Code Files

The Application Wizard in Iron Speed Designer collects basic information about an application including the name, the code language and the database, tables and views that you would like to use in your application.  Iron Speed Designer creates a variety of pages for each database table and view you select in the Application Wizard, giving you a starting point for your application.  Both the user interface and the business logic of these pages can be easily customized using Iron Speed Designer.

You can select which pages get created in the Application Wizard’s Pages step.  In general, two code files are created for each page.  The code-behind file for the page will have a .CS or .VB extension and resides in the same folder as the ASPX page.  If the page contains one or more tables or records, then a Controls.CS or .VB file is created.  The Controls.CS and .VB files are located in:

...\<App Name>\App_Code\{TableName}

Each code-behind file contains two regions.  The first region is created once and never overwritten.  This is called Section 1 and may also be referred to as the “safe” region of a file.  The second region may be updated during a rebuild and should not be modified.  This is called Section 2 and may also be referred to as the “gen” region of a file.

C#:

#region "Section 1: Place your customizations here."

     // Place your customizations in Section 1. Do not modify Section 2.

#endregion

#region "Section 2: Do not modify this section."

     // Everything in this section may be overwritten during a rebuild.

#endregion

Visual Basic .NET:

#Region "Section 1: Place your customizations here."

     ' Place your customizations in Section 1. Do not modify Section 2.

#End Region

#Region "Section 2: Do not modify this section."

     ' Everything in this section may be overwritten during a rebuild.

#End Region

Where to find pages and classes

Application Classes and Methods

Iron Speed Designer creates one or more classes to retrieve and display data on each page.  There are four types of classes that are created.  The page class is created in the code-behind file and the remaining three types of classes are created in the Controls.VB or .CS file.  Code can be easily customized without fear of it being overwritten by subsequent regeneration.  The classes and the key methods for each of the four class types are described below.

Page code-behind class

The page’s code-behind class is derived from the Microsoft .NET Framework’s page class.  This class contains event handlers and methods that load the data from the database, display the data in user interface controls and save the updated data back into the database.  The page class event handlers and methods call on some of the other classes to actually perform the specific task.  Most of the code customization should be performed in these other classes.

In the above example, an AddCustomers class is created in the AddCustomers.aspx.cs or .vb code-behind file.  A CustomersRecordControl class is also created in the AddCustomers.Controls.cs or .vb file to read and display the data from the database.  This class is described in more detail in later sections.

Microsoft .NET Framework does not provide the flexibility of sub-classing of page classes where the controls are defined at both the base class and the sub-class.  To provide the ability to customize application code, Iron Speed Designer creates a Base method for each of the methods.  For example, Iron Speed Designer creates LoadData and LoadData_Base methods.  LoadData calls LoadData_Base to perform the actual work.  LoadData can be customized, while LoadData_Base should not be modified.  LoadData can call perform additional work before or after the call to LoadData_Base, or the entire call to LoadData_Base can be replaced with custom code in LoadData.

The AddCustomers class is derived from the BaseApplicationPage, BasePage and the .NET Page classes.  The BaseApplicationPage class can be customized to make a common customization across all pages within an application.

Sample AddCustomers code-behind including methods that can be customized and those that should not be.

Some of the key methods created in the page class are:

• Page_Load: Calls Authorize to verify if user has access to the page, and then calls LoadData to load the data.

• LoadData and LoadData_Base: LoadData calls the LoadData_Base method which in turn calls each of the record and table controls to actually load the data for the respective controls.  The Base method (LoadData_Base) should not be modified, LoadData can be modified to perform additional tasks before or after the call to LoadData_Base or to simply replace the call.

Sample LoadData method that can be customized.  The default method calls LoadData_Base.

• Button_Click and Button_Click_Base: These methods handle the click event of a button and are named based on the name of the button.  For example, a button called SaveButton will have a SaveButton_Click and a SaveButton_Click_Base method created as part of the page class.  The SaveButton_Click calls the SaveButton_Button_Click method which in turn calls each of the record and table controls to actually save the data for the respective controls.  The Button_Click_Base method starts, commits and ends a transaction and all updating of data occurs within this one transaction.  Any errors including validation errors are reported by the lower level methods by throwing an exception. The exception is caught in Button_Click_Base and a Javascript alert is registered for display during the subsequent page load.

Record control class

If a page contains a record control such as an Add or Edit Record page, a record control class provides the methods necessary to load the data for the specific record and bind its data to the user interface control.  There are two record control classes created for each record on the page.  A Base record control class contains all the code and is named by prefixing “Base” to the name of the record control (e.g., BaseCustomersRecordControl).  An initially empty “safe” class is also created and is named the same as the name of the record control (e.g., CustomersRecordControl).  Any methods defined in the base class can be overridden in the safe class to customize the functionality of the record control.

The CustomersRecordControl class is derived from the BaseCustomersRecordControl and BaseApplicationRecordControl classes. The BaseApplicationRecordControl class can be customized to make a common customization across all record controls within an application.

Customizable CustomersRecordControl derived from the BaseCustomersRecordControl class.

Some of the key methods in the record control class are:

• LoadData: Load the data from the database based on the query specified on the Query Wizard.  This method calls the CreateWhereClause to compose the WHERE clause that will be used to read the data from the database.

• CreateWhereClause:  For Edit Record and Show Record pages, the CreateWhereClause retrieves the URL parameter and constructs a WHERE clause to read the record from the database.

• DataBind: Binds the record read from the database to the user interface controls.  Any formatting of the data such as the conversion of the stored date into a local culture specific date format is performed in this method.  DataBind calls SetField for each field displayed on the web page to bind each individual field.

• SetField:  Sets the user interface control from the database record.  The Set function checks to make sure that the value is non-NULL, and then formats the field based on the data type and the current localized culture in effect for the web page and the application.  If the value from the database is NULL, the default value from the database is used.  Many of the formatting, display and default value options can be set in the Property Sheet instead of customizing the function.

• SaveData: This method calls Validate to perform additional validation of the data, retrieves the data from the user interface controls, and then saves the data in the database.  The transaction must be started prior to a call to this function, and must be committed by the caller.  SaveData calls GetField for each control displayed on the web page to retrieve information from the user interface into a record that can be saved.

• Validate: An initially empty Validate method is created in the Base class.  You can override this method in the safe class to perform additional validation.  In case of an error, an exception must be throw with an appropriate message that can be reported to the end user.

• GetField:  Gets the value from the user interface control into a record that will be saved by SaveData.  A Get function is created for each of the user interface controls on the page. This method calls the Parse method to parse the value from the user interface control into the database record.  The Parse method first performs validation to ensure the value can be recognized based on the type of the field (e.g., date), and then, if necessary, converts the text value to the data type of the field (e.g., integer).  The Parse method may throw an exception if the value is not recognized.  The exception must be handled by the caller of the SaveData method.

Table control class

If a page contains a table control such as a Table Report page, the table control class provides the methods necessary to load, display, filter, search, sort, and paginate the table data.  There are two table control classes created for each table on the page. A Base table control class contains all the code and is named by prefixing “Base” to the name of the table control (e.g., BaseCustomersTableControl).  An initially empty “safe” class is also created and is named the same as the name of the table control (e.g., CustomersTableControl).  Any methods defined in the base class can be overridden in the safe class to customize the functionality of the table control.

In addition to the two classes for each table control, there are two additional classes created that correspond to a row in the table.

The CustomersTableControl class is derived from the BaseCustomersTableControl and BaseApplicationTableControl classes. The BaseApplicationTableControl class can be customized to make a common customization across all table controls within an application.

Customizable CustomersTableControl class derived from the BaseCustomersTableControl

Some of the key methods in the table control class are:

• LoadData: Load the data from the database based on the query specified on the Query Wizard.  This method calls the CreateWhereClause to compose the WHERE clause that will be used to read the data from the database.  CreateOrderBy is called to create the ORDER BY portion of the SELECT WHERE clause.  Both CreateWhereClause and CreateOrderBy methods can be overridden in the safe class to add, modify or replace the code.  The LoadData method sets the DataSource of the Table Control class.  The DataSource is used later by the DataBind method to bind each of the rows in the table.

• CreateWhereClause: The CreateWhereClause composes and returns a WHERE clause.  The WHERE clause is composed of the static WHERE clause defined at page creation time combined with any filtering and searching criteria specified by the end user.  You can add additional clauses by overriding the CreateWhereClause method.

• DataBind: Binds the record read from the database to the user interface controls.  This method loops through each row in the table, sets the DataSource of the row record control to the specific row from the DataSource of the table control, and then calls the DataBind method of the row record control.  Any formatting of the data such as the conversion of the stored date into a local culture specific date format is performed in this method.  Pagination controls are bound by calling BindPaginationControls method.  The data for any drop-down filter controls is loaded and bound in this method by calling Populate methods for each filter.

• SaveData: This method is applicable to editable tables and calls the SaveData method for each of the row record controls in the table.  This method also deletes and rows if the user deleted the row.  The row is deleted from the database when the Save button is clicked, not when the delete button is clicked. The transaction must be started prior to a call to this function, and must be committed by the caller.  See the SaveData method for the Record Control described above.

• BindPaginationControls:  All of the pagination controls are bound in this method.  Some controls are disabled if they are not applicable, such as the first page button if the user is already on the first page of the table.

• PopulateFieldFilter:  A PopulateFilter method is created for every dropdown filter.  The name of the method is of the form PopulateFILTERNAMEFilter.  For example, if there is a Country filter, the method will be called PopulateCountryFilter.  This method first calls the CreateWhereClause method for the specific filter (e.g., CreateWhereClause_CountryFilter), and then reads the data from the database, initializes the dropdown list with the values returned, and sets the selected value based on the current value specified in the database.  By default, a maximum of 500 items are retrieved from the database.  This can be changed by modifying the “Maximum generated items” property on the Property Sheet for the specific filter control.

The CreateWhereClause_FILTERFilter method can be overridden to add, modify or replace the method. This is the best way to display a subset of items in the filters. Please note that the filter is a Display Foreign Key As field, the query may be run on the foreign key table, and not on the table from where the data is being displayed.  This is based on the Property Sheet setting that determines whether All Values or Only Result Set is selected for the filter settings.  For example, if you are displayed the Order Details table containing a Product Id filter that is a foreign key to the Products table, then the Populate method will retrieve data from the Products table if the All Values option is selected.  If the Only Result Set option is selected, then the Populate method will retrieve data from the Order Details table.  Please take this into consideration when modifying the CreateWhereClause_FILTER methods so you can add the right WHERE clause for the applicable table.

• Sort_Click:  A Click handler is created for every column sort hyperlink in a table control.  The name of the click handler is of the form COLUMNNAME_Click.  For example, if there is a Country1 column header, the method will be called Country1_Click.  This method calls the underlying ColumnSort method to set the CurrentSortOrder variable that will be used later by the CreateOrderBy method to set the sorting column and direction.

Row class

If a page contains a table control, then in addition to the table control classes, two additional classes are created.  These classes correspond to a row in the table.  The row classes are exactly the same as the Record Control classes defined above with one difference. 

It is important to note that the row classes do not load the data from the database individually, but instead rely on their DataSource variable being set by the table control’s DataBind method.

A Row class is the same as a RecordControl class described ealier.  It is derived from the BaseApplicationRecordControl class. The BaseApplicationRecordControl class can be customized to make a common customization across all record controls and rows within an application.

The Page Lifecycle

The classes and methods created by Iron Speed Designer are easily customizable.  The Page Lifecycle explains the sequence of steps that a page goes through to initialize and load the user interface controls, read data from the database, display the page, and to validate and save the data to the database.  All pages built with Iron Speed Designer derive from the Microsoft .NET Framework’s Page class, so they follow the same page lifecycle described below.  The classes created by Iron Speed Designer extend the Page class to support loading data from the database and to provide more enhanced user interface controls that support data type formatting and validation.

This sequence of steps is called the page lifecycle as shown below:

• Initialization:  During this stage the page and all of the controls within the page (control hierarchy) are instantiated.  This includes any tables, text boxes and buttons.  Any event handlers to handle click events for buttons, sorting hyperlinks, and text changed events for filters are initialized at this stage.

• Loading:  Any event handlers to handle click events for buttons, sorting hyperlinks, and text changed events for filters are initialized at this stage. The data being displayed or edited on this page is loaded from the data-source and “bound” to each of the user interface controls.  The binding process involves formatting the information as well as loading any additional information needed such as the contents of drop-down lists.  Most of the work is performed during this step.

• Event Handling:  Post-back events caused by server controls will be handled. These event handlers include events such as button clicks, SelectedIndexChanged of a dropdown list, TextChanged of a text box and sorting hyperlink events, Note that when button clicks happen, the Init and Load event handlers will be executed as well.  Iron Speed Designer creates code for the Init and Load handlers that checks for the IsPostBack property before deciding whether to execute any code.  The Init and Load handlers do not execute any code during event handling since the code is surrounded by If Not(Me.IsPostBack), so the event handler must perform all actions including loading data from the database if necessary.

• Rendering:  The page’s HTML and all controls are sent to the browser for rendering.  Iron Speed Designer creates code for the PreRender method that checks to see if this is the first time a page is being displayed or whether this is a postback caused by an event such as a button click.  During the initial page lifecycle when the page is first being displayed, the PreRender method does not perform any actions.  During event handling postbacks, PreRender checks to see whether any data is required to be reloaded because of button clicks or other events.  If the data needs to be loaded again based on the new settings, PreRender calls LoadData to retrieve data from the database.

Overrides and Events

At each state of the page lifecycle, the Microsoft .NET Framework sends an event notification to the page.  You can handle an event to customize any aspect of the page before it is displayed to the user.  By handling an event, you can add functionality to your page, but cannot replace the existing functionality that is already provided by the underlying classes.

You can define any number of event handlers for a single event.  For example, the Init event can be handled by multiple event handlers.  In general, if you want to add functionality, it is better to define your own event handler, rather than modifying an existing event handler.  Note that the order of calling of event handlers for an event is not guaranteed.  The event handlers may be called in any order, so care must be taken to ensure that there are no dependencies between event handlers for the same event.

Note that event notifications are sent after the completion of each phase.  For example, the Init event is sent when the initialization of all the controls is complete and the Load is sent after the loading of the data and the controls of the page is completed.

Iron Speed Designer creates code to handle these standard events such as Init and Load.  The code then calls other methods to perform the specific tasks such as loading of the data or binding data to the user interface controls.

You can customize or change the behavior of any control by either:

• Code Tabs:  If you select a control in the spreadsheet layout of the Design Mode, a series of tabs are displayed at the bottom including a cell editor and a number of functions that are relevant to the selected control.  These functions are created by Iron Speed Designer, but can be modified as needed.  To modify, simply change the code as desired and the changes will be preserved automatically.  At any point you can restore the originally created code by clicking the Restore button.  If you want to add to the function, you can call the base function followed by your own customization.

• Adding your own event handler:  If the goal is to add to the functionality provided by Iron Speed Designer, you can simply add your own event handler.  This can be added in Section 1 of the code file using either the editor provided in Iron Speed Designer or by opening the project in Microsoft Visual Studio.

Table Control and Record Control Lifecycle

The Table Control and Record Control are user interface controls and have a lifecycle of their own, just like the lifecycle of each of the controls on a page.  It is important to understand the lifecycle of the table and record control classes since the page delegates the responsibility of reading the data from the database and retrieving and validating the data from the user interface prior to saving it in the database.

The query used to read data from the database is a composite query that is formed based on the static WHERE clause specified by the developer and the dynamic settings specified by the end user.  The static query might be:

SELECT  *

FROM Customers

WHERE Customers.State = California

When the end user views the information, he or she might search for some information within the table, further filter by another field, sort by a column and display the third page of information.   In this case, the pseudo-query might look something like:

SELECT    rows 31 to 40

FROM Customers

WHERE Customers.State = California AND

     Customers.LastName LIKE ‘%Smith%’

ORDER BY Customers.ZipCode DESC

During the lifecycle of a table or record control, you have an opportunity to further modify the query before it is executed by overriding the CreateWhereClause method.  You can also review the data retrieved and make any changes before presentation to the user, or calculate values that are dependent on the rows retrieved by overriding the DataBind method.   Please note that the query only returns the rows that are being displayed on the web page, not all of the rows that would be returned by the query.  For example, if each page displays 10 records, and page 4 is being currently displayed, the query will return only records 31 through 40.

The calling hierarchy of a page with a table control when displaying data shows the various methods that are called at the Page, Table Control and Record Control class levels.  When displaying data, most of the work is performed by handling the Load event at the page class level.  The Load event is handled by Page_Load, LoadData and LoadData_Base methods at the page level.  The LoadData_Base method then calls the LoadData and DataBind methods for each of the Table Control classes on the page.  LoadData then calls CreateWhereClause to create the clause that is used to read the data from the database.  The DataBind calls the SetField method for each of the fields to bind the data to the user interface controls and also binds the pagination controls.  Any dropdown filter lists are populated by calling the PopulateFilter methods.

The calling hierarchy of a page with a table control when filtering, sorting or searching data shows the various methods that are called at the Page, Table Control and Record Control class levels.  When filtering, sorting or searching data, most of the work is performed by the PreRender method at the Table Control class level.  The PreRender method takes the settings set earlier by one of the Click or SelectedIndexChanged events at the Table Control class level.  PreRender then checks to see if the data needs to be reloaded and calls the LoadData and DataBind methods for the Table Control class.  LoadData then calls CreateWhereClause to create the clause that is used to read the data from the database.  The DataBind calls the SetField function for each of the fields to bind the data to the user interface controls.  The DataBind binds the data to the user interface controls and also binds the pagination controls.  Any dropdown filter lists are re-populated by calling the PopulateFilter methods.

The calling hierarchy of a page with a record control when displaying data shows the various methods that are called at the Page and Record Control class levels.  When displaying data, most of the work is performed by handling the Load event at the page class level.  The Load event is handled by Page_Load, LoadData and LoadData_Base methods at the page level.  The LoadData_Base method then calls the LoadData and DataBind methods for each of the Record Control classes on the page.  LoadData then calls CreateWhereClause to create the clause that is used to read the data from the database.  The DataBind binds the data to the user interface controls.  The DataBind calls the SetField function for each of the fields to bind the data to the user interface controls.  Any dropdown lists used on the record page is also populated by calling the PopulateFieldDropDownList method from DataBind.

Saving Data to the Database

In the above sections we have discussed the lifecycle of retrieving and displaying data on a web page.  There is an analogous life cycle the page goes through to retrieve the data from the user interface controls into memory and subsequently save this data in the database.

The data is retrieved into memory from each of the user interface controls and validated.  The retrieved data is stored in an instance of the data access layer’s Record class.

The data from the page is retrieved within the record control class, but the transaction is committed at the page level.  This is because data from all records must be saved within a transaction and any foreign key relationships must be taken into account when the data is saved.  For example, master records are saved first followed by child records since the child records need the Id of their parent record.

You can override the SaveData method at the record control or table control to add functionality before or after saving the data. If the Id’s of the records are assigned by the database, they will be available after the data is written to the database and the transaction is committed.  This can be accomplished by customizing the SaveButton_Click method and calling the underlying methods yourself.  Please note that the SaveButton_Click_Base method at the page level contains a call to Redirect after the data is saved, so none of the code after a call to the base method will be executed.

If the button action specifies redirection to another page, the Redirect method is called within the Save button’s click handler.  The Redirect method of the base class will automatically change the URL parameters if they have been specified in the Property Sheet.

The SaveButton click handler that starts a database transaction and calls SaveData on the CustomersRecordControl.

The SaveData method for CustomersRecordControl that calls Validate then calls GetUIData to retrieve the data from the user interface controls.  Can be overridden in the CustomersRecordControl class to customize.

The calling hierarchy of a page with a record control when saving data shows the various methods that are called at the Page and Record Control class levels.  When saving data, most of the work is performed by handling the Click event of the button at the page class level.  The Click event is handled by ButtonClick and ButtonClick_Base methods at the page level.  The ButtonClick_Base method then calls the SaveData method for each of the Record Control classes on the page.  Since you may be only editing a subset of fields on the page, LoadData is called to load the complete record and perform concurrency comparison.  This is followed by a call to Validate the data entered by the user and GetUIData to retrieve the data from the user interface controls into the database record.  The GetUIData function calls GetField for each of controls displayed on the web page to retrieve the data and place it in the DataSource for saving into the database.  The GetField function calls Parse to parse the values into the native field types based on the current internationalization settings.  The record is then saved in the database.  The CommitTransaction is performed at the ButtonClick_Base to ensure that all of the data is saved within one transaction.

Page Control Hierarchy

All web pages in Microsoft .NET Framework contain a number of user interface controls that are initialized at the server side and are called server controls.  Since server controls can have children, and each of their children may have children, and so on, a control and its descendents form a tree of controls.  This tree of controls is called the control hierarchy. The root of the control hierarchy for a .NET web page is the Page-derived class.  Iron Speed Designer creates a hierarchy of controls for each of the pages. 

The loading of the data from the database is delegated by the page to the Table Control or the Record Control classes.  Since you can have a number of tables and records displayed on a page, there can be multiple Table Control and Record Control classes within a page.  Each of the classes is responsible for loading its data from the database based on the query you specified and any search, filter and pagination settings selected by the end user.  The Row record control class does not load data directly from the database, but receives its data from the Table Control class.

The dropdown filter controls load the data from the database directly in methods that are named PopulateFILTERNAME.

The field value controls do not load data from the database directly.  Instead they rely on the Table Control and Record Control classes to load the data and are bound in the DataBind methods for each of the table or record control classes.

Code Tabs

The Code Tabs in the Design Mode show the most commonly customized functions for the currently selected control in the Page Layout Spreadsheet.  For data bound controls such as a textbox displayed on the Add or Edit page, the Code Tabs show the Set, Get and Validate functions.  Similarly, selecting a button control shows its Click function, or selecting a dropdown shows its Dropdown function. Zooming out to the Table Control or the Record Control level will display functions such as CreateWhereClause,LoadData, DataBind and SaveData that are relevant to the selected controls. 

The Code Tabs allow editing of the functions by simply modifying them within the code editor. Modification of the code in the code editor automatically places a copy of the function in Section 1, and changes the color of the tab to easily indicate that the function has been modified.  At any point, clicking Restore will remove all the customizations and replace the function to its originally created code.  Subsequent builds will also preserve the customizations, and the changes made will never be overwritten, unless explicitly restored.

Within the Code Tab, the functions can be modified:

1. By a line or two of the code;

2. Changed to call the base function followed by additional lines of code; or

3. Changed in its entirety based on your needs.

All changes to the code will be preserved and never overwritten no matter how many times you rebuild your application. The code is well documented to ensure that it is easy to follow and change if the need arises.

Detailed documentation about the function can be viewed by clicking the Docs button. The Docs button displays customized documentation for the specific function being reviewed.  The documentation describes the function, displays a calling hierarchy, shows how to customize and even displays a link to the underlying Microsoft .NET Framework control documentation when relevant.  The documentation can also be opened in a separate window so the code and the documentation can be viewed side-by-side if necessary.

Referencing Page Controls in Code

The user interface controls defined on the page can be accessed easily within each of the page, table control and record control classes in the code-behind or the controls file.  Please note that user interface controls within a table control are initialized and defined when data is loaded into the table control since the number of rows displayed is determined by the result set returned by the query.  As such we recommend that you make most of your code customizations at the Record Control class level since this class is available both when displaying a single record on a page as well as for each row within a table.

Page Class

You can access any user interface control from the page class except the controls that are repeated for each row in a table.  To access the controls from the page, you simply specify the name of the control that is used in your web page.  For example, if you have an Add Customer page, you may have a field label called CompanyNameLabel and a field value text box called CompanyName.  To access this control, you can:

C#:

this.CompanyName

this.CompanyName.Text   // to access the text entered by the user

Visual Basic .NET:

Me.CompanyName

Me.CompanyName.Text ' to access the text entered by the user

If you are displaying an Add, Edit or Show Record page, you will have a record control on the page as shown in the control hierarchy above.  The record control corresponds to the record control class in the page’s code-behind file.  If the page is a Table Report page, you will have a table control on the page and a record control for each row in the table.  Both the table control and the record control will have corresponding classes in the page’s code-behind file.  You can access the record and table controls within the page as follows:

C#:

this.CustomersRecordControl          // to access the record control

// to access the company name within the

// record control

this.CustomersRecordControl.CompanyName

this.CustomersTableControl             // to access the table control

Visual Basic .NET:

Me.CustomersRecordControl ' to access the record control

' to access the company name within the

' record control

Me.CustomersRecordControl.CompanyName

Me.CustomersTableControl ' to access the table control

Table Control Class

The table control class corresponds to the table control on the page.  You can directly access all of the controls within the table except the rows of a table.  To access the rows of a table, you can either override or handle events at the record control class level, or use functions such as GetRecordControls or GetSelectedRecordControls to get an array list that can loop through to get an individual row.  To access the first (or only) selected row, use GetSelectedRecordControl function.  The search, filter, pagination and column sorting controls can be accessed directly from the table control class.

The following example is for a button placed on the button bar of a table control that sets the Discontinued flag on all selected rows of the table control.  The function calls GetSelectedRecordControls to retrieve the list of all selected rows.  For each of the rows (TableControlRow objects), we use the RecordUniqueId to retrieve the database record.  The database record is retrieved using the data access layer function GetRecord on the data access layer Table class (not the TableControl user interface class).  The second argument passed to GetRecord is True to retrieve an updateable record.  The Discontinued flag is modified and the Save function is called to save the record in the database.  The entire For loop is enclosed in a Start, Commit and End Transaction block to ensure that all of the records are committed at the same time.  Finally, the DataChanged variable is set to True so that the PreRender method on the table control refreshes the page with the latest settings from the database.

C#:

public override void MarkDiscontinued_Click(object sender, ImageClickEventArgs args)

{

     ProductsTableControlRow rc;

     try {

          DbUtils.StartTransaction();

          // Get all of the selected record controls.

          // Record controls are UI controls.

          foreach (rc in this.GetSelectedRecordControls()) {

              ProductsRecord rec;

              // Using the RecordUniqueId, read the Product record

              // from the database.

              // Use True as the second argument to make sure you

              // get a Writable record.

              rec = ProductsTable.GetRecord(rc.RecordUniqueId, true);

              if (rec != null) {

                   // Mark as Discontinued

                   rec.Discontinued = true;

                   rec.Save();

              }

          }

          DbUtils.CommitTransaction();

     }

     catch (Exception ex) {

          Utils.MiscUtils.RegisterJScriptAlert(this, "BUTTON_CLICK_MESSAGE", ex.Message);

     }

     finally {

          DbUtils.EndTransaction();

     }

     // Mark the data as changed so the data is refreshed

     this.DataChanged = true;

}

Visual Basic .NET:

Public Overrides Sub MarkDiscontinued_Click(ByVal sender As Object, ByVal args As ImageClickEventArgs)

     Dim rc As ProductsTableControlRow

     Try

          DbUtils.StartTransaction()

          ' Get all of the selected record controls. 

          ' Record controls are UI controls.

          For Each rc In Me.GetSelectedRecordControls()

              Dim rec As ProductsRecord

              ' Using the RecordUniqueId, read the Product record

              ' from the database.

              ' Use True as the second argument to make sure you

              ' get a Writable record.

               rec = ProductsTable.GetRecord(rc.RecordUniqueId, True)

              If Not (IsNothing(rec)) Then

                   ' Mark as Discontinued

                   rec.Discontinued = True

                   rec.Save()

              End If

          Next

          DbUtils.CommitTransaction()

     Catch ex As Exception

          Utils.MiscUtils.RegisterJScriptAlert(Me, "BUTTON_CLICK_MESSAGE", ex.Message)

     Finally

          DbUtils.EndTransaction()

     End Try

     ' Mark the data as changed so the data is refreshed

     Me.DataChanged = True

End Sub

To retrieve all the currently displayed rows instead of only the selected rows, use the GetRecordControls function.  To retrieve the first (or only) selected row, use the GetSelectedRecordControl function.

All controls within the row can be accessed directly using the name of the control.  In the example above, if the row contained a ProductName control, it can accessed directly by using rc.ProductName.  If the control is a textbox, all properties of the textbox can be accessed directly as well, such as rc.ProductName.Text.

Record Control Class

We recommend most of the code customizations to be made at the record control class.  The record control class corresponds to the record being displayed on the page.  If there is a table displayed on the page, the record control class corresponds to each row within a table.  The customizations will be the same regardless of whether the record control class corresponds to a single record control or to a row within a table.

You can access all of the fields within a record control including column values as shown below:

C#:

this.CompanyName

this.CompanyName.Text   // to access the text entered by the user

Visual Basic .NET:

Me.CompanyName

Me.CompanyName.Text ' to access the text entered by the user

Referencing Data Access Functions in Code

Iron Speed Designer creates two classes for each table in your database.

The Record classes represent a record from the database.  Some of the record classes specify an actual physical record in the database while others correspond to a virtual record – the result of a join between tables.  A “Record” suffix is added to the name of the table to get the name of the class.  For example, if you have a Customers table in your database, the class will be called CustomersRecord.  The record class is different from the record control class described elsewhere.  A record class corresponds to a database record, while a record control class corresponds to a user interface control that contains other fields that display or edit data.

The Table class is one of the most important classes used in Iron Speed Designer applications.  This class is created for each table in your application to provide the methods necessary to retrieve a group of records based on a query.  A “Table” suffix is added to the name of the table to get the name of the class.  For example, if you have a Customers table in your database, the class will be called CustomersTable.  A “View” or a “Query” suffix is added to the name if the class is for a view or query respectively.

To read data from the database you can use the GetRecords function on the Table class.  There are a number of variations of the GetRecords functions that either take a string-based WHERE clause or an object that specifies the clause.  Each of the GetRecords functions can take optional arguments that specify the order by clause, the number of records to retrieve, and the starting page number.

GetRecords with a WHERE clause string

The following examples show how to use the GetRecords functions with a string based WHERE clause.  This case is typically used to make a call to GetRecords when there is only one WHERE clause that is not combined with another clause using an AND or OR operator.  While the clauses can be combined in a string, it is better to use a WhereClause object defined later in this section.

C#:

CustomersRecord[] myRecords;

myRecords = CustomersTable.GetRecords("Country = USA");

if (myRecords != null)

{

     foreach (CustomersRecord rec in myRecords)

     {

          // To access a field in the record, simply specify

          //   rec.<FieldName>

          String s = "Company: " + rec.CompanyName;

     }

}

Visual Basic .NET:

Dim myRecords As CustomersRecord()

myRecords = CustomersTable.GetRecords("Country = USA")

If Not (IsNothing(myRecords)) Then

     For Each rec In myRecords

           ' To access a field in the record, simply specify

          '    rec.<FieldName>

          Dim s As String = "Company: " & rec.CompanyName

     Next

End If

The GetRecords function takes a WHERE clause and retrieves all records that meet that criteria.  Please note that the number of records returned is dependent on the number of records in the database.  If you want to limit the number of records, you can pass additional parameters to the GetRecords function.  The following code will return the third set of 50 records.

C#:

CustomersRecord[] myRecords;

myRecords = CustomersTable.GetRecords("Country = USA", null, 3, 50);

if (myRecords != null)

{

     foreach (CustomersRecord rec in myRecords)

     {

          // To access a field in the record, simply specify

          //   rec.<FieldName>

          String s = "Company: " + rec.CompanyName;

     }

}

Visual Basic .NET:

Dim myRecords As CustomersRecord()

myRecords = CustomersTable.GetRecords("Country = USA", Nothing, 3, 50)

If Not (IsNothing(myRecords)) Then

     For Each rec In myRecords

          ' To access a field in the record, simply specify

          ' rec.<FieldName>

          Dim s As String = "Company: " & rec.CompanyName

     Next

End If

The above examples demonstrate how you can read a set of records from the database.  If you want to access the records that are being displayed on a page, you would need to call the GetRecords() function on the Table Control class within the page.

In addition to the above example, you can also use the GetRecord function to read a single record from the database.  GetRecord takes a WHERE clause similar to GetRecords and will return the first record that matches the query.

GetRecords with a WhereClause object

The following examples show how to use the GetRecords functions with a WhereClause object.  This case is typically used to make a call to GetRecords when there are more than one WHERE clauses that must be combined with AND or OR operators.  The CreateWhereClause method created by Iron Speed Designer uses this method to retrieve data from the database.

You can defined a WhereClause object and then call the iAND and iOR methods to define clauses.  The iAND and iOR methods take a field name, an operator and a value.  The field name may contain spaces or other characters, so it is safer to use the name as provided by the database schema.  The database schema field name can be specified using the Table class followed by the column name such as CustomersTable.CompanyName.

C#:

public virtual void LoadData()

{

     try

     {

          DbUtils.StartTransaction();

          // The WHERE clause will be empty when displaying all records in table.

          WhereClause wc = CreateWhereClause();

          this.DataSource = CustomersTable.GetRecords(wc);

     }

     catch (Exception ex)

     {

          throw ex;

     }

     finally

     {

          DbUtils.EndTransaction();

     }

}

protected virtual WhereClause CreateWhereClause()

{

     // Start with a blank WhereClause

     WhereClause wc = new WhereClause();

     // Create a WhereClause that is as follows:

     // Country = "USA" AND State = "California" AND (Name Contains "Jones" OR CompanyName Contains "Jones")

     // Add the value selected in the Country filter dropdown

     if (this.CountryFilter.SelectedValue != "")

     {

          wc.iAND(CustomersTable.Country, EqualsTo, this.CountryFilter.SelectedValue);

     }

     if (this.StateFilter.SelectedValue != "")

     {

          wc.iAND(CustomersTable.State, EqualsTo, this.StateFilter.SelectedValue);

     }

     // Now we need to create a separate WhereClause that OR's the search string and then

     // this separate clause is ANDed with the rest of the clauses)

     if (this.CustomersSearchArea != "")

     {

          WhereClause search = new WhereClause();

          search.iOR(CustomersTable.Name, Contains, this.CustomersSearchArea.Text);

          search.iOR(CustomersTable.CompanyName, Contains, this.CustomersSearchArea.Text);

          // Now AND this with the WhereClause wc defined earlier.

          wc.iAND(search);

     }

     return wc;

}

Visual Basic .NET:

Public Overridable Sub LoadData()

     Try

          DbUtils.StartTransaction()

          ' The WHERE clause will be empty when displaying all records in table.

          Dim wc As WhereClause = CreateWhereClause()

          Me.DataSource = CustomersTable.GetRecords(wc)

     Catch ex As Exception

          Throw ex

     Finally

          DbUtils.EndTransaction()

     End Try

End Sub

Protected Overridable Function CreateWhereClause() As WhereClause

     ' Start with a blank WhereClause

     Dim wc As WhereClause = New WhereClause

     ' Create a WhereClause that is as follows:

      ' Country = "USA" AND State = "California" AND (Name Contains "Jones" OR CompanyName Contains "Jones")

     ' Add the value selected in the Country filter dropdown

     If Me.CountryFilter.SelectedValue <> "" Then

           wc.iAND(CustomersTable.Country, EqualsTo, Me.CountryFilter.SelectedValue)

     End If

      ' Add the value selected in the State filter dropdown

     If Me.StateFilter.SelectedValue <> "" Then

          wc.iAND(CustomersTable.State, EqualsTo, Me.StateFilter.SelectedValue)

     End If

     ' Now we need to create a separate WhereClause that OR's the search string and then

     ' this separate clause is ANDed with the rest of the clauses)

     If Me.CustomersSearchArea.Text <> "" Then

          Dim search As WhereClause = New WhereClause

          search.iOR(CustomersTable.Name, Contains, Me.CustomersSearchArea.Text)

          search.iOR(CustomersTable.CompanyName, Contains, Me.CustomersSearchArea.Text)

          ' Now AND this with the WhereClause wc defined earlier.

          wc.iAND(search)

     End If

     Return wc

End Function

The GetRecords function takes a WHERE clause and retrieves all records that meet that criteria.  Please note that the number of records returned is dependent on the number of records in the database.  If you want to limit the number of records, you can pass additional parameters to the GetRecords function.  The following code will return the third set of 50 records.

C#:

// By default we want to expand foreign keys and to sort in a case sensitive order

OrderBy orderBy = new OrderBy(true, true);

// Order by Name in Ascending order

orderBy.Add(CustomersTable.Name, Asc);

this.DataSource = CustomersTable.GetRecords(wc, orderBy, 3, 50);

Visual Basic .NET:

' By default we want to expand foreign keys and to sort in a case sensitive order

Dim orderBy As orderBy = New orderBy(True, True)

' Order by Name in Ascending order

orderBy.Add(CustomersTable.Name, Asc)

Me.DataSource = CustomersTable.GetRecords(wc, orderBy, 3, 50)

The above examples demonstrate how you can read a set of records from the database.  If you want to access the records that are being displayed on a page, you would need to call the GetRecords() function on the Table Control class within the page.

In addition to the above example, you can also use the GetRecord function to read a single record from the database.  GetRecord takes a WHERE clause similar to GetRecords and will return the first record that matches the query.

Handling Post Back Events

In ASP.NET, each page is reloaded when a button is pressed or if you set the AutoPostBack property of a control to be True when its value has changed.  Since the same overridden methods are called and the same event notifications are sent during a post back, you need to make sure that you check the post back property of a page when writing your custom code.  For example, if you want to initialize a field to a value when the page is first displayed then you can handle an event and initialize this value.  But if you do not check the post back property in the event handler, your field will be initialized again when the page is being reposted and thus overwrite any user entered value.  This is perhaps the most common mistake made by .NET programmers.

C#:

if (!this.Page.IsPostBack)

{

     //  This code executes when the page is first loaded.

}

else

{

     //  This code executes during a button click or other postback.

}

//  This code executes in all cases.

Visual Basic .NET:

If Not (Me.Page.IsPostBack) Then

     ' This code executes when the page is first loaded.

Else

     ' This code executes during a button click or other postback.

End If

' This code executes in all cases.

Best Practices for Code Customization

While every customization and every need will be different, we have compiled a list of best practices that we recommend you follow.  These best practices will help you add customizations at the most appropriate location for the vast majority of needs.

Where to add customizations?

What events to handle or methods to override?

Do Not Forget the IsPostBack Flag

Where to add customizations?

In general you can handle events or override methods at the page class level, at the table or record control level or even at an individual field control level.  For example, if you want to initialize the Country field to United States on a page, you can handle the PreRender event at the Page level, at the Record Control level or even the Country field level.  So what is the best place to add this code customization?

We recommend that you add your code customizations at the Record Control class level.  There are many reasons for this including:

• If you need a value from the underlying database record, you will have direct access to it by calling the GetRecord method or using the DataSource property.

• When you have a table control displayed on a page, each row of the page is created dynamically at run-time based on the number of rows being displayed.  If you customize at the page or the table control level, you need to determine the exact row you are working on in your code customization. By doing it in the record control, you will always have access to the specific record you are interested in handling.

• The code customization will be the same whether you are working within a table or just displaying a single record.

See Also

Where to add customizations?

What events to handle or methods to override?

Do Not Forget the IsPostBack Flag

What events to handle or methods to override?

Some code customizations can be performed by handling the following events.  For each event, please pay special attention to the recommended class for handling the event.

DataBind Method

If you want to initialize a value, format a value, compute a value, hide or display a control based on some criteria, or change the look and feel of a control, we recommend you override the DataBind method for the Record Control class to make these customizations.  Remember to first call the MyBase.DataBind or base.DataBind to perform the underlying functions.

C#

public override void DataBind()

{

     base.DataBind();

     // Pre-initialize Title if blank

     if (this.ContactTitle.Text == "")

     {

          this.ContactTitle.Text = "Manager";

     }

}

Visual Basic .NET

Public Overrides Sub DataBind()

     MyBase.DataBind()

     ' Pre-initialize Title if blank

     If Me.ContactTitle.Text = "" Then

          Me.ContactTitle.Text = "Manager"

     End If

End Sub

Validate Method

Two specific validators can be automatically created based on the values selected in the Property Sheet:

• RequiredFieldValidator:  Selecting the Required property on the Property Sheet creates a Required Field Validator for Textbox, Checkbox and File Upload controls.

• MaxLengthValidator: Specifying a maximum length on the Property Sheet creates a MaxLengthValidator for a Textbox control.

In addition to these two validators, you can add your own custom validation logic very easily by overriding the Validate method at the Record Control class level.  The Validate method is called from the SaveData method before the GetUIData method is called.  Note that GetUIData retrieves the data from the user interface controls and converts it into the internal format required for saving.  For example, a date specified as a text string “12-1-2006” will be converted to a Date object with the appropriate month, day and year values.

To validate fields from the user interface controls, use their user interface controls to validate the data.  Validation errors must be grouped together and an exception thrown to abort the saving of data.

C#:

public override void Validate()

{

     base.Validate();

     //  Additional Validation

     if (this.State.Text != "CA")

     {

          throw new Exception("State must be CA (California).");

     }

}

Visual Basic .NET:

Public Overrides Sub Validate()

     MyBase.Validate()

     '  Additional Validation

     If Me.State.Text <> "CA" Then

          Throw New Exception("State must be CA (California).")

     End If

End Sub

GetUIData Method

If you want to make changes to the values before they are saved, we recommend you override the GetUIData method at the Record Control class level.  The GetUIData method is called from within SaveData and retrieves all of the values from the user interface controls prior to the data being saved in the database.  You can even set fields that are not displayed to the user, such as audit control fields.

C#:

public override void GetUIData()

{

     base.GetUIData();

     // Set additional field values here

     this.DataSource.LastUpdateDate = DateTime.Now();

}

Visual Basic .NET:

Public Overrides Sub GetUIData()

     MyBase.GetUIData()

     ' Set additional field values here

     Me.DataSource.LastUpdateDate = DateTime.Now()

End Sub

CommitTransaction Method

If you want to access the Id of the record or send an email after the record is saved, we recommend you override the CommitTransaction method at the page class level.  The CommitTransaction is defined in the BasePage class and calls DBUtils.CommitTransaction.  If you override the CommitTransaction method, make sure to call the base CommitTransaction.

C#:

public override void CommitTransaction(object sender)

{

     // Call the base CommitTransaction

     base.CommitTransaction(sender);

     // Use the Me.CustomersRecordControl.GetRecord() to retrieve the record that was just updated.

     CustomersRecord myRecord;

     myRecord = this.CustomersRecordControl.GetRecord();

     // Send a confirmation email with the CustomerId.

     try

     {

          BaseClasses.Utils.MailSender email = new BaseClasses.Utils.MailSender();

          email.AddFrom("[email protected]");

          email.AddTo("[email protected]");

          email.AddBCC("[email protected]");

          email.SetSubject("Confirmation");

          email.SetContent("Thank you for your request. Your Customer Id is: " + myRecord.CustomerID);

          email.SendMessage();

     }

     catch (System.Exception ex)

     {

          // Report the error message to the user.

          Utils.RegisterJScriptAlert(this, "UNIQUE_SCRIPTKEY", "Could not send an email. Error was: " + ex.Message);

     }

}

Visual Basic .NET:

Public Overrides Sub CommitTransaction(ByVal sender As Object)

     ' Call the base CommitTransaction

     MyBase.CommitTransaction(sender)

     ' Use the Me.CustomersRecordControl.GetRecord() to retrieve the record that was just updated.

     Dim myRecord As CustomersRecord

     myRecord = Me.CustomersRecordControl.GetRecord()

     ' Send a confirmation email with the CustomerId.

     Try

          Dim email As New BaseClasses.Utils.MailSender

          email.AddFrom("[email protected]")

          email.AddTo("[email protected]")

          email.AddBCC("[email protected]")

          email.SetSubject("Confirmation")

          email.SetContent("Thank you for your request. Your Customer Id is: " & myRecord.CustomerID)

          email.SendMessage()

     Catch ex As System.Exception

          ' Report the error message to the user.

          Utils.RegisterJScriptAlert(Me, "UNIQUE_SCRIPTKEY", "Could not send an email. Error was: " & ex.Message)

     End Try

End Sub

Redirect Method

If you want to modify the URL before redirection, we recommend you override the CommitTransaction method (described above) at the Page class level and redirect after the commit.  The SaveButton_Click method calls the SaveButton_Click_Base method on the page class to save and commit the database records.  Once the save and commit happens, the SaveButton_Click_Base redirects to the URL specified in the Property Sheet.  As such, you cannot add code after the call to the SaveButton_Click_Base method in SaveButton_Click because once the redirect happens, control will not return back to the SaveButton_Click method.

C#:

public void SaveButton_Click(object sender, EventArgs args)

{

     SaveButton_Click_Base(sender, args);

     // Code below will never get executed since SaveButton_Click_Base will

     // redirect to another page.  To customize, either replace SaveButton_Click_Base

     // functionality here, or override CommitTransaction.

}

Visual Studio .NET:

Public Sub SaveButton_Click(ByVal sender As Object, ByVal args As EventArgs)

     SaveButton_Click_Base(sender, args)

     ' Code below will never get executed since SaveButton_Click_Base will

     ' redirect to another page. To customize, either replace SaveButton_Click_Base

     ' functionality here, or override CommitTransaction.

End Sub

CreateWhereClause Method

If you want to modify the query before it is executed, the best place to do it is by overriding the CreateWhereClause method at the Table Control level for Table Report pages, and the CreateWhereClause method at the Record Control class level for the Add, Edit, and Show Record pages.  Please note that CreateWhereClause is not available at the Page level since each table and record control has its own query.  You can override the CreateWhereClause and add your own WHERE clause.

C#:

protected override BaseClasses.Data.WhereClause CreateWhereClause()

{

     WhereClause wc;

     wc = base.CreateWhereClause();

     if (IsNothing(wc))

     {

          // Get a blank WHERE clause if the base function returned null.

          wc = new WhereClause();

     }

     wc.iAND(CustomersTable.City, Starts_With, "Mountain");

}

Visual Basic .NET:

Protected Overrides Function CreateWhereClause() As BaseClasses.Data.WhereClause

     Dim wc As WhereClause

     wc = MyBase.CreateWhereClause()

     If IsNothing(wc) Then

          ' Get a blank WHERE clause if the base function returned null.

          wc = New WhereClause

     End If

     wc.iAND(CustomersTable.City, Starts_With, "Mountain")

End Function

The CreateWhereClause method creates a WhereClause object that contains clauses joined together using AND, OR and NOT operators.  The default CreateWhereClause created by Iron Speed Designer combines three different types of clauses to compose the entire WHERE clause for the query.

Static Clause: The static clause defined at design time by the developer. Note that the static clause may contain multiple clauses including clauses that support role-based security and custom function calls.

Filter Clause(s): The static clause is ANDed with one or more filter settings such as from a dropdown list or date filter textbox. For dropdown list boxes, an Equals comparison operator is used. For textbox filters, you can specify the operator such as greater-than, less-than, etc.

Search Clause: This entire clause is ANDed again with a search clause that compares the search string with the various search fields. The search clause is composed of a series of OR clauses that checks whether any of the search fields contain the text value entered in the search text box. You can configure the search fields and the operator it uses (default is Contains) via the Query Wizard in the Data Sources tab.

In pseudo-code, this clause looks like:

[Psuedo-code]

     StaticClause AND

     FilterField1 = SelectedValue1 AND

     FilterField2 = SelectedValue2 AND

     ...more filter clauses... AND

     (SearchField1 Contains SearchText OR

     SearchField2 Contains SearchText OR ...)

The CreateWhereClause must return a WhereClause object. One of the main reasons Iron Speed Designer uses a WhereClause object instead of a Where string is to insulate you from knowing the syntax based on the specific database product you are using. Oracle, Microsoft SQL Server, MySQL and Microsoft Access each have a slightly different syntax for the SQL they use. Another reason to use the WhereClause is to specify whether a clause compares the raw value or the display foreign key value. This is described below.

You can override the CreateWhereClause in the derived class such as ProductsRecordControl. You can either call the base CreateWhereClause and add your own clauses to it, or completely replace the call.

The iAND and iOR take three required parameters and up to two optional arguments. It is important to understand the two optional arguments since they provide finer control over the WHERE clause.

Column: The column object is the first parameter to iAND and iOR. The column object is used to determine the actual column name. Sometimes the column name may contain spaces and other characters not easily specified in code, so by passing the column object, the WhereClause can determine and use the correct name. The format of the first parameter is typically TableTable.Field. For example if you have a field called FirstName in a table called Customers, the column object can be specified by using CustomersTable.FirstName. If a View or a Query is used, the suffix is changed from Table to View or Query respectively. For example, the FirstName field in vwActiveCustomers view would be specified as vwActiveCustomersView.FirstName.

Operator: The operator used by the WHERE clause. Operator can be one of the following:

• Contains

• Ends_With

• EqualsTo

• Greater_Than

• Greater_Than_Or_Equal

• Less_Than

• Less_Than_Or_Equal

• Not_Contains

• Not_Ends_With

• Not_Equals

• Not_Starts_With

• Starts_With

Value: The value is always specified as a string. Date and other objects must be converted to a string appropriately.

(Optional) ExpandForeignKey: If the Column or Field is a foreign key field, then sometimes it is convenient to pass the Display Foreign Key As (DFKA) value instead of the Id of the foreign key. For example, if there is a CustomerId field in the Orders table, you can create a clause to compare Orders.CustomerId = "3" or Orders.CustomerId = "Tom Jones".

The ExpandForeignKey parameter must be True if using the DFKA value such as “Tom Jones” in the above example. If the ExpandForeignKey is True, the SELECT statement will contain a JOIN with the foreign key table and the actual WHERE clause will compare the DFKA field with the value. For example:

SELECT * FROM Orders, Customers

WHERE Orders.CustomerID = Customers.CustomersID AND

Customers.Name = "Tom Jones"

If ExpandForeignKey is FALSE, then the SELECT statement created is a simple statement on a single table as shown below:

SELECT * FROM Orders WHERE Orders.CustomerID = "3"

By default, all static clauses specified on the Query Wizard will expand the foreign key (set to True). By default all filter clauses based on the end-user filter selection will not expand the foreign key (set to False) since in most cases we will have the Id values available in the dropdown

(Optional) IsCaseSensitive:  If the IsCaseSensitive parameter is True, the comparison is case sensitive.  For the Search filter, this setting can be specified via the Query Wizard (Data Sources tab) or the Property Sheet.  For the static search filter clause, the IsCaseSensitive is set to False, while the parameter is set to True for a dynamic filter dropdown control.

To add parenthesis in a WHERE clause, you can simply create another new WHERE clause and AND or OR it with the earlier clause. The pseudo-code below shows you how to specify the following clause:

A AND B AND (C OR D)

You can do the following:

C#:

WhereClause wc1 = new WhereClause();

wc1.iAND(A);

wc1.iAND(B);

// Create OR portion of the clause

WhereClause wc2 = new WhereClause();

wc2.iOR(C);

wc2.iOR(D);

// AND the OR portion of the clause.

wc1.iAND(wc2);

Visual Basic .NET:

Dim wc1 As WhereClause = New WhereClause

wc1.iAND(A)

wc1.iAND(B)

' Create OR portion of the clause

Dim wc2 As WhereClause = New WhereClause

wc2.iOR(C)

wc2.iOR(D)

' AND the OR portion of the clause.

wc1.iAND(wc2)

PopulateFilter Method

If you want to pre-initialize a filter with a URL, cookie, or session value, the best place to do this is to override the PopulateFilter method for the specific field filter at the Table Control class.  You can call the base method and then set the current value.  You must also override the CreateWhereClause method to add the initial setting of the filter to the WHERE clause.  You can also customize the WHERE clause used by the Populate Filter to limit the type of records retrieved, such as only Active customers.  For additional customization, you can even replace the entire PopulateFilter method with your own method.

C#:

protected override WhereClause CreateWhereClause()

{

     //  Call the MyBase.CreateWhereClause()

     WhereClause wc = base.CreateWhereClause();

     //  If MyBase.CreateWhereClause() returns nothing then create a new

     //  instance of WhereClause

     if ((wc == null))

     {

          wc = new WhereClause();

     }

     //  Add a WHERE clause based on the querystring

     string country = this.Page.Request.QueryString("Country");

     if (!this.Page.IsPostBack && country != "")

     {

          wc.iAND(CustomersTable.Country, EqualsTo, country);

     }

     return wc;

}

// -----------------------------------------------------------------------------------------------------------

protected override void PopulateCityFilter(string selectedValue, int maxItems)

{

     string country = this.Page.Request.QueryString("Country");

     //  The selected value only will be set when the page loads for the first time.

     if (!this.Page.IsPostBack && country != "")

     {

          base.PopulateCityFilter(country, maxItems);

     }

     else

     {

          base.PopulateCityFilter(selectedValue, maxItems);

     }

}

Visual Basic .NET:

Protected Overrides Function CreateWhereClause() As WhereClause

      ' Call the MyBase.CreateWhereClause()

     Dim wc As WhereClause = MyBase.CreateWhereClause()

      ' If MyBase.CreateWhereClause() returns nothing then create a new

      ' instance of WhereClause

     If (IsNothing(wc)) Then

          wc = New WhereClause

     End If

     ' Add a WHERE clause based on the querystring

     Dim country As String = Me.Page.Request.QueryString("Country")

     If Not (Me.Page.IsPostBack) AndAlso country <> "" Then

          wc.iAND(CustomersTable.Country, EqualsTo, country)

     End If

     Return wc

End Function

‘ -----------------------------------------------------------------------------------------------------------

Protected Overrides Sub PopulateCountryFilter(ByVal selectedValue As String, ByVal maxItems As Integer)

          Dim country As String = Me.Page.Request.QueryString("Country")

     ' The selected value only will be set when the page loads for the first time.

     If Not (Me.Page.IsPostBack) AndAlso country <> "" Then

          MyBase.PopulateCountryFilter(country, maxItems)

     Else

          MyBase.PopulateCountryFilter(selectedValue, maxItems)

     End If

End Sub

See Also

Where to add customizations?

What events to handle or methods to override?

Do Not Forget the IsPostBack Flag

Do Not Forget the IsPostBack Flag

By far the most common mistake made by .NET developers is to ignore the IsPostBack flag when handling an event or overriding a method.  Note that each method and event handler will be executed at least twice, once when the page is being displayed and once when the user presses a button to save or go to another page.  If you set AutoPostBack on a field to handle a TextChanged or SelectedItemChanged event, then each of the event handlers will be executed three or more times.  When you add custom logic, make sure you check for the IsPostBack flag to decide whether to execute your code the first time the page is displayed, or only during a postback such as a button click or always.

C#:

if (!this.Page.IsPostBack)

{

     //  This code executes when the page is first loaded.

}

else

{

     //  This code executes during a button click or other postback.

}

//  This code executes in all cases.

Visual Basic .NET:

If Not (Me.Page.IsPostBack) Then

     ' This code executes when the page is first loaded.

Else

     ' This code executes during a button click or other postback.

End If

' This code executes in all cases.

See Also

Where to add customizations?

What events to handle or methods to override?

Do Not Forget the IsPostBack Flag

Additional Page Lifecycle References

Understanding ASP.NET View State and Page Lifecycle:

http://msdn.microsoft.com/en-us/library/ms972976.aspx

The ASP.NET Page Object Model:

http://msdn.microsoft.com/en-us/library/aa479007.aspx