zagir 3 дек 2012 в 21:33

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

4 мин

6.2K

Recovery Mode

Думаю каждому разработчику баз данных (и не только их) приходилось обновлять/добавлять данные в конфигурационных таблицах. Я хочу поделится с Вами как я упростил работу с конфигурационными данными в таблицах с помощью 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);

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

Скрипт создания таблиц

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)

Я хочу Вам продемонстрировать как можно упростить работу по заполнению и обновлению таблиц 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

Плюсы:

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

Теги:

Хабы:

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

Публикации

Истории

Работа

Ближайшие события