/// <summary>
/// Creates a database out of the structure it is given
/// </summary>
/// <param name="Database">Database structure</param>
/// <param name="ConnectionString">The connection string to the database's location</param>
public static void CreateDatabase(Database Database, string ConnectionString)
{
string Command = BuildCommands(Database);
string[] Splitter = { "\n" };
string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
ConnectionString = Regex.Replace(ConnectionString, "Pooling=(.*?;)", "", RegexOptions.IgnoreCase) + ";Pooling=false;";
string DatabaseConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.*?;)", "", RegexOptions.IgnoreCase);
using (SQLHelper Helper = new SQLHelper(Commands[0], DatabaseConnectionString, CommandType.Text))
{
Helper.ExecuteNonQuery();
}
using (SQLHelper Helper = new SQLHelper("", ConnectionString, CommandType.Text))
{
IBatchCommand Batcher = Helper.Batch();
for (int x = 1; x < Commands.Length; ++x)
{
if (Commands[x].Contains("CREATE TRIGGER") || Commands[x].Contains("CREATE FUNCTION"))
{
if (Batcher.CommandCount > 0)
{
Helper.ExecuteNonQuery();
Batcher = Helper.Batch();
}
Batcher.AddCommand(Commands[x], CommandType.Text);
if (x < Commands.Length - 1)
{
Helper.ExecuteNonQuery();
Batcher = Helper.Batch();
}
}
else
{
Batcher.AddCommand(Commands[x], CommandType.Text);
}
}
Helper.ExecuteNonQuery();
}
}
/// <summary>
/// Sets up the views
/// </summary>
/// <param name="ConnectionString">Connection string</param>
/// <param name="Temp">Database object</param>
private static void SetupViews(string ConnectionString, Database Temp)
{
foreach (View View in Temp.Views)
{
string Command = "SELECT OBJECT_DEFINITION(sys.views.object_id) as Definition FROM sys.views WHERE sys.views.name=@ViewName";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.AddParameter("@ViewName", View.Name)
.ExecuteReader();
if (Helper.Read())
{
View.Definition = Helper.GetParameter("Definition", "");
}
}
Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE] FROM sys.views INNER JOIN sys.columns on sys.columns.object_id=sys.views.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id WHERE (sys.views.name = @ViewName) AND (sys.systypes.xusertype <> 256)";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.AddParameter("@ViewName", View.Name)
.ExecuteReader();
while (Helper.Read())
{
string ColumnName = Helper.GetParameter("Column", "");
string ColumnType = Helper.GetParameter("COLUMN TYPE", "");
int MaxLength = Helper.GetParameter("MAX LENGTH", 0);
if (ColumnType == "nvarchar")
MaxLength /= 2;
bool Nullable = Helper.GetParameter("IS NULLABLE", false);
View.AddColumn<string>(ColumnName, ColumnType.TryTo<string, SqlDbType>().ToDbType(), MaxLength, Nullable);
}
}
}
}
/// <summary>
/// Sets up the tables (pulls columns, etc.)
/// </summary>
/// <param name="ConnectionString">Connection string</param>
/// <param name="Temp">Database object</param>
private static void SetupTables(string ConnectionString, Database Temp)
{
foreach (Table Table in Temp.Tables)
{
string Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE], sys.columns.is_identity as [IS IDENTITY], sys.index_columns.index_id as [IS INDEX], key_constraints.name as [PRIMARY KEY], key_constraints_1.name as [UNIQUE], tables_1.name as [FOREIGN KEY TABLE], columns_1.name as [FOREIGN KEY COLUMN], sys.default_constraints.definition as [DEFAULT VALUE] FROM sys.tables INNER JOIN sys.columns on sys.columns.object_id=sys.tables.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id LEFT OUTER JOIN sys.index_columns on sys.index_columns.object_id=sys.tables.object_id and sys.index_columns.column_id=sys.columns.column_id LEFT OUTER JOIN sys.key_constraints on sys.key_constraints.parent_object_id=sys.tables.object_id and sys.key_constraints.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=sys.key_constraints.unique_index_id and sys.key_constraints.type='PK' LEFT OUTER JOIN sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id=sys.tables.object_id and sys.foreign_key_columns.parent_column_id=sys.columns.column_id LEFT OUTER JOIN sys.tables as tables_1 on tables_1.object_id=sys.foreign_key_columns.referenced_object_id LEFT OUTER JOIN sys.columns as columns_1 on columns_1.column_id=sys.foreign_key_columns.referenced_column_id and columns_1.object_id=tables_1.object_id LEFT OUTER JOIN sys.key_constraints as key_constraints_1 on key_constraints_1.parent_object_id=sys.tables.object_id and key_constraints_1.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=key_constraints_1.unique_index_id and key_constraints_1.type='UQ' LEFT OUTER JOIN sys.default_constraints on sys.default_constraints.object_id=sys.columns.default_object_id WHERE (sys.tables.name = @TableName) AND (sys.systypes.xusertype <> 256)";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.AddParameter("@TableName", Table.Name)
.ExecuteReader();
while (Helper.Read())
{
string ColumnName = Helper.GetParameter("Column", "");
string ColumnType = Helper.GetParameter("COLUMN TYPE", "");
int MaxLength = Helper.GetParameter("MAX LENGTH", 0);
if (ColumnType == "nvarchar")
MaxLength /= 2;
bool Nullable = Helper.GetParameter("IS NULLABLE", false);
bool Identity = Helper.GetParameter("IS IDENTITY", false);
bool Index = Helper.GetParameter("IS INDEX", 0) != 0;
bool PrimaryKey = Helper.GetParameter("PRIMARY KEY", "").IsNullOrEmpty() ? false : true;
bool Unique = Helper.GetParameter("UNIQUE", "").IsNullOrEmpty() ? false : true;
string ForeignKeyTable = Helper.GetParameter("FOREIGN KEY TABLE", "");
string ForeignKeyColumn = Helper.GetParameter("FOREIGN KEY COLUMN", "");
string DefaultValue = Helper.GetParameter("DEFAULT VALUE", "");
if (Table.ContainsColumn(ColumnName))
{
Table.AddForeignKey(ColumnName, ForeignKeyTable, ForeignKeyColumn);
}
else
{
Table.AddColumn(ColumnName, ColumnType.TryTo<string, SqlDbType>().ToDbType(), MaxLength, Nullable, Identity, Index, PrimaryKey, Unique, ForeignKeyTable, ForeignKeyColumn, DefaultValue);
}
}
}
Command = "SELECT sys.triggers.name as Name,sys.trigger_events.type as Type,OBJECT_DEFINITION(sys.triggers.object_id) as Definition FROM sys.triggers INNER JOIN sys.trigger_events ON sys.triggers.object_id=sys.trigger_events.object_id INNER JOIN sys.tables on sys.triggers.parent_id=sys.tables.object_id where sys.tables.name=@TableName";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.AddParameter("@TableName", Table.Name)
.ExecuteReader();
while (Helper.Read())
{
string Name = Helper.GetParameter("Name", "");
int Type = Helper.GetParameter("Type", 0);
string Definition = Helper.GetParameter("Definition", "");
Table.AddTrigger(Name, Definition, Type.ToString(CultureInfo.InvariantCulture).TryTo<string, TriggerType>());
}
}
}
foreach (Table Table in Temp.Tables)
{
Table.SetupForeignKeys();
}
}
/// <summary>
/// Sets up stored procedures
/// </summary>
/// <param name="ConnectionString">Connection string</param>
/// <param name="Temp">Database object</param>
private static void SetupStoredProcedures(string ConnectionString, Database Temp)
{
string Command = "SELECT sys.procedures.name as NAME,OBJECT_DEFINITION(sys.procedures.object_id) as DEFINITION FROM sys.procedures";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.ExecuteReader();
while (Helper.Read())
{
string ProcedureName = Helper.GetParameter("NAME", "");
string Definition = Helper.GetParameter("DEFINITION", "");
Temp.AddStoredProcedure(ProcedureName, Definition);
}
}
foreach (StoredProcedure Procedure in Temp.StoredProcedures)
{
Command = "SELECT sys.systypes.name as TYPE,sys.parameters.name as NAME,sys.parameters.max_length as LENGTH,sys.parameters.default_value as [DEFAULT VALUE] FROM sys.procedures INNER JOIN sys.parameters on sys.procedures.object_id=sys.parameters.object_id INNER JOIN sys.systypes on sys.systypes.xusertype=sys.parameters.system_type_id WHERE sys.procedures.name=@ProcedureName AND (sys.systypes.xusertype <> 256)";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.AddParameter("@ProcedureName", Procedure.Name)
.ExecuteReader();
while (Helper.Read())
{
string Type = Helper.GetParameter("TYPE", "");
string Name = Helper.GetParameter("NAME", "");
int Length = Helper.GetParameter("LENGTH", 0);
if (Type == "nvarchar")
Length /= 2;
string Default = Helper.GetParameter("DEFAULT VALUE", "");
Procedure.AddColumn<string>(Name, Type.TryTo<string, SqlDbType>().ToDbType(), Length, Default);
}
}
}
}
/// <summary>
/// Sets up the functions
/// </summary>
/// <param name="ConnectionString">Connection string</param>
/// <param name="Temp">Database object</param>
private static void SetupFunctions(string ConnectionString, Database Temp)
{
string Command = "SELECT SPECIFIC_NAME as NAME,ROUTINE_DEFINITION as DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE='FUNCTION'";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.ExecuteReader();
while (Helper.Read())
{
string Name = (string)Helper.GetParameter("NAME", "");
string Definition = (string)Helper.GetParameter("DEFINITION", "");
Temp.AddFunction(Name, Definition);
}
}
}
/// <summary>
/// Gets the tables for a database
/// </summary>
/// <param name="ConnectionString">Connection string</param>
/// <param name="Temp">The database object</param>
private static void GetTables(string ConnectionString, Database Temp)
{
string Command = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
Helper.ExecuteReader();
while (Helper.Read())
{
string TableName = Helper.GetParameter("TABLE_NAME", "");
string TableType = Helper.GetParameter("TABLE_TYPE", "");
if (TableType == "BASE TABLE")
{
Temp.AddTable(TableName);
}
else if (TableType == "VIEW")
{
Temp.AddView(TableName);
}
}
}
}
/// <summary>
/// Builds the list of commands to build the database
/// </summary>
/// <param name="Database">Database object</param>
/// <returns>The commands needed to build the database</returns>
private static string BuildCommands(Database Database)
{
StringBuilder Builder = new StringBuilder();
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE DATABASE ").Append(Database.Name).Append("'\n");
foreach (Table Table in Database.Tables)
{
Builder.Append(GetTableCommand(Table));
}
foreach (Table Table in Database.Tables)
{
Builder.Append(GetForeignKeyCommand(Table));
}
foreach (Table Table in Database.Tables)
{
Builder.Append(GetTriggerCommand(Table));
}
foreach (Function Function in Database.Functions)
{
Builder.Append(GetFunctionCommand(Function));
}
foreach (View View in Database.Views)
{
Builder.Append(GetViewCommand(View));
}
foreach (StoredProcedure StoredProcedure in Database.StoredProcedures)
{
Builder.Append(GetStoredProcedure(StoredProcedure));
}
return Builder.ToString();
}
private static string BuildCommands(Database DesiredDatabase, Database CurrentDatabase)
{
StringBuilder Builder = new StringBuilder();
foreach (Table Table in DesiredDatabase.Tables)
{
Table CurrentTable = CurrentDatabase[Table.Name];
if (CurrentTable == null)
{
Builder.Append(GetTableCommand(Table));
}
else
{
Builder.Append(GetAlterTableCommand(Table, CurrentTable));
}
}
foreach (Table Table in DesiredDatabase.Tables)
{
Table CurrentTable = CurrentDatabase[Table.Name];
if (CurrentTable == null)
{
Builder.Append(GetForeignKeyCommand(Table));
}
}
foreach (Table Table in DesiredDatabase.Tables)
{
Table CurrentTable = CurrentDatabase[Table.Name];
if (CurrentTable == null)
{
Builder.Append(GetTriggerCommand(Table));
}
else
{
Builder.Append(GetAlterTriggerCommand(Table, CurrentTable));
}
}
foreach (Function Function in DesiredDatabase.Functions)
{
bool Found = false;
foreach (Function CurrentFunction in CurrentDatabase.Functions)
{
if (CurrentFunction.Name == Function.Name)
{
Builder.Append(GetAlterFunctionCommand(Function, CurrentFunction));
Found = true;
break;
}
}
if (!Found)
{
Builder.Append(GetFunctionCommand(Function));
}
}
foreach (View View in DesiredDatabase.Views)
{
bool Found = false;
foreach (View CurrentView in CurrentDatabase.Views)
{
if (CurrentView.Name == View.Name)
{
Builder.Append(GetAlterViewCommand(View, CurrentView));
Found = true;
break;
}
}
if (!Found)
{
Builder.Append(GetViewCommand(View));
}
}
foreach (StoredProcedure StoredProcedure in DesiredDatabase.StoredProcedures)
{
bool Found = false;
foreach (StoredProcedure CurrentStoredProcedure in CurrentDatabase.StoredProcedures)
{
if (StoredProcedure.Name == CurrentStoredProcedure.Name)
{
Builder.Append(GetAlterStoredProcedure(StoredProcedure, CurrentStoredProcedure));
Found = true;
break;
}
}
if (!Found)
{
Builder.Append(GetStoredProcedure(StoredProcedure));
}
}
return Builder.ToString();
}
/// <summary>
/// Gets the structure of a database
/// </summary>
/// <param name="ConnectionString">Connection string</param>
/// <returns>The database structure</returns>
public static Database GetDatabaseStructure(string ConnectionString)
{
string DatabaseName = Regex.Match(ConnectionString, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", "");
if (!DoesDatabaseExist(DatabaseName, ConnectionString))
return null;
Database Temp = new Database(DatabaseName);
GetTables(ConnectionString, Temp);
SetupTables(ConnectionString, Temp);
SetupViews(ConnectionString, Temp);
SetupStoredProcedures(ConnectionString, Temp);
SetupFunctions(ConnectionString, Temp);
return Temp;
}