/// <summary>
/// Generate Update Command from give object
/// </summary>
/// <param name="tableName"></param>
/// <param name="input"></param>
/// <param name="keyGetter"></param>
/// <returns></returns>
public static SqlCommand GenerateUpdateCommand(UpdateEntry entry)
{
SqlCommand cmd = new SqlCommand();
var command = new StringBuilder();
command.AppendLine("UPDATE " + entry.TableName + " SET");
foreach (var k in entry.Data.Properties())
{
if (k.Name.StartsWith("$") || k.Name == entry.KeyName)
{
continue;
}
command.AppendLine(k.Name + " = @" + k.Name + ",");
}
command.Remove(command.Length - 3, 3);
command.AppendLine("\r\nWHERE " + entry.KeyName + " = @IDParameter");
cmd.CommandText = command.ToString();
foreach (var k in entry.Data.Properties())
{
if (k.Name.StartsWith("$") || k.Name == entry.KeyName)
{
continue;
}
var value = entry.Data[k.Name].ToObject(SQLDatabaseAccess.GetTypeFromJTokenType(entry.Data[k.Name].Type));
if (value != null)
{
cmd.Parameters.AddWithValue("@" + k.Name, value);
}
else
{
cmd.Parameters.AddWithValue("@" + k.Name, DBNull.Value);
}
}
cmd.Parameters.AddWithValue("@IDParameter",
entry.Data[entry.KeyName].ToObject(
SQLDatabaseAccess.GetTypeFromJTokenType(entry.Data[entry.KeyName].Type)));
return(cmd);
}
/// <summary>
/// Update all input
/// </summary>
/// <param name="connectionString"></param>
/// <param name="input"></param>
/// <returns></returns>
public static void UpdateAll(string connectionString, IEnumerable <UpdateEntry> input)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var item in input)
{
var cmd = SQLDatabaseAccess.GenerateUpdateCommand(item);
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
}