{"id":159849,"date":"2012-12-03T20:34:02","date_gmt":"2012-12-03T16:34:02","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=159849"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=159849","title":{"rendered":"\u0421\u043a\u0440\u0435\u0449\u0438\u0432\u0430\u0435\u043c T4 \u0438 SQL<\/span>"},"content":{"rendered":"
\t\u0414\u0443\u043c\u0430\u044e \u043a\u0430\u0436\u0434\u043e\u043c\u0443 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0443 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0438 \u043d\u0435 \u0442\u043e\u043b\u044c\u043a\u043e \u0438\u0445) \u043f\u0440\u0438\u0445\u043e\u0434\u0438\u043b\u043e\u0441\u044c \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0442\u044c\/\u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u0432 \u043a\u043e\u043d\u0444\u0438\u0433\u0443\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445. \u042f \u0445\u043e\u0447\u0443 \u043f\u043e\u0434\u0435\u043b\u0438\u0442\u0441\u044f \u0441 \u0412\u0430\u043c\u0438 \u043a\u0430\u043a \u044f \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u043b \u0440\u0430\u0431\u043e\u0442\u0443 \u0441 \u043a\u043e\u043d\u0444\u0438\u0433\u0443\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u044b\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u043c\u0438 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e T4.
\u041f\u0440\u043e\u0449\u0435 \u0433\u043e\u0432\u043e\u0440\u044f \u0434\u0430\u043d\u043d\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435\u0439 \u044f \u0445\u043e\u0447\u0443 \u043f\u043e\u043a\u0430\u0437\u0430\u0442\u044c \u043a\u0430\u043a \u0432\u043e\u0442 \u044d\u0442\u043e\u0442 SQL \u0441\u043a\u0440\u0438\u043f\u0442:<\/p>\n
SET IDENTITY_INSERT dbo.Config ON INSERT INTO dbo.Config(ID, ColorId, CategoryId, Name) VALUES(2, 2, 4, N'\u0417\u0435\u043b\u0435\u043d\u044b\u0439 \u0430\u0432\u0442\u043e\u0431\u0443\u0441') 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) <\/code><\/pre>\n
  \u043f\u0440\u0435\u0432\u0440\u0430\u0442\u0438\u0442\u044c \u0432 \u043a\u043e\u0434 C#:<\/p>\n
var config = new ConfigTable(); config.Color = "Green"; config.Category = "Bus"; config.Name = "\u0417\u0435\u043b\u0435\u043d\u044b\u0439 \u0430\u0432\u0442\u043e\u0431\u0443\u0441"; config.Id = 2;  config.Details.Add("Wheel", 4); config.Details.Add("Engine", 1); config.Details.Add("Door", 3); <\/code><\/pre>\n
  <\/a>
  \u041f\u0440\u0435\u0434\u043f\u043e\u043b\u043e\u0436\u0438\u043c \u0443 \u043d\u0430\u0441 \u0435\u0441\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b:<\/p>\n
  <\/p>\n
\u0421\u043a\u0440\u0438\u043f\u0442 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446<\/b><\/p>\n
\n
CREATE TABLE dbo.Color (  \tID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, \tName NVARCHAR(20) NOT NULL )  CREATE TABLE dbo.Category ( \tID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, \tName NVARCHAR(20) NOT NULL )  CREATE TABLE dbo.Detail( \tID int IDENTITY(1,1) NOT NULL PRIMARY KEY, \tName NVARCHAR(20) NOT NULL )  CREATE TABLE dbo.Config( \tID int IDENTITY(1,1) NOT NULL PRIMARY KEY, \tName] nvarchar(20) NOT NULL, \tColorID int NOT NULL, \tCategoryID 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( \tID int IDENTITY(1,1) NOT NULL PRIMARY KEY, \tConfigID int NOT NULL, \tDetailID int NOT NULL, \tCount 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) <\/code><\/pre>\n
  <\/div>\n<\/div>\n
  \u042f \u0445\u043e\u0447\u0443 \u0412\u0430\u043c \u043f\u0440\u043e\u0434\u0435\u043c\u043e\u043d\u0441\u0442\u0440\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u043a\u0430\u043a \u043c\u043e\u0436\u043d\u043e \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u0443 \u043f\u043e \u0437\u0430\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044e \u0438 \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044e \u0442\u0430\u0431\u043b\u0438\u0446 Config \u0438 CarDetail. \u041f\u0440\u0435\u0434\u043f\u043e\u043b\u043e\u0436\u0438\u043c \u0447\u0442\u043e \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u043c\u043e\u0435 \u0442\u0430\u0431\u043b\u0438\u0446 Color, Category, Detail \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0440\u0435\u0434\u043a\u043e. \u041f\u0443\u0441\u0442\u044c \u0432 \u043d\u0438\u0445 \u043b\u0435\u0436\u0430\u0442 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0435:<\/p>\n
  
  \u0414\u043b\u044f \u043d\u0430\u0447\u0430\u043b\u0430 \u043e\u0442\u043a\u0440\u044b\u0432\u0430\u0435\u043c VS 2010\/2012, \u0441\u043e\u0437\u0434\u0430\u0435\u043c Solution ‘SqlT4’, \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u043a\u043e\u043d\u0441\u043e\u043b\u044c\u043d\u044b\u0439 \u043f\u0440\u043e\u0435\u043a\u0442 ‘SqlTemplate’, \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u0432 \u043d\u0435\u0433\u043e \u043a\u043b\u0430\u0441\u0441 Tables.cs \u0441\u043e \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u043c \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u043c\u044b\u043c:<\/p>\n
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; }     } } <\/code><\/pre>\n
  \u041a\u0430\u043a \u043d\u0435 \u0442\u0440\u0443\u0434\u043d\u043e \u0434\u043e\u0433\u0430\u0434\u0430\u0442\u044c\u0441\u044f \u044d\u0442\u043e \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u043c\u043e\u0435 \u0442\u0430\u0431\u043b\u0438\u0446 Color, Category, Detail. 
  \u0414\u0430\u043b\u0435\u0435 \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u0432 \u043f\u0440\u043e\u0435\u043a\u0442 ‘SqlTemplate’ \u0444\u0430\u0439\u043b ‘Config.cs’ \u0441\u043e \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u043c \u043a\u043e\u0434\u043e\u043c:<\/p>\n
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);             }         }     } } <\/code><\/pre>\n
  \u042d\u0442\u043e \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u044f\u0447\u0435\u0435\u043a \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 Config.
  \u041a\u043e\u043c\u043f\u0438\u043b\u0438\u0440\u0443\u0435\u043c Solution \u0438 \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u043d\u043e\u0432\u044b\u0439 \u043f\u0440\u043e\u0435\u043a\u0442 ‘SqlT4’. \u0414\u0435\u043b\u0430\u0435\u043c \u043f\u0440\u0438\u0432\u044f\u0437\u043a\u0443 \u043a \u043f\u0440\u043e\u0435\u043a\u0442\u0443 ‘SqlTemplate’. \u0414\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u0432 \u043d\u0435\u0433\u043e \u0444\u0430\u0439\u043b ‘GreenBus.tt’ \u2014 \u044d\u0442\u043e \u0444\u0430\u0439\u043b T4. \u041f\u0443\u0441\u0442\u044c \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u043c\u043e\u0435 \u0443 \u043d\u0435\u0433\u043e \u0431\u0443\u0434\u0435\u0442 \u0442\u0430\u043a\u0438\u043c;<\/p>\n
<#@ output extension=".sql" #> <#@ Assembly name="$(SolutionDir)SqlT4\\bin\\Debug\\SqlTemplate.dll"#> <#@ import namespace="System" #> <#@ import namespace="SqlTemplate" #> <# \tvar config = new ConfigTable(); \tconfig.Color = "Green"; \tconfig.Category = "Bus"; \tconfig.Name = "\u0417\u0435\u043b\u0435\u043d\u044b\u0439 \u0430\u0432\u0442\u043e\u0431\u0443\u0441"; \tconfig.Id = 2; \t \tconfig.Details.Add("Wheel", 4); \tconfig.Details.Add("Engine", 1); \tconfig.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 #>)  <#}#>  -- \u0421\u043a\u0440\u0438\u043f\u0442 \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f  UPDATE dbo.Config SET Name = N'<#= config.Name #>' \t, ColorId = <#= config.ColorId #> \t, CategoryId =  <#= config.CategoryId #> WHERE ID = <#= config.Id #> <\/code><\/pre>\n
  \u0421\u043e\u0445\u0440\u0430\u043d\u044f\u0435\u043c \u0444\u0430\u0439\u043b \u0438 \u043f\u043e\u043b\u0443\u0447\u0430\u0435\u043c \u043d\u0430 \u0432\u044b\u0445\u043e\u0434\u0435:<\/p>\n
 SET IDENTITY_INSERT dbo.Config ON INSERT INTO dbo.Config(ID, ColorId, CategoryId, Name) VALUES(2, 2, 4, N'\u0417\u0435\u043b\u0435\u043d\u044b\u0439 \u0430\u0432\u0442\u043e\u0431\u0443\u0441') 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)  -- \u0421\u043a\u0440\u0438\u043f\u0442 \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f UPDATE dbo.Config SET Name = N'\u0417\u0435\u043b\u0435\u043d\u044b\u0439 \u0430\u0432\u0442\u043e\u0431\u0443\u0441' \t, ColorId = 2 \t, CategoryId =  4 WHERE ID = 2 <\/code><\/pre>\n
  \u041f\u043b\u044e\u0441\u044b:  <\/p>\n
    \n
  1. \u042d\u043a\u043e\u043d\u043e\u043c\u0438\u044f \u0432\u0440\u0435\u043c\u0435\u043d\u0438 (\u0442\u0440\u0443\u0434\u043d\u043e \u043e\u0446\u0435\u043d\u0438\u0442\u044c \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043e\u043d\u043e \u043c\u043d\u0435 \u0441\u044d\u043a\u043e\u043d\u043e\u043c\u0438\u043b\u043e \u0432\u0440\u0435\u043c\u0435\u043d\u0438, \u043d\u043e \u0436\u0438\u0437\u043d\u044c \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u043b\u043e \u044d\u0442\u043e \u0442\u043e\u0447\u043d\u043e)<\/li>\n
  2. \u041c\u043e\u0436\u043d\u043e \u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0441\u043a\u0440\u0438\u043f\u0442\u044b \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u0434\u0430\u043d\u043d\u044b\u0445, \u0438 \u0432 \u043b\u044e\u0431\u043e\u0439 \u043c\u043e\u043c\u0435\u043d\u0442 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u0432 \u0443\u0434\u043e\u0431\u043d\u043e\u043c \u0444\u043e\u0440\u043c\u0430\u0442\u0435  <\/li>\n<\/ol>\n
    <\/div>\n<\/p><\/div>\n
     \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438  http:\/\/habrahabr.ru\/post\/159849\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
       \t\u0414\u0443\u043c\u0430\u044e \u043a\u0430\u0436\u0434\u043e\u043c\u0443 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0443 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0438 \u043d\u0435 \u0442\u043e\u043b\u044c\u043a\u043e \u0438\u0445) \u043f\u0440\u0438\u0445\u043e\u0434\u0438\u043b\u043e\u0441\u044c \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0442\u044c\/\u0434\u043e\u0431\u0430\u0432\u043b\u044f\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u0432 \u043a\u043e\u043d\u0444\u0438\u0433\u0443\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445. \u042f \u0445\u043e\u0447\u0443 \u043f\u043e\u0434\u0435\u043b\u0438\u0442\u0441\u044f \u0441 \u0412\u0430\u043c\u0438 \u043a\u0430\u043a \u044f \u0443\u043f\u0440\u043e\u0441\u0442\u0438\u043b \u0440\u0430\u0431\u043e\u0442\u0443 \u0441 \u043a\u043e\u043d\u0444\u0438\u0433\u0443\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u044b\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u043c\u0438 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0445 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e T4. 
      \u041f\u0440\u043e\u0449\u0435 \u0433\u043e\u0432\u043e\u0440\u044f \u0434\u0430\u043d\u043d\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435\u0439 \u044f \u0445\u043e\u0447\u0443 \u043f\u043e\u043a\u0430\u0437\u0430\u0442\u044c \u043a\u0430\u043a \u0432\u043e\u0442 \u044d\u0442\u043e\u0442 SQL \u0441\u043a\u0440\u0438\u043f\u0442:<\/p>\n
    SET IDENTITY_INSERT dbo.Config ON INSERT INTO dbo.Config(ID, ColorId, CategoryId, Name) VALUES(2, 2, 4, N'\u0417\u0435\u043b\u0435\u043d\u044b\u0439 \u0430\u0432\u0442\u043e\u0431\u0443\u0441') 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) <\/code><\/pre>\n
      \u043f\u0440\u0435\u0432\u0440\u0430\u0442\u0438\u0442\u044c \u0432 \u043a\u043e\u0434 C#:<\/p>\n
    var config = new ConfigTable(); config.Color = "Green"; config.Category = "Bus"; config.Name = "\u0417\u0435\u043b\u0435\u043d\u044b\u0439 \u0430\u0432\u0442\u043e\u0431\u0443\u0441"; config.Id = 2;  config.Details.Add("Wheel", 4); config.Details.Add("Engine", 1); config.Details.Add("Door", 3); <\/code><\/pre>\n
      <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-159849","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/159849","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=159849"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/159849\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=159849"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=159849"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=159849"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}