public static bool TryCreateFromDacpac(InstanceInfo instance, string dacpacPath, out SqlTestDB db, out string error, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
error = null;
string dbName = string.Empty;
try
{
dbName = Path.GetFileNameWithoutExtension(dacpacPath);
db = SqlTestDB.CreateFromDacpac(instance, dacpacPath, deployOptions, dropDatabaseOnCleanup);
return(true);
}
catch (Exception ex)
{
error = ExceptionText.GetText(ex);
db = null;
bool dbCreated = SafeDatabaseExists(instance, dbName);
if (dbCreated)
{
db = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
}
return(false);
}
}
public static SqlTestDB CreateTestDatabase(InstanceInfo instance, string dbName)
{
// Cleanup the database if it already exists
TestUtils.DropDatabase(instance, dbName);
// Create the test database
string createDB = string.Format(CultureInfo.InvariantCulture, "create database [{0}]", dbName);
ExecuteNonQuery(instance, "master", CommonConstants.DefaultCommandTimeout, createDB);
SqlTestDB db = new SqlTestDB(instance, dbName, true);
return(db);
}
public static SqlTestDB CreateFromDacpac(InstanceInfo instance, string dacpacPath, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(dacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (DacPackage dp = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory))
{
ds.Deploy(dp, dbName, true, deployOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return(sqlDb);
}
public static SqlTestDB CreateFromBacpac(InstanceInfo instance, string bacpacPath, DacImportOptions importOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(bacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (BacPackage bp = BacPackage.Load(bacpacPath, DacSchemaModelStorageType.Memory))
{
importOptions = FillDefaultImportOptionsForTest(importOptions);
ds.ImportBacpac(bp, dbName, importOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return(sqlDb);
}
private static bool SafeDatabaseExists(InstanceInfo instance, string dbName)
{
try
{
SqlTestDB masterDb = new SqlTestDB(instance, "master");
using (SqlConnection connection = masterDb.OpenSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = string.Format(CultureInfo.CurrentCulture, "select count(*) from sys.databases where [name]='{0}'", dbName);
object result = command.ExecuteScalar();
int count;
return(result != null && int.TryParse(result.ToString(), out count) && count > 0);
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return(false);
}
}
private static bool SafeDatabaseExists(InstanceInfo instance, string dbName)
{
try
{
SqlTestDB masterDb = new SqlTestDB(instance, "master");
using (SqlConnection connection = masterDb.OpenSqlConnection())
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = string.Format(CultureInfo.CurrentCulture, "select count(*) from sys.databases where [name]='{0}'", dbName);
object result = command.ExecuteScalar();
int count;
return result != null && int.TryParse(result.ToString(), out count) && count > 0;
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return false;
}
}
public static bool TryCreateFromDacpac(InstanceInfo instance, string dacpacPath, out SqlTestDB db, out string error, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
error = null;
string dbName = string.Empty;
try
{
dbName = Path.GetFileNameWithoutExtension(dacpacPath);
db = SqlTestDB.CreateFromDacpac(instance, dacpacPath, deployOptions, dropDatabaseOnCleanup);
return true;
}
catch (Exception ex)
{
error = ExceptionText.GetText(ex);
db = null;
bool dbCreated = SafeDatabaseExists(instance, dbName);
if (dbCreated)
{
db = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
}
return false;
}
}
public static SqlTestDB CreateFromDacpac(InstanceInfo instance, string dacpacPath, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(dacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (DacPackage dp = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory))
{
ds.Deploy(dp, dbName, true, deployOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return sqlDb;
}
public static SqlTestDB CreateFromBacpac(InstanceInfo instance, string bacpacPath, DacImportOptions importOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(bacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (BacPackage bp = BacPackage.Load(bacpacPath, DacSchemaModelStorageType.Memory))
{
importOptions = FillDefaultImportOptionsForTest(importOptions);
ds.ImportBacpac(bp, dbName, importOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return sqlDb;
}
public static void ExecuteNonQuery(SqlTestDB db, IList <string> scripts, int commandTimeout = CommonConstants.DefaultCommandTimeout)
{
ExecuteNonQuery(db.Instance, db.DatabaseName, scripts, commandTimeout);
}
public static void ExecuteNonQuery(SqlTestDB db, params string[] scripts)
{
ExecuteNonQuery(db, (IList <string>)scripts);
}
public static void ExecuteNonQuery(SqlTestDB db, int commandTimeout, params string[] scripts)
{
ExecuteNonQuery(db, (IList <string>)scripts, commandTimeout);
}
public static void ValidateQuery(string query, SqlTestDB db, bool expectQueryToPass)
{
Console.WriteLine("Testing query '{0}'", query);
Console.WriteLine("Loading model from DB");
using (TSqlModel model = TSqlModel.LoadFromDatabase(db.BuildConnectionString()))
{
// Currently we just give a message but no source information. Will work around this for now by ensuring the model is fully valid before proceeding
bool modelInitiallyValid = PrintModelState(model, true);
if (!modelInitiallyValid)
{
Console.WriteLine("Quitting due to invalid model");
return;
}
AssertModelHasNProcedures(model, 0);
string myFileName = "QueryFile.sql";
// validate bad query fails validation
model.AddOrUpdateObjects(CreateQueryAsProc(query), myFileName, null);
PrintModelState(model, expectQueryToPass);
AssertModelHasNProcedures(model, 1);
}
}
