使用 SQL CLR 函数克服 SQL 用户定义函数的限制
解释如何使用 SQL CLR 函数克服标准 SQL 用户定义函数执行即席 SQL 查询的限制。
引言
从包含多行的列中提取数据并将其转换为 CSV 字符串是开发人员经常执行的任务。有许多创造性的 SQL 语句可用于此任务。我最喜欢的是类似于以下内容的方法，该方法生成部门名称的 CSV 字符串。
列表 1
SELECT STUFF((SELECT ', ' + Name FROM Department
WHERE GroupName='Executive General and Administration'
FOR XML PATH('')), 1, 2, '')
FOR XML PATH(‘’) 用于将多行名称转换为单行，该行包含重复的 ‘, ‘ + Name，然后 STUFF() 函数删除多余的前导逗号和空格 ‘, ‘ 以获得常规 CSV 字符串。为了更好地利用上述 SQL 查询，SQL 用户定义函数非常方便。
列表 2
CREATE FUNCTION [dbo].[udfs_SelectCSV]
(
@GroupName NVARCHAR(50)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT STUFF((SELECT ', ' + Name FROM CLRDemo.dbo.Department
WHERE GroupName=@GroupName FOR XML PATH('')), 1, 2, '')
)
END
在复杂的 SQL 查询中使用该函数包含 CSV 列非常方便。这在我们需要为不同列和表生成 CSV 时效果很好，例如，某个类别下的书籍名称的 CSV。在这种情况下，必须创建另一个具有相关 SQL 查询的用户定义函数。如果可以将即席 SQL 查询传递到 SQL 函数中并执行，那么就可以根据需要从任何列（或表）获取 CSV，这将非常理想。但是，根据设计，SQL 函数不允许执行即席查询。
幸运的是，SQL CLR 函数可以帮助克服这一限制。
SQL CLR 函数
有大量关于使用 Visual Studio 创建 SQL CLR 函数的参考资料。此处将使用 SQL Server 2012 和 Visual Studio 2015 介绍步骤和屏幕截图。
首先，在 SSMS 中创建并命名您的 SQL Server 上的数据库为 CLRDemo，然后运行清单 3 中的 SQL 脚本来创建和填充数据表：Department，该表借自 AdventureWorks。脚本还创建了一个用户定义函数 udfs_SelectCSV。
列表 3
USE [CLRDemo]
GO
/****** Object: Table [dbo].[Department] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
[DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[GroupName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (1, N'Engineering', N'Research and Development')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (2, N'Tool Design', N'Research and Development')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (3, N'Sales', N'Sales and Marketing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (4, N'Marketing', N'Sales and Marketing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (5, N'Purchasing', N'Inventory Management')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (6, N'Research and Development', N'Research and Development')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (7, N'Production', N'Manufacturing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (8, N'Production Control', N'Manufacturing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (9, N'Human Resources', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (10, N'Finance', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (11, N'Information Services', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (12, N'Document Control', N'Quality Assurance')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (13, N'Quality Assurance', N'Quality Assurance')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (14, N'Facilities and Maintenance', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (15, N'Shipping and Receiving', N'Inventory Management')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (16, N'Executive', N'Executive General and Administration')
SET IDENTITY_INSERT [dbo].[Department] OFF
GO
CREATE FUNCTION [dbo].[udfs_SelectCSV]
(
@GroupName NVARCHAR(50)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT STUFF((SELECT ', ' + Name FROM CLRDemo.dbo.Department
WHERE GroupName=@GroupName FOR XML PATH('')), 1, 2, '')
)
END
其次，确保您的 SQL Server 已启用 CLR。
列表 4
--See if CLR is enabled
SELECT * FROM sys.configurations WHERE name = 'clr enabled'
Go
--Enable CLR on SQL Server if not enabled
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'clr enabled', 1
RECONFIGURE
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
现在可以准备在 Visual Studio 2015 中创建 SQL CLR 函数了。
1. 在 Visual Studio 2015 中创建一个 SQL Server 数据库项目，并将其命名为 SqlCLRFunction。
2. 右键单击刚刚创建的项目，然后选择“属性”。
3. 在“属性”窗口中，选择正确的 SQL Server 版本和 .NET 版本。在此演示中，它们分别是 SQL Server 2012 和 .NET Framework 4.5.2。
4. 向项目中添加一个 SQL CLR C# 用户定义函数代码文件，并将其命名为 CLRFunction.cs。
5. Visual Studio 会创建一个包含必需指令的默认函数。
6. 用清单 5 中的代码替换默认函数。公共静态方法 udfs_CLR_GetCSV_AdhocSQL 是要部署到 SQL 数据库 CLRDemo 的 CLR 函数。稍后将对代码进行审查。
列表 5
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString udfs_CLR_GetCSV_AdhocSQL(string SQL, object parameterValue)
{
/*
SQL: a adhoc sql statement including a parameter, for example,
SELECT Name from CLRDemo.dbo.Department WHERE GroupName=@GroupName
parameterValue: a parameter value. Since data type is unknown, use "object" type
*/
//handle parameter in the SQL
SQL = ModifyParameter(SQL, parameterValue);
//Modify SQL so that it returns a CSV string
SQL = ModifySQL(SQL);
//execute SQL to return a CSV string
return ExecuteSQL(SQL);
}
private static string ModifyParameter(string SQL, object parameterValue)
{
//A parameter exists in the SQL statement similar to "Where GroupName=@GroupName"
if (parameterValue != null && SQL.IndexOf("@") > -1)
{
int pos = SQL.IndexOf("@");
//Get a string from the @ position to the end of the SQL string
string ParameterName = SQL.Replace(SQL.Substring(0, pos), "");
//Get the parameter name by removing trailing characters after the parameter name
string[] temp = ParameterName.Split(' ');
ParameterName = temp[0];
//Repplace parameter name with parameter value based on sql data type
if (parameterValue.GetType().ToString().ToLower().IndexOf("sqlstring") > -1)
{
//parameter value of a sql string (char or varchar) type
SQL = SQL.Replace(ParameterName, "'" + parameterValue.ToString() + "'");
}
else
{
//parameter value of a int type
SQL = SQL.Replace(ParameterName, parameterValue.ToString());
}
}
return SQL;
}
private static string ModifySQL(string SQL)
{
/*Modify sql statement to return a CSV string
Original SQL sample:
SELECT Name FROM CLRDemo.dbo.Department WHERE GroupName=@GroupName
Modified SQL sample:
SELECT STUFF((
SELECT ', ' + Name FROM Department WHERE GroupName=@GroupName FOR XML PATH('')
), 1, 2, '')
*/
SQL = SQL.ToUpper();
SQL = SQL.Replace("SELECT ", "SELECT ', ' + ") + " FOR XML PATH('')";
SQL = "SELECT STUFF((" + SQL + "), 1, 2, '')";
return SQL;
}
private static string ExecuteSQL(string SQL)
{
string CSV = "";
//No server and database name are required in the connection.
using (SqlConnection con = new SqlConnection("Context Connection = true;"))
{
SqlCommand cmd = new SqlCommand(SQL, con);
con.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
CSV = o.ToString();
}
}
return CSV;
}
}
7. 右键单击 SqlCLRFunction 项目，然后选择“生成”。
8. “生成”完成后，再次右键单击项目并选择“发布”。
9. 在“发布”对话框中，选择您的 SQL Server 和 CLRDemo 数据库。
10. 在上面的“连接属性”对话框中单击“确定”，然后单击“发布”按钮。
11. 发布进度显示在 Visual Studio 屏幕的左下角，并以“发布成功完成”结束。
12. 在 SSMS 中转到 CLRDemo 数据库，查看已发布的 SQL CLR 函数 udfs_CLR_GetCSV_AdhocSQL。udfs_SelectCSV 是先前通过脚本创建的常规 SQL 用户定义函数。
13. 使用清单 6 中的脚本在 SSMS 中进行测试。CLR 函数和常规 SQL 函数返回相同的结果。
列表 6
--CLR Function
SELECT [CLRDemo].[dbo].[udfs_CLR_GetCSV_AdhocSQL](
'SELECT Name from CLRDemo.dbo.Department WHERE GroupName=@GroupName',
'Executive General and Administration') AS Departments
--Regular SQL Function
SELECT [CLRDemo].[dbo].[udfs_SelectCSV]('Executive General and Administration')
CLR 函数代码详细信息
SQL CLR 函数的完整 C# 代码在上面的清单 5 中提供，其中公共静态方法：udfs_GetCSV_by_AdhocSQL(SQL, parameterValue) 是已发布到 CLRDemo 数据库的 CLR 函数。该方法基本上执行三个私有静态方法，这些方法处理即席 SQL 查询并在执行时返回 CSV 字符串。下面我们来看看私有方法：ModifyParameter()、ModifySQL() 和 ExecuteSQL()。
ModifyParameter()
private static string ModifyParameter(string SQL, object parameterValue)
该方法接受两个参数：SQL 和 parameterValue。SQL 是清单 6 中的即席 SQL 查询，如下所示：
SELECT Name from CLRDemo.dbo.Department WHERE GroupName = @GroupName
该查询仅检索一组部门名称的列表。它包含一个名为 @GroupName 的 SQL 参数。parameterValue 是 @GroupName 的实际值。在我们的示例中，值为“Executive General and Administration”。请注意 parameterValue 的数据类型为 object。这是因为传递的值的数据类型在运行时才知道。该方法首先检测 parameterValue 的数据类型（int 或 varchar），然后根据 SQL 语法执行相应的字符串连接。在我们的示例中，它将 @GroupName 替换为“Executive General and Administration”的值，并返回一个完整的 SQL 语句，例如：
SELECT Name from CLRDemo.dbo.Department WHERE GroupName= 'Executive General and Administration'
ModifySQL()
private static string ModifySQL(string SQL)
在参数值与即席 SQL 查询合并后，此方法会将即席查询转换为生成 CSV 字符串的查询。即：
从：
SELECT Name from CLRDemo.dbo.Department WHERE GroupName = 'Executive General and Administration'
改为
SELECT STUFF((SELECT ', ' + Name FROM Department
WHERE GroupName='Executive General and Administration'
FOR XML PATH('')), 1, 2, '')
ExecuteSQL()
private static string ExecuteSQL(string SQL)
最后，此方法使用 ADO.NET 执行通过前面两个方法准备好的 SQL 查询，并返回一个文字 CSV 字符串。由于此处使用了 C# 和 ADO.NET，因此可以毫无问题地执行即席 SQL 查询。这就是克服标准 SQL 用户定义函数限制的方法。由于已发布的 CLR 函数驻留在 CLRDemo 数据库内部，因此需要特殊的连接字符串“Context Connection = true;”。但是，在其他 ADO.NET 应用程序中经常看到的带有服务器名称、数据库名称和数据库凭据的“常规”连接字符串则不需要。
SqlConnection con = new SqlConnection("Context Connection = true;");
关注点
本文解释了如何使用 SQL CLR 函数克服标准 SQL 用户定义函数执行即席 SQL 查询的限制。在演示中，为了说明概念，我们涉及了一个带有单个参数的简单 SQL 查询。可能需要进行额外的研究来扩展其功能，例如处理 SQL 关键字，如“TOP”和“DISTINCT”，以及处理多个 SQL 参数等。此外，可以传入一个不同的分隔符，如 | 或 *，而不是 CSV，到 CLR 函数中以创建自定义分隔的字符串。虽然 C# 是一种强大的编程语言，但在 SQL CLR 方面，某些功能可能不适用。例如，已成功测试了方法重载。