写在面前
本类别著作一共分为四有的：
1. CLR概述。
2. 在Visual Studio中展开CLR集成编程并部署到SQL
Server，包括存储过程、触发器、自定义函数、自定义类型和聚众。
3. CLR集成编程的调节和所遇到的题目。
4. 拔取CLR同步SQL Server表和Sharepoint
List（来源于实际项目选用）。
本系列作品建立在偏下软件条件的根底上：
- Windows Server 2003 Enterprise Edition Service Pack 2
- Microsoft Visual Studio Team System 2008
- Microsoft SQL Server 2008
- Microsoft Office Sharepoint Server 2007
准备工作
默认情状下SQL
Server对CLR的允许状态是关门的，大家需要在询问分析器中开辟它，将CLR设置为允许，这样该服务器将对负有数据库实例开放CLR执行标准。可以依据下边的步调举办：
Exec
sp_configure ‘clr enabled’
该系统存储过程用于查看当前CLR的情事，重临的结果中一旦config_value字段的值为0则意味禁止CLR在该服务器上运行，值为1则为允许。扩充第二个参数到该存储过程，然后再实践。
Exec
sp_configure ‘clr enabled’,
1–1 enabled,
0 disabled
查询分析器的音信窗口中付出提醒：Configuration option ‘clr enabled’ changed from 0
to 1. Run the RECONFIGURE statement to install.
按指示运行reconfigure命令举办安装。
reconfigure with
override
现在SQL
Server服务器已经允许实施CLR托管代码了，接下去大家只需要在Visual
Studio中编辑代码，将转变的dll部署到SQL Server中即可。按下图，成立SQL
Server
Project并安装好数据库连接，然后就可以编制用户自定义类型、存储过程、用户自定义函数、聚合和触发器了。
开始
一个简易的自定义函数
现在我们已经得以在Visual Studio中开头CLR项目了，在Solution
Explorer中右键单击项目文件，点击Add，在中间挑选你所要添加的类型。我们从最简单易行的门类起先，选中User-Defined
Function…，取名为Date提姆ePre.cs，该自定义函数紧要实现在用户给定的字符串数据前增长系统当下时刻前缀，编写代码如下。
1 using
System;
2 using
System.Data;
3 using
System.Data.SqlClient;
4 using
System.Data.SqlTypes;
5 using
Microsoft.SqlServer.Server;
6
7 public partial class
UserDefinedFunctions
8 {
9 [Microsoft.SqlServer.Server.SqlFunction]
10 public static
SqlString DateTimePre(string input,
string format)
11 {
12 string sRst
= string.Format(“{0}:{1}”,
DateTime.Now.ToString(format), input);
13 return new
SqlString(sRst);
14 }
15 };
代码很粗略，就是在用户给定的文本前增长当前时间前缀，第二个参数用于指定时间突显的体制。先表明一下代码的结构。SQLCLR中定义的类（用户自定义的类除外）皆以partial关键字开端，表示它是一个分部类，这一个大家不需要去改它，默认的类名可以修改，同一个类中可以有三个自定义的花色（自定义函数、触发器、存储过程等），但都必须用特色属性展现指明类型，如SqlFunction、SqlProcedure、SqlTrigger、SqlUserDefinedType等，那么些序列必须定义为public类型的同时必须为static，以向SQL
Server完全公之于世，有些项目必须要有重回值，如自定义函数，倘使不需要类型再次来到值，则足以定义为存储过程类型，那几个前边再举例。
编译代码，部署到SQL Server服务器，那些服务器的地址取决于你在Visual
Studio中所指定的数据库服务器，就是我们在创建项近年来所指定的百般数据库服务器。在Solution
Explorer中右键单击项目文件，点击Deploy，Visual
Studio会自动为你举行程序集的配置。因为我们的这多少个示例很简短，也绝非提到到走访外部资源的代码，所以不要考虑程序集的拜访级别和安全性，让Visual
Studio遵照默认的图景自行举办就足以了。我会在后头专门介绍咋样设置程序集的造访级别以及中间境遇的题材。
部署成功后转到SQL Server Management
Studio，打开你所连接的数据库，依次选取Programmability—Functions—Scalar-valued
Functions，在底下可以找到我们刚创设的那些类型，表示一切顺利！
接下去我们在查询分析器中推行它，看一下实践结果，OK。一个简便的自定义函数就成功了，因为有Visual
Studio的声援，使得SQLCLR的编撰变得至极简单，否则，你需要在命令行方式入手动举办配置，那多少个过程相比复杂，也不是本文的关键。
Select
dbo.DateTimePre(‘Begin’,
”)
流式表值函数（电视F）的兑现
前边我在介绍CLR集成性能的时候提到了流式表值函数（电视F），它回到IEnumerable接口的托管函数，通过特征属性指定重返结果的样式和定义方法，将结果以数据表的款式在SQL
Server的查询分析器中输出。它的性能将打折在SQL
Server查询分析器中应用扩充存储过程的性质。
在刚刚创立的class文件中再写一个措施，用于落实流式表值函数。示例给出的是在用户指定的职位搜索出用户指定项目标富有文件，然后以确定的报表样式将结果在SQL
Server的查询分析器中输出。
1 [SqlFunction(FillRowMethodName = “BuildRow”,
2 TableDefinition = “Name
nvarchar(32), Length bigint, Modified DateTime”)]
3 public static
IEnumerable FileListCs(string
directoryName, string pattern)
4 {
5 FileInfo[] files;
6 //模拟当前SQL安全上下文
7 WindowsImpersonationContext contect
= SqlContext.WindowsIdentity.Impersonate();
8 try
9 {
10 DirectoryInfo di = new
DirectoryInfo(directoryName);
11 files =
di.GetFiles(pattern);
12 }
13 finally
14 {
15 if (contect
!= null)
16 {
17 contect.Undo();
18 }
19 }
20 return
files;
21 }
22
23 private static void
BuildRow(object Obj,
24 ref SqlString
fileName,
25 ref SqlInt64
fileLength,
26 ref
SqlDateTime fileModified)
27 {
28 if (Obj
!= null)
29 {
30 FileInfo file =
(FileInfo)Obj;
31 fileName =
file.Name;
32 fileLength =
file.Length;
33 fileModified =
file.LastWriteTime;
34 }
35 else
36 {
37 fileName =
SqlString.Null;
38 fileLength =
SqlInt64.Null;
39 fileModified =
SqlDateTime.Null;
40 }
41 }
对代码的认证。特征属性中的FillRowMethodName用来告诉该函数，输出的结果需要用它指定的函数举行格式化，而格式化的样式（即Table的概念）由TableDefinition的值指定。这也实属，我们需要协调编辑FillRowMethodName所指定的函数，即代码中的BuildRow方法。该方法有一个输入型参数Obj，用于收纳流式表值函数的再次来到值，此外多少个ref型的参数必须与TableDefinition所规定的情节相同，并且必须是ref类型的参数。经过如此的确定，流式表值函数就会听从我们定义好的表结构在SQL
Server的询问分析器中输出结果。WindowsImpersonationContext对象用于在SQL
Server中模拟当前安全上下文，以SQL
Server进程的地方实施程序代码，在访问外部资源时（尤其是网络资源），假设当前SQL
Server没有访问权限，则依附于它的CLR程序集的拜会也会失败。注目的在于对CLR进行安全上下文模拟之后必须开展Undo操作！
有一个前提条件需要表明一下。前边我在CLR集成安全性中涉及，当CLR的顺序集需要拜访外部资源时（例如大家这里所访问的磁盘文件），需要设置程序集的访问级别为External，而且需要将宿主数据库的权柄级别设置为EXTERNAL_ACCESS，否则SQL
Server的询问分析器会提醒错误。
Use
Test
Go
Alter Database Test
Set trustworthy On
Go
编译、部署，转到SQL
Server查询分析器中，在Programmability—Functions—Table valued
Functions下得以看看我们刚创造的流式表值函数。执行它！
Select * From
Test.dbo.FileListCs(‘D:\TreeView’,
‘*.*’)
出现了错误！原来俺们在推行流式表值函数的表征属性时少了一个DataAccess，下面补上。再运行，函数给出了正确的结果。
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine
or aggregate “FileListCs”:
System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to
obtain data from FillRow method of a Table Valued Function, or is a UDT
validation method.
System.InvalidOperationException:
at
System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode
eRc)
at
System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink
sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at
Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink
eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_WindowsIdentity()
at UserDefinedFunctions.FileListCs(String directoryName, String
pattern)
.
[SqlFunction(DataAccess =
DataAccessKind.Read,
FillRowMethodName = “BuildRow”,
TableDefinition = “Name
nvarchar(32), Length bigint, Modified DateTime”)]
仓储过程的落实
在Visual Studio中再度成立一个序列为Stored
Procedure的class，编写代码如下。
1 [Microsoft.SqlServer.Server.SqlProcedure]
2 public static void
GetData(string tbName)
3 {
4 using
(SqlConnection cn = new
SqlConnection(“context connection=true”))
5 {
6 using
(SqlCommand cmd = cn.CreateCommand())
7 {
8 cmd.CommandText = string.Format(“Select *
from {0}”, tbName);
9 cn.Open();
10
SqlContext.Pipe.Send(cmd.ExecuteReader());
11 }
12 }
13 }
依据自己在前边CLR集成安全性中介绍的，CLR在EXTERNAL_ACCESS访问格局下默认以SQL
Server当前的劳动账户运行，所以我们得以运用近来上下文来拿到数据库连接字符串，并开展相关数据处理（如输出Message，访问站点等）。SqlContext.Pipe.Send方法用于在此时此刻上下文中输出结果到SQL
Server查询分析器的Results窗口中，它可以收起SqlData里德(Reade)r、SqlDataRecord和string类型的参数，同时也得以SqlContext.Pipe.ExecuteAndSend(SqlCommand)这样来用。
编译、部署，转到SQL
Server查询分析器中，刚刚编写的存储过程现身在Programmability—Stored
Procedure下，直接调用该存储过程，得到结果。
触发器的兑现
触发器的兑现相比简单，首要仍旧模式前的表征属性需要描述清楚，这里给出一个示范，当对表Area举办更新操作的时候会在表Region中更新相应的值，相关截图和代码如下。
 |
 |
|
| 表Area（修改前） | 表Region（修改前） |
1 [Microsoft.SqlServer.Server.SqlTrigger(Name
= “Triggers”, Target
= “Area”, Event
= “FOR
UPDATE”)]
2 public static void
TriggersTest()
3 {
4 string
comText = @” Declare
@oldTitle varchar(50)
5 Declare @newTitle
varchar(50)
6
7 Select @oldTitle =
Title From Deleted
8 Select @newTitle =
Title From Inserted
9
10 Update Region Set
Area = @newTitle Where Area = @oldTitle”;
11
12 using
(SqlConnection cn = new
SqlConnection())
13 {
14 cn.ConnectionString = “context
connection=true”;
15 cn.Open();
16 using
(SqlCommand cmd = cn.CreateCommand())
17 {
18 cmd.CommandText = comText;
19
SqlContext.Pipe.ExecuteAndSend(cmd);
20 }
21 }
22 }
编译并配备，触发器出现在相关表下边的Triggers目录下，并且该触发器的图标上有一个小锁，表示该触发器是由CLR生成的，并且不可能被改动。现在update表Area的一条数据，会意识Region表中相关的记录也发出了变更，表示触发器已经生效了。
Update
Area Set Title = ‘APAC’ Where Title
= ‘APAC1’
 |
 |
|
| 表Area（修改后） | 表Region（修改后） |
用户自定义类型的贯彻
用户自定义类型通晓起来也许有点有点复杂，在实际应用当中可能也很少用到，我在那里平昔引用了MSDN上的一个例子，了然一下。实际上，当我们在Visual
Studio上创造一个UserDefinedType时，IDE已经为我们做了重重工作了，剩下的做事只需要以补充的点子周到代码即可，这样说来，实际上也不是那么复杂啊，至少Visual
Studio在为大家搭建好的代码结构中早已有了不少阐明，大家相应了然怎么去做。
这么些示例中定义了一个UserDefinedType类型，用户使用的时候能够给定一个字符串值，然后经过内部的转移显示出相应的信息。如给定1:2，则代表right
top，给定-1:3，则代表left
top等，内部的转换规则需要团结去贯彻，当然，你也得以兑现更为扑朔迷离的布局。
1 [Serializable]
2 [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
3 public struct
UserDefinedType : INullable
4 {
5 public override string
ToString()
6 {
7 if
(this.IsNull)
8 {
9 return “NULL”;
10 }
11 else
12 {
13 return this.m_x
+ “:” + this.m_y;
14 }
15 }
16
17 public bool IsNull
18 {
19 get {
return this.m_Null;
}
20 set {
this.m_Null = value; }
21 }
22
23 public static
UserDefinedType Null
24 {
25 get
26 {
27 UserDefinedType h = new
UserDefinedType();
28 h.m_Null = true;
29 return h;
30 }
31 }
32
33 public static
UserDefinedType Parse(SqlString s)
34 {
35 if
(s.IsNull)
36 return
Null;
37
38 string str
= Convert.ToString(s);
39 string[] xy
= str.Split(‘:’);
40
41 UserDefinedType u = new
UserDefinedType();
42 u.X =
Convert.ToInt32(xy[0]);
43 u.Y =
Convert.ToInt32(xy[1]);
44
45 return u;
46 }
47
48 public
SqlString Quadrant()
49 {
50 if (m_x
== 0 && m_y
== 0)
51 {
52 return “centered”;
53 }
54
55 SqlString stringReturn = “”;
56
57 if (m_x
== 0)
58 {
59 stringReturn = “center”;
60 }
61 else if (m_x
> 0)
62 {
63 stringReturn = “right”;
64 }
65 else if (m_x
< 0)
66 {
67 stringReturn = “left”;
68 }
69
70 if (m_y
== 0)
71 {
72 stringReturn =
stringReturn + ”
center”;
73 }
74 else if (m_y
> 0)
75 {
76 stringReturn =
stringReturn + ”
top”;
77 }
78 else if (m_y
< 0)
79 {
80 stringReturn =
stringReturn + ”
bottom”;
81 }
82
83 return
stringReturn;
84 }
85
86 // This is a
place-holder field member
87 public int X
88 {
89 get {
return this.m_x;
}
90 set {
this.m_x = value; }
91 }
92
93 public int Y
94 {
95 get {
return this.m_y;
}
96 set {
this.m_y = value; }
97 }
98
99 // Private
member
100 private int m_x;
101 private int m_y;
102 private bool
m_Null;
103 }
编译部署，在SQL
Server的询问分析器中打开Programmability—Types—User-Defined
Types，可以见到刚创制的体系，执行后可以看出结果。
CREATE TABLE
test_table (column1 UserDefinedType)
go
INSERT INTO
test_table (column1) VALUES
(‘1:2’)
INSERT INTO
test_table (column1) VALUES
(‘-2:3’)
INSERT INTO
test_table (column1) VALUES
(‘-3:-4’)
Select column1.Quadrant() From
test_table
聚集的实现
与自定义类型类似，在Visual
Studio中创建聚合（Aggreagte），IDE同样曾经为我们做好了备选。这里自己也交由一个MSDN上的事例（不过对此嵌套循环稍做了修改），用来在联谊中总括字符串数据类型中的原音字符的个数。
1 [Serializable]
2 [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
3 public struct
Aggregate
4 {
5 public void Init()
6 {
7 countOfVowels = 0;
8 }
9
10 public void
Accumulate(SqlString value)
11 {
12 // list of
vowels to look for
13 List<string> vowels
= new
List<string>();
14 vowels.Add(“a”);
15 vowels.Add(“e”);
16 vowels.Add(“i”);
17 vowels.Add(“o”);
18 vowels.Add(“u”);
19
20 // for each
character in the given parameter
21 for
(int i = 0; i
< value.ToString().Length; i++)
22 {
23 if
(vowels.Contains(value.Value.Substring(i, 1).ToLower()))
24 {
25 // it is a
vowel, increment the count
26 countOfVowels += 1;
27 }
28 }
29 }
30
31 public void
Merge(Aggregate value)
32 {
33 Accumulate(value.Terminate());
34 }
35
36 public
SqlString Terminate()
37 {
38 return
countOfVowels.ToString();
39 }
40
41 // This is a
place-holder member field
42 private
SqlInt32 countOfVowels;
43 }
编译部署，在SQL
Server的询问分析器中开辟Programmability—Functions—Aggreate
Functions，可以看来刚创立的聚合函数，下边是实践后的结果。
结语
CLR创设SQL
Server对象应该还不止上边提到的这两种档次，但常用的着力都在此间了，尤其是储存过程、函数和触发器。利用C#来编排这多少个品种，灵活性更大，可操控性也更强了。下一篇我将会介绍怎么着在Visual
Studio中举行CLR调试，对程序集的分发和手动部署，以及广大问题的解决办法。
| 1 | 2 | 3 | 4 |