/// <summary>
/// Replaces all text occurrences in the SqlStatement
/// </summary>
/// <param name="find"></param>
/// <param name="replace"></param>
/// <param name="ignoreCase"></param>
/// <returns></returns>
public SqlStatement Replace(string find, string replace, bool ignoreCase)
{
var builder = new SqlStatementBuilder(this);
builder.Replace(find, replace, ignoreCase);
return(builder.ToSqlStatement());
}
/// <summary>
/// Formats an SqlStatement
/// </summary>
/// <param name="format"></param>
/// <param name="sqlStatements"></param>
/// <returns></returns>
public static SqlStatement Format(string format, IList <SqlStatement> sqlStatements)
{
var builder = new SqlStatementBuilder();
builder.AppendFormat(format, sqlStatements);
return(builder.ToSqlStatement());
}
/// <summary>
/// Builds an insert clause
/// </summary>
/// <param name="table">Table name</param>
/// <param name="inputColumns">Columns to be inserted</param>
/// <param name="inputValues">Values to be inserted into columns</param>
/// <returns></returns>
public virtual SqlStatement GetInsert(SqlStatement table, IList<SqlStatement> inputColumns, IList<SqlStatement> inputValues)
{
if (inputColumns.Count == 0)
return SqlStatement.Empty;
var insertBuilder = new SqlStatementBuilder("INSERT INTO ");
insertBuilder.Append(table);
insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputColumns));
insertBuilder.Append(" VALUES");
insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputValues));
return insertBuilder.ToSqlStatement();
}
/// <summary>
/// Joins SqlStatements into a new SqlStatement
/// </summary>
/// <param name="sqlStatement"></param>
/// <param name="sqlStatements"></param>
/// <returns></returns>
public static SqlStatement Join(SqlStatement sqlStatement, IList <SqlStatement> sqlStatements)
{
// optimization: if we have only one statement to join, we return the statement itself
if (sqlStatements.Count == 1)
{
return(sqlStatements[0]);
}
var builder = new SqlStatementBuilder();
builder.AppendJoin(sqlStatement, sqlStatements);
return(builder.ToSqlStatement());
}
/// <summary>
/// Builds the statements that gets back the IDs for the inserted statement
/// </summary>
/// <param name="table"></param>
/// <param name="autoPKColumn">Auto-generated PK columns for reference (i.e. AUTO_INCREMENT)</param>
/// <param name="inputPKColumns">PK columns for reference</param>
/// <param name="inputPKValues">PK values for reference</param>
/// <param name="outputParameters">Expected output parameters</param>
/// <param name="outputExpressions">Expressions (to help generate output parameters)</param>
/// <returns></returns>
public virtual SqlStatement GetInsertIds(SqlStatement table, IList<SqlStatement> autoPKColumn, IList<SqlStatement> pkColumns, IList<SqlStatement> pkValues, IList<SqlStatement> outputColumns, IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions)
{
if (autoPKColumn.Count == outputParameters.Count)
return "SELECT @@IDENTITY";
var insertIds = new SqlStatementBuilder("SELECT ");
insertIds.AppendFormat(" ({0})", SqlStatement.Join(", ", outputColumns));
insertIds.Append(" FROM ");
insertIds.Append(table);
insertIds.Append(" WHERE ");
bool valueSet = false;
if (autoPKColumn.Count > 0)
{
insertIds.AppendFormat("{0} = @@IDENTITY", autoPKColumn[0]);
valueSet = true;
}
for (IEnumerator<SqlStatement> column = pkColumns.GetEnumerator(), value = pkValues.GetEnumerator(); column.MoveNext() && value.MoveNext();)
{
if (valueSet)
insertIds.Append(" AND ");
insertIds.AppendFormat("{0} = {1}", column.Current, value.Current);
valueSet = true;
}
return insertIds.ToSqlStatement();
}
/// <summary>
/// Builds a delete clause
/// </summary>
/// <param name="table"></param>
/// <param name="inputPKColumns">PK columns for reference</param>
/// <param name="inputPKValues">PK values for reference</param>
/// <returns></returns>
public SqlStatement GetDelete(SqlStatement table, IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues)
{
if (inputPKColumns.Count == 0)
return SqlStatement.Empty;
var deleteBuilder = new SqlStatementBuilder("DELETE FROM ");
deleteBuilder.Append(table);
deleteBuilder.Append(" WHERE ");
bool valueSet = false;
for (IEnumerator<SqlStatement> column = inputPKColumns.GetEnumerator(), value = inputPKValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
{
if (valueSet)
deleteBuilder.Append(" AND ");
deleteBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
valueSet = true;
}
return deleteBuilder.ToSqlStatement();
}
/// <summary>
/// Builds an update clause
/// </summary>
/// <param name="table"></param>
/// <param name="inputColumns">Columns to be inserted</param>
/// <param name="inputValues">Values to be inserted into columns</param>
/// <param name="outputParameters">Expected output parameters</param>
/// <param name="outputExpressions">Expressions (to help generate output parameters)</param>
/// <param name="inputPKColumns">PK columns for reference</param>
/// <param name="inputPKValues">PK values for reference</param>
/// <returns></returns>
public SqlStatement GetUpdate(SqlStatement table, IList<SqlStatement> inputColumns,
IList<SqlStatement> inputValues,
IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions,
IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues)
{
if (inputColumns.Count == 0)
return SqlStatement.Empty;
var updateBuilder = new SqlStatementBuilder("UPDATE ");
updateBuilder.Append(table);
updateBuilder.Append(" SET ");
bool valueSet = false;
for (IEnumerator<SqlStatement> column = inputColumns.GetEnumerator(), value = inputValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
{
if (valueSet)
updateBuilder.Append(", ");
updateBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
valueSet = true;
}
updateBuilder.Append(" WHERE ");
valueSet = false;
for (IEnumerator<SqlStatement> column = inputPKColumns.GetEnumerator(), value = inputPKValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
{
if (valueSet)
updateBuilder.Append(" AND ");
updateBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
valueSet = true;
}
return updateBuilder.ToSqlStatement();
}
public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit)
{
// return string.Format("SELECT FIRST {0} FROM ({1})", limit, select);
var trimSelect = "SELECT ";
if (select.Count > 0 && select[0].Sql.StartsWith(trimSelect))
{
var selectBuilder = new SqlStatementBuilder(select);
var remaining = select[0].Sql.Substring(trimSelect.Length);
selectBuilder.Parts[0] = new SqlLiteralPart(remaining);
return SqlStatement.Format("SELECT FIRST {0} {1}", limit, selectBuilder.ToSqlStatement());
}
throw new ArgumentException("Invalid SELECT format");
}
public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit)
{
var trimSelect = "SELECT ";
if (select.Count > 0 && select[0].Sql.StartsWith(trimSelect))
{
var selectBuilder = new SqlStatementBuilder(select);
var remaining = select[0].Sql.Substring(trimSelect.Length);
selectBuilder.Parts[0] = new SqlLiteralPart(remaining);
return SqlStatement.Format("SELECT TOP ({0}) {1}", limit, selectBuilder.ToSqlStatement());
}
throw new ArgumentException("S0051: Unknown select format");
}