Generate Entities for the Database Tables With T4 Templates

With the T4.FileManager we got the missing part to create a code generator based on T4 templates that puts classes into their own files. With that problem solved, we can start our automation project and generate classes for all our database tables, generate the repositories to access the data and finish with integration tests to make sure that our repositories work as expected. Let us start with the entity generator.

 

Prepare the database

To generate classes for tables in a database, we need to have a few tables. I created a database called T4Demo and run this CREATE TABLE script:

 

Why not use an ORM?

We are going to use Dapper as a micro ORM. Especially when you have specific requirements for the database structure, you may want something that offers you more control than a full grown ORM. We not only need to keep data around for an extraordinarily long time, but we also need the structure to be self-documenting and support analytics that no one knows about now. While ORMs like Entity Framework got much better with .Net Core, they are still a large black box that needs a lot of knowledge to get the exact results you want.

We found it much simpler to go with plain SQL, Dapper and repositories that encapsulate the whole database logic.

 

Limitations of the automation example in this blog series

The purpose of this series is to show you how you can create the code generators with T4. We look at all the important parts, but we focus on the big picture and not on every detail. The most important restrictions are as follows:

• It only supports tables in the dbo schema.
• It only covers a few basic data types in SQL Server.
• It only automates the happy paths for the integration tests.
• It generates all code into the same project.

The limitations are due to the space it requires to blog about it, not a limitation of T4. You can put a little effort into it yourself and add the missing features, should you want to do that.

The production code we use at work is significantly more elaborated, but that code is a trade secret, and I cannot share it with you. Therefore, we use this limited feature set I wrote from scratch.

 

Read the database metadata

To create a class for each table, we first need to know what tables are in the database. We can use the INFORMATION_SCHEMA.TABLES object in SQL Server to give us that answer. As soon as we know the tables, we can use the INFORMATION_SCHEMA.COLUMNS object to get the metadata for the columns.

We will need to reuse this code, which makes it a good candidate for a *.ttinclude file. Such a file is a plain text file that we do not run a custom tool on and instead reference it inside our regular *.tt files.

We can create a *.txt file in Visual Studio, rename it to _DbAccess.ttinclude, set the “Copy to Output Directory” to always and write this code to access the database:

This gives us the class SchemaReader with its two methods ReadTables() and ReadColumns(). The Column class is our container to get the column name and its data type back.

The ReadColumns() method does a basic translation for data types in SQL Server to those we can use in C#. It is not complete, but it will work for our example.

 

Imports and assemblies

We need some assemblies and imports for our code generator to work. Since we need them everywhere, we best put that code into its own *.ttinclude file, name it _Imports.ttinclude and set the “Copy to Output Directory” to always:

In our .Net project, we can add the three packages T4.FileManager.VisualStudio, Dapper and Microsoft.Data.SqlClient as we usually add a NuGet package. Our *.ttinclude reads those packages from the NuGet cache and we do not need to copy those files by hand into our output directory. That will save us a lot of time and makes sure that we use the right packages.

Check that the version in the *.ttinclude matches the installed packages.

 

Build the project

With the two *.ttinclude files in the project and our NuGet packages installed, we can now build the project. That will copy the *.ttinclude files into the output directory and prepares everything for the next step.

 

Create the class generator

For our class generator we create a T4 template and name it ClassGeneratorFromDb.tt. Inside the template we combine the SchemaReader with the class generator from the T4.FileManager example to this generator:

We reuse the naming convention and use .g.cs for generated C# code. Since we create everything as partial, we can create a file with the same name but the *.cs extension when we need to add hand-written code. That separation allows us to regenerate the code while our manual changes stay safe in their own file.

 

Run the generator

When we run the custom tool in Visual Studio for our ClassGeneratorFromDb.tt, it will access the database, read the metadata, and creates two classes inside the FromDb\Entities\ folder:

The generated Customer.g.cs file should look like this:

The code in Product.g.cs should have this content:

 

Next

With our generated C# classes matching our tables in the database, we reached a first important milestone: We can generate code based on a database. From this point we can move ahead and generate more code for our application. Next week we generate the repositories to access the data in the database.