public int ExecuteUpdateStatements(IDbConnection con)
{
// Check for no updates
if (regs.Count == 0)
{
return(0);
}
// Creating the command and the parameters
IDictionary <string, object> parameters = new Dictionary <string, object>(regs.Count * 2);
IDictionary <object, int> parametersIdx = new Dictionary <object, int>(regs.Count * 2);
// The StringBuilder with all the UPDATE statements
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using (IDbCommand com = con.CreateCommand())
{
int parameterIdx = 0;
foreach (ObjectAndColumns reg in regs)
{
SqlFragment frag = CreateUpdateStatement(reg);
sb.Append(frag.ToSqlString(ref parameterIdx, parameters, parametersIdx) + ";");
}
// Defines the command text, composed of all the updates
com.CommandText = sb.ToString();
foreach (var param in parameters)
{
IDbDataParameter com_param = com.CreateParameter();
com_param.ParameterName = param.Key;
com_param.Value = param.Value;
com.Parameters.Add(com_param);
}
Console.WriteLine("Executing the following updates:\n{0}", com.CommandText);
return(com.ExecuteNonQuery());
}
}
public int ExecuteUpdateStatement(IDbConnection con)
{
SqlFragment query = new SqlFragment("UPDATE " + table + " SET ");
// Update fields section
for (int i = 0; i < chosenPropsOrFields.Count; i++)
{
var getter = chosenPropsOrFields.ElementAt(i);
if (i == chosenPropsOrFields.Count - 1)
{
query.AppendText("{0}=t.{0}", getter.Key);
}
else
{
query.AppendText("{0}=t.{0},", getter.Key);
}
}
// Values and ids section
query.AppendText(" FROM (VALUES ");
for (int r = 0; r < regs.Count; r++)
{
T reg = regs[r];
query.AppendText("(");
for (int i = 0; i < chosenPropsOrFields.Count; i++)
{
var getter = chosenPropsOrFields.ElementAt(i);
query.AppendParameter(getter.Value(reg));
query.AppendText(",");
}
query.AppendParameter(idGetter(reg))
.AppendText(")");
if (r < regs.Count - 1)
{
query.AppendText(",");
}
}
query.AppendText(") AS t(");
for (int i = 0; i < chosenPropsOrFields.Count; i++)
{
var getter = chosenPropsOrFields.ElementAt(i);
query.AppendText(getter.Key + ",");
}
query.AppendText("id) WHERE " + idColumn + "=t.id");
// Creating the command and the parameters
IDictionary <string, object> parameters = new Dictionary <string, object>(regs.Count * (chosenPropsOrFields.Count + 1));
IDictionary <object, int> parametersIdx = new Dictionary <object, int>(regs.Count * (chosenPropsOrFields.Count + 1));
using (IDbCommand com = con.CreateCommand())
{
int initialParameterIdx = 0;
com.CommandText = query.ToSqlString(ref initialParameterIdx, parameters, parametersIdx);
foreach (var param in parameters)
{
IDbDataParameter com_param = com.CreateParameter();
com_param.ParameterName = param.Key;
com_param.Value = param.Value;
com.Parameters.Add(com_param);
}
return(com.ExecuteNonQuery());
}
}