private SqlFragment CreateUpdateStatement(ObjectAndColumns reg)
{
SqlFragment query = new SqlFragment("UPDATE " + reg.table + " SET ");
// Update fields section
for (int i = 0; i < reg.chosenPropsOrFields.Count; i++)
{
var getter = reg.chosenPropsOrFields.ElementAt(i);
if (i == reg.chosenPropsOrFields.Count - 1)
{
query.AppendText("{0}=", getter.Key)
.AppendParameter(getter.Value(reg.obj));
}
else
{
query.AppendText("{0}=", getter.Key)
.AppendParameter(getter.Value(reg.obj))
.AppendText(",");
}
}
// WHERE id=?
query.AppendText(" WHERE {0}=", reg.idColumn)
.AppendParameter(reg.idGetter(reg.obj));
return(query);
}
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());
}
}
public SqlFragment ToSqlFragment()
{
if (selectedProjections.Count == 0)
{
throw new Exception("No SELECT columns specified");
}
SqlFragment sf = new SqlFragment();
// The SELECT clause
sf.AppendText("SELECT ");
for (int i = 0; i < selectedProjections.Count; i++)
{
sf.AppendFragment(selectedProjections[i]);
if (i < selectedProjections.Count - 1)
{
sf.AppendText(", ");
}
}
// The FROM clause (it IS optional)
if (tableOrSubquery != null)
{
sf.AppendText(" FROM ")
.AppendFragment(tableOrSubquery);
}
// The joins, if any
foreach (SqlFragment joinFrag in joinFragments)
{
sf.AppendText(" ");
sf.AppendFragment(joinFrag);
}
// The WHERE clause, if any
if (whereCondition != null)
{
if (whereCondition != null)
{
sf.AppendText(" WHERE ")
.AppendFragment(whereCondition);
}
}
// The GROUP BY clause, if any
if (groupedColumns.Count > 0)
{
sf.AppendText(" GROUP BY ");
for (int i = 0; i < groupedColumns.Count; i++)
{
sf.AppendFragment(groupedColumns[i]);
if (i < groupedColumns.Count - 1)
{
sf.AppendText(", ");
}
}
}
// The ORDER BY clause, if any
if (orderByColumns.Count > 0)
{
sf.AppendText(" ORDER BY ");
for (int i = 0; i < orderByColumns.Count; i++)
{
sf.AppendFragment(orderByColumns[i]);
if (i < orderByColumns.Count - 1)
{
sf.AppendText(", ");
}
}
}
// Offset and limit
if (offset > 0)
{
sf.AppendText(" OFFSET {0}", offset);
}
if (limit > 0)
{
sf.AppendText(" LIMIT {0}", limit);
}
return(sf);
}