A. 如何更新sql Server里的CLR程序集
这样大大增强了Sql Server的功能，为编程开发带来了方便。怎样创建CLR存储过程网上的介绍文章已经很多了，本文不讨论了。 作者在使用过程中遇到了这样的问题，就是程序集assembly的更新问题。在Sql Server中程序集不能修改，只能删了重建。见下图： 接下来如果想用drop命令把它删掉，就会出现这个：
由于对象 'IsLegalDate' 引用了 'ZSqlExtend'，DROP ASSEMBLY 失败。原来基于这个程序集肯定要建很多存储过程和函数，这些对象引用了这个程序集，DROP ASSEMBLY 失败。可以单击上图中“查看依赖关系”。怎么办呢？那只能把引用这个程序集的所有对象都删掉，这样一来工作量可就大了，而且重建程序集后还要建这些存储过程和函数。这些存储过程和函数也有可能被引用，也删不掉。这样我们更新程序集岂不是太麻烦了？笔者想出一个变通的办法，与广大网友交流。首先ZSqlExtend的代码如下，里面有一个IsLegalDate函数。用VS2008编译后的文件是ZSqlExtend.dll。using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text.RegularExpressions; namespace ZSqlExtend_NS{public class ZSqlExtend_cls{[SqlFunction(DataAccess = DataAccessKind.Read)]
public static bool IsLegalDate(int iyear, int imonth, int iday){bool ild = false;try{DateTime newdate = new DateTime(iyear, imonth, iday);
DateTime dMin = new DateTime(1900, 1, 1);
DateTime dMax = new DateTime(2079, 6, 7);
ild = newdate >= dMin && newdate < dMax;}catch{ild = false;}return ild;
}} //END CLASS }根据这个dll文件在Sql Server中创建程序集和函数：create assembly ZSqlExtend
from 'E:\hs1\ZSqlExtend\bin\ZSqlExtend.dll' with permission_set = Safe;
go CREATE FUNCTION IsLegalDate
(@y int,@m int,@d int)
RETURNS bit
AS EXTERNAL NAME ZSqlExtend.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDate;
go 然后再建一个类库项目，比如我再做一个ZSqlExtend_mmy的类库项目，用VS2008编译后的文件是ZSqlExtend_mmy.dll，这个是ZSqlExtend.dll的代替品。把ZSqlExtend.dll的内容在这个再写一遍，不过这个是假的，所以可以写成这样：using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text.RegularExpressions; namespace ZSqlExtend_NS{public class ZSqlExtend_cls{[SqlFunction(DataAccess = DataAccessKind.Read)]
public static bool IsLegalDate(int iyear, int imonth, int iday){return true;
} } //END CLASS }把这个“假的”ZSqlExtend_mmy.dll也注册进Sql Server。create assembly ZSqlExtend_mmy
from 'E:\hs1\ZSqlExtend_mmy\bin\ZSqlExtend_mmy.dll' with permission_set = Safe;go我们的第一步工作就做完了。现在我在项目ZSqlExtend里修改了IsLegalDate，需要重建程序集，就出现了本文一开始的情况。当然你可以先把IsLegalDate删掉，但是我不能这么做，因为很多表的计算字段和视图引用了IsLegalDate。所以我只能让IsLegalDate和程序集ZSqlExtend脱钩，然后将程序集ZSqlExtend删除重建。要想脱钩就得用那个ZSqlExtend_mmy了，因为它其中的函数签名和ZSqlExtend完全一样。函数IsLegalDate是不能删了，但我们可以修改它，把它改在别的程序集上。Sql Server中执行：alter FUNCTION IsLegalDate
(@y int,@m int,@d int)
RETURNS bit
AS EXTERNAL NAME ZSqlExtend_mmy.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDate;goOK，此时程序集ZSqlExtend已经可以删除重建了，在Sql Server中运行：drop assembly ZSqlExtend;create assembly ZSqlExtend
from 'E:\hs1\ZSqlExtend\bin\ZSqlExtend.dll' with permission_set = Safe;
go当然还要把函数IsLegalDate再改回来alter FUNCTION IsLegalDate
(@y int,@m int,@d int)
RETURNS bit
AS EXTERNAL NAME ZSqlExtend.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDate;go这里需要注意2个问题：1、也许你会问，既然要把函数IsLegalDate改掉，让它和程序集ZSqlExtend无关，何不直接用语句：ALTER FUNCTION [dbo].[IsLegalDate](@y [int], @m [int], @d [int])
RETURNS [bit]beginreturn 1;end哈哈，你会得到如下提示： 无法对 'dbo.IsLegalDate' 进行更改，因为它是不兼容的对象类型。2、为什么要再编译个DLL呢？把编译好的ZSqlExtend.dll改个名那个“假的”不就有了吗。
B. 如何在SQL Server中运用CLR调用.NET
如何在SQL Server中运用CLR调用.NET
1、在.NET中新建一个类库项目，并在这个项目中添加一个类文件，并把要被SQL Server调用的方法定义为公有的，静态的方法。
2、把这个项目编译为一个DLL。
3、在SQL Server中注册这个DLL。
4、通过使用SQL Server的函数来访问指定的.NET方法。
C. 如何：创建和运行 CLR SQL Server 用户定义的函数
部署成功后，可调用并执行用户定义的函数。注意 在默认情况下，Microsoft SQL Server 中关闭了公共语言运行库 (CLR) 集成功能。必须启用该功能才能使用 SQL Server 项目项。若要启用 CLR 集成，请使用 sp_configure 存储过程的“启用 clr”选项。有关更多信息，请参见启用 CLR 集成。注意显示的对话框和菜单命令可能会与帮助中的描述不同，具体取决于您现用的设置或版本。若要更改设置，请在“工具”菜单上选择“导入和导出设置”。有关更多信息，请参见 Visual Studio 设置。 创建用户定义的 SQL Server 函数 使用Visual Studio 建立和部署此函数 C++ 示例在编译时必须使用 /clr:safe 编译器选项。 对于Visual Basic 和 Visual C#，在“解决方案资源管理器”中，打开“TestScripts”文件夹，然后双击“Test.sql”文件，以打开它进行编辑。添加执行您的用户定义函数的代码。请参见下面的第二个示例。对于Visual C++，在“解决方案资源管理器”中双击“debug.sql”文件，以打开它进行编辑。添加执行您的用户定义函数的代码。请参见下面的第二个示例。将用户定义函数部署到 SQL Server。有关更多信息，请参见如何：将 SQL Server 项目项部署到 SQL Server 中。按F5 通过在 SQL Server 上执行用户定义的函数来对其进行调试。下面的代码示例创建用户定义的将价格作为参数的标量函数 addTax，添加销售税并返回价格和销售税的加和。创建该函数后，将其部署到 SQL Server。有关更多信息，请参见如何：将 SQL Server 项目项部署到 SQL Server 中Visual Basic 复制代码 Imports System.Data.SqlTypes Imports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions Public Const SALES_TAX As Double = 0.086 _ Public Shared Function addTax(ByVal originalAmount As SqlDouble) As SqlDouble Dim taxAmount As SqlDouble = originalAmount * SALES_TAX Return originalAmount + taxAmount End FunctionEnd ClassC# 复制代码 using System.Data.SqlTypes; using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{public const double SALES_TAX = .086; [SqlFunction()] public static SqlDouble addTax(SqlDouble originalAmount){SqlDouble taxAmount = originalAmount * SALES_TAX; return originalAmount + taxAmount;}}C++ 复制代码 #include "stdafx.h"#using #using #using using namespace System; using namespace System::Data; using namespace System::Data::Sql; using namespace System::Data::SqlTypes; using namespace Microsoft::SqlServer::Server;// In order to debug your Aggregate, add the following to your debug.sql file://// SELECT dbo.addTax(10) //public ref class UserDefinedFunctions{public:static initonly double SALES_TAX = 0.086; [SqlFunction()] static SqlDouble AddTax(SqlDouble originalAmount){SqlDouble taxAmount = originalAmount * SALES_TAX; return originalAmount + taxAmount;}};向您的项目中 TestScripts 文件夹中的 Test.sql（在 Visual C++ 中为 debug.sql）文件，添加用于测试用户定义函数的代码。例如，若要测试此函数，请使用查询，如“SELECT dbo.addTax(10)”。您应见到返回的值“10.86”。 复制代码
D. SQL clr对数据库有影响吗
没有影响，同时解决了好多问题，只有好处。
1、 SQLCLR提供编程结构使数据操作和计算更加容易。
T-SQL 专门为数据库中的直接数据访问和操作而设计。尽管 T-SQL 在数据访问和管理方面领先，但是它没有提供编程结构来使数据操作和计算更加容易。例如，T-SQL 不支持数组、集合、for-each 循环、位转移或类。尽管在 T-SQL 中可以模拟其中某些构造，但是托管代码对这些构造提供集成支持。根据方案的不同，这些功能可以为使用托管代码实现某些数据库功能提供令人心动的理由。
2、 对于计算和复杂的执行逻辑，托管代码比 T-SQL 更适合，它全面支持许多复杂的任务，包括字符串处理和正则表达式。
通过 .NET Framework 库中提供的功能，可以访问数千个预生成的类和例程。可以很容易从任何存储过程、触发器或用户定义函数进行访问。基类库 (BCL) 包括的类提供用于字符串操作、高级数学运算、文件访问、加密等的功能。
3、 一般来说，函数和聚合是SQL CLR的出色应用。
SQL CLR 代码的开发人员可以利用 .NET Framework API中存在的大量有用函数和类。这个类/函数库比 T-SQL 中支持的内置函数要丰富得多。此外，CLR 编程语言提供了 T-SQL 中所没有的丰富构造（例如数组和列表等）。与 T-SQL（它是一种解释语言）相比，CLR 编程语言之所以具有更好的性能，是因为托管代码是已编译的。对于涉及算术计算、字符串处理、条件逻辑等的操作，托管代码的性能可能要优于 T-SQL 一个数量级。
4、 托管代码的一个优点是类型安全性，即确保代码只通过正确定义并且权限许可的方式访问类型。
在执行托管代码之前，CLR 将验证代码是否安全。例如，通过检查代码来确保不读取以前未曾写入的内存。CLR 还可以帮助确保代码不操作非托管内存。
5、 开发人员应该将SQLCLR作为一种无法使用T-SQL显式表达逻辑的备选解决方案。
SQLCLR给开发人员提供了另一种编写存储过程的方法，但是利用T-SQL的声明性结构来处理基于集合的数据选择与修改要远远优于在.net中的过程化结构和ADO.NET对象模型中进行处理，因此SQLCLR不能作为实现业务层逻辑的替代品。那么根据这个规则，开发人员应该首先使用T-SQL解决问题。
6、 SQLCLR的局限
尽管其中许多类可以从 SQL Server 的 CLR 代码中使用，但是不适合服务器端使用的类（例如窗口类）将无法使用。
E. 怎样从SQL Server 2005 CLR存储过程返回结果集
部署CLR存储过程 上面的示例代码需要进行编译才能通过存储过程来调用。在命令行中执行下面的命令来编译这些代码并创建类库DLL MSSQLTipsCLRLib.dll： CSC/target:libraryStoredProceres.cs/out:MSSQLTipsCLRLib.dll 假设你使用的是Microsoft .NET框架的第二版本，那么你可以在C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727目录下看到CSC.EXE。 执行下面的T-SQL脚本来创建这个存储过程： ALTERDATABASEmssqltips FROM'C:\mssqltips\MSSQLTipsCLRLib.dll' WITHPERMISSION_SET=EXTERNAL_ACCESSGOCREATEPROCEDUREdbo.GetListOfFiles @pathNVARCHAR(256) ,@patternNVARCHAR(64) ,@.StoredProceres.GetListOfFiles 在编译了这个代码到类库(.dll)之后，CREATE ASSEMBLY命令就被执行了，它将这个DLL与ASSEMBLY数据库对象关联起来。FROM条件从句必须指向DLL的实际路径。PERMISSION_SET必须设置为EXTERNAL_ACCESS，因为.NET代码将访问SQL Server 之外的文件系统。TRUSTWORTHY选项被设置为on，以允许外部访问。最后，CREATE PROCEDURE命令的EXTERNAME NAME将这个集合、类和函数与这个存储过程名称关联起来。 要执行这个存储过程，执行下面的脚本： EXECdbo.GetListOfFiles'C:\mssqltips','*.*',0 你将看到类似于下面的输出，这取决于你选择的文件夹的内容;例如，一个只有一列、每一行是一个文件的结果集：图2接下来的步骤 在这里下载示例脚本并执行从一个CLR存储过程返回结果集。 记住，当在Microsoft .NET框架中有满足你需求的函数时，使用SQL Server的CLR可能是个很好的解决方法。
F. SQL Server数据库如何手动部署CLR程序集
（中国软件网讯）
1.如果以前部署过当前程序集，则必须先删除引用当前程序集的所有模块(各类函数和存储过程等)，然后删除当前程序集。
2.重新部署当前程序集，然后再创建相关模块(各类函数和存储过程等)。
以下是一个sql模板，有需要的可以套一下：
-- ================================= 删除先 ================================= --
-- MyCLRSqlserver 为程序集部署名称
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyCLRSqlserver' and is_user_defined = 1)BEGIN
-- 删除集合函数。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StrJoin]') AND type = N'AF')
DROP AGGREGATE dbo.StrJoin;
-- 删除标量函数。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[___fun_CLR_ChangeSalarieScope]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.___fun_CLR_ChangeSalarieScope;
-- 卸载程序集。
DROP ASSEMBLY [MyCLRSqlserver];END;
-- ================================= 重新创建 ================================= --
-- 注册程序集(如果是在服务器上部署，需要将此dll复制到服务器，然后修改文件地址)。
CREATE ASSEMBLY [MyCLRSqlserver] FROM 'G:AAXX.dll';
---------------------- [dbo].[StrJoin] ----------------------
CREATE AGGREGATE [dbo].[StrJoin]
(@Value [nvarchar](4000))
RETURNS[nvarchar](4000)
EXTERNAL NAME [MyCLRSqlserver].[MyCLRSqlserver.StrJoin]GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'StrJoin.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=14 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO
-- ================================= END ================================= --
以上是手工创建过程比较麻烦，当然如果模块不多的话这样也没问题，如果很多的话可以查当前数据库的系统视图assembly_moles获取所有CLR模块，然后写代码自动生成模块DROP和CREATE过程。
G. SQL中CLR用户自定义数据是什么意思
SQL Server CLR 集成简介
公共语言运行库 (CLR) 是 Microsoft .NET Framework 的核心，为所有 .NET Framework 代码提供执行环境。在 CLR 中运行的代码称为托管代码。CLR 提供执行程序所需的各种函数和服务，包括实时 (JIT) 编译、分配和管理内存、强制类型安全性、异常处理、线程管理和安全性。
通过在 Microsoft SQL Server 中托管 CLR（称为 CLR 集成），可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数。因为托管代码在执行之前会编译为本机代码，所以，在有些方案中可以大大提高性能。
托管代码使用代码访问安全性 (CAS)、代码链接和应用程序域来阻止程序集执行某些操作。SQL Server 2005 使用 CAS 帮助保证托管代码的安全，并避免操作系统或数据库服务器受到威胁。
CLR 集成的优点
Transact-SQL 专门为数据库中的直接数据访问和操作而设计。尽管 Transact-SQL 在数据访问和管理方面领先，但是它没有提供编程构造来使数据操作和计算更加容易。例如，Transact-SQL 不支持数组、集合、for-each 循环、位转移或类。尽管在 Transact-SQL 中可以模拟其中某些构造，但是托管代码对这些构造提供集成支持。根据方案的不同，这些功能可以为使用托管代码实现某些数据库功能提供令人心动的理由。
Microsoft Visual Basic .NET 和 Microsoft Visual C# 提供面向对象的功能，例如封装、继承和多态性。现在，相关代码可以很容易在类和命名空间中进行组织。在使用大量服务器代码时，这样可以更容易地组织和维护您的代码。
对于计算和复杂的执行逻辑，托管代码比 Transact-SQL 更适合，它全面支持许多复杂的任务，包括字符串处理和正则表达式。通过 .NET Framework 库中提供的功能，可以访问数千个预生成的类和例程。可以很容易从任何存储过程、触发器或用户定义函数进行访问。基类库 (BCL) 包括的类提供用于字符串操作、高级数学运算、文件访问、加密等的功能。
注意
尽管其中许多类可以从 SQL Server 的 CLR 代码中使用，但是不适合服务器端使用的类（例如窗口类）将无法使用。
托管代码的一个优点是类型安全性，即确保代码只通过正确定义并且权限许可的方式访问类型。在执行托管代码之前，CLR 将验证代码是否安全。例如，通过检查代码来确保不读取以前未曾写入的内存。CLR 还可以帮助确保代码不操作非托管内存。
选择 Transact-SQL 和托管代码
在编写存储过程、触发器和用户定义函数时，必须做的一个决定是使用传统的 Transact-SQL 还是使用 Visual Basic .NET 或 Visual C# 等 .NET Framework 语言。对于几乎或根本不需要过程逻辑的数据访问，请使用 Transact-SQL。对于具有复杂逻辑的 CPU 密集型函数和过程，或要使用 .NET Framework 的 BCL 时，请使用托管代码。
选择在服务器中执行和在客户端中执行
决定使用 Transact-SQL 还是托管代码的另一个因素是您希望代码驻留的位置，驻留在服务器计算机上还是客户端计算机上。Transact-SQL 和托管代码均可以在服务器上运行。这样使代码和数据距离很近，可以利用服务器的处理能力。另一方面，您可能希望避免将处理器密集型任务放在数据库服务器上。现在，大多数客户端计算机非常强大，您可能希望将尽可能多的代码放在客户端上，以利用客户端的处理能力。托管代码可以在客户端计算机上运行，而 Transact-SQL 不能。
选择扩展存储过程和托管代码
生成的扩展存储过程可以执行 Transact-SQL 存储过程无法执行的功能。但是，扩展存储过程会影响 SQL Server 进程的完整性，而通过类型安全性验证的托管代码不会。另外，内存管理、线程和构造的调度以及同步服务在 CLR 的托管代码与 SQL Server 之间更深入地集成。通过 CLR 集成，可以通过比扩展存储过程更加安全、可伸缩性更强的方式来编写所需的存储过程，以执行 Transact-SQL 中无法执行的任务。
H. 在sql clr 项目中，如何实现多行字符串
利用函数：group_concat(),实现一个ID对应多个名称时，原本为多行数据，把名称合并成一行。
其完整语法：
GROUP_CONCAT(expr)
该函数返回带有来自一个组的连接的非NULL值的字符串结果。其完整的语法如下所示：
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
mysql> SELECT student_name,
-> GROUP_CONCAT(test_score)
-> FROM student
-> GROUP BY student_name;
Or:
mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR ' ')
-> FROM student
-> GROUP BY student_name;
在MySQL中,你可以获取表达式组合的连接值。你可以使用DISTINCT删去重复值。假若你希望多结果值进行排序，则应该使用 ORDER BY子句。若要按相反顺序排列，将 DESC (递减) 关键词添加到你要用ORDER BY 子句进行排序的列名称中。默认顺序为升序；可使用ASC将其明确指定。 SEPARATOR 后面跟随应该被插入结果的值中间的字符串值。默认为逗号 (‘,')。通过指定SEPARATOR '' ，你可以删除所有分隔符。
使用group_concat_max_len系统变量，你可以设置允许的最大长度。 程序中进行这项操作的语法如下，其中 val 是一个无符号整数：
SET [SESSION | GLOBAL] group_concat_max_len = val;
I. Sql Server CLR创建程序集时报错，有大神知道该怎么搞么
/*
1.例,创建CLR标量函数(根据表名,统计该表记录数)
用记事本编辑,保存FirstUdf.cs
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
public class T
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int ReturnOrderCount(string tName)
{
using (SqlConnection conn
= new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
string.Format(@"SELECT COUNT(*) AS Cnt FROM {0}",tName), conn);
return (int)cmd.ExecuteScalar();
}
}
}
2.编译DLL
2.1打CMD,进入.Net目录(C:\Windows\Microsoft.NET\Framework\v3.5) 输入命令
或者
2.2该目录,创建BAT,执行即
编译命令
csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs
3.加载DLL,创建函数,查看结
*/
CREATE ASSEMBLY FirstUdf FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\FirstUdf.dll';
GO
CREATE FUNCTION Fn_GetCount(@A nVarchar(100)) RETURNS INT
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;
GO
SELECT dbo.Fn_GetCount('apo_city');
GO