public void CreateView(string instanceName)
{
var 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
var 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
var tsqlCommandList = Regex.Split(Code, "GO");
foreach (var tsqlCommand in tsqlCommandList)
{
db.ExecuteNonQuery(tsqlCommand);
}
}
}
/// <summary>
/// Execute any query stored in a resource file against your SQLLocalDB instance.
/// </summary>
/// <param name="filePath">Full namespace path to the embedded resource file</param>
/// <param name="database"></param>
public static void ExecuteSQLCodeFromFile(string filePath, string database)
{
using (var db = new ADODatabaseContext("TEST", database))
{
var assembly = Assembly.GetCallingAssembly();
using (var stream = assembly.GetManifestResourceStream(filePath))
{
if (stream == null)
{
throw new Exception("Cannot find SQL query file, make sure it is set to Embedded Resource!");
}
using (var reader = new StreamReader(stream))
{
var code = reader.ReadToEnd();
var tsqlCommandList = Regex.Split(code, "GO");
foreach (var tsqlCommand in tsqlCommandList)
{
if (tsqlCommand.Trim() != "")
{
db.ExecuteNonQuery(tsqlCommand.Replace("\n", "").Replace("\r", ""));
}
}
}
}
}
}
/// <summary>
/// Execute a raw query string against a test database
/// </summary>
/// <param name="query">One SQL statement to be executed</param>
/// <param name="database">database to execute against</param>
public static void ExecuteSQLQuery(string query, string database)
{
using (var db = new ADODatabaseContext("TEST", database))
{
db.ExecuteNonQuery(query);
}
}
/// <summary>
/// Create one constraint between two tables. Use this method to create a few constraints for the tables you use in one test.
/// </summary>
/// <param name="pConstraintList">This is the list of available constraints in the code created by the Unit Test Database Creator utility</param>
/// <param name="table1">The first table that the constraint applies to</param>
/// <param name="table2">The second table the constraint applies to</param>
public static void CreateConstraint(List <ConstraintDefinition> pConstraintList, string table1, string table2)
{
var constraintList = pConstraintList.Where(x => x.PkTable.ToLower() == table1 && x.FkTable.ToLower() == table2).ToList();
foreach (var constraint in constraintList)
{
var 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 = pConstraintList.Where(x => x.PkTable.ToLower() == table2 && x.FkTable.ToLower() == table1).ToList();
foreach (var constraint in constraintList)
{
var 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);
}
}
}
/// <summary>
/// Clear all the constraints in the database.
/// </summary>
/// <param name="pConstraintList">This is the list of available constraints in the code created by the Unit Test Database Creator utility</param>
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;
}
}
}
}
}
}
/// <summary>
/// This method will create a list of tables in the database provided. You must call this method
/// for each database you want to create tables inside of.
/// </summary>
/// <param name="tableList">List of table definitions provided by the code generated with the Unit Test Database Generator utility.</param>
/// <param name="databaseName">The database name these tables will be created inside of.</param>
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)
{
var query = tableDefinition.CreateScript;
using (var db = new ADODatabaseContext("TEST", databaseName))
{
db.ExecuteNonQuery(query);
}
}
}
/// <summary>
/// Use this method to create a stored procedure defined in the code generated by the Unit Test Generator utility.
/// </summary>
/// <param name="stream"></param>
/// <param name="databaseName"></param>
/// <param name="spName"></param>
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
var 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 (var reader = new StreamReader(stream))
{
var storedProcText = reader.ReadToEnd();
var tsqLcommandList = Regex.Split(storedProcText, "GO");
foreach (var tsqlCommand in tsqLcommandList)
{
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())
{
var foreignKeyTableName = reader["TableName"].ToString();
var constraintName = reader["ConstraintName"].ToString();
var schemaName = reader["SchemaName"].ToString();
using (var dbExec = new ADODatabaseContext("TEST", database))
{
var query = "ALTER TABLE [" + database + "].[" + schemaName + "].[" + foreignKeyTableName + "] DROP CONSTRAINT " + constraintName;
dbExec.ExecuteNonQuery(query);
}
}
}
}
}
private void BuildQueryInsertData()
{
var 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");
}
}
}
/// <summary>
/// Read a generic List of your datatype from a table
/// </summary>
/// <typeparam name="T">Define a POCO of your table results</typeparam>
/// <param name="tableName">Table to query</param>
/// <param name="database">Database containing table</param>
/// <param name="schema">Optional schema</param>
/// <returns></returns>
public static List <T> ReadDataFromTable <T>(string tableName, string database, string schema = "dbo") where T : new()
{
// make sure the schema name doesn't already contain a "."
schema = schema.Replace(".", "");
using (var db = new ADODatabaseContext("TEST", database))
{
var query = $"SELECT * FROM [{schema}].[{tableName}]";
var data = db.ReadDataSet(query);
var result = data.Tables[0].ToList <T>();
return(result);
}
}
private static void CreateDatabase(string databaseName)
{
var 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%)
" );
}
}
private string ReadTableIdentityFieldName()
{
var 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))
{
var 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);
}
}
}
/// <summary>
/// Returns the total records in a table specified. This can be used to verify how many records are created in your unit test.
/// </summary>
/// <param name="tableName">Table to check</param>
/// <param name="database">Database that table resides in</param>
/// <param name="schema">Optional schema name. If not specified, then it will be set to "dbo"</param>
/// <returns></returns>
public static int TotalRecords(string tableName, string database, string schema = "dbo")
{
var results = 0;
// make sure the schema name doesn't already contain a "."
schema = schema.Replace(".", "");
using (var db = new ADODatabaseContext("TEST", database))
{
var query = $"SELECT COUNT(*) AS total FROM [{schema}].[{tableName}]";
using (var reader = db.ReadQuery(query))
{
while (reader.Read())
{
results = int.Parse(reader["total"].ToString());
break;
}
}
}
return(results);
}
/// <summary>
/// Truncate all tables in the databases setup.
/// This method will remove all constraints before truncating tables. You must
/// re-apply your constraints for each test, if you need them.
/// </summary>
public static void TruncateData()
{
var 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())
{
var tableName = reader["table_name"].ToString();
var 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);
}
}
}
