Database access architecture: Difference between revisions
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 | == Winforms generated from typed tables == | ||
* http://openpetraorg.git.sourceforge.net/git/gitweb.cgi?p=openpetraorg/openpetraorg;a=tree;f=csharp/ICT/PetraTools/ | 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
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:
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:
here is an example of the result, the generated code for winforms with data display and data storing: