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);
}
/// <summary>
/// AND's the specified <paramref name="andCondition"/> to the QueryBuilder.
/// </summary>
/// <param name='andCondition'>
/// The condition to be ANDed.
/// </param>
public QueryBuilder Where(SqlFragment andCondition)
{
if (whereCondition == null)
{
whereCondition = new WhereCondition(andCondition);
}
else
{
whereCondition.And(andCondition);
}
return(this);
}
/// <summary>
/// Prepends the SqlFragment <paramref name="frag"/> to this fragment. The fragment to be prepended is not copied, i.e. we only keep a reference, so
/// if the prepended fragment is changed, this fragment changes too.
/// </summary>
/// <param name='frag'>
/// The Sql Fragment to be prepended.
/// </param>
public SqlFragment PrependFragment(SqlFragment frag)
{
if (frag == null)
{
throw new ArgumentNullException("frag");
}
IsEmpty = false;
FragmentType = SqlFragmentType.Complex;
Fragments.AddFirst(frag);
return(this);
}
/// <summary>
/// OR's the specified <paramref name="orCondition"/> to the QueryBuilder.
/// </summary>
/// <param name='orCondition'>
/// The condition to be ORed.
/// </param>
public QueryBuilder Or(SqlFragment orCondition)
{
if (whereCondition == null)
{
return(Where(orCondition));
}
else
{
whereCondition.Or(orCondition);
return(this);
}
}
/// <summary>
/// Creates a fragment that represents a join. This fragment should render to "INNER JOIN table ON condition" or similar.
/// </summary>
public JoinFragment(string table, SqlFragment joinCondition, JoinType joinType)
{
if (joinType == JoinType.InnerJoin)
{
AppendText("INNER JOIN ");
}
else
{
AppendText("LEFT OUTER JOIN ");
}
AppendText(table + " ON ");
AppendFragment(joinCondition);
}
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 WhereCondition And(SqlFragment andCondition)
{
return(And(new WhereCondition(andCondition)));
}
public SqlFragment(SqlFragment sqlFragment) : this()
{
IsEmpty = false;
AppendFragment(sqlFragment);
}
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());
}
}
/// <summary>
/// ANDs a condition to the query. The caller must watch out for names of tables and properties.
/// </summary>
/// <param name="cond">A fragment to be inserted in the WHERE clause of the query.</param>
public RootQueryBuilder <T> Where(SqlFragment cond)
{
Qb.Where(cond);
return(this);
}
public static LikeCondition Like(SqlFragment leftSideColumnOrExpression, string match)
{
return(new LikeCondition(leftSideColumnOrExpression, match));
}
public static LessOrEqual LessOrEqual(string leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
{
return(new LessOrEqual(leftSideColumnOrExpression, rightSideColumnOrExpression));
}
/// <summary>
/// Creates a order by fragment.
/// </summary>
/// <param name='sqlFragment'>
/// The column or expression that will be the ordering criteria.
/// </param>
/// <param name='orderBy'>
/// Defines if results will be ordered in ascending or descending fashion.
/// </param>
public OrderByFragment(SqlFragment sqlFragment, OrderBy orderBy)
{
this.orderBy = orderBy;
this.AppendFragment(sqlFragment);
}
public static EqualTo EqualTo(SqlFragment leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
{
return(new EqualTo(leftSideColumnOrExpression, rightSideColumnOrExpression));
}
public static NullCondition IsNotNull(SqlFragment leftSideColumnOrExpression)
{
return(new NullCondition(leftSideColumnOrExpression, true));
}
public static NotEqualTo NotEqualTo(string leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
{
return(new NotEqualTo(leftSideColumnOrExpression, rightSideColumnOrExpression));
}
/// <summary>
/// Creates a projection with a name. Projections should have the same name of the properties of
/// the results' class or an expression, in which case you should give it an alias with the name of a valid property.
/// </summary>
/// <param name='fragment'>
/// The textual part of the projection.
/// </param>
/// <param name='alias'>
/// The alias of the projection (used with "AS alias")
/// </param>
public ProjectionFragment(SqlFragment fragment, string alias) : this(fragment)
{
this.alias = alias;
}
public QueryBuilder GroupBy(SqlFragment frag)
{
groupedColumns.Add(frag);
return(this);
}
public QueryBuilder Join(string table, SqlFragment joinCondition, JoinType joinType)
{
joinFragments.Add(new JoinFragment(table, joinCondition, joinType));
return(this);
}
public static GreaterThan GreaterThan(string leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
{
return(new GreaterThan(leftSideColumnOrExpression, rightSideColumnOrExpression));
}
public WhereCondition Or(SqlFragment orCondition)
{
return(Or(new WhereCondition(orCondition)));
}
public static InCondition In(SqlFragment leftSideColumnOrExpression, IList values)
{
return(new InCondition(leftSideColumnOrExpression, values));
}
public WhereCondition(SqlFragment frag) : this()
{
base.AppendFragment(frag);
}
public static LessOrEqual LessOrEqual(SqlFragment leftSideColumnOrExpression, object @value)
{
return(new LessOrEqual(leftSideColumnOrExpression, value));
}
/// <summary>
/// Creates a projection with a name. Projections should have the same name of the properties of
/// the results' class or an expression, in which case you should give it an alias with the name of a valid property.
/// </summary>
/// <param name='fragment'>
/// The textual part of the projection.
/// </param>
public ProjectionFragment(SqlFragment fragment)
{
this.AppendFragment(fragment);
}
public QueryBuilder OrderBy(SqlFragment frag)
{
orderByColumns.Add(frag);
return(this);
}
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);
}
/// <summary>
/// Creates a order by ascending fragment.
/// </summary>
/// <param name='sqlFragment'>
/// The column or expression that will be the ordering criteria.
/// </param>
public OrderByFragment(SqlFragment sqlFragment)
{
orderBy = OrderBy.Asc;
this.AppendFragment(sqlFragment);
}
/// <summary>
/// Creates a fragment that represents a subquery. This means that ToSqlString will render the subquery inside parentheses and alias it.
/// </summary>
/// <param name='sf'>
/// A valid query sql fragment.
/// </param>
/// <param name='alias'>
/// The alias of the subquery.
/// </param>
public FromFragment(SqlFragment sf, string alias)
{
this.alias = alias;
this.AppendFragment(sf);
}
public SqlFragmentGeneratorTreeVisitor() : base()
{
Fragment = new WhereCondition();
TableEntities = new Dictionary <Type, string>(2);
VisitedTypeProperties = new Dictionary <Type, IList <MemberInfo> >();
}