/// <summary>
/// Constructor
/// </summary>
/// <param name="Name">Name</param>
/// <param name="ParentDatabase">Parent database</param>
public Table(string Name,Database ParentDatabase)
{
this.Name = Name;
this.ParentDatabase = ParentDatabase;
Columns = new List<Column>();
Triggers = new List<Trigger>();
}
/// <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))
{
try
{
Helper.BeginTransaction();
for (int x = 1; x < Commands.Length; ++x)
{
Helper.Command = Commands[x];
Helper.ExecuteNonQuery();
}
Helper.Commit();
}
catch { Helper.Rollback(); throw; }
}
}
/// <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>
/// Constructor
/// </summary>
/// <param name="Name">Name</param>
/// <param name="Definition">Definition</param>
/// <param name="ParentDatabase">Parent database</param>
public StoredProcedure(string Name, string Definition, Database ParentDatabase)
{
this.Name = Name;
this.Definition = Definition;
this.ParentDatabase = ParentDatabase;
Columns = new List<Column>();
}
public void CreateDatabase()
{
Database Database = new Database("TestDatabase");
Table TestTable = Database.AddTable("TestTable");
TestTable.AddColumn<string>("ID_", DbType.Int32);
TestTable.AddColumn<string>("Value1", DbType.String, 100);
TestTable.AddColumn<string>("Value2", DbType.Double);
Utilities.SQL.SQLServer.SQLServer.CreateDatabase(Database, "Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false");
using (Utilities.SQL.SQLHelper Helper = new Utilities.SQL.SQLHelper("insert into TestTable(ID_,Value1,Value2) VALUES (@ID_,@Value1,@Value2)", "Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false", CommandType.Text))
{
Helper.AddParameter<int>("@ID_", 1);
Helper.AddParameter<string>("@Value1", "Test String");
Helper.AddParameter<float>("@Value2", 3.0f);
Assert.Equal(1, Helper.ExecuteNonQuery());
}
}
public void GetDatabaseStructure()
{
Database Database = new Database("TestDatabase");
Table TestTable = Database.AddTable("TestTable");
TestTable.AddColumn<string>("ID_", DbType.Int32);
TestTable.AddColumn<string>("Value1", DbType.String, 100);
TestTable.AddColumn<string>("Value2", DbType.Double);
Utilities.SQL.SQLServer.SQLServer.CreateDatabase(Database, "Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false");
Database Database2 = Utilities.SQL.SQLServer.SQLServer.GetDatabaseStructure("Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false");
Assert.Equal(Database.Tables.First().Name, Database2.Tables.First().Name);
Assert.Equal(Database.Tables.First().Columns.Count, Database2.Tables.First().Columns.Count);
Assert.Equal(DbType.Int32, Database2.Tables.First().Columns.First(x => x.Name == "ID_").DataType);
Assert.Equal(DbType.String, Database2.Tables.First().Columns.First(x => x.Name == "Value1").DataType);
Assert.Equal(DbType.Double, Database2.Tables.First().Columns.First(x => x.Name == "Value2").DataType);
Assert.Equal(100, Database2.Tables.First().Columns.First(x => x.Name == "Value1").Length);
Assert.Equal(4, Database2.Tables.First().Columns.First(x => x.Name == "ID_").Length);
Assert.Equal(8, Database2.Tables.First().Columns.First(x => x.Name == "Value2").Length);
}
/// <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().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();
}
/// <summary>
/// Builds a list of commands for a datatbase
/// </summary>
/// <param name="DesiredDatabase">Desired database structure</param>
/// <param name="CurrentDatabase">Current database structure</param>
/// <returns>A list of commands</returns>
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>
/// 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>
/// Constructor
/// </summary>
/// <param name="Name">Name</param>
/// <param name="ParentDatabase">Parent dataabse</param>
public View(string Name,Database ParentDatabase)
{
this.Name = Name;
this.ParentDatabase = ParentDatabase;
Columns = new List<Column>();
}
public void UpdateDatabase()
{
Database Database = new Database("TestDatabase");
Table TestTable = Database.AddTable("TestTable");
TestTable.AddColumn<string>("ID_", DbType.Int32);
TestTable.AddColumn<string>("Value1", DbType.String, 100);
TestTable.AddColumn<string>("Value2", DbType.Double);
Utilities.SQL.SQLServer.SQLServer.CreateDatabase(Database, "Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false");
Database Database2 = new Database("TestDatabase");
TestTable = Database2.AddTable("TestTable");
TestTable.AddColumn<string>("ID_", DbType.Int32);
TestTable.AddColumn<string>("Value1", DbType.String, 100);
TestTable.AddColumn<string>("Value2", DbType.Double);
TestTable.AddColumn<string>("Value3", DbType.Boolean);
Utilities.SQL.SQLServer.SQLServer.UpdateDatabase(Database2, Database, "Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false");
using (Utilities.SQL.SQLHelper Helper = new Utilities.SQL.SQLHelper("insert into TestTable(ID_,Value1,Value2,Value3) VALUES (@ID_,@Value1,@Value2,@Value3)", "Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false", CommandType.Text))
{
Helper.AddParameter<int>("@ID_", 1);
Helper.AddParameter<string>("@Value1", "Test String");
Helper.AddParameter<float>("@Value2", 3.0f);
Helper.AddParameter<bool>("@Value3", true);
Assert.Equal(1, Helper.ExecuteNonQuery());
}
Database Database3 = Utilities.SQL.SQLServer.SQLServer.GetDatabaseStructure("Data Source=localhost;Initial Catalog=TestDatabase;Integrated Security=SSPI;Pooling=false");
Assert.Equal(Database2.Tables.First().Name, Database3.Tables.First().Name);
Assert.Equal(Database2.Tables.First().Columns.Count, Database3.Tables.First().Columns.Count);
Assert.Equal(DbType.Int32, Database3.Tables.First().Columns.First(x => x.Name == "ID_").DataType);
Assert.Equal(DbType.String, Database3.Tables.First().Columns.First(x => x.Name == "Value1").DataType);
Assert.Equal(DbType.Double, Database3.Tables.First().Columns.First(x => x.Name == "Value2").DataType);
Assert.Equal(100, Database3.Tables.First().Columns.First(x => x.Name == "Value1").Length);
Assert.Equal(4, Database3.Tables.First().Columns.First(x => x.Name == "ID_").Length);
Assert.Equal(8, Database3.Tables.First().Columns.First(x => x.Name == "Value2").Length);
}
/// <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>
/// Constructor
/// </summary>
/// <param name="Name">Name of the function</param>
/// <param name="Definition">Definition of the function</param>
/// <param name="ParentDatabase">Parent database</param>
public Function(string Name,string Definition,Database ParentDatabase)
{
this.Name=Name;
this.Definition=Definition;
this.ParentDatabase=ParentDatabase;
}
/// <summary>
/// Updates a database (only adds new fields, tables, etc. does not delete old fields)
/// </summary>
/// <param name="DesiredDatabase">The desired structure of the database</param>
/// <param name="CurrentDatabase">The current database structure</param>
/// <param name="ConnectionString">Connection string to the database</param>
public static void UpdateDatabase(Database DesiredDatabase, Database CurrentDatabase, string ConnectionString)
{
if (CurrentDatabase == null)
{
CreateDatabase(DesiredDatabase, ConnectionString);
return;
}
string Command = BuildCommands(DesiredDatabase, CurrentDatabase);
string[] Splitter = { "\n" };
string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
ConnectionString = Regex.Replace(ConnectionString, "Pooling=(.*?;)", "", RegexOptions.IgnoreCase) + ";Pooling=false;";
using (SQLHelper Helper = new SQLHelper("", ConnectionString, CommandType.Text))
{
try
{
Helper.BeginTransaction();
for (int x = 0; x < Commands.Length; ++x)
{
Helper.Command = Commands[x];
Helper.ExecuteNonQuery();
}
Helper.Commit();
}
catch { Helper.Rollback(); 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(";", "");
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;
}
/// <summary>
/// Updates a database (only adds new fields, tables, etc. does not delete old fields)
/// </summary>
/// <param name="DesiredDatabase">The desired structure of the database</param>
/// <param name="CurrentDatabase">The current database structure</param>
/// <param name="ConnectionString">Connection string to the database</param>
public static void UpdateDatabase(Database DesiredDatabase, Database CurrentDatabase, string ConnectionString)
{
try
{
if (CurrentDatabase == null)
{
CreateDatabase(DesiredDatabase, ConnectionString);
return;
}
string Command = BuildCommands(DesiredDatabase, CurrentDatabase);
string[] Splitter = { "\n" };
string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
for (int x = 0; 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; }
}