private string ReduceAndPopulate(string sql, NameValueCollection requestParams) { bool stringified = false; int commented = 0; string result = null; // The goal here is to reduce the SQL to as basic of a representation as possible, // without changing the actuale execution results. try { Errors = new List <string>(); var buffer = new StringBuilder(); Action <string, int> ParseLine = null; #region Inner Function: ParseLine ParseLine = (line, depth) => { int startComment, startString, startSingleComment, endComment, endString; if (line.Length == 0 || (!stringified && line.Trim().Length == 0)) { return; } if (depth == 10) { // Should never happen, but if we get this deep it's just safer to bail throw new StackOverflowException("SQL reduction has likely gone wrong"); } else { ++depth; } if (commented > 0) { endComment = line.IndexOf("*/"); if (endComment != -1) { commented += line.Substring(0, endComment).OccurencesOf("/*") - 1; line = line.Substring(endComment + 2); if (commented > 0) { ParseLine(line, depth); return; } } else { commented += line.OccurencesOf("/*"); return; } } else if (stringified) { endString = line.IndexOf("'"); if (endString == -1) { buffer.Append(ScanSegment(line)).Append('\n'); return; } buffer.Append(ScanSegment(line.Substring(0, endString + 1))); line = line.Substring(endString + 1); if (line.Length > 0 && line[0] == '\'') { // We didn't actually end the string, because the single quote we // indexed before was actually escaping this single quote. buffer.Append('\''); ParseLine(line.Substring(1), depth); return; } else if (line.Length == 0) { // We run into spacing issues without this if this string terminates the line buffer.Append(" "); } stringified = false; } List <string> substitutions = new List <string>(); string remainder = null; line = QuotesRegex.Replace(line, (match) => { substitutions.Add(match.Value); return("~S" + (substitutions.Count - 1)); }); startSingleComment = line.IndexOf("--"); startSingleComment = startSingleComment == -1 ? line.Length : startSingleComment; startString = line.IndexOf('\'', 0, startSingleComment); startComment = line.IndexOf("/*", 0, startSingleComment); if (startString != -1 || startComment != -1) { if ((startString < startComment && startString != -1) || startComment == -1) { stringified = true; } else { ++commented; remainder = line.Substring(startComment + 2); line = line.Substring(0, startComment); } } else { line = line.Substring(0, startSingleComment); } for (int i = substitutions.Count - 1; i > -1; --i) { line = line.Replace("~S" + i, substitutions[i]); } if (!stringified) { line = line.Trim(); } else if (startString != -1) { line = line.TrimStart(); } if (line.Length > 0 || stringified) { string firstWord = null; if (!stringified) { int space = line.IndexOf(' '); firstWord = line; if (space != -1) { firstWord = firstWord.Substring(0, space); } firstWord = firstWord.ToUpper(); if (PreBreakWords.Contains(firstWord) && buffer.Length > 0) { if (buffer[buffer.Length - 1] == ' ') { buffer[buffer.Length - 1] = '\n'; } else { buffer.Append('\n'); } } } buffer.Append(ScanSegment(line)); if (stringified || firstWord == "GO") { buffer.Append('\n'); } else { buffer.Append(' '); } } if (remainder != null) { ParseLine(remainder, depth); } }; #endregion foreach (string line in sql.Split('\n')) { ParseLine(line, 0); } result = buffer.ToString().Trim(); foreach (string name in Parameters.Keys) { var parameter = Parameters[name]; string value = requestParams != null ? requestParams[name] : null; ParameterType type = null; if (!string.IsNullOrEmpty(parameter.Type)) { type = GetType(parameter.Type); } if (!string.IsNullOrEmpty(parameter.Default) && type != null) { if (!type.Validator(parameter.Default)) { Errors.Add(string.Format("Expected default value {0} for {1} to be a {2}!", parameter.Default, name, type.TypeName)); continue; } } if (string.IsNullOrEmpty(value)) { if (!string.IsNullOrEmpty(parameter.Default)) { value = parameter.Default; } else { Errors.Add(string.Format("Missing value for {0}!", name)); continue; } } if (type != null) { if (!type.Validator(value)) { Errors.Add(string.Format("Expected value of {0} to be a {1}!", name, type.TypeName)); continue; } value = type.Encoder(value); } parameter.Value = value; result = result.Replace("##" + name + "##", value); } if (commented > 0) { Errors.Add("Missing end comment mark */"); } } catch (StackOverflowException) { result = SubstituteParams(sql, requestParams); } return(result); }