Cross T4 and SQL

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

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)

turn into 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 demonstrate to you how to simplify the work of filling and updating the Config and CarDetail tables. Suppose that the contents of the Color, Category, Detail tables are rarely updated. Let them contain the following data:


First, open VS 2010/2012, create a 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
                {
                    {"Red", 1},{"Green", 2},{"Blue", 3},{"Orange", 4}
                };
        }
        public static Dictionary Dic { get; set; }
    }
    public static class CategoryTable
    {
        static CategoryTable()
        {
            Dic = new Dictionary
                {
                    {"Bike", 1},{"Car", 2},{"Truck", 3},{"Bus", 4}
                };
        }
        public static Dictionary Dic { get; set; }
    }
    public static class DetailTable
    {
        static DetailTable()
        {
            Dic = new Dictionary
                {
                    {"Engine", 1},{"Wheel", 2},{"Door", 3}
                };
        }
        public static Dictionary Dic { get; set; }
    }
}

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

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 _details = new Dictionary();
        public Dictionary Details
        {
            get { return _details; }
        }
        public Dictionary DetailIdList
        {
            get
            {
                return Details.ToDictionary(detail => DetailTable.Dic[detail.Key], detail => detail.Value);
            }
        }
    }
}

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

<#@ 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 #>

We 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:
  1. Save time (it’s hard to estimate how much time it saved for me, but life has simplified it for sure)
  2. You can store data creation scripts, and at any time you can see the information in a convenient format

Also popular now: