public DbCommandExtensionsTest()
{
int? nullableInt = null;
var parameters = new { a = "aVal", b = 1, c = false, d = nullableInt };
cmd = new SqlCommand() { CommandType = CommandType.StoredProcedure };
cmd.AddParameters(parameters);
}
public void AddNullableParametersTest()
{
var command = new SqlCommand();
command.AddParameters(new { NullIntParam = new int?(), DateParam = (DateTime?)DateTime.MinValue });
Assert.AreEqual(2, command.Parameters.Count);
Assert.AreEqual(DBNull.Value, command.Parameters["NullIntParam"].Value);
Assert.AreEqual(DateTime.MinValue, command.Parameters["DateParam"].Value);
}
public IEnumerable<string> GetMemberFirstNames(int[] memberIds)
{
var sql = "select firstname from member where memberid in (@memberid)";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameters("@memberid", memberIds, SqlDbType.Int);
return _helper.ExecuteArray<string>(cmd);
}
}
public void AddParametersTest()
{
var command = new SqlCommand();
command.AddParameters(new { IntParam = 123, DateParam = DateTime.MinValue });
Assert.AreEqual(2, command.Parameters.Count);
Assert.AreEqual(123, command.Parameters["IntParam"].Value);
Assert.AreEqual(DateTime.MinValue, command.Parameters["DateParam"].Value);
command = new SqlCommand();
command.AddParameters<object>(null);
Assert.AreEqual(0, command.Parameters.Count);
}
public void AddParameters_AnonymousType_AddsAllPropertiesAsParameters()
{
var parameters = new {Id = 1, Name = "Reginald", DateOfBirth = new DateTime(1985, 10, 08)};
var cmd = new SqlCommand();
cmd.AddParameters(parameters, s => s.ToSnakeCase().ToLower());
var commandParameters = cmd.Parameters.Cast<SqlParameter>().ToDictionary(p => p.ParameterName, p => p.Value);
var commandParameterKeys = commandParameters.Select(p => p.Key).ToArray();
var expectedKeys = new[] {"id", "name", "date_of_birth"};
expectedKeys.Action(k => Assert.That(commandParameterKeys.Contains(k)));
Assert.AreEqual(1, cmd.Parameters["id"].Value);
Assert.AreEqual("Reginald", cmd.Parameters["name"].Value);
Assert.AreEqual(new DateTime(1985, 10, 08), cmd.Parameters["date_of_birth"].Value);
}
public void AddParameters_DictionaryObject_AddsAllParameters()
{
var parameters = new Dictionary<string, object> {
{"id", 1},
{"name", "Reginald"},
{"date_of_birth", new DateTime(1985, 10, 08)}
};
var cmd = new SqlCommand();
cmd.AddParameters(parameters);
var commandParameters = cmd.Parameters.Cast<SqlParameter>().ToDictionary(p => p.ParameterName, p => p.Value);
var commandParameterKeys = commandParameters.Select(p => p.Key).ToArray();
var missing = parameters.Where(p => p.Key.NotIn(commandParameterKeys));
Assert.AreEqual(0, missing.Count());
foreach (var p in parameters)
Assert.AreEqual(p.Value, cmd.Parameters[p.Key].Value);
}
public void VerifyExtensionMethods()
{
var excludeTheseStateAbbreviations = new string[] { "CO", "CA", "TX" };
var sql = "select * from state where abbreviation not in (@abbreviation)";
using (var cmd = new SqlCommand(sql))
{
cmd.AddParameters("@abbreviation", excludeTheseStateAbbreviations);
var data = _helper.ExecuteDataTable(cmd);
Assert.AreEqual(68, data.Rows.Count);
}
}