public void Try_Create_Bacpac()
{
var dbName = "BacpacTestDb";
var resultingFilePath = "NsagaCreated.bacpac";
try
{
// Arrange
SqlHelpers.ExecuteSql(ConnectionString, $"Create database {dbName}");
var sql = $@"use {dbName}
GO
create table [{dbName}].[dbo].[Table1] (id int null, name nvarchar(max) null);
Go
insert into [{dbName}].[dbo].[Table1] (id, name) values (42, 'Ultimate Question of Life');
Go";
context.ExecuteSqlCommand(ConnectionString, sql);
// Act
SqlBacpacImpl.CreateBacpacFile(context, ConnectionString, dbName, resultingFilePath);
// Assert
File.Exists(resultingFilePath).Should().BeTrue();
}
finally
{
SqlHelpers.DropDatabase(ConnectionString, dbName);
File.Delete(resultingFilePath);
}
}
public void ExecuteSqlFile_does_not_change_connection_state()
{
const string dbName = "ForFileExecution";
SqlHelpers.ExecuteSql(ConnectionString, $"Create database {dbName}");
try
{
using (var connection = SqlServerAliases.OpenSqlConnection(context, ConnectionString))
{
connection.StateChange += FailOnStateChange;
try
{
SqlServerAliases.ExecuteSqlFile(context, connection, GetSqlFilePath());
}
finally
{
connection.StateChange -= FailOnStateChange;
}
}
}
finally
{
SqlHelpers.ExecuteSql(ConnectionString, $"Drop database {dbName}");
}
}
public void Try_Extract_Dacpac()
{
var dbName = "DacpacTestDb";
var resultingFilePath = "NsagaCreated.dacpac";
var settings = new ExtractDacpacSettings("TestApp", "1.0.0.0")
{
OutputFile = resultingFilePath
};
try
{
// Arrange
SqlHelpers.ExecuteSql(ConnectionString, $"create database {dbName}");
var sql = $@"use {dbName}
GO
create table [{dbName}].[dbo].[Table1] (id int null, name nvarchar(max) null);
Go";
context.ExecuteSqlCommand(ConnectionString, sql);
// Act
SqlDacpacImpl.ExtractDacpacFile(context, ConnectionString, dbName, settings);
// Assert
File.Exists(resultingFilePath).Should().BeTrue();
}
finally
{
SqlHelpers.DropDatabase(ConnectionString, dbName);
File.Delete(resultingFilePath);
}
}
public void CreateDatabase_Creates_Correctly()
{
// Act
var dbName = "Unknown";
SqlServerAliases.CreateDatabase(context, ConnectionString, dbName);
// Assert
SqlHelpers.DbExists(ConnectionString, dbName).Should().BeTrue();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}
public void DropDatabase_ActuallyDrops()
{
//Arrange
var dbName = "WillBeDropped";
SqlHelpers.ExecuteSql(ConnectionString, $"Create database {dbName}");
// Act
SqlServerAliases.DropDatabase(context, ConnectionString, dbName);
// Assert
SqlHelpers.DbExists(ConnectionString, dbName).Should().BeFalse();
}
public void CreateDatabaseIfNotExists()
{
// Act
var dbName = "CakeTest";
SqlServerAliases.CreateDatabaseIfNotExists(context, ConnectionString, dbName);
// Assert
SqlHelpers.DbExists(ConnectionString, dbName).Should().BeTrue();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}
public void DatabaseExists_DoesExist_ReturnTrue()
{
// Arrange
var dbName = "WillExists";
SqlHelpers.ExecuteSql(ConnectionString, $"Create database {dbName}");
// Act
var dbExists = SqlServerAliases.DatabaseExists(context, ConnectionString, dbName);
// Assert
dbExists.Should().BeTrue();
}
public void CreateDatabaseIfNotExists_DbExists_DoesNotThrow()
{
// Act
var dbName = "ToBeRecreatedAgain";
SqlServerAliases.CreateDatabase(context, ConnectionString, dbName);
SqlServerAliases.CreateDatabaseIfNotExists(context, ConnectionString, dbName);
// Assert
SqlHelpers.DbExists(ConnectionString, dbName).Should().BeTrue();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}
public void Dispose()
{
SqlHelpers.DropDatabase(ConnectionString, "ForFileExecution");
SqlHelpers.DropDatabase(ConnectionString, "WillBeDropped");
SqlHelpers.DropDatabase(ConnectionString, "CakeTest");
SqlHelpers.DropDatabase(ConnectionString, "ToBeRecreated");
SqlHelpers.DropDatabase(ConnectionString, "ToBeRecreatedAgain");
SqlHelpers.DropDatabase(ConnectionString, "ForSqlExecution");
SqlHelpers.DropDatabase(ConnectionString, "Unknown");
SqlHelpers.DropDatabase(ConnectionString, "test");
SqlHelpers.DropDatabase(ConnectionString, "hack");
SqlHelpers.DropDatabase(ConnectionString, "test]] create database hack--");
SqlHelpers.ExecuteSql(ConnectionString, "if (select DB_ID('some'')) is null create database hack--')) is not null drop database [some')) is null create database hack--]");
}
public void CreateDatabase_SqlNameInjection_DoesNotInject()
{
// Act
SqlServerAliases.CreateDatabase(context, ConnectionString, "test] create database hack--");
// Assert
SqlHelpers.DbExists(ConnectionString, "test").Should().BeFalse();
SqlHelpers.DbExists(ConnectionString, "hack").Should().BeFalse();
SqlHelpers.DbExists(ConnectionString, "test] create database hack--").Should().BeTrue();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, "if (select DB_ID('test')) is not null drop database [test]");
SqlHelpers.ExecuteSql(ConnectionString, "if (select DB_ID('hack')) is not null drop database [hack]");
SqlHelpers.ExecuteSql(ConnectionString, "if (select DB_ID('test] create database hack--')) is not null drop database [test]] create database hack--]");
}
public void CreateDatabase_DbAlreadyExists_Throws()
{
// Act
var dbName = "Unknown";
SqlHelpers.ExecuteSql(ConnectionString, $"create database {dbName}");
// Assert
Action act = () => SqlServerAliases.CreateDatabase(context, ConnectionString, dbName);
act.ShouldThrow <SqlException>();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}
public void CreateDatabaseIfNotExists_WithPrimaryFile()
{
// Act
var dbName = "CakeTest";
var mdfFilePath = Path.Combine(Path.GetTempPath(), "MyCakeTest.mdf");
var createSettings = new CreateDatabaseSettings().WithPrimaryFile(mdfFilePath);
SqlServerAliases.CreateDatabaseIfNotExists(context, ConnectionString, dbName, createSettings);
// Assert
SqlHelpers.DbExists(ConnectionString, dbName).Should().BeTrue();
File.Exists(mdfFilePath).Should().BeTrue();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}
public void DropAndCreateDatabase_Always_RemovesTable()
{
//Arrange
var dbName = "ToBeRecreated";
var tableName = "WillNotExist";
SqlHelpers.ExecuteSql(ConnectionString, $"Create database {dbName}");
SqlHelpers.ExecuteSql(ConnectionString, $"create table [{dbName}].dbo.{tableName} (id int null)");
SqlHelpers.TableExists(ConnectionString, dbName, tableName).Should().BeTrue();
// Act
SqlServerAliases.DropAndCreateDatabase(context, ConnectionString, dbName);
// Assert
SqlHelpers.TableExists(ConnectionString, dbName, tableName).Should().BeFalse();
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}
public void DropDatabase_LiteralInjection_DoesNotInject()
{
// Act
try
{
SqlServerAliases.DropDatabase(context, ConnectionString, "some')) is null create database hack--");
}
catch (Exception)
{
// do nothing
}
// Assert
SqlHelpers.DbExists(ConnectionString, "hack").Should().BeFalse();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, "if (select DB_ID('hack')) is not null drop database [hack]");
}
public void ExecuteSqlFile_does_not_change_connection_state()
{
const string dbName = "ForFileExecution";
SqlHelpers.ExecuteSql(ConnectionString, $"create database {dbName}");
try
{
using (var connection = SqlServerAliases.OpenSqlConnection(context, ConnectionString))
using (var monitoringSubject = connection.Monitor())
{
SqlServerAliases.ExecuteSqlFile(context, connection, GetSqlFilePath());
monitoringSubject.Should().NotRaise(nameof(connection.StateChange));
}
}
finally
{
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}
}
public void ExecuteSqlFile_Executes_Successfuly()
{
//Arrange
var connectionString = @"data source=(LocalDb)\v12.0;Database=ForFileExecution";
var dbName = "ForFileExecution";
var tableName1 = "WillExist1";
var tableName2 = "WillExist2";
SqlHelpers.ExecuteSql(ConnectionString, $"Create database {dbName}");
var sqlFilePath = GetSqlFilePath();
// Act
SqlServerAliases.ExecuteSqlFile(context, connectionString, sqlFilePath);
// Assert
SqlHelpers.TableExists(ConnectionString, dbName, tableName1).Should().BeTrue();
SqlHelpers.TableExists(ConnectionString, dbName, tableName2).Should().BeTrue();
SqlServerAliases.DropDatabase(context, ConnectionString, dbName);
}
public void DropDatabase_LiteralInjection_DoesNotInject()
{
// Act
try
{
SqlServerAliases.DropDatabase(context, ConnectionString, "some')) is null create database hack--");
}
#pragma warning disable CC0004 // Catch block cannot be empty
catch
{
// do nothing
}
#pragma warning restore CC0004 // Catch block cannot be empty
// Assert
SqlHelpers.DbExists(ConnectionString, "hack").Should().BeFalse();
// Cleanup
SqlHelpers.ExecuteSql(ConnectionString, "if (select DB_ID('hack')) is not null drop database [hack]");
}
public void Try_Publish_Dacpac()
{
var dbName = "DacpacTestDb";
try
{
// Arrange
SqlHelpers.ExecuteSql(ConnectionString, $"create database {dbName}");
// Act
SqlDacpacImpl.PublishDacpacFile(context, ConnectionString, dbName, GetDacpacFilePath());
// Assert
SqlHelpers.TableExists(ConnectionString, dbName, "Table1").Should().BeTrue();
}
finally
{
SqlHelpers.DropDatabase(ConnectionString, dbName);
}
}
public void DropAndCreateDatabase_WithCreateParams()
{
//Arrange
var dbName = "ToBeRecreated";
var tableName = "WillNotExist";
SqlHelpers.ExecuteSql(ConnectionString, $"create database {dbName}");
SqlHelpers.ExecuteSql(ConnectionString, $"create table [{dbName}].dbo.{tableName} (id int null)");
SqlHelpers.TableExists(ConnectionString, dbName, tableName).Should().BeTrue();
var mdfFilePath = Path.Combine(Path.GetTempPath(), "MyCakeTest.mdf");
var logFilePath = Path.Combine(Path.GetTempPath(), "MyCakeTest.ldf");
var createSettings = new CreateDatabaseSettings().WithPrimaryFile(mdfFilePath).WithLogFile(logFilePath);
// Act
SqlServerAliases.DropAndCreateDatabase(context, ConnectionString, dbName, createSettings);
// Assert
File.Exists(mdfFilePath).Should().BeTrue();
File.Exists(logFilePath).Should().BeTrue();
}
public void ExecuteSqlCommand_CreatesTables()
{
//Arrange
var dbName = "ForSqlExecution";
var tableName1 = "WillExist1";
var tableName2 = "WillExist2";
SqlHelpers.ExecuteSql(ConnectionString, $"Create database {dbName}");
var sql = $@"
create table [{dbName}].dbo.{tableName1} (id int null);
Go
create table [{dbName}].dbo.{tableName2} (id int null);
Go
";
// Act
SqlServerAliases.ExecuteSqlCommand(context, ConnectionString, sql);
// Assert
SqlHelpers.TableExists(ConnectionString, dbName, tableName1).Should().BeTrue();
SqlHelpers.TableExists(ConnectionString, dbName, tableName2).Should().BeTrue();
SqlHelpers.ExecuteSql(ConnectionString, $"drop database {dbName}");
}