Using OpenPetra's DataAccess Objects

Overview

Purpose of OpenPetra's DataAccess Objects

OpenPetra's DataAccess Objects form an Object-Relational Mapper (ORM [1]).

The OpenPetra DataAccess ORM ...

• relieves the programmer from the need to write SQL commands;
• provides compile-time checks as the DataAccess Objects expose Methods with Typed Arguments. Contrast that with SQL commands, which are simple strings embedded in source code. No compile-time checks (such as for correctness of syntax and existence of a certain DB table) can be done on those;
• ensures correctly formed SQL commands are generated that work across all supported RDBMS's;
• always uses Parameterized Queries instead of Dynamic Queries (in Dynamic Queries, query arguments are simply turned into strings and are part of the SQL command string). Using Parameterized Queries has two advantages:
  • it goes a long way in making SQL Injection Attacks more difficult and allows for 'Prepared Statements' which can be executed faster by an RDBMS when thay are executed more often;
  • query arguments cannot be mis-interpreted by different RDBMS. For instance, different RDBMS's can understand date query arguments differently, e.g. interpreting the argument string as day-month-year or as month-day-year. This cannot happen with Prepared Statements as the data type of a query argument and the value of a query argument are passed to the RDBMS in a defined and type-safe way that is guaranteed to be understood correctly by each RDBMS.

Functionality

The OpenPetra DataAccess ORM ...

• performs deserialisation of data contained in database tables and serialisation of data in DataTables to DB tables, thereby relieving the programmer from writing SQL commands for ...
  • reading (SELECT)
  • writing (INSERT/UPDATE)
  • deleting (DELETE)
  • additional functionality, such as counting (SELECT COUNT(*)), checking for existence of records
• manages Optimistic Locking of database records
• provides a common exception model for database errors, constraint violations and optimistic locking
• All the Methods that expect data to be passed in or that return data work with Typed DataRows, Typed DataTables and Typed DataSets.
• Planned functionality
  • Should provide segmenting of data (record- and field level access security based on user's access rights)

Limitations

• Data from exactly one DB Table can be retrieved using a given DataAccess object.
  • Although a JOIN might be done by some of the Load... and Count... Methods, only the data of the database table whose DataAccess Object the the programmer uses can be retrieved.
• When using any of the Load... methods, parameters for the WHERE clause can be submitted (using a Collection or a TSearchCriteria array), but the parameters are all AND-ed. OR-ing of the parameters is not possible. Other limitations to such parameters apply as well.

DataAccess Objects are Auto-Generated

OpenPetra's DataAccess Objects are automatically generated for each DB table in the \db\petra.xml file (this file holds the complete definition of the OpenPetra DB). They are organised into Namespaces according to OpenPetra Modules/Submodules. These associations come from the \db\petra.xml file. Example Namespace: Ict.Petra.Server.MPartner.Partner.Data.Access (found in file Partner.Access.cs).

Performance

Good performance was very much in our minds when designing OpenPetra's DataAccess Object ORM.

• The source code for the DataAccess Objects is created using a code generator whenever a change to the OpenPetra database occurs. It does therefore not occur 'on-the-fly' at run-time, as is sometimes the case with certain ORM implementations.
  • The command to re-generate the DataAccess Objects is nant generateORM.
• The DataAccess Objects' methods are all static and the Methods they might call in the TTypedDataAccess Object are all static as well. This means that the calling of DataAccess Methods don't necessitate the instantiation of Objects on which those Methods can be called, which is good not only for speed but also for memory and Garbage Collection reasons.
• Some of the Methods in the DataAccess Objects and in the TTypedDataAccess Object consist of little source code. Because of that, the C# compiler might choose to compile them 'in-line' at compile-time, which means that the compiler 'spells out' the code of the called Method directly into the Method that is calling it. The result of such 'in-lining' is that at run-time the code of the called Method is executed just as other code that calls that Method. That means that a calls to the called Method is not necessary, speeding up things. (For a more complete explanation of 'in-lining', check out the introduction of this Wikipedia article.)

Because of those facts the DataAccess Objects perform very well.

Note: The execution of the database commands might be a bit slower than with 'on-the-fly stringed-together' Dynamic Queries because the DataAccess Objects use Parameterized Queries exclusively, but that is done for security reasons (to help protect against SQL Injection Attacks) and comes at a small price that is well worth paying, considering the solid advantage it gives (read this section for more advantages of Parameterized Queries).

Selectively Increasing the Performance of Queries

Since the DataAccess Objects exclusively generate Parameterized Queries, this allows for 'Prepared Statements' which are faster when they are executed more than once or twice.

'Prepared Statements' are 'compiled' once by the RDBMS. At that time the parameters are parsed and the query execution plan is figured out by the RDBMS. That compiled ('prepared') statement is kept in memory by the RDBMS and it will be used every time the same SQL query (=the same DataAccess Method overload) is executed within the scope of the same database connection - even if the value of the parameters of the SQL query change. Note: Not all RDBMS's support Prepared Statements (PostgreSQL, MySQL and SQLite DO support it), and the speed gain from preparing a SQL query might vary somewhat from RDBMS to RDMBS. More details about Prepared Statments can be found here (for PostgreSQL) or here (for MySQL), for example.

If a programmer wants to execute a Prepared Statement, (s)he needs to turn on that feature for every single DataAccess Method call.

• By default, no Prepared Statements are created.
• To tell the RDMBS that the next (and only the next) DataAccess Method call should be executed as Prepared Statement, call the static Method 'DBAccess.GDBAccess.PrepareNextCommand()' before calling the DataAccess Method. After the DataAccess Method call executes, all DataAccess Method calls that follow it are again executed non-prepared.
  • Should a RDBMS not support Prepared Statements, no error is given (and there won't be a speed gain, obviously). (PostgreSQL, MySQL and SQLite DO support Prepared Statements.)

Use The DataAccess Objects As Much As Possible !!!

Because of the many advantages listed in the preceding sections, OpenPetra's DataAccess ORM should be used wherever possible. Only resort to database access without DataAccess Objects when you hit limitations of the DataAccess Objects.

Reading Data From The Database

Data is read using one of the many Load... Methods of a DataAccess Object. These Methods all result in a SQL 'SELECT ...' command of some sort.

Remarks:

All the Methods in this section are heavily overloaded. Please see General Arguments for Load... Methods for details about one reason for the many overloads. The other reason for the overloads is that the Methods can either return the read-in data in a specific Typed DataTable or merge the read-in data into an existing Typed DataSet. The methods with the first approach return a new Typed DataTable, the ones with the latter approach return void.

When choosing which overload to use, first decide whether you need a new Typed DataTable returned from the Method call or whether you need a Typed DataTable merged into an already existing DataSet. This narrows down the overloads that will pertain to your situation.

The following Methods exist:

LoadAll Methods (overloaded)

Loads all data that is contained in the specified database table.

Sample SQL code generated:

SELECT * FROM p_partner;

LoadByPrimaryKey Methods (overloaded)

Loads the single data row of the given database table that matches the specified Primary Key in the specified database table.

Starting from either the first Argument or the second Argument, as many Arguments as make up the Primary Key of the chosen database table need to be supplied when calling the Method, followed by other Arguments that make up the overloads.

• Example 1: The p_partner database table's Primary Key consists of only one column, p_partner_key_n, so either as the first or second Argument you need to supply the Argument 'Int64 APartnerKey' when calling the Method.
• Example 2: The a_batch database table's Primary Key consists of two columns, a_ledger_number_i and a_batch_number_i, so from either the first or second Argument onwards you need to supply the following two Arguments 'Int32 ALedgerNumber, Int32 ABatchNumber' when calling the Method.

Sample SQL code generated:

SELECT * FROM p_partner where p_partner_key_n = 0029001234;
SELECT * FROM a_batch where a_ledger_number_i = 43 AND a_batch_number_i = 10;

LoadByUniqueKey Methods (overloaded) [available only for some DB Tables]

Loads the single data row of the given database table that matches the specified Unique Key in the specified database table. This Method is only available for database tables that have a Unique Key (also know as a 'Unique Index') specified, apart from the Primary Key.

Starting from either the first Argument or the second Argument, as many Arguments as make up the Unique Key of the chosen database table need to be supplied when calling the Method, followed by other Arguments that make up the overloads.

• Example 1: The p_venue database table's Unique Key consists of only one column, p_venue_code_c, so either as the first or second Argument you need to supply the Argument 'String AVenueCode' when calling the Method.
• Example 2: The p_person database table's Unique Key consists of two columns, p_family_key_n and p_family_id_i, so from either the first or second Argument onwards you need to supply the following two Arguments 'Int64 AFamilyKey, Int32 AFamilyId' when calling the Method.

Sample SQL code generated:

SELECT * FROM p_venue where p_venue_code_c = 'ABC';
SELECT * FROM p_person where p_family_key_n = 0029001234 AND p_family_id_i = 2;

LoadVia... Methods (overloaded)

Loads the data row(s) of the given database table that match the specified Primary Key of another database table that ...

1. is either referenced to (using a Foreign Key) by the database table whose DataAccess Method you are using (direct relationship), or
2. that itself references (using a Foreign Key, or using another database table 'in the middle') the database table whose DataAccess Method you are using (reciprocal relationship). Note: The reciprocal relationships are discovered even indirectly via other database tables!
   1. Example: A 'many-to-many (n:m)' relationship is found from the p_location [2] database table to the p_partner [3] database table although there is no direct or reciprocal relationship between those database tables. The reciprocal relationship is found via the p_partner_location [4] database table in this case, which establishes the 'many-to-many (n:m)' relationship between the p_partner and p_location database tables. Hence also several records might be returned when the LoadViaPLocation Method of the PPartnerAccess Object is used.

Note: There can be many LoadVia... Methods on a given database table's DataAccess object, in fact more than you might anticipate, because of the many reciprocal links (direct and indirect) between the many database tables in the whole database!

These Methods provide a convenient method of loading specific data row(s) of a given database table for which you don't know the Primary Key in that database table, but where you know the Primary Key of another database table that is in direct or reciprocal relationship (even indirectly) with the given database table.

Example: For the example given above we use the following overload to return all Partners (held in the p_partner [5] database table) that are n:m linked to a Location (held in the p_location [6] database table) whose Primary Key is a compound key and whose values are '0' for the SiteKey '3' for the LocationKey:

public static PPartnerTable LoadViaPLocation(Int64 ASiteKey, Int32 ALocationKey, TDBTransaction ATransaction)

This overload is called in program code like this (ReadTransaction needs to be a started database transaction):

Int64 SiteKey = 0;
Int32 LocationKey = 3;
PPartnerTable MyPartnersDT = PPartnerAccess.LoadViaPLocation(SiteKey, LocationKey, ReadTransaction);

SQL code generated from the example above:

SELECT p_partner.* FROM p_partner, p_partner_location 
 WHERE p_partner_location.p_partner_key_n = p_partner.p_partner_key_n 
   AND p_partner_location.p_site_key_n = 0 AND p_partner_location.p_location_key_i = 3

LoadUsingTemplate Methods (overloaded)

Loads the data row(s) of the given database table that match the specified template.

The template can be specified in one of two ways:

• A Typed DataRow is supplied that contains (a) value(s) in the DataColumn(s) on which the WHERE clause of the generated SQL command should filter.
  • The Typed DataRow that is supplied needs to contain all the DataColumns of the Typed DataTable that results from the DataAccess Method.
    • A Typed DataRow like this can be obtained using the MyTypedDataTable.NewRowTyped(false) Method.
  • An overload allows to specify the Argument StringCollection ATemplateOperators. Use this to change the criteria comparison, which defaults to equals (' = '), or 'LIKE' in case of a string. Add one Operator per Column that is set to a value, in the order of the Columns as they are specified for the database table.
    • Important: If you use Operators you are responsible for the correctness of the syntax of the SQL that results. For that reason use only the standard ANSI SQL-92 operators and not RDBMS-specific operators, as these would not work across the range of the RDBMS's that are supported by OpenPetra! The correctness of the syntax can only be proven at run-time.
    • To create the needed StringCollection easily and on-the-fly, use one of the following static Methods in Ict.Common.StringHelper: InitStrArr or StrSplit.
• A TSearchCriteria array is supplied that specifies the column(s) and value(s) on which the WHERE clause of the generated SQL command should filter.
  • The TSearchCriteria Class has a Constructor that takes the name of a database column and a value.
    • Note: The criteria comparison defaults to equals (' = '), but can be changed by altering an TSearchCriteria's instance's public 'comparator' Field.
    • Important: When specifying the name of the database column, please don't hardcode it's name! Instead, use the static 'Get...DBName' Method of the Typed DataTable that contains that database column to get the name of the database column. Using this method we can ensure that source code that uses a TSearchCriteria instance will not compile in case a database column has been renamed or does no longer exist. This could not be prevented if a simple string is used as the compiler does not check the string's content!
    • Important: When specifying the value of the database column you are responsible that the value can be sucessfully assigned to an OdbcParameter of the OdbcType that matches the database column! Whether this works can only be proven at run-time.

LoadVia...Template Methods (overloaded)

A very convenient and powerful combination of the LoadVia... Methods and the LoadUsingTemplate Methods. It loads the data row(s) of the given database table that match the specified 'Template Row' of another database table.

General Arguments for Load... Methods

StringCollection AFieldList: Specifying Columns That Should Be Returned

• If an overload without that Argument is chosen, all Columns of the database Table are returned.
• If an overload with that Argument is chosen, specify the names of Columns that should be returned. Only those Columns will be returned, plus the Primary Key Columns, plus the Column 's_modification_id_c'. The latter is used for tracking changes in database rows - that Column exists in every database table of OpenPetra and its content is automatically maintained by the DataAccess Methods that write to the database in any way. It is used for Optimistic Locking and needs to be read in so it can be determined if the read-in record has been changed by somebody else in the meantime, should that record be written to the database.
• To create the needed StringCollection easily and on-the-fly, use one of the following static Methods in Ict.Common.StringHelper: InitStrArr or StrSplit.

StringCollection AOrderBy: Ordering and Grouping of Returned Data

• If an overload without that Argument is chosen, the data is returned as returned by the RDBMS (in case the database Table has got a Primary Key then it will be sorted by this), non-grouped.
• If an overload with that Argument is chosen
  • and the first item in the StringCollection is 'ORDER BY',
    • specify the names of Columns that the returned data should be sorted by.
      • You can postfix the name of a Column with ' DESC' to specify that descending sort order should be used for this Column. Optionally, you can also postfix the name of a Column with ' ASC' to specify that ascending sort order should be used for this Column.
  • and the first item in the StringCollection is 'GROUP BY',
    • specify the names of Columns that the returned data should be grouped by.
• To create the needed StringCollection easily and on-the-fly, use one of the following static Methods in Ict.Common.StringHelper: InitStrArr or StrSplit.

Example:

AGiftAccess.LoadViaPPartner(LastGiftDS, APartnerKey, null, ReadTransaction, 
    StringHelper.InitStrArr(new String[] { "ORDER BY", AGiftTable.GetDateEnteredDBName() + " DESC" }), 0, 1);

int AStartRecord, int AMaxRecords: Limiting The Records Returned

• If an overload without those Arguments is chosen, all records are returned from the result of the database query that is executed.
• If an overload with those Arguments is chosen then the records that lie before AStartRecord and that lie after AMaxRecords in the found DataRows are discarded before the Method returns the result of the database query that is executed.
  • Note: Currently no optimised queries for the underlying RDBMS are executed (this would be done using e.g. the LIMIT Clause of PostgreSQL). That means that the database query that is executed selects all records it can select and the records that don't fit the AStartRecord and AMaxRecords criteria are simply deleted from memory after it returns from the database. That means that there is currently no speed gain in executing a DataAccess Method with those Arguments - it just spares you the task of deleting the unwanted DataRows by yourself!

Counting of Data And Checking For Existence of Data In The Database

Counting of Data In The Database

Records are counted using one of the many Count... Methods of a DataAccess Object. These Methods all result in a SQL 'SELECT COUNT(*)...' command of some sort. All 'Count...' Methods return an int.

The following Methods exist:

CountAll Method

Counts all records that are contained in the specified database table.

Sample SQL code generated:

SELECT COUNT(*) FROM p_partner;

CountVia... Method

Works the same way than the overloaded LoadVia... Methods in the Reading Data From The Database section, but it counts the records that result from the query instead of returning the records.

Sample SQL code generated from the example that is mentioned in the above section:

SELECT COUNT(*) FROM p_partner, p_partner_location 
 WHERE p_partner_location.p_partner_key_n = p_partner.p_partner_key_n 
   AND p_partner_location.p_site_key_n = 0 AND p_partner_location.p_location_key_i = 3

CountUsingTemplate Methods (overloaded)

Works the same way than the overloaded LoadUsingTemplate... Methods in the Reading Data From The Database section, but it counts the records that result from the query instead of returning the records.

CountVia...Template Methods (overloaded)

A very convenient and powerful combination of the CountVia... Method and the CountUsingTemplate Methods. It counts the records(s) of the given database table that match the specified 'Template Row' of another database table.

Checking For Existence of Data In The Database

Exists Method

Checks if a record exists with the specified Primary Key. It returns true if it exists, otherwise false.

Writing And Deleting Data From The Database

SubmitChanges Methods (overloaded)

TODO

Exception Handling

AddOrModifyRecord Method

If a record with the specified primary key already exists in the database, that record will be updated, otherwise a new record will be added.

DeleteByPrimaryKey Method

Deletes a record in the database that matches the specified Primary Key.

DeleteUsingTemplate Methods (overloaded)

Works the same way than the overloaded LoadUsingTemplate... Methods in the Reading Data From The Database section, but it deletes the records that result from the query instead of returning the records.

Other Functionalities

GetSafeValue Methods (overloaded) [from Ict.Common.Data.TTypedDataAccess]

This Method returns either a valid Object or System.DBNull.

NotEquals Method [from Ict.Common.Data.TTypedDataAccess]

Compares the original and the current value of a DataColumn in a DataRow.

DBTABLENAME and DATATABLENAME Public Constants

Each DataAccess Object has got these two static Constants.

• 'DBTABLENAME' is the name of the database table that the DataAccess Object accesses - as found in the database (e.g. 'p_partner' for the p_partner database table).
• 'DATATABLENAME' is the name of the database table that the DataAccess Object accesses - as found in the database and 'CamelCased' (e.g. 'PPartner' for the p_partner database table).
  • Note: The name of the Typed DataTable that represents the database table starts with this string and has 'Table' appended (e.g. 'PPartnerTable' for the p_partner database table).