示例#1
0
 /// <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; }
     }
 }
示例#3
0
 /// <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; }
 }
示例#4
0
 /// <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();
        }
示例#15
0
 /// <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(); }
         }
     }
 }
示例#16
0
 /// <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);
 }
示例#18
0
 /// <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(); }
         }
     }
 }
示例#19
0
 /// <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;
 }
示例#22
0
 /// <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; }
 }