In this document you can information regarding our database model generation and usage.
Current Implementation
Project Structure (Models)
In OneDealer's solutions under the project One.Dealer, in the path Models → OneDealerEntities there is the new models implementation .
Note: |
Create new model
All the existing models have been created with the help of T4 models generation provided in ORM we use Linq2db .
From now on we have two(2) ways to create a model:
- Manually
- Auto generated
Manual Process
- Create the table in the database
- Create the class in the related folder under the "OneDealerEntities".
if it is OneDealer model, under the "OneDealer", etc. - Name the class with the desired name and add the related data table columns as class properties
- Class must inherit form BusinessData class
- Namespace should be One.Dealer.Models.Entities
- Associate with Linq2db ORM by adding the necessary attributes - example
- Above the class declaration add the attribute [Table("CREATED_TABLE_NAME")]
- Above each property add the attribute [Column] providing also all the related to the table field information
- Table column name
- Data Type (NVarchar, Int16, Decimal)
- Length
- Nullable (it is nullable)
- Add the newly created class to OneDealerEntitiesContext as following
public ITable<CLASS_NAME> PLURAL_OF_CLASS_NAME { get { return this.GetTable<CLASS_NAME>(); } }
Database objects
In general we have 3 different DB objects:
- DB tables (POCOs)
- Filepath at OneDealer solution: Application/One.Dealer/Models/OneDealerEntities/OneDealer (C# Models for DB Tables, MAIN/DEALER DB)
- Namespace: One.Dealer.Models.Entities
- Filepath at OneDealer solution: Core/One.Core/Models/OneDealerCommonEntities/OneDealer (C# Models for DB Tables, COMMON DB)
- Namespace: One.Core.Models.Entities
- Filepath at OneDealer solution: Application/One.Dealer/Models/OneDealerEntities/OneDealer (C# Models for DB Tables, MAIN/DEALER DB)
- DB views
- Filepath at OneDealer solution: Application/One.Dealer/Models/OneDealerEntities/Views (C# Models for DB Views, MAIN/DEALER DB)
- Namespace: One.Dealer.Models.Views{}
- A DB view is a virtual table based on an SQL query referring to other tables in the database. A view stores an SQL query that is executed whenever you refer to the view.
- Stored Procedures
- Namespace: One.Dealer.Models.StoredProcedures
- Filepath at OneDealer solution: Application/One.Dealer/Models/Partials/OneDealerEntitiesContextStoredProcedures.cs (C# Models for DB Stored Procedures, MAIN/DEALER DB)
- A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed
Code-related Database details
How to know which entity exists at the Dealer DB or the Common DB:
- Comon DB entity: The namespace is One.Core.Models.Entities
- Dealer DB entity: The namespace is One.Dealer.Models.Entities
When creating a new table/view (regarding the main/dealer DB):
- if it is a table, add a file here: ~\One.Dealer\Models\OneDealerEntities\OneDealer\Model.cs
- if it is a view, add a file here: ~\One.Dealer\Models\OneDealerEntities\Views\Model.cs
- Then go to ~\One.Dealer\Models\OneDealerEntities\OneDealerEntitiesContext.cs and add a public property of the table/view, similar to the others
- Then go to ~\OneDealer.Infrastructure.DependencyInjection\GeneratedBindingsModule.cs and add for the table/view, similar to the others
SAP B1 - Object Tables
If the class we created represents an object table of SAP B1 that means that this entity should be accessed through Service Layer call.
We need to make a last step.
In SAPB1DAL.cs (PATH: $OneDealer\Dev\Source\One.Dealer\One.Dealer\Repositories\SL\SAPB1DAL.cs)
- find the ServiceLayerObjectNameConvension dictionary
- add the table name and the name convention which was created in OUDO
This implementation is under investigation whether can be better.
For example, we can create a custom attribute in the class that indicates that it is a Service Layer Entity
SQL to C# class
If you have a lot of tables/fields there are some tools online that can be helpful.
Sql 2 Object is an example, which converts the result of a SQL query to c# class.
It is based on the logic described here.
Now we are ready to use the new database table and model.
Auto generated Process
The auto generated procedure is similar to the previous implementation we had.
The main differences are
- It is generated a common database model for Hana and Service Layer, in contrast to the previous implementation which was generating two models (OneDealer Entities & Proxies)
- The Service Layer calls are being made through HTTP calls (web requests) and not through WCF service reference (DataService)
Prerequisites
Visual Studio 2019. T4 models make use of C# 7.3
How to run the cycle of models for the auto generated process:
|
Behind the scenes
In depth description of the auto generated process
Previous Implementation
Describe the previous implementation
Related Files
Alternative way to autogenerate DB models
Another way to create the C# models (instead of manually creating them by copying another class to have reference on what to add inside) for the tables and views is to call the stored procedure
- For tables
- CALL "IDMS_UTILITY_OBJECT2CSHARPCLASS_SP"('@TABLE_NAME', 0)
- For views
- CALL "IDMS_UTILITY_OBJECT2CSHARPCLASS_SP"('VIEW_NAME', 1)
0 Comments