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 retun a Typed DataTable, the ones with the latter approach return void.

The following Methods exist:

LoadAll Methods (overloaded)

TODO

LoadByPrimaryKey Methods (overloaded)

TODO

LoadByUniqueKey Methods (overloaded)

TODO

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