public void ThrowExceptionWhenParameterIsNullAndDataTypeIsNotSetInQuery()
{
var parameters = new CommandParameterCollection
{
{ "@Param1", 1 },
{ "@Param2", null }
};
using (SqlServerTestHelper helper = CreateHelper((string)null))
{
QueryProvider provider = CreateQueryProvider(helper.Connection);
Action executeNonQuery = () => provider.ExecuteNonQuery("NO QUERY", parameters);
executeNonQuery.Should().Throw <ArgumentException>().WithMessage("*@Param2*");
}
}
public void ReturnCorrectItemsFromSelectTableStoredProcedure_WhenUseConnectionString()
{
string[] initScripts = { CreateTable_TestTable, CreateProcedure_TableResult };
using (SqlServerTestHelper helper = CreateHelper(initScripts))
{
QueryProvider provider = CreateQueryProvider(helper.Connection.ConnectionString);
List <TestItem> result = provider.ExecuteStoredProcedure <IEnumerable <TestItem> >(Procedure_TableResult).ToList();
result.Should().BeEquivalentTo(new TestItem[] {
new TestItem(1, 10, "Lorem ipsum"),
new TestItem(2, 20, null),
new TestItem(3, 30, "Hello world"),
});
}
}
public void ExecuteUpdateQuery()
{
using (SqlServerTestHelper helper = CreateHelper(CreateTable_TestTable))
{
var query = $"UPDATE {Table_TestTable} SET Number = @Number WHERE Id >= @Id";
var parameters = new CommandParameterCollection
{
{ "@Id", 2 },
{ "@Number", 666 }
};
QueryProvider provider = CreateQueryProvider(helper.Connection);
int result = provider.ExecuteNonQuery(query, parameters);
result.Should().Be(2); // Updated 2 rows.
}
}
public void NotThrowWhenCreatingTableAndStoredProcedureForIdGeneratorAndTheyExist()
{
const string tableName = "IdStore";
const string procedureName = "spGetNewId";
using (var helper = new SqlServerTestHelper(BaseConnectionString, BaseDatabaseName, DatabaseInitScripts))
{
HasTable(helper.Connection, tableName).Should().BeTrue();
HasProcedure(helper.Connection, procedureName).Should().BeTrue();
var idGenerator = new SqlServerIdGenerator(helper.Connection, "TestTable", 1);
idGenerator.InitDatabaseForIdGenerator();
HasTable(helper.Connection, tableName).Should().BeTrue();
HasProcedure(helper.Connection, procedureName).Should().BeTrue();
}
}
public void CreateTableAndStoredProcedureForIdGeneratorIfNotExits()
{
const string tableName = "IdStore";
const string procedureName = "spGetNewId";
using (var helper = new SqlServerTestHelper(BaseConnectionString, BaseDatabaseName))
{
HasTable(helper.Connection, tableName).Should().BeFalse();
HasProcedure(helper.Connection, procedureName).Should().BeFalse();
var idGenerator = new SqlServerIdGenerator(helper.Connection, "TestTable", 1);
idGenerator.InitDatabaseForIdGenerator();
HasTable(helper.Connection, tableName).Should().BeTrue();
HasProcedure(helper.Connection, procedureName).Should().BeTrue();
}
}
public void InitForIdGenerator()
{
string dbName = $"KORM_InitIdGenerator";
string idStoreTableName = "IdStore";
using (var testHelper = new SqlServerTestHelper(IntegrationTestConfig.ConnectionString, dbName))
using (IDatabase database = new Database(testHelper.Connection))
{
SqlServerIntIdGeneratorFactory.Register();
database.InitDatabaseForIdGenerator();
var result = database.ExecuteScalar(
$"IF EXISTS (SELECT 1 FROM sys.Tables WHERE Name = N'{idStoreTableName}' AND Type = N'U') " +
"SELECT 'true' ELSE SELECT 'false'");
result.Should().Be("true");
}
}
public void ReturnCorrectValueFromSelectRowStoredProcedure()
{
using (SqlServerTestHelper helper = CreateHelper(CreateProcedure_RowResultWithMultipleValues))
{
var parameters = new CommandParameterCollection
{
{ "@Id", 1 },
{ "@Number", 10 },
{ "@Description", "Lorem ipsum" }
};
QueryProvider provider = CreateQueryProvider(helper.Connection);
TestItem result = provider.ExecuteStoredProcedure <TestItem>(Procedure_RowResultWithMultipleValues, parameters);
result.Should().Be(new TestItem(1, 10, "Lorem ipsum"));
}
}
public void ExecuteInsertQuery()
{
using (SqlServerTestHelper helper = CreateHelper(CreateTable_TestTable))
{
var query = $"INSERT INTO {Table_TestTable} (Id, Number, Description) VALUES (@Id, @Number, @Description)";
var parameters = new CommandParameterCollection
{
{ "@Id", 6 },
{ "@Number", 666 },
{ "@Description", "Sed ac lobortis magna." }
};
QueryProvider provider = CreateQueryProvider(helper.Connection);
int result = provider.ExecuteNonQuery(query, parameters);
result.Should().Be(1); // Inserted 1 row.
}
}
public void ReturnCorrectValueFromStoredProcedure()
{
using (SqlServerTestHelper helper = CreateHelper(CreateProcedure_ScalarResult))
{
const int param1 = 11;
const int param2 = 22;
const int expected = 33;
var parameters = new CommandParameterCollection
{
{ "@Param1", param1 },
{ "@Param2", param2 }
};
QueryProvider provider = CreateQueryProvider(helper.Connection);
int result = provider.ExecuteStoredProcedure <int>(Procedure_ScalarResult, parameters);
result.Should().Be(expected);
}
}
public void ReturnCorrectValueFromStoredProcedureUsingOutputParameter()
{
using (SqlServerTestHelper helper = CreateHelper(CreateProcedure_OutputParameter))
{
const int inputParamValue = 10;
const int inputOutputParamValue = 100;
var parameters = new CommandParameterCollection
{
{ "@InputParam", inputParamValue },
{ "@InputOutputParam", inputOutputParamValue, DbType.Int32, ParameterDirection.InputOutput },
{ "@OutputParam", 0, DbType.Int32, ParameterDirection.Output }
};
QueryProvider provider = CreateQueryProvider(helper.Connection);
int result = provider.ExecuteStoredProcedure <int>(Procedure_OutputParameter, parameters);
parameters["@OutputParam"].Value.Should().Be(inputParamValue * 2);
parameters["@InputOutputParam"].Value.Should().Be(inputOutputParamValue * 2);
}
}
protected override void Dispose(bool disposing)
{
base.Dispose(disposing);
_sqlServerTestHelper?.Dispose();
_sqlServerTestHelper = null;
}
public TestDatabase(SqlServerTestHelper sqlServerTestHelper) : base(sqlServerTestHelper.Connection)
{
_sqlServerTestHelper = sqlServerTestHelper;
}
internal BackupManager(SqlServerTestHelper helper, IMonitorTestHelper others)
{
_helper = helper;
Helper = others;
}
