/// <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 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 "orderby": return isCount ? StringTemplate.TokenResult.NotApplicable : new StringTemplate.TokenResult(orderBy);
case "columns": return new StringTemplate.TokenResult(columns);
case "recordoffset": return new StringTemplate.TokenResult(query.RecordOffset);
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;
}));
}
/// <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);
}
/// <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);
}
/// <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);
}
