Database access architecture: Difference between revisions

From OpenPetra Wiki
Jump to navigation Jump to search
m (1 revision)
 
 
(5 intermediate revisions 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)


== Ict.Common.DB.TDataBase ==
== 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:
The following main subjects are addressed in this class:
* Connection
* Connection
Line 31: Line 34:
== supported database systems ==
== supported database systems ==
see [[Notes about PostgreSQL]] (recommended), [[Notes about SQLite]], [[Notes about MySQL]] (not yet implemented)
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

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: