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; }
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; } }
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(" "); }
private void GenerateUpdate(StringBuilder builder, QueryTemplate template) { builder.AppendFormat("UPDATE {0} SET ", template.TableName); }
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 + " "); } }
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; } } }
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); } } }
private IDataCommand BuildSelect(QueryTemplate template) { IDataCommand cmd = null; if (template.AggregateType == AggregateType.None) { cmd = BuildStandardSelect(template); } else { cmd = BuildAggregateSelect(template); } return cmd; }
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(") "); }
private void GenerateDelete(StringBuilder builder, QueryTemplate template) { builder.AppendFormat("DELETE FROM {0} ", template.TableName); }
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); } }
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; } }
private void GenerateAggregate(StringBuilder builder, QueryTemplate template) { var aggregate = FormatAggregate(template.AggregateType); builder.AppendFormat("SELECT {0}({1}) AS Value ", aggregate, template.AggregateColumn); }
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; }
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; }
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(") "); }
protected SqlQuery(IDataProvider provider, QueryType type) { _provider = provider; _template = new QueryTemplate(type); }
public IDataCommand Generate(QueryTemplate template) { return _generator.Build(template); }