public static void CreateStoredProcedure(Stream stream, string databaseName, string spName) { using (var db = new ADODatabaseContext("TEST", databaseName)) { // first, drop the stored procedure if it already exists string sp = @"if exists (select * from sys.objects where name = N'" + spName + @"' and type = N'P') begin drop procedure " + spName + @" end" ; db.ExecuteNonQuery(sp); // need to read the text file and create the stored procedure in the test database using (StreamReader reader = new StreamReader(stream)) { string storedProcText = reader.ReadToEnd(); string[] TSQLcommands = Regex.Split(storedProcText, "GO"); foreach (var tsqlCommand in TSQLcommands) { db.ExecuteNonQuery(tsqlCommand); } } } }
public void CreateView(string instanceName) { string connectionString = "server=(localdb)\\" + instanceName + ";" + "Trusted_Connection=yes;" + "database=" + Database + @"; " + "Integrated Security=true; "; using (var db = new ADODatabaseContext(connectionString)) { // first, drop the view if it already exists string sp = @"if exists (select * from sys.views where name = N'" + Name + @"' and type = N'P') begin drop view " + Name + @" end"; db.ExecuteNonQuery(sp); // need to read the text file and create the view in the test database string[] TSQLcommands = Regex.Split(Code, "GO"); foreach (var tsqlCommand in TSQLcommands) { db.ExecuteNonQuery(tsqlCommand); } } }
private void BuildQueryInsertData() { string query = ""; foreach (var field in Fields) { if (field.Value != null) { query += "[" + field.Name + "],"; } } if (query[query.Length - 1] == ',') { query = query.Substring(0, query.Length - 1); } if (query == "") { return; } query = "INSERT INTO " + _databaseName + "." + _schemaName + "." + _tableName + " (" + query + ") VALUES ("; foreach (var field in Fields) { if (field.Value != null) { query += field.Value + ","; } } if (query[query.Length - 1] == ',') { query = query.Substring(0, query.Length - 1); } query += ")"; using (var db = new ADODatabaseContext(_connectionString)) { if (_DataContainsPrimaryKey) { db.ExecuteNonQuery("SET IDENTITY_INSERT " + _databaseName + "." + _schemaName + "." + _tableName + " ON"); } db.ExecuteNonQuery(query); if (_DataContainsPrimaryKey) { db.ExecuteNonQuery("SET IDENTITY_INSERT " + _databaseName + "." + _schemaName + "." + _tableName + " OFF"); } } }
public static void ClearConstraints(List <ConstraintDefinition> ConstraintList) { string schemaName = "dbo"; // delete all foreign constraints in all databases using (var db = new ADODatabaseContext("TEST")) { //_databaseList foreach (var database in _databaseList) { var constraints = ConstraintList.Where(x => x.DatabaseName == database).ToList(); foreach (var constraint in constraints) { string constraintName = "fk_" + constraint.FkTable + "_" + constraint.PkTable; if (!String.IsNullOrEmpty(constraint.SchemaName)) { schemaName = constraint.SchemaName; } string query = "SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='" + constraintName + "' AND CONSTRAINT_SCHEMA='" + schemaName + "'"; using (var reader = db.ReadQuery(query)) { while (reader.Read()) { query = "ALTER TABLE " + constraint.DatabaseName + ".." + constraint.FkTable + " DROP CONSTRAINT " + constraintName; db.ExecuteNonQuery(query); break; } } } } } }
public static void CreateConstraint(List <ConstraintDefinition> ConstraintList, string table1, string table2) { var constraintList = ConstraintList.Where(x => x.PkTable.ToLower() == table1 && x.FkTable.ToLower() == table2).ToList(); foreach (var constraint in constraintList) { string query = "ALTER TABLE " + constraint.FkTable + " ADD CONSTRAINT fk_" + constraint.FkTable + "_" + constraint.PkTable + " FOREIGN KEY (" + constraint.FkField + ") REFERENCES " + constraint.PkTable + "(" + constraint.PkField + ")"; using (var db = new ADODatabaseContext("TEST")) { db.ExecuteNonQuery(query); } } constraintList = ConstraintList.Where(x => x.PkTable.ToLower() == table2 && x.FkTable.ToLower() == table1).ToList(); foreach (var constraint in constraintList) { string query = "ALTER TABLE " + constraint.FkTable + " ADD CONSTRAINT fk_" + constraint.FkTable + "_" + constraint.PkTable + " FOREIGN KEY (" + constraint.FkField + ") REFERENCES " + constraint.PkTable + "(" + constraint.PkField + ")"; using (var db = new ADODatabaseContext("TEST", constraint.DatabaseName)) { db.ExecuteNonQuery(query); } } }
public static void CreateAllTables(List <TableDefinition> TableList, string databaseName) { // create all non "dbo" schemas in database var schemaList = (from t in TableList where t.SchemaName != "dbo" select t.SchemaName).Distinct(); foreach (var schemaName in schemaList) { if (!String.IsNullOrEmpty(schemaName)) { using (var db = new ADODatabaseContext("TEST", databaseName)) { db.ExecuteNonQuery("CREATE SCHEMA [" + schemaName + "] AUTHORIZATION [dbo]"); } } } // generate all tables listed in the table name list foreach (var tableDefinition in TableList) { string query = tableDefinition.CreateScript; using (var db = new ADODatabaseContext("TEST", databaseName)) { db.ExecuteNonQuery(query); } } }
/// <summary> /// clear all constraints in database /// </summary> private static void ClearAllConstraints(string database) { using (var db = new ADODatabaseContext("TEST", database)) { using (var reader = db.ReadQuery(@" SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT' AND OBJECT_NAME(OBJECT_ID) LIKE'fk_%'" )) { while (reader.Read()) { string fkTableName = reader["TableName"].ToString(); string constraintName = reader["ConstraintName"].ToString(); string schemaName = reader["SchemaName"].ToString(); using (var dbExec = new ADODatabaseContext("TEST", database)) { string query = "ALTER TABLE " + database + "." + schemaName + "." + fkTableName + " DROP CONSTRAINT " + constraintName; dbExec.ExecuteNonQuery(query); } } } } }
public void CreateStoredProcedure() { using (var db = new ADODatabaseContext("TEST", Database)) { // first, drop the stored procedure if it already exists var sp = @"if exists (select * from sys.objects where name = N'" + Name.Replace("_tilde_", "~") + @"' and type = N'P') begin drop procedure " + Name.Replace("_tilde_", "~") + @" end" ; db.ExecuteNonQuery(sp); // need to read the text file and create the stored procedure in the test database var tsqlCommandList = Regex.Split(Code, "GO"); foreach (var tsqlCommand in tsqlCommandList) { db.ExecuteNonQuery(tsqlCommand); } } }
private static void CreateDatabase(string databaseName) { string databaseDirectory = Directory.GetCurrentDirectory(); using (var db = new ADODatabaseContext("TEST")) { db.ExecuteNonQuery(@"CREATE DATABASE [" + databaseName + @"] CONTAINMENT = NONE ON PRIMARY ( NAME = N'" + databaseName + @"', FILENAME = N'" + databaseDirectory + @"\" + databaseName + @".mdf' , SIZE = 8096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'" + databaseName + @"_log', FILENAME = N'" + databaseDirectory + @"\" + databaseName + @"_log.ldf' , SIZE = 8096KB , FILEGROWTH = 10%) " ); } }
public static void ExecuteSQLCode(string filePath) { using (var db = new ADODatabaseContext("TEST")) { var assembly = Assembly.GetCallingAssembly(); using (Stream stream = assembly.GetManifestResourceStream(filePath)) { using (StreamReader reader = new StreamReader(stream)) { string code = reader.ReadToEnd(); string[] TSQLcommands = Regex.Split(code, "GO"); foreach (var tsqlCommand in TSQLcommands) { if (tsqlCommand.Trim() != "") { db.ExecuteNonQuery(tsqlCommand); } } } } } }
// truncate all tables in the databases setup public static void TruncateData() { List <string> tableList = new List <string>(); using (var db = new ADODatabaseContext("TEST")) { //_databaseList foreach (var database in _databaseList) { ClearAllConstraints(database); // generate a table list using (var reader = db.ReadQuery(@" SELECT * FROM " + database + @".INFORMATION_SCHEMA.tables WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME" )) { while (reader.Read()) { string tableName = reader["table_name"].ToString(); string schemaName = reader["TABLE_SCHEMA"].ToString(); tableList.Add(database + "." + schemaName + "." + tableName); } } } } using (var db = new ADODatabaseContext("TEST")) { foreach (var item in tableList) { db.ExecuteNonQuery(@"TRUNCATE TABLE " + item); } } }