DB-First Scenario

VITA framework provides a relatively easy path for DB-first scenario - when you already have a database, and possibly an already working application against this database. You can create an entity model from the existing database and start using VITA-provided facilities to build an application. You can also use VITA side-by-side with old code inside the preexisting application. All you need is a global singleton representing a VITA entity application instance. You can then use this instance to implement all data access operations in some UI forms or functional modules of your application.
vdbtool.exe console application is a tool that (among other things) can generate entity definitions and other related objects from already existing database.

Using vdbtool.exe to Generate Entity Model

The vdbtool command line application is available with VITA sources distribution. (Feb 2015: soon to be included in Vita NuGet package).
The command line for invoking the tool is the following:

vdbtool.exe dbfirst /cfg:mydb.cfg
where mydb.cfg is an XML configuration file with detailed generation options. The following is an example of configuration file for VITA's sample BookStore database:
<?xml version="1.0" encoding="utf-8" ?>
  <ConnectionString>Data Source=.;Initial Catalog=VitaBooks;Integrated Security=True</ConnectionString>
  <Options>AutoOnGuidPrimaryKeys, AddOneToManyLists, GenerateConsoleAppCode </Options>

To create a configuration file for your database, copy one of the sample files in ConfigSamples sub-folder in vdbtool source folder, and adjust entries accordingly.

Configuration File Entries

Provider and ConnectionString Entries
The Provider entry specifies the type of database you are using, available values are the following:
MsSql, SqlCe, MySql, Postgres, SQLite
The ConnectionString is a full connection string to the target database. Its syntax is dependent on the provider (database) type.

Schemas Entry
Optional, contains a (comma-delimited) list of schemas (database 'schema' objects like 'dbo') which contain the tables from which to generate the entity definitions. If the entry is empty, all schemas are taken into account. For MS SQL Server, predefined system schemas (like 'sys') are excluded automatically.

OutputPath Entry
A path to output c# file where to place the generated code.

Namespace Entry
A fully-qualified .NET namespace name for generated code.

AppClassName Entry
Part of generated code is a class representing the entity application derived from VITA-defined EntityApp class. This entry defines the name of this derived class.

Options Entry
Specifies additional options for generated code. Each option is a value of Vita.Tools.DbFirst.DbFirstOptions flag enum. The following table contains the available options:
Option Descirption
AutoOnGuidPrimaryKeys Add Auto attribute on all primary key properties of Guid type. At runtime this causes automatic generation of new GUIDs for new entities
Binary16AsGuid Primary use is in MySql databases, which has no built-in GUID type. GUIDs in MySql are usually represented as Binary(16) columns. The option tells code generator to choose Guid as type for properties corresponding to these columns
BinaryKeysAsGuid Used for SQLite database. SQLite database has no 'strong' type system, so this hint tells code generator to assume that binary-type primary keys are GUIDs
UtcDates Code generator will place Utc attribute on all Date/DateTime properties, assuming that dates are stored as UTC dates in the dataase
AddOneToManyLists Tells the code generator to create list properties on parent entities whenever there is a foreign key from child entity to the parent. See example below.
GenerateConsoleAppCode Tells the code generator to create a sample code for console application using the entity model and entity application. You can use parts of this code as a template for your application's startup routine to initialize and connect the entity application.

List properties example.: When finding foreign key from OrderDetails table to Orders table, the code generator will add the following property on IOrder entity:
  IList<IOrderDetail> OrderDetails { get; }
Note that in some cases this list property does not make sense and you should remove it manually after code generation. We explain it in later section of this document.

AutoValues Entry
Using this setting you can ask the code generator to add Auto attribute to certain properties matching the name pattern you provide. The Auto attribute lets VITA runtime to automatically set certain automatic values (like current time, or current user information) in some audit-type properties/columns, without the need for your code to explicitly setting these values. The Auto attribute accepts an optional parameter in its constructor (AutoType enum value) that specifies the 'kind' of automatic value. The value kind must match the datatype of the property (for ex, AutoType.CreatedOn specifies datetime when row was inserted, so it must be placed only on datetime properties).
In AutoValues configuration option you provide a comma-delimited list of pairs: <ColumnName>:<AutoValueType>. Whenever a column/property matches in ColumnName, the generator will place an Auto attribute on a property with corresponding AutoValueType value as a constructor argument.
The available values are fields of AutoType enum defined by VITA:
AutoValue Description
NewGuid Generate new Guid
CreatedOn Assign current datetime for newly created record
UpdatedOn Set current datetime whenever the record is updated (or created initially)
CreatedBy Set the current user's UserName when record is created
CreatedById Set the current user's ID when record is created
UpdatedBy Set the current user's UserName whenever the record is updated (or created)
UpdatedById Set the current user's ID whenever the record is updated (or created)

Code generation process

Once you finished preparing the configuration file, you can launch the vdbtool.exe to generate the c# code implementing the matching entity model. The generation proceeds in several steps: connecting to the database, loading the database objects information, generating the matching entity model and generating the c# code.
Once the .cs file is generated, the tool performs additional verification steps. First, it compiles the generated code to verify that it is correct against the current version of VITA itself.
Secondly, the tool builds the database model from the generated code and compares it to actual original model in the database. If any differences are found, they are reported. The report is also appended to the generated c# file, as commented section at the end.
If there are any mismatches or warnings reported at this comparison phase, it does not necessarily mean that the process failed. There are some tricky situations in real-world databases that VITA's facility cannot express properly in code. You should inspect the warnings report and use your judgement to evaluate each warning or difference. In many cases an incident is just a warning, and you still can use the generated model.

Working with generated code - cleanup and refactoring

The generated entities may need some adjustments and modifications. DB-style column and table names may not look proper for .NET code. Adjust and modify the definitions, property and entity names using Visual Studio refactoring facilities; move entity definitions to separate source files when seems appropriate. Note that the generated entities contain explicit references to column/table names in attributes, so your renaming actions will not cause any model mismatches in the future, VITA runtime will be able to match the renamed .NET artifacts to database objects.
As an example, here is a definition of Name property in one of the generated entities:
    [Column("Name", Size = 50), Unique]
    string Name { get; set; }

You can freely use Visual Studio Rename facility to rename the property. The Column attribute explicitly links the property to Name column in the database table, and this association will not be lost in renaming the property. And DB model update process will NOT try to change the column based on different property name, all because of the Column attribute.

Cleaning-up IList<T> Properties in the Generated Code

If you use AddOneToManyLists option in the configuration file, the code generator automatically creates list properties on parent entity for each foreign key relation in the database. This is convenient way to generate list properties resulting from relationships. But in some cases, these lists do not make sense, and should be removed after code generation. This happens when a 'transactional' type table like ProductOrderLine references a 'catalog'-type table Product. In this case the generator creates the following list property on IProduct entity:
IList<IProductOrderLine> ProductOrderLines { get; }
Obviously, this property does not make much sense. The Orders and OrderLines are constantly growing tables storing incoming product orders from customers, potentially reaching millions or records, and retrieving ALL order lines through property of Product is not a good idea. So you need to remove properties like these manually after the code is generated.

Special case: cutting a sub-set of tables

In some cases you may want to generate entities only for a sub-set of tables in the database. For example, you have a big existing database and you need to build some feature or applet working with a smaller set of specific tables. In this case you have to manually delete all unneeded entity interfaces from the generated file (and remove their registration in RegisterEntities call in module constructor).
One problem you may run into is handling references in entities you keep to entities you are deleting. These reference properties should be replaced with plain foreign key references. Example: let's say you have the following entity-type property in one of the tables you are going to keep, but IUser entity/table will be removed from your code (but still will be there in the database):
  [EntityRef(KeyColumns = "User_Id"), Index, Nullable]
  IUser User { get; set; }
- replace it with the following:
  [Column("User_Id"), Index, Nullable]
  int UserId { get; set; }
Zero value of UserId will be interpreted is NULL by the system. Alternatively you can use nullable (int?) type:
  [Column("User_Id"), Index]
  int? UserId { get; set; }
You will have to adjust your code to use primary key values and assign them directly instead of using entity instances.

Typical DB-First Usage Scenario

For the most part the DB-First scenario is essentially 'run-once'. The generated entity definitions may need some minor adjustments, refactoring and cleanup. Repeated code generations will overwrite the manual code modifications. The suggested scenario is the following:
  1. Generate initial entity definitions using vdbtool.exe
  2. Include the generated entities and accompanying classes (modules, startup code) into a .NET class library project. This will become a DAL/business logic layer of your application.
  3. Adjust and refactor the code as described above.
  4. In the future, whenever you need to change the database model, you can do it by changing the entity definitions (entity model), and letting VITA runtime adjust the database to match the entity model. Alternatively, if database schema changes are in control of database programmers/admins, you can manually adjust the entities to match the changed database model.

Last edited Apr 18, 2015 at 7:47 PM by rivantsov, version 18


No comments yet.