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) 

 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) 

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

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

 <#@ 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. आप डेटा निर्माण स्क्रिप्ट को संग्रहीत कर सकते हैं, और किसी भी समय आप जानकारी को एक सुविधाजनक प्रारूप में देख सकते हैं