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); } } }
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 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); } } } }
// 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) { // generate a table list 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(); tableList.Add(database + ".." + tableName); } reader.Close(); } } using (var db = new ADODatabaseContext("TEST")) { foreach (var item in tableList) { db.ExecuteNonQuery(@"TRUNCATE TABLE " + item); } } }
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 + ".." + _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, _databaseName)) { if (_HasIdentity) { db.ExecuteNonQuery("SET IDENTITY_INSERT " + _databaseName + ".." + _tableName + " ON"); } db.ExecuteNonQuery(query); if (_HasIdentity) { db.ExecuteNonQuery("SET IDENTITY_INSERT " + _databaseName + ".." + _tableName + " OFF"); } } }
public static void CreateAllTables(List <TableDefinition> TableList, string databaseName) { // 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); } } }
private void BuildFieldsForTable() { Fields.Clear(); using (var db = new ADODatabaseContext(_connectionString, _databaseName)) { string columnQuery = "SELECT * FROM " + _databaseName + ".INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + _tableName + "'"; var reader = db.ReadQuery(columnQuery); while (reader.Read()) { Fields.Add(new FieldDefinition { Name = reader["COLUMN_NAME"].ToString(), Type = reader["DATA_TYPE"].ToString() }); } } }
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 ClearConstraints(List <ConstraintDefinition> ConstraintList) { // 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 query = "ALTER TABLE " + constraint.DatabaseName + ".." + constraint.FkTable + " DROP CONSTRAINT fk_" + constraint.FkTable + "_" + constraint.PkTable; db.ExecuteNonQuery(query); } } } }
private bool DoesTableHaveIdentityField() { string query = @" SELECT * FROM " + _databaseName + @".sys.identity_columns AS a INNER JOIN " + _databaseName + @".sys.objects AS b ON a.object_id=b.object_id WHERE LOWER(b.name)='" + _tableName + @"' AND type='U'" ; using (var db = new ADODatabaseContext(_connectionString, _databaseName)) { var reader = db.ReadQuery(query); if (reader.HasRows) { return(true); } } return(false); }
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) { db.ExecuteNonQuery(tsqlCommand); } } } } }