| Intro |
|
CookBook: |
| Data Layer |
| Web Application |
| Release |
| Installation |
| Download |
| License |
Relational Artist Cookbook: Data Classes
In the following, we will create an example that uses the Relational Persistence Library to page through a recordset. The example is relatively simple, but not to the point of becoming so trivial that it is not representative any longer.
The example tables
The example has three tables: TSUPPLIER, TPRODUCT, and TCANSUPPLY.
| Table | Columns | Meaning |
|---|---|---|
| TSUPPLIER | SUPPID INT, SUPPNAME VARCHAR(50), SUPPADDRESS VARCHAR(200) | contains one record per supplier in our system. SUPPID is its primary key. |
| TPRODUCT | OURPRODID INT, PRODNAME VARCHAR(50), PREFSUPPID INT, SALESPRICE DECIMAL(9,2) | contains one record per product in which we trade. PRODID is its primary key. PREFSUPPID stands for preferred supplier id and refers to TSUPPLIER.SUPPID. |
| TCANSUPPLY | SUPPID INT, OURPRODID INT, THEIRPRODID VARCHAR(50), PURCHPRICE DECIMAL(9,2) | The table TCANSUPPLY contains one record per product that a supplier can supply. The combination (SUPPID, OURPRODID) is its primary key, where SUPPID refers TSUPPLIER.SUPPID and OURPRODID to TPRODUCT.OURPRODID. |
The random data loader
The windows application Org.RelationalArtist.CookBook.LoaderGUI facilitates loading the example tables with random data. It will ask you to fill in the number of suppliers, the number of products and the average number of products per supplier. In order to increase or decrease the variation in supplier -and product names, it will ask you to fill in the number of random characters to start supplier -and product names with.
Feel free to test performance with increasingly large numbers of records in the tables. Paging performance should be affected only minimally.
The data source
The CookBook assumes you will run the pages under IIS with process isolation. If you insist on multi-threading, you should either add wrappers that will ensure thread safety or else create a new connection for every thread. We define a static singleton, that encapsulates an Sql server data source. Feel free to change the data source to MsAccess or MySql:
using System;
using Org.RelationalArtist.MsSqlServer;
namespace Org.RelationalArtist.CookBook
{
/*-----------------------------------------------
CLASS DEFINITION
-----------------------------------------------*/
///
/// CookBookDb represents the application's database connection.
///
public class CookBookDb
{
/*-----------------------------------------------
CONSTANTS
-----------------------------------------------*/
//hard-coded for the example
public const string SERVERNAME="localhost";
public const string USERID="test";
public const string PASSWORD="test";
public const string DATABASE="test";
/*-----------------------------------------------
CLASS VARIABLES
-----------------------------------------------*/
private static DataSource mDataSource;
private static bool mInitialized;
/*-----------------------------------------------
GET DATA SOURCE
-----------------------------------------------*/
public static DataSource GetDataSource()
{
if(!mInitialized)
{
mDataSource=new DataSource(SERVERNAME, USERID, PASSWORD, DATABASE);
mInitialized=true;
}
return mDataSource;
}
}
}
You may alternatively want to obtain the security context from the web.config file. All entities can now use this singleton to connect to the database.
The data source root entity
We will now define a root entity for all CookBook entities:
using System;
using Org.RelationalArtist.MsSqlServer;
namespace Org.RelationalArtist.CookBook
{
/*-----------------------------------------------
CLASS DEFINITION
-----------------------------------------------*/
///
/// This class defines a CookBookDbEntity entity. All entities in this application
/// derive from here. It's single purpose is to indicate what the data source is.
///
public abstract class CookBookDbEntity: Entity
{
/*-----------------------------------------------
CONSTRUCTOR
-----------------------------------------------*/
public CookBookDbEntity(string pTableName):base(pTableName) {}
/*-----------------------------------------------
GET DATA SOURCE -- MUST IMPLEMENT
-----------------------------------------------*/
public override IDataSource GetDataSource()
{
return CookBookDb.GetDataSource();
}
}
}
The CookBookDbEntity class implements the abstract Entity class by overriding the GetDataSource() method. This method points to the CookBookDb singleton.
The Supplier entity
We will now implement the Supplier entity. It inherits from the CookBookDbEntity and creates an object representation for the Supplier table and its rows:
using System;
namespace Org.RelationalArtist.CookBook
{
/*-----------------------------------------------
CLASS DEFINITION
-----------------------------------------------*/
///
/// This class defines a Supplier entity.
///
public class EntSupplier: CookBookDbEntity
{
/*-----------------------------------------------
CONSTANTS
-----------------------------------------------*/
public const string ENTITYNAME="TSUPPLIER";
//fields
public const string fSUPPID="SUPPID";
public const string fSUPPNAME="SUPPNAME";
public const string fSUPPADDRESS="SUPPADDRESS";
/*-----------------------------------------------
CONSTRUCTOR
-----------------------------------------------*/
public EntSupplier():base(ENTITYNAME)
{
AddPrimaryIntegerField(fSUPPID);
AddStringField(fSUPPNAME);
AddStringField(fSUPPADDRESS);
}
/*-----------------------------------------------
CLONE -- MUST IMPLEMENT
-----------------------------------------------*/
public override object Clone()
{
EntSupplier clone=new EntSupplier();
clone.EntityFields=(Fields) mFields.Clone();
return clone;
}
/*-----------------------------------------------
FIND BY PRIMARY KEY
-----------------------------------------------*/
public bool FindByPrimaryKey(int pSuppId)
{
this.SuppId=pSuppId;
return FindByPrimaryKey();
}
/*-----------------------------------------------
TYPED ACCESSORS: SUPP ID
-----------------------------------------------*/
public int SuppId
{
get {return (int)mFields[fSUPPID];}
set {mFields[fSUPPID]=value;}
}
/*-----------------------------------------------
TYPED ACCESSORS: SUPP NAME
-----------------------------------------------*/
public string SuppName
{
get {return (string)mFields[fSUPPNAME];}
set {mFields[fSUPPNAME]=value;}
}
/*-----------------------------------------------
TYPED ACCESSORS: SUPP ADDRESS
-----------------------------------------------*/
public string SuppAddress
{
get {return (string)mFields[fSUPPADDRESS];}
set {mFields[fSUPPADDRESS]=value;}
}
}
}
The EntSupplier class specifies the table name and table fields in its constants. Next, it creates typed accessors for each field. Note that it fulfills its final obligation to the abstract Entity class, by supplying a Clone() method.
For each table, you will find an entity class that specifies strongly typed fields. It is almost sufficient to represent the data model in object-accessible form. In order to represent the data model fully, it must also indicate the relations between the entities. For example, in order to represent the relation between EntSupplier and EntProduct, the EntProduct class specifies that you can resolve its related preferred supplier:
Resolving related entities
using System;
namespace Org.RelationalArtist.CookBook
{
/*-----------------------------------------------
CLASS DEFINITION
-----------------------------------------------*/
///
/// This class defines a Product entity.
///
public class EntProduct: CookBookDbEntity
{
/*-----------------------------------------------
CONSTANTS
-----------------------------------------------*/
...
/*-----------------------------------------------
CONSTRUCTOR
-----------------------------------------------*/
...
/*-----------------------------------------------
CLONE -- MUST IMPLEMENT
-----------------------------------------------*/
...
/*-----------------------------------------------
FIND BY PRIMARY KEY
-----------------------------------------------*/
...
/*-----------------------------------------------
TYPED ACCESSORS
-----------------------------------------------*/
...
/*-----------------------------------------------
RESOLVE PREF SUPPLIER
-----------------------------------------------*/
public EntSupplier ResolvePrefSupplier()
{
EntSupplier prefSupplier=new EntSupplier();
if(prefSupplier.FindByPrimaryKey(this.PrefSuppId))
{
return prefSupplier;
}
else
{
return null;
}
}
}
}
This enables you to navigate the data model with the object expressions like:
Console.Writeln("Preferred supplier:" + product.ResolvePrefSupplier().SuppName);
The Supplier entity page
Once the entities are defined, we can use them in our Entity pages. An entity page allows us to page through a query on the entities. The simples example would be to page through all suppliers, without conditions:
using System;
using Org.RelationalArtist.MsSqlServer;
namespace Org.RelationalArtist.CookBook
{
/*-----------------------------------------------
CLASS DEFINITION
-----------------------------------------------*/
///
/// This class defines a supplier entity page.
///
public class EntSupplierPage: EntityPage
{
/*-----------------------------------------------
CONSTRUCTOR
-----------------------------------------------*/
public EntSupplierPage(): base(new EntSupplier())
{
this.MaxRecords=10;
}
/*-----------------------------------------------
FIRST
-----------------------------------------------*/
public EntSupplier First
{
get {return (EntSupplier) GetFirst(); }
}
/*-----------------------------------------------
LAST
-----------------------------------------------*/
public EntSupplier Last
{
get {return (EntSupplier) GetLast(); }
}
/*-----------------------------------------------
FIND FIRST PAGE -- NO CONDITIONS
-----------------------------------------------*/
public void FindFirstPageNoConditions()
{
InitFields(0);
FindFirstPage();
}
/*-----------------------------------------------
FIND NEXT PAGE -- NO CONDITIONS
-----------------------------------------------*/
public void FindNextPageNoConditions(int pLastOfSuppId)
{
InitFields(pLastOfSuppId);
FindNextPage();
}
/*-----------------------------------------------
FIND LAST PAGE -- NO CONDITIONS
-----------------------------------------------*/
public void FindLastPageNoConditions()
{
InitFields(int.MaxValue);
FindLastPage();
}
/*-----------------------------------------------
FIND PREVIOUS PAGE -- NO CONDITIONS
-----------------------------------------------*/
public void FindPreviousPageNoConditions(int pFirstOfSuppId)
{
InitFields(pFirstOfSuppId);
FindPreviousPage();
}
/*-----------------------------------------------
INIT FIELDS
-----------------------------------------------*/
private void InitFields(int pSuppId)
{
ResetFields();
AddKeyField(EntSupplier.fSUPPID, pSuppId);
}
}
The EntSuppliersPage class inherits the abstract EntityPage class. It defines the convenience methods First() and Last() for the first and last entity on the page. Next, it defines the forward paging methods:
void FindFirstPageNoConditions(); void FindNextPageNoConditions(int pLastOfSuppId);
The InitFields() method indicates (sort) keys and conditions. In this example it is relatively simple.
For the first page, you initialize the EntSupplierPage with FindFirstPageNoConditions(). It is important to store the last id of the page you will obtain. You can access it with EntSupplierPage.First.SuppId. To obtain the next page you can initialize the EntSupplierPage with FindFirstPageNoConditions(int pFirstOfSuppId). In this way you can chain through all pages. The Entity page will tell you if there are next pages in the property HasNextPage. Chaining backward if very similar:/P>
void FindLastPageNoConditions(); void FindPreviousPageNoConditions(int pFirstOfSuppId);
Behind the scenes, the EntityPage class will generate the SQL appropriate the retrieve the next chunk of records from the data source:
| FindNextPageNoConditions(10) | SELECT TOP 11 * FROM TSUPPLIER WHERE (SUPPID>10) ORDER BY SUPPID |
You can inspect the SQL generated with the inherited method GetPagerSql().
This is the relatively simple case in which the results are sorted by primary key and the primary key consists of just one column; and in which there are no additional conditions. You will find that the library is relatively simple to configure for much more complex settings.
The Supplier Entity Page: sorted by non-primary keys
The EntSupplierPage results are sorted by their primary keys. If you want to sort by keys that not the primary keys of the result, the class should inherit from EntityPageNPK instead of EntityPage. In the following example, the results are sorted by SuppName:
using System;
using Org.RelationalArtist.MsSqlServer;
namespace Org.RelationalArtist.CookBook
{
/*-----------------------------------------------
CLASS DEFINITION
-----------------------------------------------*/
///
/// This class defines a supplier entity Page NPK.
///
public class EntSupplierPageNPK: EntityPageNPK
{
/*-----------------------------------------------
CONSTRUCTOR
-----------------------------------------------*/
public EntSupplierPageNPK(): base(new EntSupplier())
{
this.MaxRecords=10;
this.NumberOfRecordsToRead=12;
}
/*-----------------------------------------------
FIRST
-----------------------------------------------*/
public EntSupplier First
{
get {return (EntSupplier) GetFirst(); }
}
/*-----------------------------------------------
LAST
-----------------------------------------------*/
public EntSupplier Last
{
get {return (EntSupplier) GetLast(); }
}
/*-----------------------------------------------
FIND FIRST PAGE NO CONDITIONS
-----------------------------------------------*/
public void FindFirstPageNoConditions()
{
InitFields(String.Empty, 0);
FindFirstPage();
}
/*-----------------------------------------------
FIND LAST PAGE NO CONDITIONS
-----------------------------------------------*/
public void FindLastPageNoConditions()
{
InitFields(String.Empty, int.MaxValue);
FindLastPage();
}
/*-----------------------------------------------
FIND NEXT PAGE NO CONDITIONS
-----------------------------------------------*/
public void FindNextPageNoConditions(string pLastOfSuppName, int pLastOfSuppId)
{
InitFields(pLastOfSuppName, pLastOfSuppId);
FindNextPage();
}
/*-----------------------------------------------
FIND PREVIOUS PAGE NO CONDITIONS
-----------------------------------------------*/
public void FindPreviousPageNoConditions(string pFirstOfSuppName, int pFirstOfSuppId)
{
InitFields(pFirstOfSuppName, pFirstOfSuppId);
FindPreviousPage();
}
/*-----------------------------------------------
INIT FIELDS
-----------------------------------------------*/
private void InitFields(string pSuppName, int pSuppId)
{
ResetFields();
AddKeyField(EntSupplier.fSUPPNAME, pSuppName);
AddPrimaryKeyField(EntSupplier.fSUPPID,pSuppId);
}
}
}
The number of records in the page, MaxRecords, is 10. We must, however, also indicate in the property NumberOfRecordsToRead how many records the library should try to read in a first attempt to constitute a page of 10 records (NumberOfRecordsToRead=12). If the library fails to read the whole page, it will systematically increase the number of records to read and try again. In case the sort keys contain no duplicates -- which is the case if they are primary keys, the sufficient number of records to read would be MaxRecords + 2. The less variation there is in the sort keys, the larger the number of records to read should be. It is possible to compute the optimal number of records to read, based on the statistical variation in the sort keys, minimizing the total cost of reading by balancing the number of re-tries versus the cost of excessive reading that is incurred by enlarging the number of records to read.
Browsing forward (or backward) requires that you indicate the last (first) sort keys (SuppName) as well as the last (first) primary keys (SuppId).
The SQL generated is:
| FindNextPageNoConditions(10) | SELECT TOP 21 * FROM TSUPPLIER WHERE (SUPPNAME>='SUPP A') ORDER BY SUPPNAME , SUPPID |
The Product entity page: sorted by non-primary keys
In the following example, we will page through the products, sorted by name, for a chosen supplier. Since we will sort by product name, the entity to create a page on, is the EntProduct class. Since the product name is not the primary key for this table, we need to derive from the class EntityPageNPK:
using System;
using Org.RelationalArtist.MsSqlServer;
namespace Org.RelationalArtist.CookBook
{
/*-----------------------------------------------
CLASS DEFINITION
-----------------------------------------------*/
///
/// This class defines a product entity Page NPK.
///
public class EntProductPageNPK: EntityPageNPK
{
/*-----------------------------------------------
CONSTRUCTOR
-----------------------------------------------*/
public EntProductPageNPK(): base(new EntProduct())
{
this.MaxRecords=10;
this.NumberOfRecordsToRead=12;
}
/*-----------------------------------------------
FIRST
-----------------------------------------------*/
public EntProduct First
{
get {return (EntProduct) GetFirst(); }
}
/*-----------------------------------------------
LAST
-----------------------------------------------*/
public EntProduct Last
{
get {return (EntProduct) GetLast(); }
}
/*-----------------------------------------------
FIND FIRST PAGE FOR SUPPLIER
-----------------------------------------------*/
public void FindFirstPageForSupplier(int pSuppId)
{
InitFields(pSuppId, String.Empty, 0);
FindFirstPage();
}
/*-----------------------------------------------
FIND LAST PAGE FOR SUPPLIER
-----------------------------------------------*/
public void FindLastPageForSupplier(int pSuppId)
{
InitFields(pSuppId, String.Empty, int.MaxValue);
FindLastPage();
}
/*-----------------------------------------------
FIND NEXT PAGE FOR SUPPLIER
-----------------------------------------------*/
public void FindNextPageForSupplier(int pSuppId, string pLastOfProdName, int pLastOfOurProdId)
{
InitFields(pSuppId, pLastOfProdName, pLastOfOurProdId);
FindNextPage();
}
/*-----------------------------------------------
FIND PREVIOUS PAGE FOR SUPPLIER
-----------------------------------------------*/
public void FindPreviousPageForSupplier(int pSuppId, string pFirstOfProdName, int pFirstOfOurProdId)
{
InitFields(pSuppId, pFirstOfProdName, pFirstOfOurProdId);
FindPreviousPage();
}
/*-----------------------------------------------
INIT FIELDS
-----------------------------------------------*/
private void InitFields(int pSuppId, string pProdName, int pOurProdId)
{
ResetFields();
AddConditionExists(GetSuppIdExistsStatement(pSuppId));
AddKeyField(EntProduct.fPRODNAME, pProdName);
AddPrimaryKeyField(EntProduct.fOURPRODID,pOurProdId);
}
/*-----------------------------------------------
GET SUPP ID EXISTS STATEMENT
-----------------------------------------------*/
private StatementSelect GetSuppIdExistsStatement(int pSuppId)
{
StatementSelect statement=new StatementSelect(EntCanSupply.ENTITYNAME);
statement.AddConditionJoin(EntCanSupply.fOURPRODID,
EntProduct.ENTITYNAME, EntProduct.fOURPRODID);
statement.AddWhereField(EntCanSupply.fSUPPID, pSuppId,true);
return statement;
}
}
}
For a chosen SuppId, we must indicate last (first) sort key, ProdName, and last (first) primary key, OurProdId. Which supplier can supply what products, is indicated in the table TCANSUPPLY. Therefore, we may page through the table TPRODUCT, but we must indicate the condition in TCANSUPPLY. We can achieve this, by adding an SQL EXISTS clause to the query, and, in this clause, join with TCANSUPPLY.
The SQL generated is:
| FindNextPageForSupplier(SuppId=65, ProdName="PRD TW", LastOfOurProdId=1694) | SELECT TOP 12 * FROM TPRODUCT WHERE (PRODNAME>=' PRD TW') AND EXISTS (SELECT * FROM TCANSUPPLY WHERE SUPPID=65 AND OURPRODID=TPRODUCT.OURPRODID) ORDER BY PRODNAME , OURPRODID |
In the next topic, we will create web pages that will use the classes above.
Example with multiple sort keys and multiple primary key fields
We will now walk through a last example that pages through results sorted by multiple sort keys for results having multiple fields in their primary key. You can find the example in the NUnitTest name spaces for MsAccess, MsSqlServer, and MySql.
Suppose we want to page through the table, for OTHERFIELD1="ABC" and OTHERFIELD starting with "A":
TBLEXAMPLE(PKEY1, PKEY2, PKEY3, SORTFIELD1, SORTFIELD2, OTHERFIELD1, OTHERFIELD2)
The initialization of the fields would be as following:
/*-----------------------------------------------
INIT FIELDS
-----------------------------------------------*/
private void InitFields(
string pOtherField1,
string pOtherField2,
string pSortField1,
string pSortField2,
string pSortField3,
string pPKey1,
string pPKey2,
string pPKey3)
{
ResetFields();
AddPrimaryKeyField(EntExample.fPKEY1,pPKey1);
AddPrimaryKeyField(EntExample.fPKEY2,pPKey2);
AddPrimaryKeyField(EntExample.fPKEY3,pPKey3);
AddKeyField(EntExample.fSORTFIELD1, pSortField1);
AddKeyField(EntExample.fSORTFIELD2, pSortField2);
AddKeyField(EntExample.fSORTFIELD3, pSortField3);
AddWhereField(EntExample.fOTHERFIELD1, pOtherField1);
AddConditionLikePrefix(EntExample.fOTHERFIELD2, pOtherField2);
}
The SQL generated is:
| FindNextPage(OtherField1="ABC", OtherField2="A", SortField1="SortField1", SortField2="SortField2", LastPKey1="PKEY1", LastPKey2="PKEY2", LastPKey3="PKEY3") |
SELECT TOP 20 * FROM TBLEXAMPLE WHERE ((SORTFIELD1>='SORTFIELD1') OR (SORTFIELD1='SORTFIELD1' AND SORTFIELD2>='SORTFIELD2') OR (SORTFIELD1='SORTFIELD1' AND SORTFIELD2='SORTFIELD2' AND SORTFIELD3>='SORTFIELD3')) AND OTHERFIELD1='ABC' AND OTHERFIELD2 LIKE 'A%' ORDER BY SORTFIELD1 , SORTFIELD2 , SORTFIELD3 , PKEY1 , PKEY2 , PKEY3 |
A first attempt to retrieve the page would limit the number of records to 20. If the variation in the sort fields is insufficient to retrieve 10 records from these 20, the EntityPageNPK class will retry in one or more new attempts with successively larger limits. It is important to set this limit sufficiently large to avoid retries, but not too large, to avoid excessive stress on the data source.