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

see also http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Common/DB/Access.cs;hb=master

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

supported database systems

see Notes about PostgreSQL (recommended), Notes about SQLite, Notes about MySQL (not yet implemented)

We are using these libraries, or newer versions (see also the readme files in http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=tree;f=csharp/ThirdParty;hb=master):

• http://dev.mysql.com/downloads/connector/net/6.1.html mysql-connector-net-6.1.3-noinstall.zip
• http://sourceforge.net/projects/sqlite-dotnet2
• Npgsql is a .Net data provider for Postgresql. http://pgfoundry.org/projects/npgsql/ Npgsql2.0.7-bin-ms.net.zip

We have implementations for each supported database in

• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Common/DB/PostgreSQL.cs;hb=master
• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Common/DB/MySQL.cs;hb=master
• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Common/DB/SQLite.cs;hb=master
• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Common/DB/ProgressODBC.cs;hb=master

Typed Datastore

We have our own generated Typed Datastore.

Here are the tools to generate the datastore:

• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=tree;f=csharp/ICT/PetraTools/GenerateORM;hb=master

The result is for example:

• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Petra/Shared/lib/MCommon/data/Common.Tables.cs;hb=master
• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Petra/Shared/lib/MCommon/data/Common.DataSets.cs;hb=master

Winforms generated from typed tables

Here are the tools to generate the winforms screens with scripted data links:

• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=tree;f=csharp/ICT/PetraTools/CodeGeneration/Winforms;hb=master

here is an example of the result, the generated code for winforms with data display and data storing:

• http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=blob;f=csharp/ICT/Petra/Client/lib/MFinance/gui/UC_GLTransactions.cs;hb=master