Skip to content

Generate Repositories With T4 Templates

Last week we reached our first milestone by accessing the database with our T4 template and create classes that match our tables (entities). We can now build on top of that knowledge and generate repositories to work with the data in our database.

Limitations and the *.ttincludes files

Be aware of the limitations of the code generators we create in this blog series. The reason for that is the limited space in a blog post and not due to T4.

You can find the code for the _DbAccess.ttinclude and the _Imports.ttinclude in this post.

Generate the repositories and the interfaces

To make testing simpler, I like to create an interface for each repository. We will not unit test the repository itself, then the generated SQL code only shows us errors if we run it against a real database. But for all the code that uses our repositories, the interface will allow us to test the logic in isolation, and thus removes the need for a database full of test data.

For our code generator, we create a new template named RepositoryGeneratorFromDb.tt, add our *.ttincludes and loop twice through our list of tables. The first time to create the interface, the second time to generate the repository itself:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ include file="$(TargetDir)\_Imports.ttinclude" #>
<#@ output extension=".txt" #>

<#@ include file="$(TargetDir)\T4.FileManager.VisualStudio.ttinclude" #>
<#@ include file="$(TargetDir)\_DbAccess.ttinclude" #>

<#
var schemaReader = new SchemaReader();
var fileManager = new T4FileManager(this);

foreach(var name in schemaReader.ReadTables())
{
    fileManager.StartNewFile("I" + name + "Repository.g.cs","","FromDb/Repositories");
#>
using System;
using GenerateCodeFromDb.FromDb.Entities;

namespace GenerateCodeFromDb.FromDb.Repositories
{
    public partial interface I<#= name #>Repository
    {
        int Create(<#= name #> <#= name.ToLower() #>);
        <#= name #> FindById(int id);
        void Update(<#= name #> <#= name.ToLower() #>);
        void Delete(<#= name #> <#= name.ToLower() #>);
    }
}

<#
fileManager.Process();
};
#>


<#

foreach(var name in schemaReader.ReadTables())
{
    fileManager.StartNewFile(name + "Repository.g.cs","","FromDb/Repositories");
#>
using System;
using System.Data;
using System.Linq;
using Dapper;
using GenerateCodeFromDb.FromDb.Entities;

namespace GenerateCodeFromDb.FromDb.Repositories
{
    public partial class <#= name #>Repository : I<#= name #>Repository
    {
        private readonly IDbConnection connection;

        public <#= name #>Repository(IDbConnection connection)
        {
            this.connection = connection;
        }

        public int Create(<#= name #> <#= name.ToLower() #>)
        {
            var sql = @"<#= GenerateInsert(name, schemaReader.ReadColumns(name))#>";

            return this.connection.Query<int>(sql, <#= name.ToLower() #>).Single();
        }

        public <#= name #> FindById(int id)
        {
            var sql = @"<#= GenerateSelect(name, schemaReader.ReadColumns(name))#>";

            return this.connection.Query<<#= name #>>(sql, new {Id = id}).FirstOrDefault();
        }

        public void Update(<#= name #> <#= name.ToLower() #>)
        {
            var sql = @"<#= GenerateUpdate(name, schemaReader.ReadColumns(name))#>";

            this.connection.Execute(sql, <#= name.ToLower() #>);
        }

        public void Delete(<#= name #> <#= name.ToLower() #>)
        {
            var sql = "DELETE FROM <#= name #> WHERE Id = @Id";

            this.connection.Execute(sql, new {Id = <#= name.ToLower() #>.Id});
        }
    }
}

<#
fileManager.Process();
};
#>



<#+

public string GenerateInsert(string table, List<Column> columns)
{
    var sb = new StringBuilder();
    sb.AppendLine();
    sb.AppendLine("INSERT INTO dbo." + table + "(");

    var index = 0;
    foreach(var col in columns)
    {
        if(col.Name == "Id")
        {
            continue;
        }
        if(index > 0)
        {
        sb.Append(",");
        }

        sb.AppendLine($"[{col.Name}]");
        index++;
    }

    sb.AppendLine(") VALUES (");
    index = 0;
    foreach(var col in columns)
    {
        if(col.Name == "Id")
        {
            continue;
        }
        if(index > 0)
        {
        sb.Append(",");
        }

        sb.AppendLine($"@{col.Name}");
        index++;
    }
    sb.AppendLine(")");

    sb.AppendLine("SELECT CAST(SCOPE_IDENTITY() as INT);");

    return sb.ToString();
}

public string GenerateSelect(string table, List<Column> columns)
{
    var sb = new StringBuilder();
    sb.AppendLine();
    sb.AppendLine("SELECT");

    var index = 0;
    foreach(var col in columns)
    {
        if(index > 0)
        {
        sb.Append(",");
        }

        sb.AppendLine($"[{col.Name}]");
        index++;
    }

    sb.AppendLine("FROM dbo." + table);
    sb.AppendLine("WHERE Id = @Id");

    return sb.ToString();
}

public string GenerateUpdate(string table, List<Column> columns)
{
    var sb = new StringBuilder();
    sb.AppendLine();
    sb.AppendLine("UPDATE dbo." + table);
    sb.AppendLine("SET");

    var index = 0;
    foreach(var col in columns)
    {
        if(col.Name == "Id")
        {
            continue;
        }
        if(index > 0)
        {
        sb.Append(",");
        }

        sb.AppendLine($"[{col.Name}] = @{col.Name}");
        index++;
    }

    sb.AppendLine("WHERE Id = @Id");

    return sb.ToString();
}

#>

The template is a bit longer, so let us dive into the different parts:

  • The interface offers us the 4 methods Create(), FindById(), Update() and Delete() to cover the basic CRUD operations.
  • The IDbConnection in the constructor for the repository is our access to the database. The caller of our repository manages the lifecycle of the connection, and our repository does not need to know any credentials to access the database.
  • In each of the CRUD methods we create a parametrised SQL statement, hand it to Dapper and run it against our database.
  • Since the queries for SELECT, UPDATE and INSERT are longer, we create them in dedicated methods at the end of our generator.

Run the generator

When we run the custom tool for our generator, it will create us two interfaces and two classes in the FromDb\Repositories folder:

The generator created 4 files inside the FromDb\Repositories folder.

Our generated ICustomerRepository.g.cs contains these method definitions:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
using System;
using GenerateCodeFromDb.FromDb.Entities;

namespace GenerateCodeFromDb.FromDb.Repositories
{
    public partial interface ICustomerRepository
    {
        int Create(Customer customer);
        Customer FindById(int id);
        void Update(Customer customer);
        void Delete(Customer customer);
    }
}

The implementation is in CustomerRepository.g.cs:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
using System;
using System.Data;
using System.Linq;
using Dapper;
using GenerateCodeFromDb.FromDb.Entities;

namespace GenerateCodeFromDb.FromDb.Repositories
{
    public partial class CustomerRepository : ICustomerRepository
    {
        private readonly IDbConnection connection;

        public CustomerRepository(IDbConnection connection)
        {
            this.connection = connection;
        }

        public int Create(Customer customer)
        {
            var sql = @"
INSERT INTO dbo.Customer(
[LastName]
,[FirstName]
,[Email]
,[IsActive]
,[CreatedOn]
) VALUES (
@LastName
,@FirstName
,@Email
,@IsActive
,@CreatedOn
)
SELECT CAST(SCOPE_IDENTITY() as INT);
";

            return this.connection.Query<int>(sql, customer).Single();
        }

        public Customer FindById(int id)
        {
            var sql = @"
SELECT
[Id]
,[LastName]
,[FirstName]
,[Email]
,[IsActive]
,[CreatedOn]
FROM dbo.Customer
WHERE Id = @Id
";

            return this.connection.Query<Customer>(sql, new {Id = id}).FirstOrDefault();
        }

        public void Update(Customer customer)
        {
            var sql = @"
UPDATE dbo.Customer
SET
[LastName] = @LastName
,[FirstName] = @FirstName
,[Email] = @Email
,[IsActive] = @IsActive
,[CreatedOn] = @CreatedOn
WHERE Id = @Id
";

            this.connection.Execute(sql, customer);
        }

        public void Delete(Customer customer)
        {
            var sql = "DELETE FROM Customer WHERE Id = @Id";

            this.connection.Execute(sql, new {Id = customer.Id});
        }
    }
}

The IProductRepository.g.cs offers us this contract:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
using System;
using GenerateCodeFromDb.FromDb.Entities;

namespace GenerateCodeFromDb.FromDb.Repositories
{
    public partial interface IProductRepository
    {
        int Create(Product product);
        Product FindById(int id);
        void Update(Product product);
        void Delete(Product product);
    }
}

The implementation in IProductRepository.g.cs is done this way:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
using System;
using System.Data;
using System.Linq;
using Dapper;
using GenerateCodeFromDb.FromDb.Entities;

namespace GenerateCodeFromDb.FromDb.Repositories
{
    public partial class ProductRepository : IProductRepository
    {
        private readonly IDbConnection connection;

        public ProductRepository(IDbConnection connection)
        {
            this.connection = connection;
        }

        public int Create(Product product)
        {
            var sql = @"
INSERT INTO dbo.Product(
[Name]
,[Price]
,[IsActive]
,[CreatedOn]
,[Description]
) VALUES (
@Name
,@Price
,@IsActive
,@CreatedOn
,@Description
)
SELECT CAST(SCOPE_IDENTITY() as INT);
";

            return this.connection.Query<int>(sql, product).Single();
        }

        public Product FindById(int id)
        {
            var sql = @"
SELECT
[Id]
,[Name]
,[Price]
,[IsActive]
,[CreatedOn]
,[Description]
FROM dbo.Product
WHERE Id = @Id
";

            return this.connection.Query<Product>(sql, new {Id = id}).FirstOrDefault();
        }

        public void Update(Product product)
        {
            var sql = @"
UPDATE dbo.Product
SET
[Name] = @Name
,[Price] = @Price
,[IsActive] = @IsActive
,[CreatedOn] = @CreatedOn
,[Description] = @Description
WHERE Id = @Id
";

            this.connection.Execute(sql, product);
        }

        public void Delete(Product product)
        {
            var sql = "DELETE FROM Product WHERE Id = @Id";

            this.connection.Execute(sql, new {Id = product.Id});
        }
    }
}

Next

It was not a giant leap from creating the entities to generating the repositories. Nevertheless, seeing that we can use a single generator to create multiple different but related files (like classes and interfaces) is a crucial step to understand the potential of code generators. Next week we go yet another step further and generate integration tests for our repositories.