private (string, List <DbCommandParameter.DbCommandParameter>, IList <ParseError>) CreateInsertStatement(string tableName, DataTable records, ICollection <DbCommandParameter.DbCommandParameter> parameter = null)
{
var statement = CreateStatement(tableName);
statement = AddColumnReferenceExpression(statement, records);
statement.InsertSpecification.InsertOption = InsertOption.Into;
var listParams = new List <DbCommandParameter.DbCommandParameter>(parameter ?? new DbCommandParameter.DbCommandParameter[] { });
var numberParams = 0;
statement.InsertSpecification.InsertSource = new ValuesInsertSource();
IList <ParseError> parseErrors;
foreach (DataRow row in records.Rows)
{
var rv = new RowValue();
foreach (DataColumn column in records.Columns)
{
var cell = row[column];
if (cell is string name)
{
var exp = new TSql100Parser(false).ParseExpression(
new System.IO.StringReader(name), out parseErrors);
if (exp.GetType() == typeof(VariableReference) || exp.GetType() == typeof(GlobalVariableExpression))
{
rv.ColumnValues.Add(new VariableReference()
{
Name = name
});
continue;
}
}
while (listParams.Any(_ => _.Name == $"p{numberParams}"))
{
numberParams++;
}
rv.ColumnValues.Add(new VariableReference()
{
Name = $"@p{numberParams}"
});
listParams.Add(
new DbCommandParameter.DbCommandParameter(
$"p{numberParams}",
DbType.Object,
cell));
numberParams++;
}
((ValuesInsertSource)statement.InsertSpecification.InsertSource).RowValues.Add(rv);
}
var generator = new Sql100ScriptGenerator();
generator.GenerateScript(statement, out var query, out parseErrors);
return(query, listParams, parseErrors);
}
private static SqlScriptGenerator GetGenerator(GenerationOptions options)
{
SqlScriptGenerator generator = null;
switch (options.SqlVersion)
{
case SqlVersion.Sql80:
generator = new Sql80ScriptGenerator();
break;
case SqlVersion.Sql100:
generator = new Sql100ScriptGenerator();
break;
case SqlVersion.Sql110:
generator = new Sql110ScriptGenerator();
break;
case SqlVersion.Sql120:
generator = new Sql120ScriptGenerator();
break;
case SqlVersion.Sql130:
generator = new Sql130ScriptGenerator();
break;
case SqlVersion.Sql140:
generator = new Sql140ScriptGenerator();
break;
case SqlVersion.Sql150:
generator = new Sql150ScriptGenerator();
break;
default:
generator = new Sql90ScriptGenerator();
break;
}
generator.Options.Apply(options);
return(generator);
}
/// <summary>
/// Simplifies a query by removing the value of certain types of token. Basically the ones that might be parameterised
/// </summary>
/// <param name="query"></param>
/// <param name="value"></param>
/// <returns></returns>
public static bool TrySimplify(string query, out string value)
{
value = "";
var parser = new TSql100Parser(true);
IList<ParseError> errors;
var result = parser.Parse(new StringReader(query), out errors);
// cannot parse, cannot simplify
if (errors.Count > 0)
{
LastError = "Cannot parse";
return false;
}
// without at least one batch with at least one statement, cannot simplify
// (should be 1 batch, 1 statement really as we are tracing StatementEnd)
var script = result as TSqlScript;
if (script == null || script.Batches.Count <= 0 && script.Batches[0].Statements.Count <= 0)
{
LastError = "Not 1 batch 1 statement";
return false;
}
// only interested in certain types of statements (date manipulation ones)
if (!HandledStatementType.Contains(script.Batches[0].Statements[0].GetType()))
{
LastError = "Not handled statement";
return false;
}
// basically remove all comments, newlines and extra whitespace
var options = new SqlScriptGeneratorOptions
{
AlignClauseBodies = false,
AlignColumnDefinitionFields = false,
AlignSetClauseItem = false,
AsKeywordOnOwnLine = false,
IncludeSemicolons = false,
IndentSetClause = false,
IndentViewBody = false,
NewLineBeforeCloseParenthesisInMultilineList = false,
NewLineBeforeFromClause = false,
NewLineBeforeGroupByClause = false,
NewLineBeforeHavingClause = false,
NewLineBeforeJoinClause = false,
NewLineBeforeOpenParenthesisInMultilineList = false,
NewLineBeforeOrderByClause = false,
NewLineBeforeOutputClause = false,
NewLineBeforeWhereClause = false,
MultilineInsertSourcesList = false,
MultilineInsertTargetsList = false,
MultilineSelectElementsList = false,
MultilineSetClauseItems = false,
MultilineViewColumnsList = false,
MultilineWherePredicatesList = false
};
var generator = new Sql100ScriptGenerator(options);
var tokens = generator.GenerateTokens(script);
var summary = new StringBuilder();
foreach (var token in tokens)
{
// replace values for parameterisable token types
if(ReplaceTokens.Contains(token.TokenType))
{
summary.Append("?");
}
else
{
summary.Append(token.Text);
}
}
// trim some junk
value = summary.ToString().TrimEnd(RemoveChars);
return true;
}
private void SQLScripter100(SqlScriptGeneratorOptions options, bool quotedIdentifier, String inputScript)
{
Sql100ScriptGenerator scripter = new Sql100ScriptGenerator(options);
Generate(scripter, quotedIdentifier, inputScript);
}
private void Generate(Sql100ScriptGenerator scripter, bool quotedIdentifier, String inputScript)
{
SQLParser parser = new SQLParser(SqlVersion.Sql100, quotedIdentifier, inputScript);
scripter.GenerateScript(parser.Fragment, out script);
}
private void Generate(Sql100ScriptGenerator scripter, bool quotedIdentifier, String inputScript)
{
SQLParser parser = new SQLParser(SqlVersion.Sql100, quotedIdentifier, inputScript);
scripter.GenerateScript(parser.Fragment, out script);
}
private void SQLScripter100(SqlScriptGeneratorOptions options, bool quotedIdentifier, String inputScript)
{
Sql100ScriptGenerator scripter = new Sql100ScriptGenerator(options);
Generate(scripter, quotedIdentifier, inputScript);
}