/// <summary>
/// Creates a SQL query to select some columns matching the specified <see cref="QueryConstraint"/> from a table.
/// </summary>
/// <param name="tableName">Name of the table to select records for.</param>
/// <param name="constraint">The <see cref="QueryConstraint"/> to be applied for record selection.
/// If <paramref name="constraint"/> is <c>null</c>, no constraints are respected.</param>
/// <param name="columns">The columns to be respected within selection.</param>
/// <exception cref="InvalidOperationException">Thrown, if this method is
/// called for a <see cref="QueryType"/> other than <see cref="QueryType.Select"/>.</exception>
/// <returns>
/// The SQL query.
/// </returns>
internal string CreateSelectSql(string tableName, QueryConstraint constraint, params string[] columns)
{
// Throw exception, if this method is called for wrong query type
if (type != QueryType.Select)
{
throw new InvalidOperationException(string.Format("Can't create select SQL statement for {0}!",
Enum.GetName(typeof(QueryType), type)));
}
StringBuilder columnBuilder = new StringBuilder();
// Define table / columns to be respected
if (columns == null || columns.Length == 0)
{
// No columns specified => select all columns
builder.Append(string.Format(" * from {0}", tableName));
}
else
{
// Define table and columns to respected
foreach (var column in columns)
{
columnBuilder.Append(column).Append(Separator);
}
builder.Append(columnBuilder.ToString().Trim(Separator));
builder.AppendLine(string.Format(" from {0} ", tableName));
}
// No constraint specified => select all records
if (constraint != null && !string.IsNullOrEmpty(constraint.Get()))
{
builder.Append(string.Format(" where {0}", constraint.Get()));
}
return builder.ToString();
}
/// <summary>
/// Creates a SQL query to update a single record.
/// </summary>
/// <param name="tableName">Name of the table to update.</param>
/// <param name="updateColumnValues">The column values to be updated.</param>
/// <param name="constraint">The <see cref="QueryConstraint"/> defining update constraints.</param>
/// <exception cref="InvalidOperationException">Thrown, if this method is
/// called for a <see cref="QueryType"/> other than <see cref="QueryType.Update"/>.</exception>
/// <returns>The SQL query.</returns>
internal string CreateUpdateSql(string tableName, IEnumerable<Pair<string, object>> updateColumnValues, QueryConstraint constraint)
{
// Throw exception, if this method is called for wrong query type
if (type != QueryType.Update)
{
throw new InvalidOperationException(string.Format("Can't create update SQL statement for {0}!",
Enum.GetName(typeof(QueryType), type)));
}
// Define table
builder.AppendLine(tableName);
builder.Append(" set ");
StringBuilder updateValueBuilder = new StringBuilder();
// Define column values to be updated
foreach (var updateColumnValue in updateColumnValues)
{
string columnName = updateColumnValue.Left;
object value = updateColumnValue.Right;
bool isTimeStamp = columnName.ToLower() == "timestamp";
// Special formatting to be applied for timestamp
string formattedValue = (isTimeStamp) ? FormatValue(value) : FormatValue(value, true);
updateValueBuilder.Append(string.Format("{0} = {1}{2}",
updateColumnValue.Left,
formattedValue,
Separator)
);
}
builder.AppendLine(updateValueBuilder.ToString().Trim(Separator));
// Get the constraints, append them to where clause and return
return builder.Append(string.Format(" where {0}", constraint.Get())).ToString();
}
/// <summary>
/// Creates an SQL query to delete records from specified table matching the
/// specified <see cref="QueryConstraint"/>.
/// </summary>
/// <param name="tableName">Name of the table to delete records for..</param>
/// <param name="constraint">The <see cref="QueryConstraint"/> providing constraints.</param>
/// <exception cref="InvalidOperationException">Thrown, if this method is
/// called for a <see cref="QueryType"/> other than <see cref="QueryType.Delete"/>.</exception>
/// <returns>The SQL query.</returns>
internal string CreateDeleteSql(string tableName, QueryConstraint constraint)
{
// Throw exception, if this method is called for wrong query type
if (type != QueryType.Delete)
{
throw new InvalidOperationException(string.Format("Can't create delete SQL statement for {0}!",
Enum.GetName(typeof(QueryType), type)));
}
if (constraint == null)
{
// Defining no constraints tries to delete all records => DELETE FROM [TABLE]
return builder.Append(tableName).ToString();
}
// Append constraints to where clause and return
return builder.Append(string.Format(" {0} where {1}", tableName, constraint.Get())).ToString();
}