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

LoadViaForeignKey Methods (overloaded)

TODO

LoadVia... Methods (overloaded)

TODO

LoadVia...Template Methods (overloaded)

TODO

LoadUsingTemplate Methods (overloaded)

TODO

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

CountAll Method

TODO

CountUsingTemplate Methods (overloaded)

TODO

CountViaForeignKey Methods (overloaded)

TODO

CountVia... Method

TODO

CountVia...Template Methods (overloaded)

TODO

Checking For Existence of Data In The Database

Exists Method

Checks if a row exists with the specified primary key.

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