public static class SqlParametersExtensions
{
public static IEnumerable<SqlParameter> ToSqlParameters(this IDictionary<string, object> parameters)
=> parameters.Select(kvp => new SqlParameter(kvp.Key, kvp.Value));
}
static void LegibleCodeWithDictionary()
{
string query = "INSERT INTO Briefings (Msg, Time) VALUES (@msg, @time)";
var parameters = new Dictionary<string, object>()
{
["@msg"] = "hello, world",
["@time"] = DateTime.Now.AddDays(1)
};
using (var connection = new SqlConnection("myconn"))
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddRange(parameters.ToSqlParameters().ToList());
connection.Open();
command.ExecuteNonQuery();
}
}
static void LegibleCodeWithExplicitParameters()
{
using (var connection = new SqlConnection("myconn"))
using (var command = new SqlCommand("INSERT INTO Briefings (Msg, Time) VALUES (@msg, @time)", connection))
{
command.Parameters.AddWithValue("@msg", "hello, world");
command.Parameters.AddWithValue("@time", DateTime.Now.AddDays(1));
connection.Open();
command.ExecuteNonQuery();
}
}
public static IEnumerable<SqlParameter> BriefingForSmartPeople(string Msg, DateTime Time)
{
yield return new SqlParameter("@msg", Msg);
yield return new SqlParameter("@time", Time);
}
static void LegibleCodeWithSmartBriefingMethod()
{
using (var connection = new SqlConnection("myconn"))
using (var command = new SqlCommand("INSERT INTO Briefings (Msg, Time) VALUES (@msg, @time)", connection))
{
foreach (var parameter in BriefingForSmartPeople("hello, world", DateTime.Now.AddDays(1)))
command.Parameters.Add(parameter);
connection.Open();
command.ExecuteNonQuery();
}
}
public static IEnumerable BriefingForMyDumbTeacher(string Msg, DateTime Time)
{
return new ArrayList()
{
new SqlParameter("@msg", Msg),
new SqlParameter("@time", Time)
};
}
static void LegibleCodeWithDumbBriefingMethod()
{
using (var connection = new SqlConnection("myconn"))
using (var command = new SqlCommand("INSERT INTO Briefings (Msg, Time) VALUES (@msg, @time)", connection))
{
foreach (var parameter in BriefingForMyDumbTeacher("hello, world", DateTime.Now.AddDays(1)))
command.Parameters.Add(parameter);
connection.Open();
command.ExecuteNonQuery();
}
}