在C#中执行包含GO语句的SQL批处理
本文关键字:语句 SQL 批处理 GO 包含 执行 | 更新日期: 2023-09-27 18:19:52
我正在尝试构建一个程序,该程序可以批量执行sql语句并进行错误处理(因此我不使用SMO)。
问题是GO不是SQL的一部分,当使用.NET执行语句时,它最终会出现错误(SMO会处理它,但不会给出执行是否失败的任何指示)。
string statements = File.ReadAllText("c:''test.sql");
string[] splitted = statements.split("GO");
使用以上几行并不能解决我的问题,因为GO关键字也可以出现在注释中(我不想从语句中删除注释),注释也可以出现/**/或在两个破折号之后--
例如,我不希望解析以下代码:
/*
GO
*/
(我在谷歌上搜索了一下,但那里没有解决方案)
ScriptDom
最简单的解决方案(也是最健壮的)是使用T-SQL解析器。好消息是,你不必写它,只需添加参考:
Microsoft.Data.Schema.ScriptDom
Microsoft.Data.Schema.ScriptDom.Sql
然后使用代码:
static void Main(string[] args)
{
string sql = @"
/*
GO
*/
SELECT * FROM [table]
GO
SELECT * FROM [table]
SELECT * FROM [table]
GO
SELECT * FROM [table]";
string[] errors;
var scriptFragment = Parse(sql, SqlVersion.Sql100, true, out errors);
if (errors != null)
{
foreach (string error in errors)
{
Console.WriteLine(error);
return;
}
}
TSqlScript tsqlScriptFragment = scriptFragment as TSqlScript;
if (tsqlScriptFragment == null)
return;
var options = new SqlScriptGeneratorOptions { SqlVersion = SqlVersion.Sql100, KeywordCasing = KeywordCasing.PascalCase };
foreach (TSqlBatch batch in tsqlScriptFragment.Batches)
{
Console.WriteLine("--");
string batchText = ToScript(batch, options);
Console.WriteLine(batchText);
}
}
public static TSqlParser GetParser(SqlVersion level, bool quotedIdentifiers)
{
switch (level)
{
case SqlVersion.Sql80:
return new TSql80Parser(quotedIdentifiers);
case SqlVersion.Sql90:
return new TSql90Parser(quotedIdentifiers);
case SqlVersion.Sql100:
return new TSql100Parser(quotedIdentifiers);
case SqlVersion.SqlAzure:
return new TSqlAzureParser(quotedIdentifiers);
default:
throw new ArgumentOutOfRangeException("level");
}
}
public static IScriptFragment Parse(string sql, SqlVersion level, bool quotedIndentifiers, out string[] errors)
{
errors = null;
if (string.IsNullOrWhiteSpace(sql)) return null;
sql = sql.Trim();
IScriptFragment scriptFragment;
IList<ParseError> errorlist;
using (var sr = new StringReader(sql))
{
scriptFragment = GetParser(level, quotedIndentifiers).Parse(sr, out errorlist);
}
if (errorlist != null && errorlist.Count > 0)
{
errors = errorlist.Select(e => string.Format("Column {0}, Identifier {1}, Line {2}, Offset {3}",
e.Column, e.Identifier, e.Line, e.Offset) +
Environment.NewLine + e.Message).ToArray();
return null;
}
return scriptFragment;
}
public static SqlScriptGenerator GetScripter(SqlScriptGeneratorOptions options)
{
if (options == null) return null;
SqlScriptGenerator generator;
switch (options.SqlVersion)
{
case SqlVersion.Sql80:
generator = new Sql80ScriptGenerator(options);
break;
case SqlVersion.Sql90:
generator = new Sql90ScriptGenerator(options);
break;
case SqlVersion.Sql100:
generator = new Sql100ScriptGenerator(options);
break;
case SqlVersion.SqlAzure:
generator = new SqlAzureScriptGenerator(options);
break;
default:
throw new ArgumentOutOfRangeException();
}
return generator;
}
public static string ToScript(IScriptFragment scriptFragment, SqlScriptGeneratorOptions options)
{
var scripter = GetScripter(options);
if (scripter == null) return string.Empty;
string script;
scripter.GenerateScript(scriptFragment, out script);
return script;
}
SQL Server管理对象
添加引用到:
Microsoft.SqlServer.Smo
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
然后你可以使用这个代码:
using (SqlConnection connection = new SqlConnection("Server=(local);Database=Sample;Trusted_Connection=True;"))
{
ServerConnection svrConnection = new ServerConnection(connection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(script);
}
CodeFluent运行时
CodeFluent运行时数据库有一个小型sql文件解析器。它不处理复杂的情况,但例如支持注释。
using (StatementReader statementReader = new CodeFluent.Runtime.Database.Management.StatementReader("GO", Environment.NewLine, inputStream))
{
Statement statement;
while ((statement = statementReader.Read(StatementReaderOptions.Default)) != null)
{
Console.WriteLine("-- ");
Console.WriteLine(statement.Command);
}
}
或者更简单的
new CodeFluent.Runtime.Database.Management.SqlServer.Database("connection string")
.RunScript("path", StatementReaderOptions.Default);
ScriptDom(2023年更新)
@meziantou的答案的编辑队列已满(500条建议的大小似乎没有任何拥有编辑权限的人会通过它),所以我将这个更新后的答案添加为社区wiki。ScriptDom现在住在nuget,github
ScriptDom是一个支持批处理文件的T-SQL解析器。只需添加对nuget包Microsoft.SqlServer.Transactionsql.ScriptDom的引用即可。示例:
using Microsoft.SqlServer.TransactSql.ScriptDom;
namespace ScriptDomTest;
internal class Program
{
static void Main(string[] args)
{
string sql = @"
/*
GO
*/
SELECT * FROM [table]
GO
SELECT * FROM [table]
SELECT * FROM [table]
GO
SELECT * FROM [table]";
string[]? errors;
var scriptFragment = Parse(sql, SqlVersion.Sql100, true, out errors);
if (errors != null)
{
foreach (string error in errors)
{
Console.WriteLine(error);
return;
}
}
TSqlScript? tsqlScriptFragment = scriptFragment as TSqlScript;
if (tsqlScriptFragment == null)
return;
var options = new SqlScriptGeneratorOptions { SqlVersion = SqlVersion.Sql100, KeywordCasing = KeywordCasing.PascalCase };
foreach (TSqlBatch batch in tsqlScriptFragment.Batches)
{
Console.WriteLine("--");
string batchText = ToScript(batch, options);
Console.WriteLine(batchText);
}
}
public static TSqlParser GetParser(SqlVersion level, bool quotedIdentifiers)
{
return level switch
{
SqlVersion.Sql80 => new TSql80Parser(quotedIdentifiers),
SqlVersion.Sql90 => new TSql90Parser(quotedIdentifiers),
SqlVersion.Sql100 => new TSql100Parser(quotedIdentifiers),
SqlVersion.Sql110 => new TSql110Parser(quotedIdentifiers),
SqlVersion.Sql120 => new TSql120Parser(quotedIdentifiers),
SqlVersion.Sql130 => new TSql130Parser(quotedIdentifiers),
SqlVersion.Sql140 => new TSql140Parser(quotedIdentifiers),
SqlVersion.Sql150 => new TSql150Parser(quotedIdentifiers),
SqlVersion.Sql160 => new TSql160Parser(quotedIdentifiers),
_ => throw new ArgumentOutOfRangeException(nameof(level)),
};
}
public static TSqlFragment? Parse(string sql, SqlVersion level, bool quotedIndentifiers, out string[]? errors)
{
errors = null;
if (string.IsNullOrWhiteSpace(sql)) return null;
sql = sql.Trim();
TSqlFragment scriptFragment;
IList<ParseError> errorlist;
using (var sr = new StringReader(sql))
{
scriptFragment = GetParser(level, quotedIndentifiers).Parse(sr, out errorlist);
}
if (errorlist != null && errorlist.Count > 0)
{
errors = errorlist.Select(e => string.Format("Column {0}, Number {1}, Line {2}, Offset {3}",
e.Column, e.Number, e.Line, e.Offset) +
Environment.NewLine + e.Message).ToArray();
return null;
}
return scriptFragment;
}
public static SqlScriptGenerator? GetScripter(SqlScriptGeneratorOptions options)
{
if (options == null) return null;
return options.SqlVersion switch
{
SqlVersion.Sql80 => new Sql80ScriptGenerator(options),
SqlVersion.Sql90 => new Sql90ScriptGenerator(options),
SqlVersion.Sql100 => new Sql100ScriptGenerator(options),
SqlVersion.Sql110 => new Sql110ScriptGenerator(options),
SqlVersion.Sql120 => new Sql120ScriptGenerator(options),
SqlVersion.Sql130 => new Sql130ScriptGenerator(options),
SqlVersion.Sql140 => new Sql140ScriptGenerator(options),
SqlVersion.Sql150 => new Sql150ScriptGenerator(options),
SqlVersion.Sql160 => new Sql160ScriptGenerator(options),
_ => throw new ArgumentOutOfRangeException(nameof(options)),
};
}
public static string ToScript(TSqlFragment scriptFragment, SqlScriptGeneratorOptions options)
{
var scripter = GetScripter(options);
if (scripter == null) return string.Empty;
string script;
scripter.GenerateScript(scriptFragment, out script);
return script;
}
}
是的,go是SSMS必须允许您分解的东西。正如您所提到的,它不是sql的一部分。SSMS使用SMO来完成它的工作,所以这就是它在那里工作的原因。
正如你的评论所表明的,但问题很模糊,你需要在处理之前删除所有评论块。如果您不想这样做,您需要将文件作为流处理,并在/*
处开始忽略,在*/
处停止。。。可能还有CCD_ 8和CCD_ 9
您也可以使用正则表达式将其拆分(如果您将其作为文本blob读取,但尚未按行拆分):
var text = File.ReadAllText("file.txt")
var cleanedText = Regex.Replace(text, @"/'*.*'*/", "", RegexOptions.Singleline)
var parts = Regex.Split(cleanedText, @"^'s*GO.*$", RegexOptions.Multiline);
for(var part in parts) {
executeBatch(part);
}
// but this is getting ugly
var str = "what /*'n the 'n'n GO 'n*/heck'nGO'nand then";
var cleanedText = Regex.Replace(str, @"/'*.*'*/", "'n", RegexOptions.Singleline)
var split = Regex.Split(cleanedText, @"^'s*GO.*$", RegexOptions.Multiline);
// == ["what'nheck", "'nand then"]
是的,正如评论所说,真正的答案是编写一个解析器。即使有了关于注释的内容,您仍然可以将/*
和*/
嵌入到insert
内部的STRING中。所以…
只有当"GO"站在一条孤独的线上或有空白时才拆分,如下所示:
Regex.Split(statements, @"^'s+GO's+$");