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"); }