public void GENERATE_DB_SCRIPT_Update()
{
if (Generate_fromDatabase == true)
{
Table_Name = The_Table_Name.Text;
}
else
{
Table_Name = Manual_Table_Name.Text;
}
Database_Script_Output_StringText += "/***********************************************************/" + Environment.NewLine;
Database_Script_Output_StringText += "/*************************************************/" + Environment.NewLine;
Database_Script_Output_StringText += "/****** - UPDATE STORED PROCEDURE - ******/" + Environment.NewLine;
Database_Script_Output_StringText += "/*************************************************/" + Environment.NewLine;
Database_Script_Output_StringText += "/***********************************************************/" + Environment.NewLine;
//UPDATE
Database_Script_Output_StringText += "/****** Object: StoredProcedure [dbo].[SP_DMCS_UPDATE_" + Table_Name.ToUpper() + "] Script Date: " + DateTime.Now + " ******/" + Environment.NewLine;
Database_Script_Output_StringText += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_DMCS_UPDATE_" + Table_Name.ToUpper() + "]') AND type in (N'P', N'PC'))" + Environment.NewLine;
Database_Script_Output_StringText += "DROP PROCEDURE [dbo].[SP_DMCS_UPDATE_" + Table_Name.ToUpper() + "]" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "SET ANSI_NULLS ON" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "SET QUOTED_IDENTIFIER ON" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "CREATE PROCEDURE [dbo].[SP_DMCS_UPDATE_" + Table_Name.ToUpper() + "]" + Environment.NewLine;
for (int i = 0; i < DB_Array.Length - 1; i++)
{
script_generator script_generator = new script_generator();
script_generator = script_generator.Select(Table_Name, DB_Array[i]);
//check for percisionable value
if (script_generator.result == 0 || DB_Type_Array[i] == "text" || DB_Type_Array[i] == "geography" || DB_Type_Array[i] == "image")
{
if (i == DB_Array.Length - 2)
{
Database_Script_Output_StringText += " @" + DB_Array[i] + " as " + DB_Type_Array[i] + Environment.NewLine;
}
else
{
Database_Script_Output_StringText += " @" + DB_Array[i] + " as " + DB_Type_Array[i] + "," + Environment.NewLine;
}
}
//check for MAX
else if (script_generator.result == -1)
{
if (i == DB_Array.Length - 2)
{
Database_Script_Output_StringText += " @" + DB_Array[i] + " as " + DB_Type_Array[i] + "(MAX)" + Environment.NewLine;
}
else
{
Database_Script_Output_StringText += " @" + DB_Array[i] + " as " + DB_Type_Array[i] + "(MAX)," + Environment.NewLine;
}
}
//All others
else
{
if (i == DB_Array.Length - 2)
{
Database_Script_Output_StringText += " @" + DB_Array[i] + " as " + DB_Type_Array[i] + "(" + script_generator.result + ")" + Environment.NewLine;
}
else
{
Database_Script_Output_StringText += " @" + DB_Array[i] + " as " + DB_Type_Array[i] + "(" + script_generator.result + ")," + Environment.NewLine;
}
}
}
Database_Script_Output_StringText += "AS" + Environment.NewLine;
Database_Script_Output_StringText += "BEGIN" + Environment.NewLine;
Database_Script_Output_StringText += " -- SET NOCOUNT ON added to prevent extra result sets from" + Environment.NewLine;
Database_Script_Output_StringText += " -- interfering with SELECT statements." + Environment.NewLine;
Database_Script_Output_StringText += " SET NOCOUNT ON;" + Environment.NewLine;
for (int i = 1; i < DB_Array.Length - 1; i++)
{
if (DB_Type_Array[i] == "varchar" || DB_Type_Array[i] == "char")
{
Database_Script_Output_StringText += " IF @" + DB_Array[i] + " = ''" + Environment.NewLine;
Database_Script_Output_StringText += " BEGIN" + Environment.NewLine;
Database_Script_Output_StringText += " SET @" + DB_Array[i] + " = null" + Environment.NewLine;
Database_Script_Output_StringText += " END" + Environment.NewLine;
}
else if (DB_Type_Array[i] == "datetime")
{
Database_Script_Output_StringText += " IF @" + DB_Array[i] + " = '9/9/1900 12:00:00 AM'" + Environment.NewLine;
Database_Script_Output_StringText += " BEGIN" + Environment.NewLine;
Database_Script_Output_StringText += " SET @" + DB_Array[i] + " = null" + Environment.NewLine;
Database_Script_Output_StringText += " END" + Environment.NewLine;
}
else if (DB_Type_Array[i] == "text" || DB_Type_Array[i] == "geography" || DB_Type_Array[i] == "image")
{
}
else
{
Database_Script_Output_StringText += " IF @" + DB_Array[i] + " = 0" + Environment.NewLine;
Database_Script_Output_StringText += " BEGIN" + Environment.NewLine;
Database_Script_Output_StringText += " SET @" + DB_Array[i] + " = null" + Environment.NewLine;
Database_Script_Output_StringText += " END" + Environment.NewLine;
}
}
Database_Script_Output_StringText += " UPDATE [dbo].[" + Table_Name + "]" + Environment.NewLine;
Database_Script_Output_StringText += " SET" + Environment.NewLine;
for (int i = 1; i < DB_Array.Length - 1; i++)
{
if (i == DB_Array.Length - 1 - 1)
{
Database_Script_Output_StringText += " [" + DB_Array[i] + "] = @" + DB_Array[i] + Environment.NewLine;
}
else
{
Database_Script_Output_StringText += " [" + DB_Array[i] + "] = @" + DB_Array[i] + "," + Environment.NewLine;
}
}
Database_Script_Output_StringText += " WHERE " + DB_Array[0] + " = @" + DB_Array[0] + Environment.NewLine;
Database_Script_Output_StringText += "END" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
}
public void GENERATE_DB_SCRIPT_Create_Table()
{
if (Generate_fromDatabase == true)
{
Table_Name = The_Table_Name.Text;
}
else
{
Table_Name = Manual_Table_Name.Text;
}
Database_Script_Output_StringText += "/****** Object: Table [dbo].[" + Table_Name + "] Script Date: " + DateTime.Now + " ******/" + Environment.NewLine;
Database_Script_Output_StringText += "SET ANSI_NULLS ON" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "SET QUOTED_IDENTIFIER ON" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "SET ANSI_PADDING ON" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "CREATE TABLE [dbo].[" + Table_Name + "](" + Environment.NewLine;
Database_Script_Output_StringText += " [" + DB_Array[0] + "] " + DB_Type_Array[0] + " IDENTITY(1,1) " + Null_Array[0] + "," + Environment.NewLine;
for (int i = 1; i < DB_Array.Length - 1; i++)
{
script_generator script_generator = new script_generator();
script_generator = script_generator.Select(Table_Name, DB_Array[i]);
//check for percisionable value
if (script_generator.result == 0)
{
Database_Script_Output_StringText += " [" + DB_Array[i] + "] " + DB_Type_Array[i] + " " + Null_Array[i] + "," + Environment.NewLine;
}
//check for MAX
else if (script_generator.result == -1)
{
Database_Script_Output_StringText += " [" + DB_Array[i] + "] " + DB_Type_Array[i] + "(MAX) " + Null_Array[i] + "," + Environment.NewLine;
}
//All others
else
{
Database_Script_Output_StringText += " [" + DB_Array[i] + "] " + DB_Type_Array[i] + "(" + script_generator.result + ") " + Null_Array[i] + "," + Environment.NewLine;
}
}
Database_Script_Output_StringText += " CONSTRAINT [" + Table_Name + "_" + DB_Array[0] + "_pk] PRIMARY KEY CLUSTERED " + Environment.NewLine;
Database_Script_Output_StringText += "(" + Environment.NewLine;
Database_Script_Output_StringText += " [" + DB_Array[0] + "] ASC" + Environment.NewLine;
Database_Script_Output_StringText += ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" + Environment.NewLine;
Database_Script_Output_StringText += ") ON [PRIMARY] " + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "SET ANSI_PADDING OFF" + Environment.NewLine;
Database_Script_Output_StringText += "GO" + Environment.NewLine;
Database_Script_Output_StringText += "/*************************************************/" + Environment.NewLine;
Database_Script_Output_StringText += "/****** FK TEMPLATE - NEEDS ACCURATE DATA - ******/" + Environment.NewLine;
Database_Script_Output_StringText += "/*************************************************/" + Environment.NewLine;
Database_Script_Output_StringText += "/********** ALTER TABLE [dbo].[THIS_TABLE_NAME] WITH CHECK ADD CONSTRAINT [NAME_OF_CONSTRAINT] FOREIGN KEY([FK_ON_THIS_TABLE]) **********/" + Environment.NewLine;
Database_Script_Output_StringText += "/********** REFERENCES [dbo].[OTHER_TABLE_NAME] ([OTHER_TABLE_FK]) **********/" + Environment.NewLine;
Database_Script_Output_StringText += "/********** GO **********/" + Environment.NewLine;
Database_Script_Output_StringText += "/********** ALTER TABLE [dbo].[THIS_TABLE_NAME] CHECK CONSTRAINT [NAME_OF_CONSTRAINT] **********/" + Environment.NewLine;
Database_Script_Output_StringText += "/********** GO **********/" + Environment.NewLine;
}
