Database access architecture: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
 
(One intermediate revision by one other user not shown)
Line 5: Line 5:
** see csharp\ICT\Petra\Server\app\Core\DBAccess.cs
** 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
* 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 | DB Access Exception Handling]]
* Ict.Common.DB.TDataBase provides an interface to several RDBMS (PostgreSQL, SQLite, etc)
* Ict.Common.DB.TDataBase provides an interface to several RDBMS (PostgreSQL, SQLite, etc)


Line 47: Line 48:
== Typed Datastore ==
== Typed Datastore ==
We have our own generated 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:
The result is for example:
Line 52: Line 56:
* 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
* 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


Here are the tools to generate the datastore:
== Winforms generated from typed tables ==
* http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=tree;f=csharp/ICT/PetraTools/GenerateORM;hb=master
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

Latest revision as of 14:09, 24 April 2014

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

We have implementations for each supported database in

Typed Datastore

We have our own generated Typed Datastore.

Here are the tools to generate the datastore:

The result is for example:

Winforms generated from typed tables

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

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