We cross T4 and SQL

I think every database developer (and not only them) had to update / add data in configuration tables. I want to share with you how I simplified working with configuration data in tables using T4.
Simply put this article, I want to show how this SQL script:

SET IDENTITY_INSERT dbo.Config ON INSERT INTO dbo.Config(ID, ColorId, CategoryId, Name) VALUES(2, 2, 4, N' ') SET IDENTITY_INSERT dbo.Config OFF INSERT INTO dbo.CarDetail(ConfigID, DetailID, Count) VALUES(2, 2, 4) INSERT INTO dbo.CarDetail(ConfigID, DetailID, Count) VALUES(2, 1, 1) INSERT INTO dbo.CarDetail(ConfigID, DetailID, Count) VALUES(2, 3, 3)

convert to C # code:

 var config = new ConfigTable(); config.Color = "Green"; config.Category = "Bus"; config.Name = " "; config.Id = 2; config.Details.Add("Wheel", 4); config.Details.Add("Engine", 1); config.Details.Add("Door", 3);

Suppose we have the following tables:
')

Table creation script

 CREATE TABLE dbo.Color ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(20) NOT NULL ) CREATE TABLE dbo.Category ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(20) NOT NULL ) CREATE TABLE dbo.Detail( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(20) NOT NULL ) CREATE TABLE dbo.Config( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Name] nvarchar(20) NOT NULL, ColorID int NOT NULL, CategoryID int NOT NULL, ) GO ALTER TABLE dbo.Config WITH CHECK ADD CONSTRAINT FK_Config_Category FOREIGN KEY(CategoryID) REFERENCES dbo.Category(ID) GO ALTER TABLE dbo.Config WITH CHECK ADD CONSTRAINT FK_Config_Color FOREIGN KEY(ColorID) REFERENCES dbo.Color (ID) GO CREATE TABLE dbo.CarDetail( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, ConfigID int NOT NULL, DetailID int NOT NULL, Count int NOT NULL) GO ALTER TABLE dbo.CarDetail WITH CHECK ADD CONSTRAINT FK_CarDetail2Config FOREIGN KEY(ConfigID) REFERENCES dbo.Config (ID) GO ALTER TABLE dbo.CarDetail WITH CHECK ADD CONSTRAINT FK_CarDetail2Detail FOREIGN KEY(DetailID) REFERENCES dbo.Detail (ID)

I want to show you how to simplify the work on filling in and updating the Config and CarDetail tables. Suppose that the contents of the tables Color, Category, Detail are rarely updated. Suppose they contain the following data:

To begin, open VS 2010/2012, create the Solution 'SqlT4', add the console project 'SqlTemplate', add the Tables.cs class to it with the following contents:

 namespace SqlTemplate { public static class ColorTable { static ColorTable() { Dic = new Dictionary<string, int> { {"Red", 1},{"Green", 2},{"Blue", 3},{"Orange", 4} }; } public static Dictionary<string, int> Dic { get; set; } } public static class CategoryTable { static CategoryTable() { Dic = new Dictionary<string, int> { {"Bike", 1},{"Car", 2},{"Truck", 3},{"Bus", 4} }; } public static Dictionary<string, int> Dic { get; set; } } public static class DetailTable { static DetailTable() { Dic = new Dictionary<string, int> { {"Engine", 1},{"Wheel", 2},{"Door", 3} }; } public static Dictionary<string, int> Dic { get; set; } } }

As it is not difficult to guess this is the contents of the tables Color, Category, Detail.
Next, add the file 'Config.cs' with the following code to the 'SqlTemplate' project:

 namespace SqlTemplate { public class ConfigTable { public int Id { get; set; } public string Name { get; set; } public string Color { get; set; } public string Category { get; set; } public int ColorId { get { return ColorTable.Dic[Color]; } } public int CategoryId { get { return CategoryTable.Dic[Category]; } } private Dictionary<string, int> _details = new Dictionary<string, int>(); public Dictionary<string, int> Details { get { return _details; } } public Dictionary<int, int> DetailIdList { get { return Details.ToDictionary(detail => DetailTable.Dic[detail.Key], detail => detail.Value); } } } }

These are the cell values in the Config table.
Compile Solution and add new project 'SqlT4'. Make a binding to the project 'SqlTemplate'. Add the 'GreenBus.tt' file to it - this is the T4 file. Let the content be his;

 <#@ output extension=".sql" #> <#@ Assembly name="$(SolutionDir)SqlT4\bin\Debug\SqlTemplate.dll"#> <#@ import namespace="System" #> <#@ import namespace="SqlTemplate" #> <# var config = new ConfigTable(); config.Color = "Green"; config.Category = "Bus"; config.Name = " "; config.Id = 2; config.Details.Add("Wheel", 4); config.Details.Add("Engine", 1); config.Details.Add("Door", 3); #> SET IDENTITY_INSERT dbo.Config ON INSERT INTO dbo.Config(ID, ColorId, CategoryId, Name) VALUES(<#= config.Id #>, <#= config.ColorId #>, <#= config.CategoryId #>, N'<#= config.Name #>') SET IDENTITY_INSERT dbo.Config OFF <#foreach (var dIter in config.DetailIdList){#> INSERT INTO dbo.CarDetail(ConfigID, DetailID, Count) VALUES(<#= config.Id #>, <#= dIter.Key #>, <#= dIter.Value #>) <#}#> --   UPDATE dbo.Config SET Name = N'<#= config.Name #>' , ColorId = <#= config.ColorId #> , CategoryId = <#= config.CategoryId #> WHERE ID = <#= config.Id #>

Save the file and get the output:

 SET IDENTITY_INSERT dbo.Config ON INSERT INTO dbo.Config(ID, ColorId, CategoryId, Name) VALUES(2, 2, 4, N' ') SET IDENTITY_INSERT dbo.Config OFF INSERT INTO dbo.CarDetail(ConfigID, DetailID, Count) VALUES(2, 2, 4) INSERT INTO dbo.CarDetail(ConfigID, DetailID, Count) VALUES(2, 1, 1) INSERT INTO dbo.CarDetail(ConfigID, DetailID, Count) VALUES(2, 3, 3) --   UPDATE dbo.Config SET Name = N' ' , ColorId = 2 , CategoryId = 4 WHERE ID = 2

Pros:

Saving of time (it is difficult to estimate how much time it has saved me, but life has simplified it for sure)
You can store data creation scripts, and at any time you can view the information in a convenient format.

Source: https://habr.com/ru/post/159849/

All Articles

We cross T4 and SQL

More articles: