I was solving the task to execute a script, generated by SqlPackage tool programmatically, to set up the database for integration tests.
After thorough investigation, I still have not found any standard .NET library to parse or execute SQLCMD mode queries.
So I have considered the following options:
- Call
sqlcmd.exe command line tool.
- Call
Invoke-SqlCmd PowerShell command.
- Use one of the third-party libraries to run SQLCMD mode script.
None of the options were satisfactory for me after deeper consideration and experimenting.
On the other hand, I have noticed that probably the support of all language features of SQLCMD language is not required for me. More specifically, I only need variables substitution and ignoring some directives. So I have decided to implement my own limited SQLCMD mode parser based on regular expressions:
internal static class SqlCmdScriptParser
{
private static readonly Command[] ControlCommands =
{
new()
{
Id = "setvar",
Pattern = new(@":(?<command>setvar)\s+(?<name>\w+)\s+""(?<value>.*)"""),
IsFullLine = true,
MatchSubstitution =
(match, variables) =>
{
variables.Add(match.Groups["name"].Value, match.Groups["value"].Value);
return string.Empty;
},
},
new()
{
Id = "on error",
Pattern = new(@":(?<command>on error)\s+(?<value>exit|ignore)"),
IsFullLine = true,
MatchSubstitution = (_, _) => string.Empty,
},
new()
{
Id = "$",
Pattern = new(@"(?<command>\$)\((?<name>\w+)\)"),
IsFullLine = false,
MatchSubstitution =
(match, variables) =>
variables.GetValueOrDefault(match.Groups["name"].Value) ?? string.Empty,
},
};
private static readonly IReadOnlyDictionary<string, Command> ControlCommandsMap =
ControlCommands.ToDictionary(c => c.Id, StringComparer.OrdinalIgnoreCase);
private static readonly Regex ControlCommandsPattern = GetControlCommandsPattern();
private static readonly Regex BatchSeparatorPattern = new Regex("GO").ToFullLine();
public static IReadOnlyCollection<string> Parse(
string input, IReadOnlyDictionary<string, string>? variables = null) =>
input
.SubstituteControlCommands(variables)
.SplitBatch();
private static Regex GetControlCommandsPattern()
{
var patterns = ControlCommands
.Select(c => c.IsFullLine ? c.Pattern.ToFullLine() : c.Pattern)
.Select(p => $"({p})")
.ToList();
var combinedPattern = string.Join("|", patterns);
return new Regex(combinedPattern, RegexOptions.Multiline | RegexOptions.Compiled);
}
private static Regex ToFullLine(this Regex source) =>
new($@"^\s*{source}\s*$\n?", RegexOptions.Multiline | RegexOptions.Compiled);
private static string SubstituteControlCommands(
this string input, IReadOnlyDictionary<string, string>? variables)
{
var establishedVariables = new Dictionary<string, string>(
variables ?? new Dictionary<string, string>(), StringComparer.OrdinalIgnoreCase);
return ControlCommandsPattern
.Replace(input, match => SubstituteControlCommandMatch(match, establishedVariables));
}
private static string SubstituteControlCommandMatch(
Match match, Dictionary<string, string> variables)
{
var commandId = match.Groups["command"].Value;
var command = ControlCommandsMap.GetValueOrDefault(commandId)
?? throw new InvalidOperationException($"Unknown command: {commandId}");
return command.MatchSubstitution(match, variables);
}
private static IReadOnlyCollection<string> SplitBatch(this string input) =>
BatchSeparatorPattern.Split(input)
.Where(s => !string.IsNullOrEmpty(s))
.ToList();
private sealed class Command
{
public string Id { get; init; } = string.Empty;
public Regex Pattern { get; init; } = new(string.Empty);
public bool IsFullLine { get; init; }
public Func<Match, Dictionary<string, string>, string> MatchSubstitution { get; init; } =
(_, _) => string.Empty;
}
}
Usage:
var inputScript = '-- Set your input SQLCMD mode script here';
// Set your variables here
var variables = new Dictionary<string, string>();
var batches = SqlCmdScriptParser.Parse(inputScript, variables);
As output, it generates the collection of SQL commands ready to be executed.
This implementation is currently limited, but it is easily extensible, since supported commands can be added in declarative way. In case the commands should be precisely implemented (not just ignored), some parser state can be introduced, which can result in form settings, associated with each generated SQL command. These settings can be used during the execution of generated SQL commands.
Example in .NET Fiddle
