public void OpenSqlConnection_returns_open_connection()
{
using (var connection = SqlServerAliases.OpenSqlConnection(context, ConnectionString))
{
connection.State.Should().Be(ConnectionState.Open);
}
}
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 Trying_To_Connect_WithBadConnString_ThowsMeaningful_Exception()
{
var connString = "data source=(LocalDb)\v12.0";
Action act = () => SqlServerAliases.CreateDatabaseIfNotExists(context, connString, "ShouldThrow");
act.ShouldThrow <Exception>()
.WithMessage("Looks like you are trying to connect to LocalDb. Have you correctly escaped your connection string with '@'? It should look like 'var connString = @\"(localDb)\\v12.0\"'");
}
public void SetCommandTimeout_ShortTimeout_Throws()
{
SqlServerAliases.SetSqlCommandTimeout(context, 1);
Action act = () => SqlServerAliases.ExecuteSqlCommand(context, ConnectionString, "WAITFOR DELAY '00:00:02'");
act.ShouldThrow <Exception>();
}
public void SetCommandTimeout_LongTimeout_DoesNotThrow()
{
SqlServerAliases.SetSqlCommandTimeout(context, 3);
Action act = () => SqlServerAliases.ExecuteSqlCommand(context, ConnectionString, "WAITFOR DELAY '00:00:02'");
act.ShouldNotThrow();
}
public void ExecuteSqlCommand_does_not_change_connection_state()
{
using (var connection = SqlServerAliases.OpenSqlConnection(context, ConnectionString))
{
connection.MonitorEvents();
SqlServerAliases.ExecuteSqlCommand(context, connection, "select * from sys.tables");
connection.ShouldNotRaise(nameof(connection.StateChange));
}
}
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 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 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 ExecuteSqlCommand_does_not_change_connection_state()
{
using (var connection = SqlServerAliases.OpenSqlConnection(context, ConnectionString))
{
connection.StateChange += FailOnStateChange;
try
{
SqlServerAliases.ExecuteSqlCommand(context, connection, "select * from sys.tables");
}
finally
{
connection.StateChange -= FailOnStateChange;
}
}
}
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 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 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 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}");
}
public void DatabaseExists_DoesNotExist_ReturnFalse()
{
var dbExists = SqlServerAliases.DatabaseExists(context, ConnectionString, "DoesNotExist");
dbExists.Should().BeFalse();
}
public void DropDatabase_DoesNotExist_DoesNotThrow()
{
Action act = () => SqlServerAliases.DropDatabase(context, ConnectionString, "DoesNotExist");
act.ShouldNotThrow();
}