GO语句破坏了.NET中的sql执行
本文关键字:中的 sql 执行 NET 坏了 语句 GO | 更新日期: 2023-09-27 18:29:59
我有一个非常简单的C#命令shell应用程序,它执行sql Server生成的sql脚本,用于编写模式和数据的脚本。它在"GO"声明上爆炸了。错误消息:
"GO"附近的语法不正确。
以下是完整的sql脚本:
/****** Object: Table [gym].[MembershipStatus] Script Date: 9/3/2013 9:24:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [gym].[MembershipStatus](
[MembershipStatusID] [tinyint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](75) NOT NULL,
[Description] [varchar](400) NOT NULL,
[AllowCheckin] [bit] NOT NULL,
[IncludeInCollections] [bit] NOT NULL,
[ScheduleFutureInvoices] [bit] NOT NULL,
CONSTRAINT [MembershipStatus_PK] PRIMARY KEY CLUSTERED
(
[MembershipStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [gym].[MembershipStatus] ON
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (1, N'Active', N'Active', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (2, N'Cancelled', N'Cancelled', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (3, N'Collection', N'Collection', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (4, N'Deleted', N'Deleted', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (5, N'Expired', N'Expired', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (6, N'Freeze', N'Freeze', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (7, N'Inactive', N'Inactive', 0, 1, 1)
SET IDENTITY_INSERT [gym].[MembershipStatus] OFF
ALTER TABLE [gym].[MembershipStatus] ADD DEFAULT ('') FOR [Name]
GO
ALTER TABLE [gym].[MembershipStatus] ADD DEFAULT ('') FOR [Description]
GO
ALTER TABLE [gym].[MembershipStatus] ADD DEFAULT ((0)) FOR [AllowCheckin]
GO
ALTER TABLE [gym].[MembershipStatus] ADD DEFAULT ((0)) FOR [IncludeInCollections]
GO
ALTER TABLE [gym].[MembershipStatus] ADD DEFAULT ((0)) FOR [ScheduleFutureInvoices]
GO
我的代码的相关部分如下:
SqlCommand command = new SqlCommand(script, connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
有什么想法吗?
正如其他人所提到的,通过GO
语句分割字符串。但是要小心,您的脚本的其他部分可能包含文本"GO"
。在GO语句之前或之后也可能有空格,在GO语句之后的行中也可能有注释。任何这些在SSMS中都是有效的,所以你可能想测试一下。
这是我使用的方法:
private static IEnumerable<string> SplitSqlStatements(string sqlScript)
{
// Make line endings standard to match RegexOptions.Multiline
sqlScript = Regex.Replace(sqlScript, @"('r'n|'n'r|'n|'r)", "'n");
// Split by "GO" statements
var statements = Regex.Split(
sqlScript,
@"^['t ]*GO['t ]*'d*['t ]*(?:--.*)?$",
RegexOptions.Multiline |
RegexOptions.IgnorePatternWhitespace |
RegexOptions.IgnoreCase);
// Remove empties, trim, and return
return statements
.Where(x => !string.IsNullOrWhiteSpace(x))
.Select(x => x.Trim(' ', ''n'));
}
如果您想使用GO
,您需要参考以下dlls
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll
然后像这样执行
using (SqlConnection conn = new SqlConnection(connection))
{
Server db = new Server(new ServerConnection(conn));
string script = File.ReadAllText(scriptPath);
db.ConnectionContext.ExecuteNonQuery(script);
}
GO
不是SQL的一部分,它是SQL Server Management Studio为您拆分脚本所做的。
您需要做的是将查询读取到字符串中,然后在GO
上单独拆分一行(您可能需要使用Regex)
//Its better to dispose the SqlCommand, I also switched constructors so I could re-use the SqlCommand.
using(SqlCommand command = new SqlCommand())
{
command.Connection = connection;
var scripts = Regex.Split(script, @"^'w+GO$", RegexOptions.Multiline);
foreach(var splitScript in scripts)
{
command.CommandText = splitScript;
command.ExecuteNonQuery();
}
}
看看Matt Johnson对GO
分裂的一个不那么天真的实现的回答。
GO不是一个有效的QA命令,它是一个批处理分隔符。。。它由Enterprise Manager处理以分离SQL脚本。因此,它将在Enterprise Manager中工作,但不能在来自C#或其他外部程序的数据库调用中工作。。。。
这个答案很有帮助,但最终对我不起作用。我有一个.NET Framework 6.0项目。那些dll不起作用。
要使此代码工作:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
Server db = new Server(new ServerConnection(conn));
db.ConnectionContext.ExecuteNonQuery(sql);
我需要使用NuGet(161.47021.0
)安装Microsoft.SqlServer.SqlManagementObjects
然后,使用GO
执行SQL时没有出现错误
如另一个答案中所述,不支持GO
。
您可以在脚本中使用String.Split()
,使用GO
语句作为分隔符,并将每个段作为命令单独运行。
作为对脚本进行处理以使其能够通过C#运行的替代方案,您可以使用sqlcmd
实用程序按原样运行它们。详细信息请访问:
http://technet.microsoft.com/en-us/library/ms180944.aspx
通过使用sqlcmd,您可以编写任意数量的SQL Server生成脚本的执行脚本,而无需剥离Go
语句。
string[] commands = sql.Split(
new string[]{"GO'r'n", "GO ", "GO't"}, StringSplitOptions.RemoveEmptyEntries );
foreach (string c in commands)
{
command = new SqlCommand(c, masterConnection);
command.ExecuteNonQuery();
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
masterConnection.Close();
}
}
在这里找到。http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx
最上面的答案有一个错误。我刚刚测试了一个有效的解决方案:您应该留出空间,";"或GO 前的新线
var scripts = Regex.Split(statementText, @"('s+|;|'n|'r)GO", RegexOptions.Multiline);
foreach(var splitScript in scripts.Where(splitScript => !splitScript.IsNullOrWhiteSpace())) {
cmd.CommandText = splitScript;
cmd.ExecuteNonQuery();
}
对于"iamkrillin"的答案,还有一点是额外的,即使用旧的DLL使其工作。
在添加对这些DLL的引用之后
Microsoft.SqlServer.ConnectionInfo.dll,Microsoft.SqlServer.Management.Sdk.Sfc.dllMicrosoft.SqlServer.Smo.dll,Microsoft.SqlServer.SqlEnum.dll
来自这样的位置:"C:''Program Files(x86)''Microsoft SQL Server''130''SDK''Assembly''Microsoft.SqlServer.ConnectionInfo.dll"对于该项目,我需要在代码文件的顶部添加以下"using"指令:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
....
string query = @" //sql multi-command text here"
using (SqlConnection thisconn = new SqlConnection(connectionString)) {
Server db = new Server(new ServerConnection(thisconn));
db.ConnectionContext.ExecuteNonQuery(query);
}
FluentMigrator库中有一个非常好的SqlServerBatchParser类
支持GO
GO 3
语句。
您需要安装FluentMigrator.Runner.SqlServernuget包
示例大量复制(略有修改)自FluentMigrator源代码:
using FluentMigrator.Runner.BatchParser;
using FluentMigrator.Runner.BatchParser.SpecialTokenSearchers;
using FluentMigrator.Runner.BatchParser.Sources;
void Main()
{
var connString = "Server=.;Database=mydb;Trusted_Connection=True;";
var sql = @"select 1;
GO
SELECT 2;
GO 5";
ExecuteBatchNonQuery(connString, sql);
}
public static void ExecuteBatchNonQuery(string ConnectionString, string sql)
{
var sqlBatch = string.Empty;
var conn = new SqlConnection(ConnectionString);
conn.Open();
try
{
var parser = new SqlServerBatchParser();
parser.SqlText += (sender, args) => { sqlBatch = args.SqlText.Trim(); };
parser.SpecialToken += (sender, args) =>
{
if (string.IsNullOrEmpty(sqlBatch))
return;
if (args.Opaque is GoSearcher.GoSearcherParameters goParams)
{
using (var command = conn.CreateCommand())
{
command.CommandText = sqlBatch;
for (var i = 0; i != goParams.Count; ++i)
{
command.ExecuteNonQuery();
}
}
}
sqlBatch = null;
};
using (var source = new TextReaderSource(new StringReader(sql), true))
{
parser.Process(source, stripComments: true);
}
if (!string.IsNullOrEmpty(sqlBatch))
{
using (var command = conn.CreateCommand())
{
command.CommandText = sqlBatch;
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
using (var message = new StringWriter())
{
message.WriteLine("An error occured executing the following sql:");
message.WriteLine(string.IsNullOrEmpty(sqlBatch) ? sql : sqlBatch);
message.WriteLine("The error was {0}", ex.Message);
throw new Exception(message.ToString(), ex);
}
}
finally
{
conn?.Dispose();
}
}
学分:https://github.com/fluentmigrator/fluentmigrator/blob/v3.2.1/src/FluentMigrator.Runner.SqlServer/Processors/SqlServer/SqlServerProcessor.cs
只需替换"GO";用"它是有效的。
SqlCommand command = new SqlCommand(script.Replace("GO", ""), connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
我在C#
中开始编码时就遇到了这个问题我使用并测试了所有可能的答案,但没有找到我喜欢的答案。最后,我找到了一个不用任何regex
或第三方框架的好方法。这里的许多人都有看似合理的答案,但没有一个对我有效,以下是我的做法:
string FileUploaded = File.ReadAllText(filePath);
string[] fileSplited = FileUploaded.Split(new string[] { "GO" }, StringSplitOptions.None);
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
foreach (string str in fileSplited)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
}
yo还可以使用try-catch
块来帮助您更好地调试。正如你所看到的,这与上面的所有答案非常相似,但唯一的区别是StringSplitOptions.None
选项,我认为没有人提到
希望它能帮助
我知道这个问题有点老了,但我想我应该添加另一种方法,通过使用System.Diagnostics.Process
的sqlcmd
实用程序来实现这一点。
public void ExecuteSQLFromFile(string filePath, string sqlInstance)
{
if(File.Exists(filePath))
{
var p = new Process();
p.StartInfo.Verb = "runas";
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = "sqlcmd";
p.StartInfo.Arguments = "-S " + sqlInstance + " -i " + filePath;
p.Start();
var standardOutput = p.StandardOutput.ReadToEnd();
var standardError = p.StandardError.ReadToEnd();
p.WaitForExit();
}
}
链接至learn.ms.com文章,包含sqlcmd
下载和使用。