public static SqlQuery Parse(string text)
{
if (string.IsNullOrEmpty(text))
{
throw new ArgumentNullException("text", "text cannot be null or empty");
}
text = text.Trim();
// check beginning
if (text.StartsWith("select ", StringComparison.OrdinalIgnoreCase))
{
SelectQuery selectQuery = new SelectQuery();
text = text.Substring(text.IndexOf(' ') + 1).TrimStart();
#region Top and Distinct
// we can have just distinct
if (MatchNextWord(ref text, "distinct "))
{
// process
selectQuery.IsDistinctClause = true;
text = text.Remove(0, 8).TrimStart();
// check for top
if (MatchNextWord(ref text, "top "))
{
throw new ArgumentException("The supplied query is invalid because the Distinct keyword cannot occur before the Top clause.", "text");
}
}
// or top and then distinct
if (MatchNextWord(ref text, "top "))
{
// process
int topN = 0;
selectQuery.Top = GetTop(ref text, out topN);
if (topN < 0)
{
throw new ArgumentOutOfRangeException("text", "The supplied query is invalid because the Top clause requests less than zero rows.");
}
if (selectQuery.Top == Top.Percent && topN > 100)
{
throw new ArgumentOutOfRangeException("text", "The supplied query is invalid because the Top clause requests greater than 100 percent of rows. Percent values must be between 0 and 100");
}
selectQuery.TopNumber = topN;
// do distinct again
if (MatchNextWord(ref text, "distinct "))
{
selectQuery.IsDistinctClause = true;
text = text.Remove(0, 8).TrimStart();
}
}
#endregion
#region Get Column Names
// field names
int fromIndex = text.IndexOf(" from ", StringComparison.OrdinalIgnoreCase);
if (fromIndex < 0)
{
throw new ArgumentException("The supplied query is invalid because there is no From clause in the Select statement", "text");
}
selectQuery.SelectedColumnsList.AddRange(GetGroupedNames(text.Substring(0, fromIndex).TrimEnd(), true).Select(x => new QualifiedFieldName(x)));
if (selectQuery.SelectedColumnsList.Count == 0)
{
throw new ArgumentException("The supplied query is invalid because there are no selected column names in the Select statement", "text");
}
text = text.Substring(fromIndex).TrimStart();
#endregion
#region From, Where, and Order By
// from (which we determined in the last step exists)
if (!MatchNextWord(ref text, "from "))
{
throw new ArgumentException("A from clause must exist in this query", "text");
}
text = text.Substring(text.IndexOf(' ')).TrimStart();
int whereIndex = text.IndexOf(" where ", StringComparison.OrdinalIgnoreCase);
int orderByIndex = text.IndexOf(" order by ", StringComparison.OrdinalIgnoreCase);
// ensure they're in the correct order
if (whereIndex > orderByIndex && orderByIndex > -1)
{
throw new ArgumentException("The supplied query is invalid. The Where clause must preceed the Order By clause.", "text");
}
// no additional clauses
if (whereIndex < 0 && orderByIndex < 0)
{
selectQuery.tablesList.AddRange(GetGroupedNames(text, false));
if (selectQuery.tablesList.Count == 0)
{
throw new ArgumentException("The supplied query is invalid because there were no tables listed in the From clause.", "text");
}
}
if (whereIndex > -1)
{
selectQuery.tablesList.AddRange(GetGroupedNames(text.Substring(0, whereIndex).TrimStart(), false));
if (selectQuery.tablesList.Count == 0)
{
throw new ArgumentException("The supplied query is invalid because there were no tables listed in the From clause.", "text");
}
text = text.Substring(whereIndex + 7).TrimStart();
// process where clause
if (orderByIndex > -1)
{
selectQuery.WhereExpression = new WhereExpression(text.Substring(0, orderByIndex).Trim(), selectQuery.tablesList);
text = text.Substring(orderByIndex).TrimStart(); // bring us up to the order by clause but not past it
}
else
{
selectQuery.WhereExpression = new WhereExpression(text, selectQuery.tablesList);
}
}
if (orderByIndex > -1)
{
// it hasn't been processed yet, give it a shot
if (selectQuery.tablesList.Count == 0)
{
selectQuery.tablesList.AddRange(GetGroupedNames(text.Substring(0, orderByIndex).TrimStart(), false));
if (selectQuery.tablesList.Count == 0)
{
throw new ArgumentException("The supplied query is invalid because there were no tables listed in the From clause.", "text");
}
text = text.Substring(orderByIndex).TrimStart();
}
// now process order by clause
if (!MatchNextWord(ref text, "order "))
{
throw new ArgumentException("The supplied query is invalid because of a malformed Order By clause.", "text");
}
text = text.Substring(text.IndexOf(' ')).TrimStart();
if (!MatchNextWord(ref text, "by "))
{
throw new ArgumentException("The supplied query is invalid because of a malformed Order By clause.", "text");
}
text = text.Substring(text.IndexOf(' ')).TrimStart();
IEnumerable <QualifiedFieldName> orderByFields = GetGroupedNames(text, false).Select(x => new QualifiedFieldName(x));
foreach (var field in orderByFields)
{
// CAUTION: not sure if this is necessary but its a safety precaution
//if (!selectQuery.SelectedColumnsList.Contains(field) && !selectQuery.SelectedColumnsList.Contains(new QualifiedFieldName(field.TableQualifier + ".*"))) throw new ArgumentException("The supplied query is invalid because the Order By clause references a field that is not included in the Select clause.", "text");
// Lets just check to make sure the table is referenced if its a multipart identified
// its the table identifier is all tables then don't bother
if (field.TableQualifier.Equals("*"))
{
continue;
}
if (selectQuery.SelectedTables.FirstOrDefault(x => x.Equals(field.TableQualifier, StringComparison.OrdinalIgnoreCase)) == null)
{
throw new ArgumentException("The supplied query is invalid because the Order By clause references a table that is not included in the From clause.", "text");
}
}
selectQuery.OrderByFieldsList.AddRange(orderByFields);
}
#endregion
#region Validate Select Columns
// make sure that any multipart identifiers in the selected field names are referenced in the from clause
foreach (var field in selectQuery.SelectedColumns)
{
if (field.TableQualifier.Equals("*"))
{
continue;
}
if (selectQuery.SelectedTables.FirstOrDefault(x => x.Equals(field.TableQualifier, StringComparison.OrdinalIgnoreCase)) == null)
{
throw new ArgumentException("The supplied query is invalid because the Select clause references a table that is not included in the From clause.", "text");
}
}
#endregion
return(selectQuery);
}
else if (text.StartsWith("insert ", StringComparison.OrdinalIgnoreCase))
{
InsertQuery insertQuery = new InsertQuery();
return(insertQuery);
}
else if (text.StartsWith("delete ", StringComparison.OrdinalIgnoreCase))
{
DeleteQuery deleteQuery = new DeleteQuery();
return(deleteQuery);
}
else if (text.StartsWith("update ", StringComparison.OrdinalIgnoreCase))
{
// this is just a parser, allow it here but blowup if they try to execute it
UpdateQuery updateQuery = new UpdateQuery();
return(updateQuery);
}
throw new ArgumentException("The supplied query is invalid because it does not begin with a valid SQL keyword.", "text");
}
public static SqlQuery Parse(string text)
{
if (string.IsNullOrEmpty(text))
throw new ArgumentNullException("text", "text cannot be null or empty");
text = text.Trim();
// check beginning
if (text.StartsWith("select ", StringComparison.OrdinalIgnoreCase)) {
SelectQuery selectQuery = new SelectQuery();
text = text.Substring(text.IndexOf(' ') + 1).TrimStart();
#region Top and Distinct
// we can have just distinct
if (MatchNextWord(ref text, "distinct ")) {
// process
selectQuery.IsDistinctClause = true;
text = text.Remove(0, 8).TrimStart();
// check for top
if (MatchNextWord(ref text, "top "))
throw new ArgumentException("The supplied query is invalid because the Distinct keyword cannot occur before the Top clause.", "text");
}
// or top and then distinct
if (MatchNextWord(ref text, "top ")) {
// process
int topN = 0;
selectQuery.Top = GetTop(ref text, out topN);
if (topN < 0)
throw new ArgumentOutOfRangeException("text", "The supplied query is invalid because the Top clause requests less than zero rows.");
if (selectQuery.Top == Top.Percent && topN > 100)
throw new ArgumentOutOfRangeException("text", "The supplied query is invalid because the Top clause requests greater than 100 percent of rows. Percent values must be between 0 and 100");
selectQuery.TopNumber = topN;
// do distinct again
if (MatchNextWord(ref text, "distinct ")) {
selectQuery.IsDistinctClause = true;
text = text.Remove(0, 8).TrimStart();
}
}
#endregion
#region Get Column Names
// field names
int fromIndex = text.IndexOf(" from ", StringComparison.OrdinalIgnoreCase);
if (fromIndex < 0)
throw new ArgumentException("The supplied query is invalid because there is no From clause in the Select statement", "text");
selectQuery.SelectedColumnsList.AddRange(GetGroupedNames(text.Substring(0, fromIndex).TrimEnd(), true).Select(x => new QualifiedFieldName(x)));
if (selectQuery.SelectedColumnsList.Count == 0)
throw new ArgumentException("The supplied query is invalid because there are no selected column names in the Select statement", "text");
text = text.Substring(fromIndex).TrimStart();
#endregion
#region From, Where, and Order By
// from (which we determined in the last step exists)
if (!MatchNextWord(ref text, "from "))
throw new ArgumentException("A from clause must exist in this query", "text");
text = text.Substring(text.IndexOf(' ')).TrimStart();
int whereIndex = text.IndexOf(" where ", StringComparison.OrdinalIgnoreCase);
int orderByIndex = text.IndexOf(" order by ", StringComparison.OrdinalIgnoreCase);
// ensure they're in the correct order
if (whereIndex > orderByIndex && orderByIndex > -1)
throw new ArgumentException("The supplied query is invalid. The Where clause must preceed the Order By clause.", "text");
// no additional clauses
if (whereIndex < 0 && orderByIndex < 0) {
selectQuery.tablesList.AddRange(GetGroupedNames(text, false));
if (selectQuery.tablesList.Count == 0)
throw new ArgumentException("The supplied query is invalid because there were no tables listed in the From clause.", "text");
}
if (whereIndex > -1) {
selectQuery.tablesList.AddRange(GetGroupedNames(text.Substring(0, whereIndex).TrimStart(), false));
if (selectQuery.tablesList.Count == 0)
throw new ArgumentException("The supplied query is invalid because there were no tables listed in the From clause.", "text");
text = text.Substring(whereIndex + 7).TrimStart();
// process where clause
if (orderByIndex > -1) {
selectQuery.WhereExpression = new WhereExpression(text.Substring(0, orderByIndex).Trim(), selectQuery.tablesList);
text = text.Substring(orderByIndex).TrimStart(); // bring us up to the order by clause but not past it
}
else {
selectQuery.WhereExpression = new WhereExpression(text, selectQuery.tablesList);
}
}
if (orderByIndex > -1) {
// it hasn't been processed yet, give it a shot
if (selectQuery.tablesList.Count == 0) {
selectQuery.tablesList.AddRange(GetGroupedNames(text.Substring(0, orderByIndex).TrimStart(), false));
if (selectQuery.tablesList.Count == 0)
throw new ArgumentException("The supplied query is invalid because there were no tables listed in the From clause.", "text");
text = text.Substring(orderByIndex).TrimStart();
}
// now process order by clause
if (!MatchNextWord(ref text, "order "))
throw new ArgumentException("The supplied query is invalid because of a malformed Order By clause.", "text");
text = text.Substring(text.IndexOf(' ')).TrimStart();
if (!MatchNextWord(ref text, "by "))
throw new ArgumentException("The supplied query is invalid because of a malformed Order By clause.", "text");
text = text.Substring(text.IndexOf(' ')).TrimStart();
IEnumerable<QualifiedFieldName> orderByFields = GetGroupedNames(text, false).Select(x => new QualifiedFieldName(x));
foreach (var field in orderByFields) {
// CAUTION: not sure if this is necessary but its a safety precaution
//if (!selectQuery.SelectedColumnsList.Contains(field) && !selectQuery.SelectedColumnsList.Contains(new QualifiedFieldName(field.TableQualifier + ".*"))) throw new ArgumentException("The supplied query is invalid because the Order By clause references a field that is not included in the Select clause.", "text");
// Lets just check to make sure the table is referenced if its a multipart identified
// its the table identifier is all tables then don't bother
if (field.TableQualifier.Equals("*"))
continue;
if (selectQuery.SelectedTables.FirstOrDefault(x => x.Equals(field.TableQualifier, StringComparison.OrdinalIgnoreCase)) == null)
throw new ArgumentException("The supplied query is invalid because the Order By clause references a table that is not included in the From clause.", "text");
}
selectQuery.OrderByFieldsList.AddRange(orderByFields);
}
#endregion
#region Validate Select Columns
// make sure that any multipart identifiers in the selected field names are referenced in the from clause
foreach (var field in selectQuery.SelectedColumns) {
if (field.TableQualifier.Equals("*"))
continue;
if (selectQuery.SelectedTables.FirstOrDefault(x => x.Equals(field.TableQualifier, StringComparison.OrdinalIgnoreCase)) == null)
throw new ArgumentException("The supplied query is invalid because the Select clause references a table that is not included in the From clause.", "text");
}
#endregion
return selectQuery;
}
else if (text.StartsWith("insert ", StringComparison.OrdinalIgnoreCase)) {
InsertQuery insertQuery = new InsertQuery();
return insertQuery;
}
else if (text.StartsWith("delete ", StringComparison.OrdinalIgnoreCase)) {
DeleteQuery deleteQuery = new DeleteQuery();
return deleteQuery;
}
else if (text.StartsWith("update ", StringComparison.OrdinalIgnoreCase)) {
// this is just a parser, allow it here but blowup if they try to execute it
UpdateQuery updateQuery = new UpdateQuery();
return updateQuery;
}
throw new ArgumentException("The supplied query is invalid because it does not begin with a valid SQL keyword.", "text");
}