在C＃中安装SQL对象

在C＃中安装SQL对象 - 在安装CLR程序集和函数脚本时出现问题(Installing SQL objects in C# - issue when installing CLR assembly and function scripts)

我编写了一些C＃来运行指定的SQL脚本，在GO批处理分隔符中分离命令并执行每个命令。 这样就可以在应用程序的开头安装SQL对象。 但是，我遇到安装CLR程序集和函数的问题。

它将运行并创建CLR程序集OK，但是当它尝试创建函数时它返回错误：

关键字“FUNCTION”附近的语法不正确。 关键字“AS”附近的语法不正确。

我已经尝试在SSMS中直接使用脚本创建程序集和函数，运行正常。 在SSMS中创建程序集之后，我还在C＃中运行了函数脚本，运行正常。 如果两个脚本都通过C＃运行，它似乎似乎没有创建函数。

用于执行SQL脚本的C＃代码：

var fileInfo = new FileInfo(sqlDirectory + "\\" + fileName); var script = fileInfo.OpenText().ReadToEnd(); if (!String.IsNullOrEmpty(script)) { string sqlBatch = string.Empty; script += "\nGO"; try { foreach ( string line in script.Split(new string[2] {"\n", "\r"}, StringSplitOptions.RemoveEmptyEntries)) { if (line.ToUpperInvariant().Trim() == "GO") { if (sqlBatch != "") { cmd.CommandType = CommandType.Text; cmd.CommandText = sqlBatch; cmd.ExecuteNonQuery(); sqlBatch = string.Empty; } } else { sqlBatch += line + "\n"; } } fileInfo.OpenText().Close(); } catch (Exception err) { var context = HttpContext.Current; Global.InstallError = "File Name: " + fileName + "<br/>" + "Error: " + err.Message; } }

CLR功能：

/****** Object: UserDefinedFunction [dbo].[fn_JSON_FormatArray] Script Date: 03/07/2013 11:52:18 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE FUNCTION [dbo].[fn_JSON_FormatArray](@value [xml], @label [nvarchar](4000)) RETURNS [xml] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExtensions].[UserDefinedFunctions].[fn_JSON_FormatArray] GO EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'JSON\fn_JSON_FormatArray.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_JSON_FormatArray' GO EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'11' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_JSON_FormatArray' GO

I have written some C# to run through specified SQL scripts, separate the commands at the GO batch separator and execute each command. This is so the SQL objects can be installed at the start of the application. However, I am having an issue with installing CLR assemblies and functions.

It will run through and create the CLR assembly OK, but when it then tried to create the function it is returning the error:

Incorrect syntax near the keyword 'FUNCTION'. Incorrect syntax near the keyword 'AS'.

I have tried creating the assembly and function using the script directly in SSMS and that runs fine. I have also run the function script in my C# after creating the assembly in SSMS and this runs fine. It just doesn't seem to create the function if both scripts are run through C#.

C# Code to execute the SQL script:

var fileInfo = new FileInfo(sqlDirectory + "\\" + fileName); var script = fileInfo.OpenText().ReadToEnd(); if (!String.IsNullOrEmpty(script)) { string sqlBatch = string.Empty; script += "\nGO"; try { foreach ( string line in script.Split(new string[2] {"\n", "\r"}, StringSplitOptions.RemoveEmptyEntries)) { if (line.ToUpperInvariant().Trim() == "GO") { if (sqlBatch != "") { cmd.CommandType = CommandType.Text; cmd.CommandText = sqlBatch; cmd.ExecuteNonQuery(); sqlBatch = string.Empty; } } else { sqlBatch += line + "\n"; } } fileInfo.OpenText().Close(); } catch (Exception err) { var context = HttpContext.Current; Global.InstallError = "File Name: " + fileName + "<br/>" + "Error: " + err.Message; } }

CLR Function:

/****** Object: UserDefinedFunction [dbo].[fn_JSON_FormatArray] Script Date: 03/07/2013 11:52:18 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE FUNCTION [dbo].[fn_JSON_FormatArray](@value [xml], @label [nvarchar](4000)) RETURNS [xml] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExtensions].[UserDefinedFunctions].[fn_JSON_FormatArray] GO EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'JSON\fn_JSON_FormatArray.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_JSON_FormatArray' GO EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'11' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_JSON_FormatArray' GO

最满意答案

我现在已经解决了这个问题。 问题结果是我以后使用相同的SQLCommand执行存储过程（因为我给命令一个SQLTransaction所以我可以回滚，如果它失败）并且忘记删除我运行时添加的参数下一个文本命令。 因此，它正在运行第一个脚本文件，之后失败。

I have now fixed this problem. The issue turned out to be that I was using the same SQLCommand later on to execute a stored procedure (as I have given the command a SQLTransaction so I can rollback if it fails) and had forgotten to remove the parameters that were added when I run the next text command. Therefore it was running the first script file and failing afterwards.