Beispiel #1
0
        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);
        }
Beispiel #2
0
        /// <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);
        }
Beispiel #3
0
        /// <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);
        }
Beispiel #4
0
        /// <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);
            }
        }
Beispiel #5
0
        /// <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);
        }
Beispiel #6
0
        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());
            }
        }
Beispiel #7
0
 public WhereCondition And(SqlFragment andCondition)
 {
     return(And(new WhereCondition(andCondition)));
 }
Beispiel #8
0
 public SqlFragment(SqlFragment sqlFragment) : this()
 {
     IsEmpty = false;
     AppendFragment(sqlFragment);
 }
Beispiel #9
0
        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());
            }
        }
Beispiel #10
0
 /// <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);
 }
Beispiel #11
0
 public static LikeCondition Like(SqlFragment leftSideColumnOrExpression, string match)
 {
     return(new LikeCondition(leftSideColumnOrExpression, match));
 }
Beispiel #12
0
 public static LessOrEqual LessOrEqual(string leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
 {
     return(new LessOrEqual(leftSideColumnOrExpression, rightSideColumnOrExpression));
 }
Beispiel #13
0
 /// <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);
 }
Beispiel #14
0
 public static EqualTo EqualTo(SqlFragment leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
 {
     return(new EqualTo(leftSideColumnOrExpression, rightSideColumnOrExpression));
 }
Beispiel #15
0
 public static NullCondition IsNotNull(SqlFragment leftSideColumnOrExpression)
 {
     return(new NullCondition(leftSideColumnOrExpression, true));
 }
Beispiel #16
0
 public static NotEqualTo NotEqualTo(string leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
 {
     return(new NotEqualTo(leftSideColumnOrExpression, rightSideColumnOrExpression));
 }
Beispiel #17
0
 /// <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;
 }
Beispiel #18
0
        public QueryBuilder GroupBy(SqlFragment frag)
        {
            groupedColumns.Add(frag);

            return(this);
        }
Beispiel #19
0
        public QueryBuilder Join(string table, SqlFragment joinCondition, JoinType joinType)
        {
            joinFragments.Add(new JoinFragment(table, joinCondition, joinType));

            return(this);
        }
Beispiel #20
0
 public static GreaterThan GreaterThan(string leftSideColumnOrExpression, SqlFragment rightSideColumnOrExpression)
 {
     return(new GreaterThan(leftSideColumnOrExpression, rightSideColumnOrExpression));
 }
Beispiel #21
0
 public WhereCondition Or(SqlFragment orCondition)
 {
     return(Or(new WhereCondition(orCondition)));
 }
Beispiel #22
0
 public static InCondition In(SqlFragment leftSideColumnOrExpression, IList values)
 {
     return(new InCondition(leftSideColumnOrExpression, values));
 }
Beispiel #23
0
 public WhereCondition(SqlFragment frag) : this()
 {
     base.AppendFragment(frag);
 }
Beispiel #24
0
 public static LessOrEqual LessOrEqual(SqlFragment leftSideColumnOrExpression, object @value)
 {
     return(new LessOrEqual(leftSideColumnOrExpression, value));
 }
Beispiel #25
0
 /// <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);
 }
Beispiel #26
0
        public QueryBuilder OrderBy(SqlFragment frag)
        {
            orderByColumns.Add(frag);

            return(this);
        }
Beispiel #27
0
        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);
        }
Beispiel #28
0
 /// <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);
 }
Beispiel #29
0
 /// <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);
 }
Beispiel #30
0
 public SqlFragmentGeneratorTreeVisitor() : base()
 {
     Fragment              = new WhereCondition();
     TableEntities         = new Dictionary <Type, string>(2);
     VisitedTypeProperties = new Dictionary <Type, IList <MemberInfo> >();
 }