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);
}
}
}
}
}