public script_generator Check_Identity(string table_name)
{
script_generator script_generator = new script_generator();
string ConnectionString = IDManager.connection();
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("[SP_SCRIPT_GENERATOR_SELECT_TABLE_IDENTITY]", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@table_name", table_name);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
if (!rdr.IsDBNull(0))
{
script_generator.result = rdr.GetInt32(0);
}
else
{
script_generator.result = 100;
}
}
else
{
script_generator.SetColumnDefaults();
}
}
catch (Exception ex)
{
script_generator.SetColumnDefaults();
return(script_generator);
}
finally
{
con.Close();
}
return(script_generator);
}
public script_generator Select(string table_name, string column_name)
{
script_generator script_generator = new script_generator();
string ConnectionString = IDManager.connection();
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("[SP_SCRIPT_GENERATOR_SELECT_PERCISION]", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@table_name", table_name);
cmd.Parameters.AddWithValue("@column_name", column_name);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
if (!rdr.IsDBNull(0))
{
script_generator.result = rdr.GetInt32(0);
}
else
{
script_generator.result = 0;
}
}
else
{
script_generator.SetColumnDefaults();
}
}
catch (Exception ex)
{
script_generator.SetColumnDefaults();
return script_generator;
}
finally
{
con.Close();
}
return script_generator;
}
public void GENERATE_OBJECT_Insert()
{
if (Generate_fromDatabase == true)
{
Table_Name = The_Table_Name.Text;
}
else
{
Table_Name = Manual_Table_Name.Text;
}
//INSERT
Object_Output_StringText += " public " + Table_Name + " Insert(" + Table_Name + " id)" + Environment.NewLine;
Object_Output_StringText += " {" + Environment.NewLine;
Object_Output_StringText += " string ConnectionString = IDManager.connection();" + Environment.NewLine;
Object_Output_StringText += " SqlConnection con = new SqlConnection(ConnectionString);" + Environment.NewLine;
Object_Output_StringText += " try" + Environment.NewLine;
Object_Output_StringText += " {" + Environment.NewLine;
Object_Output_StringText += " con.Open();" + Environment.NewLine;
Object_Output_StringText += " SqlCommand cmd = new SqlCommand(\"SP_DMCS_INSERT_" + Table_Name.ToUpper() + "\", con);" + Environment.NewLine;
Object_Output_StringText += " cmd.CommandType = System.Data.CommandType.StoredProcedure;" + Environment.NewLine;
//check to see if tables PK is sequential
script_generator = script_generator.Check_Identity(Table_Name);
//if not..
if (script_generator.result == 0)
{
for (int i = 0; i < DB_Array.Length - 1; i++)
{
Object_Output_StringText += " cmd.Parameters.AddWithValue(\"" + "@" + DB_Array[i] + "\", id." + DB_Array[i] + ");" + Environment.NewLine;
}
}
else
{
for (int i = 1; i < DB_Array.Length - 1; i++)
{
Object_Output_StringText += " cmd.Parameters.AddWithValue(\"" + "@" + DB_Array[i] + "\", id." + DB_Array[i] + ");" + Environment.NewLine;
}
}
Object_Output_StringText += " cmd.ExecuteReader();" + Environment.NewLine;
Object_Output_StringText += " con.Close();" + Environment.NewLine;
Object_Output_StringText += " con.Open();" + Environment.NewLine;
Object_Output_StringText += " cmd = new SqlCommand(\"SP_DMCS_GET_" + Table_Name.ToUpper() + "\", con);" + Environment.NewLine;
Object_Output_StringText += " cmd.CommandType = System.Data.CommandType.StoredProcedure;" + Environment.NewLine;
for (int i = 1; i < DB_Array.Length - 1; i++)
{
Object_Output_StringText += " cmd.Parameters.AddWithValue(\"" + "@" + DB_Array[i] + "\", id." + DB_Array[i] + ");" + Environment.NewLine;
}
Object_Output_StringText += " SqlDataReader rdr = cmd.ExecuteReader();" + Environment.NewLine;
Object_Output_StringText += " if (rdr.HasRows)" + Environment.NewLine;
Object_Output_StringText += " {" + Environment.NewLine;
Object_Output_StringText += " rdr.Read();" + Environment.NewLine;
Object_Output_StringText += " id." + DB_Array[0] + " = rdr.GetInt32(0);" + Environment.NewLine;
Object_Output_StringText += " }" + Environment.NewLine;
Object_Output_StringText += " }" + Environment.NewLine;
Object_Output_StringText += " catch (Exception ex)" + Environment.NewLine;
Object_Output_StringText += " {" + Environment.NewLine;
Object_Output_StringText += " id.SetColumnDefaults();" + Environment.NewLine;
Object_Output_StringText += " }" + Environment.NewLine;
Object_Output_StringText += " finally" + Environment.NewLine;
Object_Output_StringText += " {" + Environment.NewLine;
Object_Output_StringText += " con.Close();" + Environment.NewLine;
Object_Output_StringText += " }" + Environment.NewLine;
Object_Output_StringText += " return id;" + Environment.NewLine;
Object_Output_StringText += " }" + Environment.NewLine;
}
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;
}
