public void TestSqlConnectionExecuteQueryViaDynamicsWithEmptyArrayParameters()
{
if (DbSettingMapper.Get(typeof(CustomDbConnection)) == null)
{
DbSettingMapper.Add(typeof(CustomDbConnection), new CustomDbSetting(), true);
}
using (var connection = new CustomDbConnection())
{
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);
}
}
