通过CLR同步SQL Server和Sharepoint List数据(二)-阿里云开发者社区

开发者社区> 浣熊干面包> 正文

通过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执行条件。可以按照下面的步骤进行:

3-20-2009 5-10-59 PM

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

3-19-2009 2-23-30 PM

3-19-2009 2-27-35 PM

开始

一个简单的自定义函数

    现在我们已经可以在Visual Studio中开始CLR项目了,在Solution Explorer中右键单击项目文件,点击Add,在其中选择你所要添加的类型。我们从最简单的类型开始,选中User-Defined Function…,取名为DateTimePre.cs,该自定义函数主要实现在用户给定的字符串数据前加上系统当前时间前缀,编写代码如下。

3-19-2009 2-47-15 PM

复制代码
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,在下面可以找到我们刚创建的这个类型,表示一切顺利!

3-19-2009 3-31-50 PM    接下来我们在查询分析器中执行它,看一下执行结果,OK。一个简单的自定义函数就完成了,因为有Visual Studio的帮助,使得SQLCLR的编写变得非常简单,否则,你需要在命令行方式下手动进行部署,这个过程比较复杂,也不是本文的重点。

Select dbo.DateTimePre('Begin''')

3-19-2009 3-34-14 PM

流式表值函数(TVF)的实现

    前面我在介绍CLR集成性能的时候提到了流式表值函数(TVF),它返回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

3-19-2009 5-14-39 PM     编译、部署,转到SQL Server查询分析器中,在Programmability—Functions—Table valued Functions下可以看到我们刚创建的流式表值函数。执行它!

3-19-2009 5-18-53 PM

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

3-19-2009 5-28-40 PM

 

存储过程的实现

    在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窗口中,它可以接收SqlDataReader、SqlDataRecord和string类型的参数,同时也可以SqlContext.Pipe.ExecuteAndSend(SqlCommand)这样来用。

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

3-19-2009 5-57-07 PM

 

触发器的实现

    触发器的实现比较简单,主要还是方法前的特征属性需要描述清楚,这里给出一个示例,当对表Area进行更新操作的时候会在表Region中更新相应的值,相关截图和代码如下。

3-20-2009 10-17-05 AM   3-20-2009 10-18-08 AM
表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'
3-20-2009 10-34-18 AM   3-20-2009 10-34-45 AM
表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
复制代码

3-20-2009 12-56-05 PM

 

聚合的实现

    与自定义类型类似,在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,可以看到刚创建的聚合函数,下面是执行后的结果。

3-20-2009 1-13-59 PM

 

结语

    CLR创建SQL Server对象应该还不止上面提到的这几种类型,但常用的基本都在这里了,尤其是存储过程、函数和触发器。利用C#来编写这些类型,灵活性更大,可操控性也更强了。下一篇我将会介绍如何在Visual Studio中进行CLR调试,对程序集的分发和手动部署,以及常见问题的解决办法。

示例代码下载

1 2 3

4



本文转自Jaxu博客园博客,原文链接:http://www.cnblogs.com/jaxu/archive/2009/03/20/1416634.html,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
.NET数据库编程求索之路--6.使用ADO.NET实现(三层架构篇-使用List传递数据-基于存储过程)(1)
6.使用ADO.NET实现(三层架构篇-使用List传递数据-基于存储过程)(1)    【 夏春涛 email: xchuntao@163.com  blog: http://www.cnblogs.
730 0
.NET数据库编程求索之路--5.使用ADO.NET实现(三层架构篇-使用List传递数据)(2)
5.使用ADO.NET实现(三层架构篇-使用List传递数据)(2)   作者:夏春涛 xchunta@163.com 转载请注明来源: http://www.cnblogs.com/SummerRain/archive/2012/07/26/2610945.
763 0
实践 —— 亲测从 RDS MySQL 通过数据集成导入 MaxCompute
作者尝试了从RDS 的MySQL数据库到MaxCompute的 ODPS 的数据同步过程,并导入成功。有需要的同学赶紧试起来吧~~~
2535 0
intellij idea搭建ssh开发框架之绑定数据源
原文: intellij idea搭建ssh开发框架之绑定数据源 在intellij idea中绑定数据源并生成hibernate实体对象。
1260 0
通过SQL Server 2008数据库复制实现数据库同步备份
原文 通过SQL Server 2008数据库复制实现数据库同步备份 SQL Server 2008数据库复制是通过发布/订阅的机制进行多台服务器之间的数据同步,我们把它用于数据库的同步备份。这里的同步备份指的是备份服务器与主服务器进行 实时数据同步,正常情况下只使用主数据库服务器,备份服务器只在主服务器出现故障时投入使用。
1498 0
通过DBCC整理Sqlserver数据库表索引碎片
昨天检查了一张效率极慢的表,两年多没有维护,逻辑扫描碎片高达99.81%,于是开始对这个表进行重点跟踪和记录日志。今天用DBCC SHOWCONTIG WITH TABLERESULTS 命令检查了一下所有表的概况,然后参照着MSDN对字段的说明,发现问题比较严重。
948 0
rsync+inotify实时同步案例
rsync+inotify实时同步案例 随着应用系统规模的不断扩大,对数据的安全性和可靠性也提出的更好的要求,rsync在高端业务系统中也逐渐暴露出了很多不足,首先,rsync同步数据时,需要扫描所有文件后进行比对,进行差量传输。
1001 0
SharePoint自动化系列——通过PowerShell创建SharePoint List Items
转载请注明出自天外归云的博客园:http://www.cnblogs.com/LanTianYou/ 代码如下(保存到本地ps1文件中,右键run with PowerShell即可): Add-PSSnapin microsoft.
711 0
SharePoint自动化系列——通过PowerShell创建SharePoint Lists
转载请注明出自天外归云的博客园:http://www.cnblogs.com/LanTianYou/ 代码如下(保存到本地ps1文件中,右键run with PowerShell即可): Add-PSSnapin microsoft.
685 0
941
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载