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);
}
