Пример #1
0
        private IDataCommand BuildDelete(QueryTemplate template)
        {
            var builder = new StringBuilder();
            var cmd = new StormCommand(_provider);

            _parameterCount = 0;

            GenerateDelete(builder, template);
            GenerateCriteria(builder, template, cmd);

            cmd.CommandText = builder.ToString();

            return cmd;
        }
Пример #2
0
 public IDataCommand Build(QueryTemplate template)
 {
     switch (template.QueryType)
     {
         case QueryType.Select:
             return BuildSelect(template);
         case QueryType.Insert:
             return BuildInsert(template);
         case QueryType.Update:
             return BuildUpdate(template);
         case QueryType.Delete:
             return BuildDelete(template);
         default:
             return null;
     }
 }
Пример #3
0
        private void GenerateUpdateValues(StringBuilder builder, QueryTemplate template, IDataCommand cmd)
        {
            bool first = true;
            foreach (var valueItem in template.UpdateValues)
            {
                if (!first) builder.Append(",");

                var paramName = "@" + valueItem.ColumnName;
                builder.AppendFormat("{0} = {1}", valueItem.ColumnName, paramName);
                cmd.AddParameter(paramName, valueItem.Value);

                first = false;
            }
            builder.Append(" ");
        }
Пример #4
0
 private void GenerateUpdate(StringBuilder builder, QueryTemplate template)
 {
     builder.AppendFormat("UPDATE {0} SET ", template.TableName);
 }
Пример #5
0
        private void GenerateSelect(StringBuilder builder, QueryTemplate template)
        {
            // Setup the select clause.
            if (template.Limit == 0)
            {
                builder.Append("SELECT ");
            }
            else
            {
                builder.AppendFormat("SELECT TOP {0} ", template.Limit);
            }

            // Add the columns to select.
            if (string.IsNullOrEmpty(template.SelectColumns))
            {
                builder.Append("* ");
            }
            else
            {
                builder.Append(template.SelectColumns + " ");
            }
        }
Пример #6
0
        private void GenerateOrderBy(StringBuilder builder, QueryTemplate template)
        {
            if (template.OrderBy.Count > 0)
            {
                builder.Append("ORDER BY ");
                bool first = true;
                foreach (var orderItem in template.OrderBy)
                {
                    if (!first) builder.Append(",");

                    var direction = orderItem.Ascending ? "ASC" : "DESC";
                    builder.AppendFormat("{0} {1}", orderItem.ColumnName, direction);

                    first = false;
                }
            }
        }
Пример #7
0
 private void GenerateJoins(StringBuilder builder, QueryTemplate template)
 {
     // Add the joins.
     foreach (var joinItem in template.Joins)
     {
         var joinTypeName = FormatJoinType(joinItem.JoinType);
         if (string.IsNullOrEmpty(joinItem.AliasName))
         {
             builder.AppendFormat("{0} JOIN {1} ON {2} = {3} ", joinTypeName, joinItem.TableName, joinItem.LeftColumn, joinItem.RightColumn);
         }
         else
         {
             builder.AppendFormat("{0} JOIN {1} AS {2} ON {3} = {4} ", joinTypeName, joinItem.TableName, joinItem.AliasName, joinItem.LeftColumn, joinItem.RightColumn);
         }
     }
 }
Пример #8
0
        private IDataCommand BuildSelect(QueryTemplate template)
        {
            IDataCommand cmd = null;

            if (template.AggregateType == AggregateType.None)
            {
                cmd = BuildStandardSelect(template);
            }
            else
            {
                cmd = BuildAggregateSelect(template);
            }

            return cmd;
        }
Пример #9
0
        private void GenerateInsert(StringBuilder builder, QueryTemplate template)
        {
            builder.AppendFormat("INSERT INTO {0} ", template.TableName);

            builder.Append("(");
            bool first = true;
            foreach (var valueItem in template.InsertValues)
            {
                if (!first) builder.Append(",");

                builder.Append(valueItem.ColumnName);

                first = false;
            }
            builder.Append(") ");
        }
Пример #10
0
 private void GenerateDelete(StringBuilder builder, QueryTemplate template)
 {
     builder.AppendFormat("DELETE FROM {0} ", template.TableName);
 }
Пример #11
0
 private void GenerateFrom(StringBuilder builder, QueryTemplate template)
 {
     // Add the from clause.
     if (string.IsNullOrEmpty(template.TableAlias))
     {
         builder.AppendFormat("FROM {0} ", template.TableName);
     }
     else
     {
         builder.AppendFormat("FROM {0} AS {1} ", template.TableName, template.TableAlias);
     }
 }
Пример #12
0
        private void GenerateCriteria(StringBuilder builder, QueryTemplate template, IDataCommand cmd)
        {
            // Add the criteria.
            var first = true;
            foreach (var criteriaItem in template.Criteria)
            {
                var whereClause = "WHERE";
                if (!first)
                {
                    whereClause = FormatConstraintType(criteriaItem.CriteriaType);
                }
                var comparisonOperator = FormatComparisonOperator(criteriaItem.Comparison);

                if (criteriaItem.Comparison == ComparisonOperator.IsNull || criteriaItem.Comparison == ComparisonOperator.IsNotNull)
                {
                    builder.AppendFormat("{0} {1} {2} ", whereClause, criteriaItem.ColumnName, comparisonOperator);
                }
                else
                {
                    var paramNames = SetupParameters(cmd, criteriaItem);
                    builder.AppendFormat("{0} {1} {2} {3} ", whereClause, criteriaItem.ColumnName, comparisonOperator, paramNames);
                }

                first = false;
            }
        }
Пример #13
0
        private void GenerateAggregate(StringBuilder builder, QueryTemplate template)
        {
            var aggregate = FormatAggregate(template.AggregateType);

            builder.AppendFormat("SELECT {0}({1}) AS Value ", aggregate, template.AggregateColumn);
        }
Пример #14
0
        private IDataCommand BuildStandardSelect(QueryTemplate template)
        {
            var builder = new StringBuilder();
            var cmd = new StormCommand(_provider);

            _parameterCount = 0;

            GenerateSelect(builder, template);
            GenerateFrom(builder, template);
            GenerateJoins(builder, template);
            GenerateCriteria(builder, template, cmd);
            GenerateOrderBy(builder, template);

            cmd.CommandText = builder.ToString();

            return cmd;
        }
Пример #15
0
        private IDataCommand BuildInsert(QueryTemplate template)
        {
            var builder = new StringBuilder();
            var cmd = new StormCommand(_provider);

            _parameterCount = 0;

            GenerateInsert(builder, template);
            GenerateInsertValues(builder, template, cmd);

            if (template.SelectIdentity)
            {
                builder.Append(";");
                GenerateSelectIdentity(builder);
            }

            cmd.CommandText = builder.ToString();

            return cmd;
        }
Пример #16
0
        private void GenerateInsertValues(StringBuilder builder, QueryTemplate template, IDataCommand cmd)
        {
            builder.Append("VALUES (");

            bool first = true;
            foreach (var valueItem in template.InsertValues)
            {
                if (!first) builder.Append(",");

                var paramName = "@" + valueItem.ColumnName;
                builder.Append(paramName);
                cmd.AddParameter(paramName, valueItem.Value);

                first = false;
            }

            builder.Append(") ");
        }
Пример #17
0
 protected SqlQuery(IDataProvider provider, QueryType type)
 {
     _provider = provider;
     _template = new QueryTemplate(type);
 }
Пример #18
0
 public IDataCommand Generate(QueryTemplate template)
 {
     return _generator.Build(template);
 }