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);
}
}
}
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 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 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);
}
}
}
}
/// <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 static void ClearConstraints(List <ConstraintDefinition> pConstraintList)
{
var schemaName = "dbo";
// delete all foreign constraints in all databases
using (var db = new ADODatabaseContext("TEST"))
{
//_databaseList
foreach (var database in _databaseList)
{
var constraints = pConstraintList.Where(x => x.DatabaseName == database).ToList();
foreach (var constraint in constraints)
{
var constraintName = "fk_" + constraint.FkTable + "_" + constraint.PkTable;
if (!string.IsNullOrEmpty(constraint.SchemaName))
{
schemaName = constraint.SchemaName;
}
var 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;
}
}
}
}
}
}
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");
}
}
}
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 int TotalRecords(string tableName, string database)
{
int results = 0;
using (var db = new ADODatabaseContext("", database))
{
string query = "SELECT COUNT(*) AS total FROM " + tableName;
using (var reader = db.ReadQuery(query))
{
while (reader.Read())
{
results = reader["total"].ToInt();
break;
}
}
}
return(results);
}
private string ReadTableIdentityFieldName()
{
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))
{
var reader = db.ReadQuery(query);
while (reader.Read())
{
return(reader["name"].ToString());
}
}
return("");
}
private void BuildFieldsForTable()
{
Fields.Clear();
using (var db = new ADODatabaseContext(_connectionString))
{
string columnQuery = "SELECT * FROM " + _databaseName + ".INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + _tableName + "'";
using (var reader = db.ReadQuery(columnQuery))
{
while (reader.Read())
{
Fields.Add(new FieldDefinition
{
Name = reader["COLUMN_NAME"].ToString(),
Type = reader["DATA_TYPE"].ToString()
});
}
}
}
}
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);
}
}
}
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);
}
}
}