protected virtual StringBuilder CreateORDERBYCluase(bool orderBy, IList <string> columns, bool asc)
{
if (orderBy == false)
{
return(null);
}
if (columns == null)
{
throw new ArgumentNullException();
}
if (columns.Count == 0)
{
throw new EmptyCollectionException();
}
else
{
StringBuilder sql = new StringBuilder(" ORDER BY ");
foreach (string column in columns)
{
sql.Append(string.Format("{0}{1}{2}, ", openingColumnMark, SQLCleanser.EscapeAndRemoveWords(column), closingColumnMark));
}
sql.Remove(sql.Length - 2, 2).Append(" ");
return((asc == true) ? sql.Append(" ASC ").Replace(" ", " ") : sql.Append(" DESC ").Replace(" ", " "));
}
}
protected virtual StringBuilder CreateFROMClause(string table)
{
if (table == null)
{
throw new ArgumentNullException();
}
if (table == string.Empty)
{
throw new ArgumentException("The table argument is an empty string");
}
string s = string.Format(" FROM {0}{1}{2} ", openingColumnMark, SQLCleanser.EscapeAndRemoveWords(table), closingColumnMark);
StringBuilder sql = new StringBuilder(s);
return(sql.Replace(" ", " "));
}
private StringBuilder CreateSELECTClause(bool distinct, List <string> columns, string limit)
{
if (columns == null)
{
return(null);
}
string startSql = (distinct == true) ? "SELECT DISTINCT " : "SELECT ";
StringBuilder sql = new StringBuilder(startSql);
if (limit != null)
{
sql.Append(" TOP " + SQLCleanser.EscapeAndRemoveWords(limit));
}
foreach (string column in columns)
{
sql.Append(string.Format("{0}{1}{2}, ", openingColumnMark, SQLCleanser.EscapeAndRemoveWords(column), closingColumnMark));
}
sql = sql.Remove(sql.Length - 2, 2).Append(" ");
return(sql.Replace(" ", " "));
}
protected virtual StringBuilder CreateSELECTClause(bool distinct, IList <string> columns)
{
if (columns == null)
{
throw new ArgumentNullException();
}
if (columns.Count == 0)
{
throw new EmptyCollectionException();
}
string startSql = (distinct == true) ? "SELECT DISTINCT " : "SELECT ";
StringBuilder sql = new StringBuilder(startSql);
foreach (string column in columns)
{
sql.Append(string.Format("{0}{1}{2}, ", openingColumnMark, SQLCleanser.EscapeAndRemoveWords(column), closingColumnMark));
}
sql = sql.Remove(sql.Length - 2, 2).Append(" ");
return(sql.Replace(" ", " "));
}
protected virtual StringBuilder CreateWHEREClause(IList <Criteria> criteria)
{
if (criteria == null)
{
return(null);
}
if (criteria.Count == 0)
{
return(null);
}
else
{
// Create array so that contents can be modified.
Criteria[] copyCriteria = new Criteria[criteria.Count];
criteria.CopyTo(copyCriteria, 0);
// First criteria's AndOr property is not needed since it's coming directly after the WHERE keyword in the SQL statement.
copyCriteria[0].AndOr = null;
StringBuilder sql = new StringBuilder(" WHERE ");
for (var i = 0; i < copyCriteria.Length; i++)
{
var theCriteria = copyCriteria[i];
if (!CriteriaHasNullColumnOrOperator(theCriteria))
{
// If Operator is "Is Null" or "Is Not Null".
if (theCriteria.Operator == Operator.IsNull || theCriteria.Operator == Operator.IsNotNull)
{
sql.Append($" {theCriteria.ToString()} ");
continue;
}
// Now that we know that the operator is something other than "Is Null or "Is Not Null", we need to check that the Filter is not null or an empty string.
if (theCriteria.Filter == null || theCriteria.Filter == string.Empty)
{
throw new Exception("A criteria has a null or empty filter, but the operator is not 'IsNull' or 'IsNotNull'");
}
// Now that we know that the Filter is not null or an empty string, test if the filter is a subquery.
if (theCriteria.FilterIsSubQuery())
{
theCriteria.Filter = SQLCleanser.EscapeAndRemoveWords(theCriteria.Filter);
sql.Append($" {theCriteria.ToString()} ");
continue;
}
// If the filter is not a subquery, then determine if the filter needs quotes or not (quotes if it's text based, no quotes if it's number based).
var columnDataType = GetColumnDataType(theCriteria.Column);
if (columnDataType == null)
{
// If the column name cannot be found in the table schema datatable, then throw Exception
throw new Exception(string.Format($"Could not find column name, {theCriteria.Column}, in table schema for {tableSchema.TableName}"));
}
else
{
var shouldHaveQuotes = IsColumnQuoted(columnDataType);
// If the operator is "In" or "Not In".
if (theCriteria.Operator == Operator.In || theCriteria.Operator == Operator.NotIn)
{
var originalFilters = theCriteria.Filter.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var newFilters = new string[originalFilters.Count()];
// If the filter should have quotes.
if (shouldHaveQuotes)
{
for (var j = 0; j < originalFilters.Count(); j++)
{
newFilters[j] = $"'{SQLCleanser.EscapeAndRemoveWords(originalFilters[j])}'";
}
theCriteria.Filter = "(" + string.Join(",", newFilters) + ")";
sql.Append($" {theCriteria.ToString()} ");
}
//If the filter should NOT have quotes.
else
{
theCriteria.Filter = "(" + SQLCleanser.EscapeAndRemoveWords(theCriteria.Filter) + ")";
sql.Append($" {theCriteria.ToString()} ");
}
}
// If the operator is anything other than "In" or "Not In" (and not "Is Null" or "Is Not Null" because that test is done earlier in the method).
else
{
var cleansedValue = SQLCleanser.EscapeAndRemoveWords(theCriteria.Filter);
theCriteria.Filter = (shouldHaveQuotes) ? $" '{cleansedValue}' " : $" {cleansedValue} ";
sql.Append($" {theCriteria.ToString()} ");
}
}
}
else
{
throw new BadSQLException("One or more of the criteria in the Criteria list has a " +
"null value for it's Column and/or operator. Please make sure each criteria has " +
"has a non-null value for each of these properties");
}
}
return(sql.Replace(" ", " "));
}
}