透过CLR同步SQL Server和Sharepoint List数据(二)

写在眼前

    本类别小说一共分为四有些:

        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执行尺度。可以遵守下边的步子举行:

ACCESS 1

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并安装好数据库连接,然后就足以编制用户自定义类型、存储过程、用户自定义函数、聚合和触发器了。

ACCESS 2

ACCESS 3

开始

一个简约的自定义函数

    现在大家曾经得以在Visual Studio中初露CLR项目了,在Solution
Explorer中右键单击项目文件,点击Add,在其中拔取你所要添加的花色。我们从最简易的档次起头,选中User-Defined
Function…,取名为Date提姆(Tim)ePre.cs,该自定义函数紧要实现在用户给定的字符串数据前增长系统当下时刻前缀,编写代码如下。

ACCESS 4

1 using
System;
2 using
System.Data;
3 using
System.Data.SqlClient;
4 using
System.Data.SqlTypes;
5 using
Microsoft.SqlServer.Server;

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完全公之于世,有些连串必须要有重临值,如自定义函数,倘诺不需要类型重回值,则足以定义为存储过程类型,那一个前面再举例。

ACCESS,    编译代码,部署到SQL Server服务器,这么些服务器的地点取决于你在Visual
Studio中所指定的数据库服务器,就是大家在开创项近日所指定的不胜数据库服务器。在Solution
Explorer中右键单击项目文件,点击Deploy,Visual
Studio会自动为您举办程序集的配置。因为大家的这些示例很简短,也并未关联到走访外部资源的代码,所以不用考虑程序集的拜会级别和安全性,让Visual
Studio遵照默认的气象自行举办就可以了。我会在末端专门介绍咋样设置程序集的访问级别以及其中碰到的题目。

    部署成功后转到SQL Server Management
Studio,打开你所连接的数据库,依次采取Programmability—Functions—Scalar-valued
Functions,在上面可以找到咱们刚创设的那些项目,表示一切顺利!

ACCESS 5   
接下去我们在查询分析器中实践它,看一下实践结果,OK。一个简单的自定义函数就完事了,因为有Visual
Studio的协助,使得SQLCLR的编纂变得分外简单,否则,你需要在命令行模式入手动举行配置,这个过程相比复杂,也不是本文的重点。

Select
dbo.DateTimePre(‘Begin’,
”)

ACCESS 6

流式表值函数(电视机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

ACCESS 7    
编译、部署,转到SQL
Server查询分析器中,在Programmability—Functions—Table valued
Functions下可以看看大家刚创造的流式表值函数。执行它!

ACCESS 8

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”)]

ACCESS 9

 

积存过程的兑现

    在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里德r、SqlDataRecord和string类型的参数,同时也足以SqlContext.Pipe.ExecuteAndSend(SqlCommand)这样来用。

    编译、部署,转到SQL
Server查询分析器中,刚刚编写的贮存过程出现在Programmability—Stored
Procedure下,间接调用该存储过程,得到结果。

ACCESS 10

 

触发器的落实

   
触发器的贯彻相比较简单,首要仍旧艺术前的特点属性需要描述清楚,这里给出一个示范,当对表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

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

ACCESS 11

 

聚拢的落实

    与自定义类型类似,在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     }

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,可以看到刚创建的聚合函数,下边是实践后的结果。

ACCESS 12

 

结语

    CLR创设SQL
Server对象应当还不止下边提到的这几体系型,但常用的主导都在此处了,尤其是储存过程、函数和触发器。利用C#来编排这么些系列,灵活性更大,可操控性也更强了。下一篇我将会介绍怎么着在Visual
Studio中展开CLR调试,对程序集的散发和手动部署,以及宽广问题的解决办法。

演示代码下载

1 2 3 4

相关文章