public void TestSqlConnectionExecuteQueryViaDynamicsWithEmptyArrayParameters()
{
DbSettingMapper.Add <PrivateDbConnection>(new CustomDbSetting(), true);
using (var connection = new PrivateDbConnection())
{
var sql = @"
select * from someTable
where id in (@normalArray)
and id in (@emptyArray)
and id in (@nullArray)
and id in (@concat1ArrayA, @concat1ArrayB)
and id in (@concat2ArrayA, @concat2ArrayB)
and id in (@concat3ArrayA, @concat3ArrayB)";
var param = new
{
normalArray = new[] { 5, 6 },
emptyArray = Array.Empty <int>(),
nullArray = (IEnumerable <int>)null,
concat1ArrayA = new[] { 100, 101 },
concat1ArrayB = new[] { 102, 103 },
concat2ArrayA = Array.Empty <int>(),
concat2ArrayB = new[] { 200, 201 },
concat3ArrayA = Array.Empty <int>(),
concat3ArrayB = Array.Empty <int>()
};
var command = connection.CreateDbCommandForExecution(sql, param, skipCommandArrayParametersCheck: false);
var expectedSql = @"
select * from someTable
where id in (@normalArray0, @normalArray1)
and id in ((SELECT @emptyArray WHERE 1 = 0))
and id in (@nullArray)
and id in (@concat1ArrayA0, @concat1ArrayA1, @concat1ArrayB0, @concat1ArrayB1)
and id in ((SELECT @concat2ArrayA WHERE 1 = 0), @concat2ArrayB0, @concat2ArrayB1)
and id in ((SELECT @concat3ArrayA WHERE 1 = 0), (SELECT @concat3ArrayB WHERE 1 = 0))";
Assert.AreEqual(expectedSql, command.CommandText);
Assert.AreEqual(13, command.Parameters.Count);
Assert.AreEqual(5, command.Parameters["@normalArray0"].Value);
Assert.AreEqual(6, command.Parameters["@normalArray1"].Value);
Assert.AreEqual(DBNull.Value, command.Parameters["@emptyArray"].Value);
Assert.AreEqual(DBNull.Value, command.Parameters["@nullArray"].Value);
Assert.AreEqual(100, command.Parameters["@concat1ArrayA0"].Value);
Assert.AreEqual(101, command.Parameters["@concat1ArrayA1"].Value);
Assert.AreEqual(102, command.Parameters["@concat1ArrayB0"].Value);
Assert.AreEqual(103, command.Parameters["@concat1ArrayB1"].Value);
Assert.AreEqual(DBNull.Value, command.Parameters["@concat2ArrayA"].Value);
Assert.AreEqual(200, command.Parameters["@concat2ArrayB0"].Value);
Assert.AreEqual(201, command.Parameters["@concat2ArrayB1"].Value);
Assert.AreEqual(DBNull.Value, command.Parameters["@concat3ArrayA"].Value);
Assert.AreEqual(DBNull.Value, command.Parameters["@concat3ArrayB"].Value);
}
}
public void ParameterDbType_DecideOrder_Attribute()
{
using var connection = new PrivateDbConnection();
var sql = "SELECT @V";
var command = connection.CreateDbCommandForExecution(sql, new WithAttributeModel {
V = null
}, skipCommandArrayParametersCheck: false);
Assert.AreEqual(DBNull.Value, command.Parameters["@V"].Value);
Assert.AreEqual(DbType.Single, command.Parameters["@V"].DbType);
command = connection.CreateDbCommandForExecution(sql, new WithAttributeModel {
V = WithAttributeEnum.B
}, skipCommandArrayParametersCheck: false);
Assert.IsInstanceOfType(command.Parameters["@V"].Value, typeof(WithAttributeEnum));
Assert.AreEqual(WithAttributeEnum.B, (WithAttributeEnum)command.Parameters["@V"].Value);
Assert.AreEqual(DbType.Single, command.Parameters["@V"].DbType);
}
public void ParameterDbType_DecideOrder_Property()
{
TypeMapper.Add <WithPropertyModel>(m => m.V, DbType.Double);
using var connection = new PrivateDbConnection();
var sql = "SELECT @V";
var command = connection.CreateDbCommandForExecution(sql, new WithPropertyModel {
V = null
}, skipCommandArrayParametersCheck: false);
Assert.AreEqual(DBNull.Value, command.Parameters["@V"].Value);
Assert.AreEqual(DbType.Double, command.Parameters["@V"].DbType);
command = connection.CreateDbCommandForExecution(sql, new WithPropertyModel {
V = WithPropertyEnum.B
}, skipCommandArrayParametersCheck: false);
Assert.IsInstanceOfType(command.Parameters["@V"].Value, typeof(WithPropertyEnum));
Assert.AreEqual(WithPropertyEnum.B, (WithPropertyEnum)command.Parameters["@V"].Value);
Assert.AreEqual(DbType.Double, command.Parameters["@V"].DbType);
}
