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