Скрещиваем T4 и SQL

Думаю каждому разработчику баз данных (и не только их) приходилось обновлять/добавлять данные в конфигурационных таблицах. Я хочу поделится с Вами как я упростил работу с конфигурационными данными в таблицах с помощью T4.
Проще говоря данной статьей я хочу показать как вот этот SQL скрипт:

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)
превратить в код C#:

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);

Предположим у нас есть следующие таблицы:

Я хочу Вам продемонстрировать как можно упростить работу по заполнению и обновлению таблиц Config и CarDetail. Предположим что содержимое таблиц Color, Category, Detail обновляется редко. Пусть в них лежат следующие данные:

Для начала открываем VS 2010/2012, создаем Solution 'SqlT4', добавляем консольный проект 'SqlTemplate', добавляем в него класс Tables.cs со следующим содержимым:

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; } } }
Как не трудно догадаться это содержимое таблиц Color, Category, Detail.
Далее добавляем в проект 'SqlTemplate' файл 'Config.cs' со следующим кодом:

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); } } } }
Это значения ячеек в таблице Config.
Компилируем Solution и добавляем новый проект 'SqlT4'. Делаем привязку к проекту 'SqlTemplate'. Добавляем в него файл 'GreenBus.tt' - это файл T4. Пусть содержимое у него будет таким;

<#@ 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 #>
Сохраняем файл и получаем на выходе:

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
Плюсы:

1. Экономия времени (трудно оценить сколько оно мне сэкономило времени, но жизнь упростило это точно)
2. Можно хранить скрипты создания данных, и в любой момент можно посмотреть информацию в удобном формате