Example #1
0
        /// <summary>
        /// Gets the automatically generated <see cref="IDbCommand"/> object to insert new record.
        /// </summary>
        /// <param name="tableName">table name</param>
        /// <param name="data">new record data</param>
        /// <returns>insert SQL command</returns>
        public virtual IDbCommand GetInsertCommand(string tableName, IEnumerable <KeyValuePair <string, IQueryValue> > data)
        {
            var cmd          = GetCommand();
            var dbSqlBuilder = GetSqlBuilder(cmd);

            // Prepare fields part
            var columns = new StringBuilder();
            var values  = new StringBuilder();

            foreach (var setField in data)
            {
                if (columns.Length > 0)
                {
                    columns.Append(',');
                }
                columns.Append(dbSqlBuilder.BuildValue((QField)setField.Key));

                if (values.Length > 0)
                {
                    values.Append(',');
                }
                values.Append(dbSqlBuilder.BuildValue(setField.Value));
            }

            var tblName = dbSqlBuilder.BuildTableName(new QTable(tableName, null));
            var colStr  = columns.ToString();
            var valStr  = values.ToString();

            var insertTpl = new StringTemplate(InsertTemplate);

            SetCommandText(cmd, insertTpl.FormatTemplate((varName) => {
                switch (varName)
                {
                case "table": return(new StringTemplate.TokenResult(tblName));

                case "columns": return(new StringTemplate.TokenResult(colStr));

                case "values": return(new StringTemplate.TokenResult(valStr));
                }
                return(StringTemplate.TokenResult.NotDefined);
            }));

            return(cmd);
        }
Example #2
0
        /// <summary>
        /// Gets the automatically generated <see cref="IDbCommand"/> object to update rows by specified <see cref="Query"/>.
        /// </summary>
        /// <param name="query">query that determines update table and conditions</param>
        /// <param name="data">changeset data</param>
        /// <returns>update SQL command</returns>
        public virtual IDbCommand GetUpdateCommand(Query query, IEnumerable <KeyValuePair <string, IQueryValue> > data)
        {
            var cmd          = GetCommand();
            var dbSqlBuilder = GetSqlBuilder(cmd);

            // prepare fields Part
            var setExpression = new StringBuilder();

            foreach (var setField in data)
            {
                if (setExpression.Length > 0)
                {
                    setExpression.Append(',');
                }

                setExpression.Append(dbSqlBuilder.BuildValue(new QField(setField.Key)));
                setExpression.Append('=');
                setExpression.Append(dbSqlBuilder.BuildValue(setField.Value));
            }

            // prepare WHERE part
            string whereExpression = dbSqlBuilder.BuildExpression(query.Condition);
            var    tblName         = dbSqlBuilder.BuildTableName(new QTable(query.Table.Name, null));

            var updateTpl = new StringTemplate(UpdateTemplate);

            SetCommandText(cmd, updateTpl.FormatTemplate((varName) => {
                switch (varName)
                {
                case "table": return(new StringTemplate.TokenResult(tblName));

                case "set": return(new StringTemplate.TokenResult(setExpression.ToString()));

                case "where": return(new StringTemplate.TokenResult(whereExpression));
                }
                return(StringTemplate.TokenResult.NotDefined);
            }));

            return(cmd);
        }
Example #3
0
        /// <summary>
        /// Gets the automatically generated <see cref="IDbCommand"/> object to delete rows by specified <see cref="Query"/>.
        /// </summary>
        /// <param name="query">query that determines delete table and conditions</param>
        /// <returns>delete SQL command</returns>
        public virtual IDbCommand GetDeleteCommand(Query query)
        {
            var cmd          = GetCommand();
            var dbSqlBuilder = GetSqlBuilder(cmd);

            // prepare WHERE part
            var whereExpression = dbSqlBuilder.BuildExpression(query.Condition);

            var deleteTpl = new StringTemplate(DeleteTemplate);

            SetCommandText(cmd, deleteTpl.FormatTemplate((varName) => {
                switch (varName)
                {
                case "table": return(new StringTemplate.TokenResult(query.Table));

                case "where": return(new StringTemplate.TokenResult(whereExpression));
                }
                return(StringTemplate.TokenResult.NotDefined);
            }));

            return(cmd);
        }
Example #4
0
        /// <summary>
        /// Generates SELECT SQL statement by given <see cref="Query"/> and <see cref="ISqlExpressionBuilder"/>.
        /// </summary>
        /// <param name="query">formal query structure</param>
        /// <param name="sqlBuilder">SQL builder component</param>
        /// <param name="isSubquery">subquery flag (true if this is sub-query select)</param>
        /// <returns>SQL select command text</returns>
        public virtual string FormatSelectSql(Query query, ISqlExpressionBuilder sqlBuilder, bool isSubquery)
        {
            var    isCount         = IsCountQuery(query);
            string columns         = BuildSelectColumns(query, sqlBuilder);
            string groupBy         = BuildGroupBy(query, sqlBuilder);
            string orderBy         = BuildOrderBy(query, sqlBuilder);
            string whereExpression = BuildWhere(query, sqlBuilder);
            var    tblName         = sqlBuilder.BuildTableName(query.Table);

            var selectTpl = new StringTemplate(SelectTemplate);

            return(selectTpl.FormatTemplate((varName) => {
                switch (varName)
                {
                case "table": return new StringTemplate.TokenResult(tblName);

                case "where": return new StringTemplate.TokenResult(whereExpression);

                case "groupby": return groupBy == null ? StringTemplate.TokenResult.NotDefined : new StringTemplate.TokenResult(groupBy);

                case "orderby": return isCount ? StringTemplate.TokenResult.NotApplicable : new StringTemplate.TokenResult(orderBy);

                case "columns": return new StringTemplate.TokenResult(columns);

                case "recordoffset": return query.RecordOffset > 0 ? new StringTemplate.TokenResult(query.RecordOffset) : StringTemplate.TokenResult.NotDefined;

                case "recordcount": return query.RecordCount < Int32.MaxValue ? new StringTemplate.TokenResult(query.RecordCount) : StringTemplate.TokenResult.NotDefined;

                case "recordtop": return query.RecordCount < Int32.MaxValue ? new StringTemplate.TokenResult(query.RecordOffset + query.RecordCount) : StringTemplate.TokenResult.NotDefined;
                }
                if (query.ExtendedProperties != null && query.ExtendedProperties.ContainsKey(varName))
                {
                    return new StringTemplate.TokenResult(query.ExtendedProperties[varName]);
                }
                return StringTemplate.TokenResult.NotDefined;
            }));
        }