As time has gone on I've made a realization. The way I was going about creating the database in my ORM wasn't that great (very inflexible and hard to maintain). So I've been working on a way to simplify that portion of the code. While I was at it, I thought it might also be nice if I could pull the structure of a current database and have the ability to create a duplicate of it (the structure, not the content). So here is what I came up with:
/// <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)
{
try
{
string Command = BuildCommands(Database);
string[] Splitter = { "\n" };
string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
string DatabaseConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.*?;)", "");
using (SQLHelper Helper = new SQLHelper(Commands[0], DatabaseConnectionString, CommandType.Text))
{
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { throw; }
finally { Helper.Close(); }
}
for (int x = 1; x < Commands.Length; ++x)
{
using (SQLHelper Helper = new SQLHelper(Commands[x], ConnectionString, CommandType.Text))
{
try
{
Helper.Open();
Helper.ExecuteNonQuery();
}
catch { throw; }
finally { Helper.Close(); }
}
}
}
catch { throw; }
}
/// <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(";", "");
Database Temp = new Database(DatabaseName);
GetTables(ConnectionString, Temp);
SetupTables(ConnectionString, Temp);
SetupViews(ConnectionString, Temp);
SetupStoredProcedures(ConnectionString, Temp);
SetupFunctions(ConnectionString, Temp);
return Temp;
}
/// <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 (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();
}
/// <summary>
/// Gets the foreign keys creation command
/// </summary>
/// <param name="Table">Table object</param>
/// <returns>The string creating the foreign keys</returns>
private static string GetForeignKeyCommand(Table Table)
{
StringBuilder Builder=new StringBuilder();
foreach (Column Column in Table.Columns)
{
if (Column.ForeignKey.Count > 0)
{
foreach (Column ForeignKey in Column.ForeignKey)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ");
Builder.Append(Column.ParentTable.Name).Append(" ADD FOREIGN KEY (");
Builder.Append(Column.Name).Append(") REFERENCES ").Append(ForeignKey.ParentTable.Name);
Builder.Append("(").Append(ForeignKey.Name).Append(")'\n");
}
}
}
return Builder.ToString();
}
/// <summary>
/// Gets the stored procedure creation command
/// </summary>
/// <param name="StoredProcedure">The stored procedure object</param>
/// <returns>The string creating the stored procedure</returns>
private static string GetStoredProcedure(StoredProcedure StoredProcedure)
{
return StoredProcedure.Definition.Replace("\n"," ").Replace("\r"," ")+"\n";
}
/// <summary>
/// Gets the view creation command
/// </summary>
/// <param name="View">The view object</param>
/// <returns>The string creating the view</returns>
private static string GetViewCommand(View View)
{
return View.Definition.Replace("\n", " ").Replace("\r", " ") + "\n";
}
/// <summary>
/// Gets the function command
/// </summary>
/// <param name="Function">The function object</param>
/// <returns>The string creating the function</returns>
private static string GetFunctionCommand(Function Function)
{
return Function.Definition.Replace("\n", " ").Replace("\r", " ") + "\n";
}
/// <summary>
/// Gets the table creation commands
/// </summary>
/// <param name="Table">Table object</param>
/// <returns>The string containing the creation commands</returns>
private static string GetTableCommand(Table Table)
{
StringBuilder Builder=new StringBuilder();
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE TABLE ").Append(Table.Name).Append("(");
string Splitter="";
foreach (Column Column in Table.Columns)
{
Builder.Append(Splitter).Append(Column.Name).Append(" ").Append(Column.DataType.ToString());
if (Column.DataType == SqlDbType.VarChar || Column.DataType == SqlDbType.NVarChar)
{
if (Column.Length == -1)
{
Builder.Append("(MAX)");
}
else
{
Builder.Append("(").Append(Column.Length.ToString()).Append(")");
}
}
if (!Column.Nullable)
{
Builder.Append(" NOT NULL");
}
if (Column.Unique)
{
Builder.Append(" UNIQUE");
}
if (Column.PrimaryKey)
{
Builder.Append(" PRIMARY KEY");
}
if (!string.IsNullOrEmpty(Column.Default))
{
Builder.Append(" DEFAULT ").Append(Column.Default.Replace("(", "").Replace(")", "").Replace("'","''"));
}
if (Column.AutoIncrement)
{
Builder.Append(" IDENTITY");
}
Splitter = ",";
}
Builder.Append(")'\n");
int Counter = 0;
foreach (Column Column in Table.Columns)
{
if (Column.Index&&Column.Unique)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE UNIQUE INDEX ");
Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
Builder.Append("'\n");
}
else if (Column.Index)
{
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE INDEX ");
Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
Builder.Append("'\n");
}
++Counter;
}
return Builder.ToString();
}
/// <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))
{
try
{
Helper.Open();
Helper.ExecuteReader();
while (Helper.Read())
{
string Name = (string)Helper.GetParameter("NAME", "");
string Definition = (string)Helper.GetParameter("DEFINITION", "");
Temp.AddFunction(Name, Definition);
}
}
catch { }
finally { Helper.Close(); }
}
}
/// <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))
{
try
{
Helper.Open();
Helper.ExecuteReader();
while (Helper.Read())
{
string ProcedureName = (string)Helper.GetParameter("NAME", "");
string Definition = (string)Helper.GetParameter("DEFINITION", "");
Temp.AddStoredProcedure(ProcedureName, Definition);
}
}
catch { }
finally { Helper.Close(); }
}
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))
{
try
{
Helper.Open();
Helper.AddParameter("@ProcedureName", Procedure.Name, 128);
Helper.ExecuteReader();
while (Helper.Read())
{
string Type = (string)Helper.GetParameter("TYPE", "");
string Name = (string)Helper.GetParameter("NAME", "");
int Length = int.Parse(Helper.GetParameter("LENGTH", 0).ToString());
string Default = (string)Helper.GetParameter("DEFAULT VALUE", "");
Procedure.AddColumn(Name, Type, Length, Default);
}
}
catch { }
finally { Helper.Close(); }
}
}
}
/// <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))
{
try
{
Helper.Open();
Helper.AddParameter("@ViewName", View.Name, 128);
Helper.ExecuteReader();
if (Helper.Read())
{
View.Definition = (string)Helper.GetParameter("Definition", "");
}
}
catch { }
finally { Helper.Close(); }
}
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))
{
try
{
Helper.Open();
Helper.AddParameter("@ViewName", View.Name, 128);
Helper.ExecuteReader();
while (Helper.Read())
{
string ColumnName = (string)Helper.GetParameter("Column", "");
string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
View.AddColumn(ColumnName, ColumnType, MaxLength, Nullable);
}
}
catch { }
finally { Helper.Close(); }
}
}
}
/// <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))
{
try
{
Helper.Open();
Helper.AddParameter("@TableName", Table.Name, 128);
Helper.ExecuteReader();
while (Helper.Read())
{
string ColumnName = (string)Helper.GetParameter("Column", "");
string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
bool Identity = (bool)Helper.GetParameter("IS IDENTITY", false);
bool Index = (bool)((int)Helper.GetParameter("IS INDEX", 0) != 0);
bool PrimaryKey = string.IsNullOrEmpty((string)Helper.GetParameter("PRIMARY KEY", "")) ? false : true;
bool Unique = string.IsNullOrEmpty((string)Helper.GetParameter("UNIQUE", "")) ? false : true;
string ForeignKeyTable = (string)Helper.GetParameter("FOREIGN KEY TABLE", "");
string ForeignKeyColumn = (string)Helper.GetParameter("FOREIGN KEY COLUMN", "");
string DefaultValue = (string)Helper.GetParameter("DEFAULT VALUE", "");
if (Table.ContainsColumn(ColumnName))
{
Table.AddForeignKey(ColumnName, ForeignKeyTable, ForeignKeyColumn);
}
else
{
Table.AddColumn(ColumnName, ColumnType, MaxLength, Nullable, Identity, Index, PrimaryKey, Unique, ForeignKeyTable, ForeignKeyColumn, DefaultValue);
}
}
}
catch { }
finally { Helper.Close(); }
}
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))
{
try
{
Helper.Open();
Helper.AddParameter("@TableName", Table.Name, 128);
Helper.ExecuteReader();
while (Helper.Read())
{
string Name = (string)Helper.GetParameter("Name", "");
int Type = (int)Helper.GetParameter("Type", 0);
string Definition = (string)Helper.GetParameter("Definition", "");
Table.AddTrigger(Name, Definition, Type);
}
}
catch { }
finally { Helper.Close(); }
}
}
foreach (Table Table in Temp.Tables)
{
Table.SetupForeignKeys();
}
}
/// <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))
{
try
{
Helper.Open();
Helper.ExecuteReader();
while (Helper.Read())
{
string TableName = (string)Helper.GetParameter("TABLE_NAME", "");
string TableType = (string)Helper.GetParameter("TABLE_TYPE", "");
if (TableType == "BASE TABLE")
{
Temp.AddTable(TableName);
}
else if (TableType == "VIEW")
{
Temp.AddView(TableName);
}
}
}
catch { }
finally { Helper.Close(); }
}
}
/// <summary>
/// Checks if something exists
/// </summary>
/// <param name="Command">Command to run</param>
/// <param name="Name">Name of the item</param>
/// <param name="ConnectionString">Connection string</param>
/// <returns>True if it exists, false otherwise</returns>
private static bool CheckExists(string Command, string Name, string ConnectionString)
{
bool Exists = false;
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
try
{
Helper.Open();
Helper.AddParameter("@Name", Name, 200);
Helper.ExecuteReader();
if (Helper.Read())
Exists = true;
}
catch { }
finally { Helper.Close(); }
}
return Exists;
}
Most of these functions are pretty simple. The database creation functions simply pull the info that they need from the structure. For functions, stored procedures, and views this is simply the objects definition (no creation on the fly). For tables it simply pulls information from the column objects (is it nullable, unique, etc.). The functions dealing with pulling an existing database's structure are a bit more complex. Specifically some of the queries that they are running. One of the wonderful items that most people seem to forget about in SQL Server are the sys views. In those views you can find statistical information, information dealing with security, and of course the structure of a database. Most of them are easy to figure out what they are (sys.triggers are triggers, sys.tables are tables, etc.), but for some of the items it's a pain in the ass. Take for instance this query:
I'm certain someone else can make something a bit better than what I came up with but this thing is really only pulling a list of columns in a table. Most of that information is stored in the sys.columns table, but sadly it doesn't hold any information about foreign keys, primary keys, whether it is unique, default value, is it an index, etc. So you have to end up joining it with about 10 different tables and you end up with a query like the one above. Past that though, most of the other ones are rather simple (even getting the current definition of an object is simple with the use of OBJECT_DEFINITION). The only other thing to show is the actual structure of the data holders, but I'm not quite ready on that front (I still need to clean up some code) but to be honest, you can probably figure that out on your own. And if not I'll be adding this code to my utility library before too long and you can get the final product then (I'll make sure to let you know when in the comments). But anyway, that's all there is to it. It's a lot of code but it's incredibly simple. So give it a try, leave feedback, and happy coding.