Database access architecture

general principles

the client never has direct access to the database
each appdomain on the server has its own database connection
on the server, only one database user is used, petraserver; access permission is granted based on the Petra user
- see csharp\ICT\Petra\Server\app\Core\DBAccess.cs
use typed datastore (generated in Ict.Petra.Shared for each Module) as much as possible, so that field name changes will cause a compiler bug and no occurance will be missed when the database structure changes
Follow guidance on DB Access Exception Handling
Ict.Common.DB.TDataBase provides an interface to several RDBMS (PostgreSQL, SQLite, etc)

Ict.Common.DB.TDataBase

The following main subjects are addressed in this class:

Connection
Transaction
read and increase a sequence
Reading data with a data adapter
reading data in one go
Writing data

There are three general types of access to the database:

Select: Read data from the database into an adapter or a datatable, or a dataset
ExecuteNonQuery: Delete, or update, or insert: changes to the database; no result is returned other than success or failure
ExecuteScalar: just one value is retrieved from the database, eg. Count etc

For each supported RDBMS, these things need to be provided:

format the sql query in a specific way for the RDBMS
convert query parameters
create the data adapter or command in a specific way

IDataBaseRDBMS

This interface (defined in csharp\ICT\Common\DB\Access.cs) needs to be implemented by a class that should provide access to an RDBMS. See the implementation for PostgreSQL as an example (see csharp\ICT\Common\DB\PostgreSQL.cs).