Geeks With Blogs
Geekette Mai Blog

 

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 applications.  This class is generated 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 generated 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.

          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.

          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.

          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.

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

Posted on Thursday, July 20, 2006 8:43 PM | Back to top


Comments on this post: Referencing Data Access Functions in Code

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Mai Nguyen | Powered by: GeeksWithBlogs.net