Using OpenPetra's DataAccess Objects

Purpose of OpenPetra's DataAccess Objects

OpenPetra's DataAccess Objects form an Object-Relational Mapper (ORM [1]). The ORM relieves the programmer from the need to write SQL commands and 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!

OpenPetra's DataAccess Objects are auto-generated from the \db\petra.xml file and use the RDMBS-agnostic Database Access Object for the execution of SQL commands (DataAccess Objects are therefore not tied to a specific RDBMS!).

An overview of OpenPetra's DataAccess Objects can be found here.

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 by the database table whose DataAccess Method you are using (direct relationship), or
2. that itself references the 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 reciprocal relationship is found from the p_location database table to the p_partner database table although there is no direct or reciprocal relationship between those database tables. The reciprocal relationship is found via the p_partner_location database table in this case, which establishes a 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.

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

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

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

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

TODO

DeleteUsingTemplate Methods (overloaded)

TODO

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 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 of 'Table' appended (e.g. 'PPartnerTable' for the p_partner database table).