/// <summary> /// Execute Next Script /// </summary> /// <returns>DateTime and comments from next script</returns> public string ExecuteNextScript() { try { DatabaseScript nextScript = NextScript(); // execute next script string returnMessage = string.Empty; if (nextScript != null) { returnMessage += DateTime.UtcNow + " " + "Script Executed" + " " + "( " + Execute(nextScript, haltOnException: true) + " )" + "\r\n"; if (IsVersionTableGood()) // make sure table exists { returnMessage += DateTime.UtcNow + " " + DefaultVersion(nextScript.DatabaseVersion) + "\r\n"; } } else { returnMessage = DateTime.UtcNow + " " + "Database is up to date\r\n"; } return(returnMessage); } catch (Exception ex) { throw new Exception("Failed to upgrade database to next script", ex); } }
/// <summary> /// Execute a script /// </summary> /// <param name="databaseScript">Database Script to execute</param> /// <param name="haltOnException">Halt if exception is caught</param> /// <returns>Comments from scripts</returns> public string Execute( DatabaseScript databaseScript, bool haltOnException = false ) { if (databaseScript.Name.Equals("CreationCheckmark")) { return(string.Empty); // do not execute this script } try { if (!Execute( databaseScript.DatabaseScriptBatch.Script, databaseScript.DatabaseScriptBatch.BreakUpToBatches )) { return("Failed"); } } catch (System.Exception ex) { if (haltOnException) { throw new Exception("Failed to execute script; " + databaseScript.Name, ex); } if (ex.HResult == -2147467261) { return("Exception; No database script to execute"); } else { return ("Script Name: " + databaseScript.Name + "\r\n" + "\r\n" + "Exception: " + ExtractException(ex)); } } return(databaseScript.Description); }
/// <summary> /// Place holder for new script /// </summary> /// <param name="majorNumber">Major number for version</param> /// <param name="minorNumber">Minor number for version</param> /// <returns>Database Version script</returns> private DatabaseVersion InitBlank( int majorNumber, int minorNumber ) { DatabaseVersion databaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = 0, DateTime = new DateTime(2018, 03, 10) }; scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = databaseVersion.SequenceIncrease, ScriptNumber = scriptNumber++, Name = "", Description = "", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" ", BreakUpToBatches = true } }); return(databaseVersion); }
void InitVersioning( int majorNumber, int minorNumber ) { int sequence = 0; scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = defaultVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 01, 21) }, ScriptNumber = this.scriptNumber++, Name = "Versioning", Description = "Database Versioning", #region script ( default_version ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" if object_id (N'default_version') is not null drop table default_version go create table default_version ( default_version_id uniqueidentifier not null primary key clustered (default_version_id asc), fun_version varchar(20) not null, number varchar(20) not null, major_number int not null, minor_number int not null, sequence_number int not null, default_user_id uniqueidentifier null , date_time datetime not null ) ", BreakUpToBatches = true } #endregion }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2016, 01, 01) }, ScriptNumber = this.scriptNumber++, Name = "Default", Description = "Part of Default sql2x Tables", #region script ( default_user, default_state_ref ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" if object_id (N'default_user') is not null drop table default_user go if object_id (N'default_state_ref') is not null drop table default_state_ref go create table default_state_ref ( default_state_rcd varchar ( 20 ) not null , default_state_name nvarchar ( 240 ) not null , default_user_id uniqueidentifier null , date_time datetime not null , constraint xpkdefault_state_ref primary key clustered ( default_state_rcd asc ) ) go -- default_state_ref insert into default_state_ref( default_state_rcd, default_state_name, date_time) values ( 'C' , 'Created' , getutcdate () ); insert into default_state_ref ( default_state_rcd , default_state_name , date_time ) values ( 'U' , 'Updated' , getutcdate () ); insert into default_state_ref ( default_state_rcd , default_state_name , date_time ) values ( 'I' , 'Invalidated' , getutcdate () ); go create table default_user ( default_user_id uniqueidentifier not null , default_user_code varchar ( 20 ) not null , default_user_name nvarchar ( 240 ) not null , email nvarchar ( 240 ) null , password nvarchar ( 240 ) null , default_state_rcd varchar ( 20 ) not null , created_by_default_user_id uniqueidentifier null , date_time datetime not null , last_activity_date_time datetime null , constraint xpkdefault_user primary key clustered ( default_user_id asc ) , constraint default_user_to_created_by_default_user foreign key ( created_by_default_user_id ) references default_user ( default_user_id ) , constraint default_user_to_default_state foreign key ( default_state_rcd ) references default_state_ref ( default_state_rcd ) ) go -- default_user insert into default_user (default_user_id, default_user_code, default_user_name, default_state_rcd, created_by_default_user_id, date_time) values ('{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', 'SUX', 'System user X', 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); go if object_id ( N'dbo.ref' ) is not null begin drop function dbo.ref end go create function dbo.ref (@ref_code nvarchar(240)) returns nvarchar(240) as begin return right ( @ref_code , len ( @ref_code ) - charindex ( '.' , @ref_code ) ) end go if object_id (N'get_defaults') is not null begin drop procedure get_defaults end go create procedure get_defaults (@user_id uniqueidentifier out) as begin -- base set @user_id = '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}' end go ", BreakUpToBatches = true } #endregion }); }
void InitIpsum( int majorNumber, int minorNumber ) { int sequence = 0; scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = this.scriptNumber++, Name = "IpsumCatalog", Description = "Ipsum Catalog", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" set nocount on go if object_id('dbo.getNewID') is not null drop view dbo.getNewID go create view dbo.getNewID as select newid() as new_id go if object_id('dbo.random_color') is not null drop function dbo.random_color go create function dbo.random_color() returns nvarchar(240) as begin declare @color varchar(240) set @color = ( SELECT top 1 color FROM ( select 'MediumPurple' as color union select 'MediumSeaGreen' union select 'MediumSlateBlue' union select 'MediumSpringGreen' union select 'MediumTurquoise' union select 'MediumVioletRed' union select 'MidnightBlue' union select 'MediumOrchid' union select 'MintCream' union select 'Moccasin' union select 'NavajoWhite' union select 'Navy' union select 'OldLace' union select 'Olive' union select 'OliveDrab' union select 'Orange' union select 'MistyRose' union select 'OrangeRed' union select 'MediumBlue' union select 'Maroon' union select 'LightBlue' union select 'LightCoral' union select 'LightGoldenrodYellow' union select 'LightGreen' union select 'LightGray' union select 'LightPink' union select 'LightSalmon' union select 'MediumAquamarine' union select 'LightSeaGreen' union select 'LightSlateGray' union select 'LightSteelBlue' union select 'LightYellow' union select 'Lime' union select 'LimeGreen' union select 'Linen' union select 'Magenta' union select 'LightSkyBlue' union select 'LemonChiffon' union select 'Orchid' union select 'PaleGreen' union select 'SlateBlue' union select 'SlateGray' union select 'Snow' union select 'SpringGreen' union select 'SteelBlue' union select 'Tan' union select 'Teal' union select 'SkyBlue' union select 'Thistle' union select 'Turquoise' union select 'Violet' union select 'Wheat' union select 'White' union select 'WhiteSmoke' union select 'Yellow' union select 'YellowGreen' union select 'Tomato' union select 'PaleGoldenrod' union select 'Silver' union select 'SeaShell' union select 'PaleTurquoise' union select 'PaleVioletRed' union select 'PapayaWhip' union select 'PeachPuff' union select 'Peru' union select 'Pink' union select 'Plum' union select 'Sienna' union select 'PowderBlue' union select 'Red' union select 'RosyBrown' union select 'RoyalBlue' union select 'SaddleBrown' union select 'Salmon' union select 'SandyBrown' union select 'SeaGreen' union select 'Purple' union select 'LawnGreen' union select 'LightCyan' union select 'Lavender' union select 'DarkKhaki' union select 'DarkGreen' union select 'DarkGray' union select 'DarkGoldenrod' union select 'DarkCyan' union select 'DarkBlue' union select 'Cyan' union select 'Crimson' union select 'Cornsilk' union select 'LavenderBlush' union select 'Coral' union select 'Chocolate' union select 'Chartreuse' union select 'DarkMagenta' union select 'CadetBlue' union select 'Brown' union select 'BlueViolet' union select 'Blue' union select 'BlanchedAlmond' union select 'Black' union select 'Bisque' union select 'Beige' union select 'Azure' union select 'Aquamarine' union select 'Aqua' union select 'AntiqueWhite' union select 'AliceBlue' union select 'Transparent' union select 'BurlyWood' union select 'DarkOliveGreen' union select 'CornflowerBlue' union select 'DarkOrchid' union select 'Khaki' union select 'Ivory' union select 'DarkOrange' union select 'Indigo' union select 'IndianRed' union select 'HotPink' union select 'Honeydew' union select 'GreenYellow' union select 'Green' union select 'Gray' union select 'Goldenrod' union select 'GhostWhite' union select 'Gainsboro' union select 'Fuchsia' union select 'Gold' union select 'FloralWhite' union select 'DarkRed' union select 'DarkSalmon' union select 'DarkSeaGreen' union select 'ForestGreen' union select 'DarkSlateGray' union select 'DarkTurquoise' union select 'DarkSlateBlue' union select 'DeepPink' union select 'DeepSkyBlue' union select 'DimGray' union select 'DodgerBlue' union select 'Firebrick' union select 'DarkViolet' ) as color ORDER BY (select * from getNewID) ) return @color end go if object_id('dbo.random_period') is not null drop function dbo.random_period go create function dbo.random_period() returns nvarchar(240) as begin declare @period varchar(240) set @period = ( SELECT top 1 period FROM ( select 'Winter' as period union select 'Summer' union select 'Spring' union select 'Fall' ) as period ORDER BY (select * from getNewID) ) return @period end go if object_id('dbo.random_latin') is not null drop function dbo.random_latin go create function dbo.random_latin() returns nvarchar(2400) as begin -- not so random latin return 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.' end go if object_id('product_info') is not null delete product_info if object_id('product_category_mapping') is not null delete product_category_mapping if object_id('cart_product') is not null delete cart_product if object_id('financial_order_line') is not null delete financial_order_line if object_id('product_attribute') is not null delete product_attribute if object_id('product_identifier') is not null delete product_identifier if object_id('product_change_set') is not null delete product_change_set if object_id('product_expose') is not null delete product_expose if object_id('product_expose_plan') is not null delete product_expose_plan if object_id('product_documentation') is not null delete product_documentation if object_id('product_image') is not null delete product_image if object_id('product') is not null delete product go ", BreakUpToBatches = true } #endregion }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = this.scriptNumber++, Name = "IpsumLast", Description = "IpsumStop", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" set quoted_identifier on go set nocount on go if object_id('dbo.random_latin') is not null drop function dbo.random_latin go create function dbo.random_latin() returns nvarchar(2400) as begin -- not so random latin return 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.' end go if object_id('dbo.ipsum') is not null drop function dbo.ipsum go create function dbo.ipsum(@rand float) returns nvarchar(2400) as begin declare @lorem nvarchar(max) ,@pos int ,@pos2 int select -- @lorem = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.' @lorem = 'Lorem ipsum dolor sit amet consectetur adipisicing elit sed do eiusmod tempor incididunt ut labore et dolore magna aliqua Ut enim ad minim veniam quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur Excepteur sint occaecat cupidatat non proident sunt in culpa qui officia deserunt mollit anim id est laborum' while 1=1 begin set @pos = cast(@rand * Len(@lorem) as int) set @pos = charindex(' ', @lorem, @pos) set @pos2 = charindex(' ', @lorem, @pos + 20) if @pos2 + 20 < len(@lorem) and @pos2 != 0 break set @rand = @rand * 0.1 end return lower(substring(@lorem, @pos, @pos2 - @pos)) end go if object_id('dbo.product_insert') is not null drop procedure dbo.product_insert go create procedure product_insert( @product_category_parent_id_3 uniqueidentifier, @chapter_text nvarchar(240), @chapter_name_last nvarchar(240)) as begin -- products declare @product_count int = 0 while @product_count < 10 begin declare @new_product_id uniqueidentifier set @new_product_id = newid(); insert into product (product_id, product_name, state_rcd, user_id, date_time) values (@new_product_id, ' ( ' + @chapter_text + ' )' + ' ' + @chapter_name_last + ' ' + cast(abs(checksum(newid())) % 14 + 1 as varchar) + ' mm' + ' ' + dbo.random_color() + ' ' + dbo.random_period(), 'A', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate() ); insert into product_category_mapping( product_category_mapping_id, product_category_id, product_id, user_id, date_time ) values ( newid(), @product_category_parent_id_3, @new_product_id, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate() ) -- attributes insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_attribute_ref.Color'), dbo.random_color(), dbo.ref('product_attribute_unit_ref.Co'), -- random color '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_attribute_ref.H'), cast(abs(checksum(newid())) % 14 + 1 as varchar), dbo.ref('product_attribute_unit_ref.MM'), -- random positive number and 'mm' '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_attribute_ref.L'), cast(abs(checksum(newid())) % 14 + 1 as varchar), dbo.ref('product_attribute_unit_ref.MM'), -- random positive number and 'mm' '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into product_attribute (product_attribute_id, product_id, product_attribute_rcd, value, product_attribute_unit_rcd, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_attribute_ref.RKM'), cast(abs(checksum(newid())) % 14 + 1 as varchar), dbo.ref('product_attribute_unit_rcd.KM'), -- random positive number '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into product_identifier (product_identifier_id, product_id, product_identifier_rcd, identifier, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_identifier_ref.GTIN13'), left(replace(CAST(CAST(CRYPT_GEN_RANDOM(8) AS bigint) as varchar), '-', ''), 13), -- random 13 char long positive number '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into product_identifier (product_identifier_id, product_id, product_identifier_rcd, identifier, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_identifier_ref.GTIN14'), left(replace(CAST(CAST(CRYPT_GEN_RANDOM(8) AS bigint) as varchar), '-', ''), 14), -- random 14 char long positive number '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into product_info (product_info_id, product_id, product_info_rcd, product_info_value, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_info_ref.PI'), left(replace(CAST(CAST(CRYPT_GEN_RANDOM(8) AS bigint) as varchar), '-', ''), 14), -- random 14 char long positive number '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into product_documentation (product_documentation_id, product_id, product_documentation_type_rcd, documentation, user_id, date_time) values (newid(), @new_product_id, dbo.ref('product_documentation_type_ref.HD'), dbo.random_latin(), -- not so random latin '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); set @product_count = @product_count + 1 end end go delete product_category_documentation go delete from product_category_mapping go delete from product_category_image go delete from product_category go --delete from product go declare @level_1 int = 01 while @level_1 < 15 begin declare @chapter_text char(6) = '000000' -- level 1 set @chapter_text = right('00' + cast(@level_1 as varchar(2)), 2) + '0000' declare @product_category_parent_id uniqueidentifier = newid() insert into product_category (product_category_id, product_category_parent_id, product_category_code, product_category_name, product_category_position, state_rcd, user_id, date_time ) values ( @product_category_parent_id, @product_category_parent_id, @chapter_text, @chapter_text + ' - ' + dbo.ipsum(rand()) , 1, 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate() ) -- level 2 declare @level_2 int = 1 while @level_2 < 3 begin set @chapter_text = right('00' + cast(@level_1 as varchar(2)), 2) + right('00' + cast(@level_2 as varchar(2)), 2) + '00' declare @product_category_parent_id_2 uniqueidentifier = newid() insert into product_category (product_category_id, product_category_parent_id, product_category_code, product_category_name, product_category_position, state_rcd, user_id, date_time ) values ( @product_category_parent_id_2, @product_category_parent_id, @chapter_text, @chapter_text + ' - ' + dbo.ipsum(rand()) , 1, 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate() ) -- level 3 declare @level_3 int = 1 while @level_3 < 6 begin set @chapter_text = right('00' + cast(@level_1 as varchar(2)), 2) + right('00' + cast(@level_2 as varchar(2)), 2) + right('00' + cast(@level_3 as varchar(2)), 2) declare @product_category_parent_id_3 uniqueidentifier = newid() declare @chapter_name_last nvarchar(240) = dbo.ipsum(rand()) insert into product_category (product_category_id, product_category_parent_id, product_category_code, product_category_name, product_category_position, state_rcd, user_id, date_time ) values ( @product_category_parent_id_3, @product_category_parent_id_2, @chapter_text, @chapter_text + ' - ' + @chapter_name_last , 1, 'C', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate() ) exec product_insert @product_category_parent_id_3, @chapter_text, @chapter_name_last set @level_3 = @level_3 + 1 end set @level_2 = @level_2 + 1 end set @level_1 = @level_1 + 1 end ", BreakUpToBatches = true } #endregion }); }
/// <summary> /// Delete all objects in the database /// </summary> /// <param name="majorNumber">Major Version number of this script</param> /// <param name="minorNumber">Minor Version number of this script</param> public void InitDatabaseClean( int majorNumber, int minorNumber ) { int sequence = 0; scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = this.scriptNumber++, Name = "DatabaseCleanScript1", Description = "Clear database script ( Drop all non-system stored procs )", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @"/* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Procedure: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) END", BreakUpToBatches = false } }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = this.scriptNumber++, Name = "DatabaseCleanScript2", Description = "Clear database script ( Drop all views )", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @"/* Drop all views */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped View: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) END ", BreakUpToBatches = false } }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = this.scriptNumber++, Name = "DatabaseCleanScript3", Description = "Clear database script ( Drop all functions )", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @"/* Drop all functions */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Function: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) END ", BreakUpToBatches = false } }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = this.scriptNumber++, Name = "DatabaseCleanScript4", Description = "Clear database script ( Drop all Foreign Key constraints )", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @"/* Drop all Foreign Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) WHILE @name is not null BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint IS NOT NULL BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' EXEC (@SQL) PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) END ", BreakUpToBatches = false } }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = this.scriptNumber++, Name = "DatabaseCleanScript5", Description = "Clear database script ( Drop all Primary Key constraints )", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @"/* Drop all Primary Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) WHILE @name IS NOT NULL BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint is not null BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' EXEC (@SQL) PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) END ", BreakUpToBatches = false } }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = this.scriptNumber++, Name = "DatabaseCleanScript6", Description = "Clear database script ( Drop all tables )", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @"/* Drop all tables */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Table: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) END ", BreakUpToBatches = false } }); }
public void InitJSONClient( int majorNumber, int minorNumber ) { int sequence = 0; scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = scriptNumber++, Name = "ClientSchema", Description = "Client Schema", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" /* SQL2X Generated code based on a SQL Server Schema SQL2X Version: 0.d http://sql2core.azurewebsites.net/ Generated Date: 21.01.2018 18:54:14 Template: sql2x.GenerateSchema.Generate */ if object_id (N'client_link') is not null drop table client_link go if object_id (N'client_identifier') is not null drop table client_identifier go if object_id (N'client_event') is not null drop table client_event go if object_id (N'client_document') is not null drop table client_document go if object_id (N'client_contact_method') is not null drop table client_contact_method go if object_id (N'client_link_type_ref') is not null drop table client_link_type_ref go if object_id (N'client_identifier_type_ref') is not null drop table client_identifier_type_ref go if object_id (N'client_event_type_ref') is not null drop table client_event_type_ref go if object_id (N'client_document_type_ref') is not null drop table client_document_type_ref go if object_id (N'client_contact_method_ref') is not null drop table client_contact_method_ref go if object_id (N'client') is not null drop table client go if object_id (N'client_address') is not null drop table client_address go if object_id (N'client_type_ref') is not null drop table client_type_ref go if object_id (N'client_nationality_ref') is not null drop table client_nationality_ref go if object_id (N'client_gender_ref') is not null drop table client_gender_ref go if object_id (N'client_title_ref') is not null drop table client_title_ref go if object_id (N'client_address_type_ref') is not null drop table client_address_type_ref go set nocount on go create table client_type_ref ( client_type_rcd varchar(20) not null, client_type_name nvarchar(240) not null, client_type_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_type_ref primary key clustered (client_type_rcd asc) ) go insert into client_type_ref(client_type_rcd, client_type_name, sort_order, active_flag, [user_id], date_time) values ('PRA', 'Private', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_type_ref(client_type_rcd, client_type_name, sort_order, active_flag, [user_id], date_time) values ('BUA', 'Business', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_nationality_ref ( client_nationality_rcd varchar(20) not null, client_nationality_name nvarchar(240) not null, client_nationality_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_nationality_ref primary key clustered (client_nationality_rcd asc) ) go insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time) values ('AFA', 'Africa', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time) values ('AMA', 'Americas', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time) values ('ASA', 'Asia', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time) values ('EUA', 'Europe', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_nationality_ref(client_nationality_rcd, client_nationality_name, sort_order, active_flag, [user_id], date_time) values ('OCA', 'Oceania', 5, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_gender_ref ( client_gender_rcd varchar(20) not null, client_gender_name nvarchar(240) not null, client_gender_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_gender_ref primary key clustered (client_gender_rcd asc) ) go insert into client_gender_ref(client_gender_rcd, client_gender_name, sort_order, active_flag, [user_id], date_time) values ('MAA', 'Male', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_gender_ref(client_gender_rcd, client_gender_name, sort_order, active_flag, [user_id], date_time) values ('FEA', 'Female', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_gender_ref(client_gender_rcd, client_gender_name, sort_order, active_flag, [user_id], date_time) values ('UNA', 'Unknown', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_title_ref ( client_title_rcd varchar(20) not null, client_title_name nvarchar(240) not null, client_title_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_title_ref primary key clustered (client_title_rcd asc) ) go insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time) values ('MR', 'Mr', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time) values ('MRS', 'Mrs', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time) values ('CHA', 'Child', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_title_ref(client_title_rcd, client_title_name, sort_order, active_flag, [user_id], date_time) values ('INA', 'Infant', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_link_type_ref ( client_link_type_rcd varchar(20) not null, client_link_type_name nvarchar(240) not null, client_link_type_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_link_type_ref primary key clustered (client_link_type_rcd asc) ) go insert into client_link_type_ref(client_link_type_rcd, client_link_type_name, sort_order, active_flag, [user_id], date_time) values ('WPE', 'Web Page', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_identifier_type_ref ( client_identifier_type_rcd varchar(20) not null, client_identifier_type_name nvarchar(240) not null, client_identifier_type_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_identifier_type_ref primary key clustered (client_identifier_type_rcd asc) ) go insert into client_identifier_type_ref(client_identifier_type_rcd, client_identifier_type_name, sort_order, active_flag, [user_id], date_time) values ('PNR', 'Passport Number', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_identifier_type_ref(client_identifier_type_rcd, client_identifier_type_name, sort_order, active_flag, [user_id], date_time) values ('ENR', 'Employee Number', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_address_type_ref ( client_address_type_rcd varchar(20) not null, client_address_type_name nvarchar(240) not null, client_address_type_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_address_type_ref primary key clustered (client_address_type_rcd asc) ) go insert into client_address_type_ref(client_address_type_rcd, client_address_type_name, sort_order, active_flag, [user_id], date_time) values ('PRA', 'Private', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_address_type_ref(client_address_type_rcd, client_address_type_name, sort_order, active_flag, [user_id], date_time) values ('OFA', 'Office', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_address_type_ref(client_address_type_rcd, client_address_type_name, sort_order, active_flag, [user_id], date_time) values ('TEA', 'Temporary', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_event_type_ref ( client_event_type_rcd varchar(20) not null, client_event_type_name nvarchar(240) not null, client_event_type_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_event_type_ref primary key clustered (client_event_type_rcd asc) ) go insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time) values ('CRA', 'Created', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time) values ('INA', 'Inactivated', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time) values ('SIM', 'Sent Introduction Mail', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time) values ('IMC', 'Introduction Mail Confirmed', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time) values ('PMS', 'Package Mail Sent', 5, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time) values ('PSV', 'Package Site Viewed', 6, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_event_type_ref(client_event_type_rcd, client_event_type_name, sort_order, active_flag, [user_id], date_time) values ('POD', 'Package Ordered', 7, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_document_type_ref ( client_document_type_rcd varchar(20) not null, client_document_type_name nvarchar(240) not null, client_document_type_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_document_type_ref primary key clustered (client_document_type_rcd asc) ) go insert into client_document_type_ref(client_document_type_rcd, client_document_type_name, sort_order, active_flag, [user_id], date_time) values ('PFP', 'Passport First Page', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_contact_method_ref ( client_contact_method_rcd varchar(20) not null, client_contact_method_name nvarchar(240) not null, client_contact_method_description nvarchar(240) null, active_flag bit not null, sort_order integer not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_contact_method_ref primary key clustered (client_contact_method_rcd asc) ) go insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('EMA', 'EMail', 1, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('TEA', 'Telephone', 2, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('LPE', 'Local Phone', 3, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('IPE', 'International Phone', 4, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('HPE', 'Home Phone', 5, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('BPE', 'Business Phone', 6, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('FAX', 'Fax', 7, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('MOA', 'Mobile', 8, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('SME', 'SMS Mobile', 9, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) insert into client_contact_method_ref(client_contact_method_rcd, client_contact_method_name, sort_order, active_flag, [user_id], date_time) values ('MME', 'MMS Mobile', 10, 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}',getutcdate()) go create table client_address ( client_address_id uniqueidentifier not null, client_id uniqueidentifier null, client_address_type_rcd varchar(20) not null, address_line_one_name nvarchar(240) not null, address_line_two_name nvarchar(240) not null, address_line_three_name nvarchar(240) not null, city_name nvarchar(240) not null, street_name nvarchar(240) not null, state_name nvarchar(240) not null, district_name nvarchar(240) not null, province_name nvarchar(240) not null, zip_code nvarchar(240) not null, po_box nvarchar(240) not null, [comment] nvarchar(240) not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_address primary key clustered (client_address_id asc), constraint client_address_to_client_address_type_ref foreign key (client_address_type_rcd) references client_address_type_ref(client_address_type_rcd) ) go create table client ( client_id uniqueidentifier not null, client_type_rcd varchar(20) not null, client_nationality_rcd varchar(20) null, client_gender_rcd varchar(20) null, client_title_rcd varchar(20) null, client_address_id uniqueidentifier null, first_name nvarchar(240) not null, middle_name nvarchar(240) null, last_name nvarchar(240) not null, [image] varbinary(max) null, image_blob_filename nvarchar(240) null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client primary key clustered (client_id asc), constraint client_to_client_type_ref foreign key (client_type_rcd) references client_type_ref(client_type_rcd), constraint client_to_client_nationality_ref foreign key (client_nationality_rcd) references client_nationality_ref(client_nationality_rcd), constraint client_to_client_gender_ref foreign key (client_gender_rcd) references client_gender_ref(client_gender_rcd), constraint client_to_client_title_ref foreign key (client_title_rcd) references client_title_ref(client_title_rcd), constraint client_to_client_address foreign key (client_address_id) references client_address(client_address_id) ) go create table client_link ( client_link_id uniqueidentifier not null, client_id uniqueidentifier not null, client_link_type_rcd varchar(20) not null, link_name nvarchar(240) null, link nvarchar(240) not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_link primary key clustered (client_link_id asc), constraint client_link_to_client foreign key (client_id) references client(client_id), constraint client_link_to_client_link_type_ref foreign key (client_link_type_rcd) references client_link_type_ref(client_link_type_rcd) ) go create table client_identifier ( client_identifier_id uniqueidentifier not null, client_id uniqueidentifier not null, client_identifier_type_rcd varchar(20) not null, client_identifier_code varchar(20) not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_identifier primary key clustered (client_identifier_id asc), constraint client_identifier_to_client foreign key (client_id) references client(client_id), constraint client_identifier_to_client_identifier_type_ref foreign key (client_identifier_type_rcd) references client_identifier_type_ref(client_identifier_type_rcd) ) go create table client_event ( client_event_id uniqueidentifier not null, client_id uniqueidentifier not null, client_event_type_rcd varchar(20) not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_event primary key clustered (client_event_id asc), constraint client_event_to_client foreign key (client_id) references client(client_id), constraint client_event_to_client_event_type_ref foreign key (client_event_type_rcd) references client_event_type_ref(client_event_type_rcd) ) go create table client_document ( client_document_id uniqueidentifier not null, client_id uniqueidentifier not null, client_document_type_rcd varchar(20) not null, document_name nvarchar(240) not null, document_date_time datetime not null, expiry_date_time datetime not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_document primary key clustered (client_document_id asc), constraint client_document_to_client foreign key (client_id) references client(client_id), constraint client_document_to_client_document_type_ref foreign key (client_document_type_rcd) references client_document_type_ref(client_document_type_rcd) ) go create table client_contact_method ( client_contact_method_id uniqueidentifier not null, client_id uniqueidentifier not null, client_contact_method_rcd varchar(20) not null, contact_method_way nvarchar(240) not null, [comment] text not null, [user_id] uniqueidentifier not null, date_time datetime not null, constraint xpk_client_contact_method primary key clustered (client_contact_method_id asc), constraint client_contact_method_to_client foreign key (client_id) references client(client_id), constraint client_contact_method_to_client_contact_method_ref foreign key (client_contact_method_rcd) references client_contact_method_ref(client_contact_method_rcd) ) ", BreakUpToBatches = false } }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2015, 11, 22) }, ScriptNumber = scriptNumber++, Name = "ClientTestData", Description = "Client Test Data", DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" -- default client uses default user declare @client_id uniqueidentifier = N'ffffffff-5555-5555-5555-ffffffffffff' insert into client ( client_id, first_name, middle_name, last_name, client_nationality_rcd, client_gender_rcd, client_title_rcd, client_type_rcd, user_id, date_time) values (@client_id, 'Test', '', 'Data', dbo.ref('client_nationality_ref.OCA'), dbo.ref('client_gender_ref.MAA'), dbo.ref('client_title_ref.MR'), dbo.ref('client_type_ref.BUA'), '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into client_contact_method ( client_contact_method_id, client_id, client_contact_method_rcd, contact_method_way, comment, user_id, date_time) values ( newid(), @client_id, dbo.ref('client_contact_method_ref.EMA'), '*****@*****.**', '', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into client_contact_method ( client_contact_method_id, client_id, client_contact_method_rcd, contact_method_way, comment, user_id, date_time) values ( newid(), @client_id, dbo.ref('client_contact_method_ref.MOA'), '+1 987 654321', '', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into client_link ( client_link_id, client_id, client_link_type_rcd, link, user_id, date_time) values ( newid(), @client_id, dbo.ref('client_link_type_ref.WPE'), 'www.google.com', '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); insert into client_event ( client_event_id, client_id, client_event_type_rcd, user_id, date_time) values ( newid(), @client_id, dbo.ref('client_event_type_ref.CRA'), '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); go declare @client_address_id uniqueidentifier = newid() insert into client_address ( client_address_id, client_id, client_address_type_rcd, address_line_one_name , address_line_two_name , address_line_three_name , city_name , street_name , state_name , district_name , province_name , zip_code , po_box , [comment] , user_id, date_time) values (@client_address_id, @client_id, dbo.ref('client_address_type_ref.PRA'), 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', 'Oslo', N'ffffffff-5555-5555-5555-ffffffffffff', getdate()) ", BreakUpToBatches = false } }); }
/// <summary> /// Init Database Manager /// </summary> /// <param name="databaseName">Name of Database to delete</param> /// <param name="toRemote">Use remote or local database</param> public DatabaseManager( string databaseName, bool toRemote ) { if (string.IsNullOrEmpty(databaseName)) { throw new ArgumentException("Missing Database Name", "databaseName"); } try { this.databaseName = databaseName; // remote or local this.toRemote = toRemote; if (this.toRemote) { connectionString = Conn.ConnectionStringRemote; } else { connectionString = Conn.ConnectionStringLocal; // check if database exist if (!DatabaseExist( connectionStringLocal: connectionString.Replace("Initial Catalog=NorSolutionPim;", ""), databaseName: this.databaseName )) { DatabaseCreate( connectionStringLocal: connectionString.Replace("Initial Catalog=NorSolutionPim;", ""), databaseName: this.databaseName ); Console.WriteLine($"Created database {this.databaseName}"); } } int minorNumber = 0; int sequence = 0; InitDatabaseClean(majorNumber: 0, minorNumber: minorNumber++); InitVersioning(majorNumber: 0, minorNumber: minorNumber++); InitJSONClient(majorNumber: 0, minorNumber: minorNumber++); InitSystemReferenceErrorAndIssue(majorNumber: 0, minorNumber: minorNumber++); InitDefaultUserActivity(majorNumber: 0, minorNumber: minorNumber++, sequence: out sequence); InitDefaultSystemSetting(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence); InitDefaultChangeLog(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence); InitDefaultRule(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence); InitDefaultPerformanceIssue(majorNumber: 0, minorNumber: minorNumber++, sequence: ref sequence); InitFinancial(majorNumber: 0, minorNumber: minorNumber++); InitProduct(majorNumber: 0, minorNumber: minorNumber++); InitOAuth(majorNumber: 0, minorNumber: minorNumber++); InitIpsum(majorNumber: 0, minorNumber: minorNumber++); // creation checkmark, do not process scripts after this until creation is created scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = 0, MinorNumber = minorNumber++, SequenceNumber = sequence++, DateTime = new DateTime(2019, 02, 05) }, ScriptNumber = scriptNumber++, Name = "CreationCheckmark" }); // intentionally left blank to serve as a template and to get the last version number maximumVersion = InitBlank(majorNumber: 0, minorNumber: minorNumber++); } catch (System.Exception ex) { throw new System.Exception( "Failed to Initialize database manager", ex ); } }
/// <summary> /// Get Next Script from current version in database /// </summary> /// <returns>Next DatabaseScript Object</returns> public DatabaseScript NextScript() { try { // find current state string whyNot = string.Empty; if (!IsConnectionGood(out whyNot)) { throw new Exception($"Connection not good; {SanitizePassword(connectionString)}\r\n Because: {whyNot}"); } if (!IsDatabaseGood()) { throw new Exception("Database is not good; " + databaseName + " on connection; " + SanitizePassword(connectionString)); } // get version DatabaseVersion databaseVersion = new DatabaseVersion(); string returnMessage = string.Empty; if (!IsVersionTableGood()) { throw new Exception("Version table is not good on connection; " + SanitizePassword(connectionString)); } databaseVersion = VersionTableLastEntry(); // is code version and database version the same? if (maximumVersion == databaseVersion) { return(null); } if (maximumVersion < databaseVersion) { throw new Exception("Maxmimum version is less than the database version" + "\r\n"); } // filter out scripts to execute List <DatabaseScript> scriptsFiltered = scripts .Where(s => s.DatabaseVersion > databaseVersion && s.DatabaseVersion <= maximumVersion) .ToList(); if (scriptsFiltered.Count == 0) { return(null); } // find next script List <DatabaseScript> scriptOrdered = scriptsFiltered .OrderBy(o => o.DatabaseVersion.MajorNumber) .ThenBy(o2 => o2.DatabaseVersion.MinorNumber) .ThenBy(o3 => o3.DatabaseVersion.SequenceNumber) .ToList(); DatabaseScript nextScript = scriptOrdered.FirstOrDefault(); // https://codereview.stackexchange.com/questions/55340/update-first-and-last-item-in-listt return(nextScript); } catch (Exception ex) { throw new Exception("Failed to get next script", ex); } }
void InitOAuth( int majorNumber, int minorNumber ) { int sequence = 0; scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = this.scriptNumber++, Name = "OAuthDropTables", Description = "OAuth Drop Tables", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" if object_id (N'AspNetUserClaims') is not null drop table AspNetUserClaims if object_id (N'AspNetUserLogins') is not null drop table AspNetUserLogins if object_id (N'AspNetUserRoles') is not null drop table AspNetUserRoles if object_id (N'AspNetUsers') is not null drop table AspNetUsers if object_id (N'AspNetRoles') is not null drop table AspNetRoles if object_id (N'__MigrationHistory') is not null drop table __MigrationHistory ", BreakUpToBatches = true } #endregion }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = this.scriptNumber++, Name = "OAuthCreateTables", Description = "OAuth Create Tables", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" CREATE TABLE [dbo].[__MigrationHistory] ( [MigrationId] NVARCHAR (150) NOT NULL, [ContextKey] NVARCHAR (300) NOT NULL, [Model] VARBINARY (MAX) NOT NULL, [ProductVersion] NVARCHAR (32) NOT NULL, CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED ([MigrationId] ASC, [ContextKey] ASC) ); go CREATE TABLE [dbo].[AspNetRoles] ( [Id] NVARCHAR (128) NOT NULL, [Name] NVARCHAR (256) NOT NULL, CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC) ); go CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex] ON [dbo].[AspNetRoles]([Name] ASC); go CREATE TABLE [dbo].[AspNetUserClaims] ( [Id] INT IDENTITY (1, 1) NOT NULL, [UserId] NVARCHAR (128) NOT NULL, [ClaimType] NVARCHAR (MAX) NULL, [ClaimValue] NVARCHAR (MAX) NULL, CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC) ); go CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserClaims]([UserId] ASC); go CREATE TABLE [dbo].[AspNetUserLogins] ( [LoginProvider] NVARCHAR (128) NOT NULL, [ProviderKey] NVARCHAR (128) NOT NULL, [UserId] NVARCHAR (128) NOT NULL, CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] ASC) ); go CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]([UserId] ASC); go CREATE TABLE [dbo].[AspNetUserRoles] ( [UserId] NVARCHAR (128) NOT NULL, [RoleId] NVARCHAR (128) NOT NULL, CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC) ); go CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]([UserId] ASC); go CREATE NONCLUSTERED INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]([RoleId] ASC); go CREATE TABLE [dbo].[AspNetUsers] ( [Id] NVARCHAR (128) NOT NULL, [Email] NVARCHAR (256) NULL, [EmailConfirmed] BIT NOT NULL, [PasswordHash] NVARCHAR (MAX) NULL, [SecurityStamp] NVARCHAR (MAX) NULL, [PhoneNumber] NVARCHAR (MAX) NULL, [PhoneNumberConfirmed] BIT NOT NULL, [TwoFactorEnabled] BIT NOT NULL, [LockoutEndDateUtc] DATETIME NULL, [LockoutEnabled] BIT NOT NULL, [AccessFailedCount] INT NOT NULL, [UserName] NVARCHAR (256) NOT NULL, CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC) ); go CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex] ON [dbo].[AspNetUsers]([UserName] ASC); go ALTER TABLE [dbo].[AspNetUserClaims] WITH NOCHECK ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE; go ALTER TABLE [dbo].[AspNetUserLogins] WITH NOCHECK ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE; go ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE; go ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE; ", BreakUpToBatches = true } #endregion }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = this.scriptNumber++, Name = "OAuthTestData", Description = "OAuth Test Data", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" set quoted_identifier on go set nocount on go ", BreakUpToBatches = true } #endregion }); }
private void InitFinancial( int majorNumber, int minorNumber ) { int sequence = 0; scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = scriptNumber++, Name = "DefaultFinancialColumnTypes", Description = "Default Financial Column Types", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" set quoted_identifier on go if type_id (N'name') is null exec sp_addtype 'name' , 'nvarchar(240)', 'not null' go if type_id (N'code') is null exec sp_addtype 'code' , 'nvarchar(240)', 'not null' go if type_id (N'comment') is null exec sp_addtype 'comment' , 'nvarchar(255)', 'not null' go if type_id (N'currency') is null exec sp_addtype 'currency' , 'decimal(15,4)', 'not null' go if type_id (N'description') is null exec sp_addtype 'description' , 'varchar(255)', 'not null' go if type_id (N'flag') is null exec sp_addtype 'flag' , 'bit', 'null' go if type_id (N'id') is null exec sp_addtype 'id' , 'uniqueidentifier', 'not null' go ", BreakUpToBatches = true } #endregion }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = scriptNumber++, Name = "DefaultFinancialDropTables", Description = "Default Financial Drop Tables", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" set quoted_identifier on go if object_id (N'financial_order') is not null drop table financial_order if object_id (N'financial_currency') is not null drop table financial_currency ", BreakUpToBatches = true } #endregion }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = scriptNumber++, Name = "DefaultFinancialCreateTables", Description = "Default Financial Create Tables", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" set quoted_identifier on go create table financial_currency ( financial_currency_id id, financial_currency_type_rcd code, financial_currency_against_financial_currency_type_rcd code not null, user_id id, date_time datetime not null, valid_from_date_time datetime not null, valid_until_date_time datetime null, amount currency, equals_amount currency, decimal_count integer not null, financial_currency_type_code name, financial_currency_type_name name, constraint xpkfinancial_currency primary key clustered(financial_currency_id asc) ) go create nonclustered index xif1financial_currency on financial_currency ( financial_currency_type_rcd asc ) go create nonclustered index xif2financial_currency on financial_currency ( financial_currency_against_financial_currency_type_rcd asc ) go create table financial_order ( financial_order_id id, user_id id, date_time datetime not null, comment name null, location_address_id id null, financial_currency_id id, financial_order_source_rcd code, client_id id, constraint xpkfinancial_order primary key clustered(financial_order_id asc) ) go create nonclustered index xif7financial_order on financial_order ( location_address_id asc ) go create nonclustered index xif8financial_order on financial_order ( financial_currency_id asc ) go create nonclustered index xif9financial_order on financial_order ( financial_order_source_rcd asc ) go create nonclustered index xif10financial_order on financial_order ( client_id asc ) go ", BreakUpToBatches = true } #endregion }); scripts.Add(lastScript = new DatabaseScript { DatabaseVersion = new DatabaseVersion { MajorNumber = majorNumber, MinorNumber = minorNumber, SequenceNumber = sequence++, DateTime = new DateTime(2018, 03, 07) }, ScriptNumber = scriptNumber++, Name = "FinancialReferenceData", Description = "Financial Reference Data", #region script ( *** ) DatabaseScriptBatch = new DatabaseScriptBatch { Script = @" set quoted_identifier on go set nocount on go -- financial currency -- SEK declare @financial_currency_swe_id uniqueidentifier set @financial_currency_swe_id = newid() insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (@financial_currency_swe_id, dbo.ref('financial_currency_type_ref.SEK'), dbo.ref('financial_currency_type_ref.SEK'), 'Swedish Krona', 1, dbo.ref('financial_currency_type_ref.SEK'), 1, getutcdate(), 0, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- EUR declare @financial_currency_eur_id uniqueidentifier set @financial_currency_eur_id = newid() insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (@financial_currency_eur_id, dbo.ref('financial_currency_type_ref.EUR'), dbo.ref('financial_currency_type_ref.EUR'), 'Euro', 1, dbo.ref('financial_currency_type_ref.EUR'), 1, getutcdate(), 4, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- against USD insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (newid(), dbo.ref('financial_currency_type_ref.EUR'), dbo.ref('financial_currency_type_ref.EUR'), 'Euro', 1, dbo.ref('financial_currency_type_ref.USD'), 7, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- THB declare @financial_currency_thb_id uniqueidentifier set @financial_currency_thb_id = newid() insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (@financial_currency_thb_id, dbo.ref('financial_currency_type_ref.THB'), dbo.ref('financial_currency_type_ref.THB'), 'Thai Bath', 1, dbo.ref('financial_currency_type_ref.THB'), 1, getutcdate(), 4, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- against USD insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (newid(), dbo.ref('financial_currency_type_ref.EUR'), dbo.ref('financial_currency_type_ref.EUR'), 'Euro', 1, dbo.ref('financial_currency_type_ref.USD'), 40, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- USD declare @financial_currency_usd_id uniqueidentifier set @financial_currency_usd_id = newid() insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (@financial_currency_usd_id, dbo.ref('financial_currency_type_ref.USD'), dbo.ref('financial_currency_type_ref.USD'), 'United States Dollar', 1, dbo.ref('financial_currency_type_ref.USD'), 1, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- against NOK insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (newid(), dbo.ref('financial_currency_type_ref.USD'), dbo.ref('financial_currency_type_ref.USD'), 'United States Dollar', 1, dbo.ref('financial_currency_type_ref.NOK'), 7, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- NOK declare @financial_currency_nok_id uniqueidentifier set @financial_currency_nok_id = newid() insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (@financial_currency_nok_id, dbo.ref('financial_currency_type_ref.NOK'), dbo.ref('financial_currency_type_ref.NOK'), 'Norwegian Krone', 1, dbo.ref('financial_currency_type_ref.NOK'), 1, getutcdate(), 1, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); -- against USD insert into financial_currency(financial_currency_id, financial_currency_type_rcd, financial_currency_type_code, financial_currency_type_name, amount, financial_currency_against_financial_currency_type_rcd, equals_amount, valid_from_date_time, decimal_count, user_id, date_time ) values (newid(), dbo.ref('financial_currency_type_ref.NOK'), dbo.ref('financial_currency_type_ref.NOK'), 'Norwegian Krone', 7, dbo.ref('financial_currency_type_ref.USD'), 1, getutcdate(), 2, '{FFFFFFFF-5555-5555-5555-FFFFFFFFFFFF}', getutcdate()); go ", BreakUpToBatches = true } #endregion }); }