提高你的数据库编程效率：Microsoft CLR Via Sql Server

川长思鸟来 2022-01-06 05:59 202阅读 0赞

你还在为数据库编程而抓狂吗？那些恶心的脚本拼接，低效的脚本调试的日子将会与我们越来越远啦。现在我们能用支持.NET的语言来开发数据库中的对象，如：存储过程，函数，触发器，集合函数已及复杂的类型。看到这些你还能淡定吗？哈哈，不仅仅是这些。那些能被.NET支持的第三方扩展通过该技术统统都能应用在数据库编程上，如：正则表达式，.NET庞大的加密解密库，以及各种.NET集成的排序和搜索算法。

下面我就来一一介绍怎么使用该技术来解放我们的双手！

### 实现存储过程 ###

**\[csharp\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1. using System; 
2. using System.Collections.Generic; 
3. using System.Linq; 
4. using System.Text; 
5. 
6. using Microsoft.SqlServer.Server; 
7. using System.Data; 
8. using System.Data.SqlClient; 
9. using System.Data.SqlTypes; 
10. using System.Collections; 
11. 
12. public class SampleStoreProcedure 
13. \{
14. \[SqlProcedure\]
15. public static void PrintStudentDetail() 
16. \{
17. SqlConnection conn = new SqlConnection("Context connection=true"); 
18. conn.Open();
19. SqlCommand cmd = new SqlCommand("select \* from student", conn); 
20. SqlCommand cmd2 = new SqlCommand("insert into studentdetail values(@detail)"); 
21. 
22. SqlDataReader reader;
23. string tmpData=string.Empty; 
24. ArrayList tmpDataArray=new ArrayList(); 
25. 
26. reader = cmd.ExecuteReader();
27. 
28. while (reader.Read()) 
29. \{
30. for (int i = 0; i < reader.FieldCount; i++) 
31. \{
32. tmpData += reader\[i\].ToString();
33. 
34. \}
35. tmpDataArray.Add(tmpData);
36. 
37. \}
38. reader.Close();
39. cmd2.Connection = conn;
40. foreach (string tmp in tmpDataArray) 
41. \{
42. cmd2.Parameters.Clear();
43. cmd2.Parameters.AddWithValue("@detail", tmp); 
44. cmd2.ExecuteNonQuery();
45. \}
46. 
47. conn.Close();
48. //conn2.Close(); 
49. 
50. \}
51. 
52. \[SqlProcedure\]
53. public static void GetStudentDetail(int id) 
54. \{
55. SqlConnection conn = new SqlConnection("Context connection=true"); 
56. SqlCommand cmd = new SqlCommand("select \* from student where id=@id", conn); 
57. SqlDataReader reader;
58. 
59. cmd.Parameters.AddWithValue("@id", id); 
60. try 
61. \{
62. conn.Open();
63. reader = cmd.ExecuteReader();
64. SqlPipe pipe = SqlContext.Pipe;
65. pipe.Send(reader);
66. reader.Close();
67. \}
68. catch 
69. \{
70. conn.Close();
71. \}
72. finally 
73. \{
74. 
75. \}
76. 
77. \}
78. \};

#### 部署步骤 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1. 1.编译项目，获取生成的DLL文件。
2. 
3. 2.在数据库中输入命令sp\_configure \[clr enabled\],1 reconfigure \--开启对程序集的信任权限。 
4. 
5. 3.输入命令:create assembly chapter34\_UDT from 'c:\\chapter34\_UDT.dll' 注册程序集 
6. 
7. 4.输入命令：
8. \--注册存储过程 
9. create procedure PrintStudentDetail 
10. as 
11. external name chapter34\_UDT.SampleStoreProcedure.PrintStudentDetail\--注册带参数的存储过程 
12. create procedure GetStudentDetail 
13. (
14. @Id int 
15. )
16. as 
17. external name chapter34\_UDT.SampleStoreProcedure.GetStudentDetail

####    执行结果 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  exec PrintStudentDetail 
2.  exec GetStudentDetail 1

![20383_1331527551R92r.jpg][]

存储过程PrintStudentDetail执行结果

![20383_1331527552r8zw.jpg][]

存储过程GetStudentDetail执行的结果

### 实现函数 ###

**\[csharp\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  using System; 
2.  using System.Collections.Generic; 
3.  using System.Linq; 
4.  using System.Text; 
5.  
6.  using System.Data; 
7.  using System.Data.SqlClient; 
8.  using System.Data.SqlTypes; 
9.  using Microsoft.SqlServer.Server; 
10. 
11. using System.Security; 
12. using System.Security.Cryptography; 
13. 
14. 
15. 
16. public class SampleFunction 
17. \{
18. public SampleFunction() 
19. \{
20. 
21. \}
22. 
23. \[SqlFunction\]
24. public static SqlString Hash(SqlString data) 
25. \{
26. SHA1 sha1 = SHA1.Create();
27. byte\[\] tmp = Encoding.ASCII.GetBytes(data.Value); 
28. string result= Convert.ToBase64String(sha1.ComputeHash(tmp)); 
29. return new SqlString(result); 
30. \}
31. 
32. \}

#### 部署步骤 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1. 1.编译项目，获取生成的DLL文件。
2. 
3. 2.在数据库中输入命令：
4. sp\_configure \[clr enabled\],1 reconfigure \--开启对程序集的信任权限。 
5. 
6. 3.输入命令:
7. create assembly chapter34\_UDT from 'c:\\chapter34\_UDT.dll' 注册程序集 
8. \--如果上述步骤已经做了就忽略 
9. 
10. 4.输入命令：
11. \--注册函数 
12. create function HashSomeThing(@data nvarchar) returns nvarchar 
13. as 
14. external name chapter34\_UDT.SampleFunction.\[Hash\]

#### 执行结果 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1. 输入调用命令:select dbo.HashSomeThing(name) from Student

![20383_1331527553ESt4.jpg][]

### 实现表值函数 ###

**\[csharp\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1. using System; 
2. using System.Collections.Generic; 
3. using System.Linq; 
4. using System.Text; 
5. 
6. 
7. using System.Data; 
8. using System.Data.SqlClient; 
9. using System.Data.SqlTypes; 
10. using Microsoft.SqlServer.Server; 
11. using System.Text.RegularExpressions; 
12. using System.Xml.Linq; 
13. using System.Xml; 
14. using System.IO; 
15. using System.Collections; 
16. 
17. 
18. public class SampleTableValueFunction 
19. \{
20. /// 
21. /// 表值函数的主体，该函数需要结合“内容填充函数”才能发挥功能。这里的“内容填充函数”是通过 
22. /// 属性“FillRowMethodName”属性来指定的。属性“TableDefinition”用来定义返回表格的格式。 
23. /// 
24. \[SqlFunction(TableDefinition = "tmp\_value nvarchar(max)", FillRowMethodName = "FillRow")\] 
25. public static IEnumerable PrintOneToTen() 
26. \{
27. IList<string> result2 = new List<string>(); 
28. var matches = new string\[\]\{ 
29. "one", 
30. "two", 
31. "three", 
32. "four", 
33. "five", 
34. "six", 
35. "seven", 
36. "eight", 
37. "nine", 
38. "ten" 
39. \};
40. 
41. return matches.AsEnumerable(); 
42. \}
43. 
44. public static void FillRow(object obj, out SqlString tmp) 
45. \{
46. tmp = new SqlString(obj.ToString()); 
47. \}
48. \}

#### 部署步骤 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  1.编译项目，获取生成的DLL文件。
2.  
3.  2.在数据库中输入命令：
4.  sp\_configure \[clr enabled\],1 reconfigure \--开启对程序集的信任权限。 
5.  
6.  3.输入命令:
7.  create assembly chapter34\_UDT from 'c:\\chapter34\_UDT.dll' 注册程序集 
8.  \--如果上述步骤已经做了就忽略 
9.  4.输入命令：
10. create function SampleTableValueFunction() returns table(tmp\_value nvarchar(max) null) 
11. as 
12. external name chapter34\_UDT.SampleTableValueFunction.PrintOneToTen

#### 执行结果 ####

![20383_13315275546Csw.jpg][]

### 实现触发器 ###

**\[csharp\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  using System; 
2.  using System.Collections.Generic; 
3.  using System.Linq; 
4.  using System.Text; 
5.  
6.  using Microsoft.SqlServer.Server; 
7.  using System.Data; 
8.  using System.Data.SqlClient; 
9.  using System.Data.SqlTypes; 
10. 
11. 
12. public class SampleTrigger 
13. \{
14. public SampleTrigger() 
15. \{
16. 
17. \}
18. \[SqlTrigger(Event = "For Insert,Update,Delete", Name = "PrintInfo", Target = "Student")\] 
19. public static void PrintInfo() 
20. \{
21. SqlTriggerContext triggerContext = SqlContext.TriggerContext;
22. SqlConnection conn = new SqlConnection("Context connection=true"); 
23. SqlCommand cmd = new SqlCommand(); 
24. cmd.Connection = conn;
25. 
26. switch (triggerContext.TriggerAction) 
27. \{
28. case TriggerAction.Insert: 
29. cmd.CommandText = "insert into StudentDetail values('insert operation!')"; 
30. break; 
31. case TriggerAction.Delete: 
32. cmd.CommandText = "insert into StudentDetail values('delete operation!')"; 
33. break; 
34. case TriggerAction.Update: 
35. cmd.CommandText = "insert into StudentDetail values('update operation!')"; 
36. break; 
37. default: 
38. break; 
39. \}
40. 
41. try 
42. \{
43. conn.Open();
44. cmd.ExecuteNonQuery();
45. \}
46. catch 
47. \{
48. 
49. \}
50. finally 
51. \{
52. conn.Close();
53. \}
54. \}
55. 
56. \[SqlTrigger(Name="InsertSomething",Target="chapter30.dbo.Student",Event="FOR INSERT")\] 
57. public static void InsertSomething() 
58. \{
59. SqlTriggerContext triggerContext = SqlContext.TriggerContext;
60. if (triggerContext.TriggerAction == TriggerAction.Insert) 
61. \{
62. var conn = new SqlConnection("Context connection=true"); 
63. var cmd = new SqlCommand(); 
64. cmd.Connection = conn;
65. cmd.CommandText = "Insert into StudentDetail values('insert event')"; 
66. conn.Open();
67. cmd.ExecuteNonQuery();
68. conn.Close();
69. \}
70. \}
71. \}

#### 部署步骤 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  1.编译项目，获取生成的DLL文件。
2.  
3.  2.在数据库中输入命令：
4.  sp\_configure \[clr enabled\],1 reconfigure \--开启对程序集的信任权限。 
5.  
6.  3.输入命令:
7.  create assembly chapter34\_UDT from 'c:\\chapter34\_UDT.dll' 注册程序集 
8.  \--如果上述步骤已经做了就忽略 
9.  4.输入命令：
10. \--注册触发器 
11. create trigger PrintSomething on Student 
12. for insert,update,delete 
13. as 
14. external name chapter34\_UDT.SampleTrigger.PrintInfo

####    执行结果 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  输入命令：
2.  insert into Student values(12345,'tmp','11','11') 
3.  update Student set Name='new'+Name where Id=12345 
4.  delete from Student where Id=12345 
5.  
6.  select \* from StudentDetail

![20383_13315275540ta2.jpg][]

### 实现聚合函数 ###

**\[csharp\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  using System; 
2.  using System.Collections.Generic; 
3.  using System.Linq; 
4.  using System.Text; 
5.  
6.  using Microsoft.SqlServer.Server; 
7.  using System.Data; 
8.  using System.Data.SqlClient; 
9.  using System.Data.SqlTypes; 
10. 
11. 
12. 
13. \[Serializable\]
14. \[SqlUserDefinedAggregate(Format.Native)\]
15. public struct SampleSum 
16. \{
17. private int sum; 
18. public void Init() 
19. \{
20. sum = 0;
21. \}
22. 
23. public void Accumulate(SqlInt32 Value) 
24. \{
25. sum += Value.Value;
26. \}
27. 
28. public void Merge(SampleSum Group) 
29. \{
30. sum += Group.sum;
31. \}
32. 
33. public SqlInt32 Terminate() 
34. \{
35. return new SqlInt32(sum); 
36. \}
37. 
38. \}

####    部署步骤 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  1.编译项目，获取生成的DLL文件。
2.  
3.  2.在数据库中输入命令：
4.  sp\_configure \[clr enabled\],1 reconfigure \--开启对程序集的信任权限。 
5.  
6.  3.输入命令:
7.  create assembly chapter34\_UDT from 'c:\\chapter34\_UDT.dll' 注册程序集 
8.  \--如果上述步骤已经做了就忽略 
9.  4.输入命令：
10. \--注册聚合函数 
11. create aggregate SampleSum(@value int) returns int 
12. external name \[chapter34\_UDT\].SampleSum

#### 执行结果 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  输入命令：
2.  select dbo.SampleSum(TAggregate) from TAggregate 
3.  select Sum(TAggregate) from TAggregate

![20383_1331527552JcY7.jpg][]

### 实现类型 ###

**\[csharp\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  using System; 
2.  using System.Collections.Generic; 
3.  using System.Linq; 
4.  using System.Text; 
5.  
6.  using Microsoft.SqlServer.Server; 
7.  using System.Data.SqlTypes; 
8.  using System.Data; 
9.  using System.Data.SqlClient; 
10. 
11. 
12. \[Serializable\]
13. \[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)\]
14. public struct Facade : INullable 
15. \{
16. public bool isNull; 
17. int hairColor; 
18. int tall; 
19. int skin; 
20. int country; 
21. 
22. public Facade(int hairColor, int tall, int skin, int country) 
23. \{
24. isNull = false; 
25. this.hairColor = hairColor; 
26. this.tall = tall; 
27. this.skin = skin; 
28. this.country = country; 
29. \}
30. 
31. public static Facade Null 
32. \{
33. get 
34. \{
35. return new Facade \{ isNull = true \}; 
36. \}
37. \}
38. 
39. public override string ToString() 
40. \{
41. StringBuilder sb = new StringBuilder(); 
42. sb.AppendFormat("\{0\};", hairColor); 
43. sb.AppendFormat("\{0\};", tall); 
44. sb.AppendFormat("\{0\};", skin); 
45. sb.AppendFormat("\{0\}", country); 
46. return sb.ToString(); 
47. 
48. \}
49. 
50. 
51. 
52. public static Facade Parse(SqlString data) 
53. \{
54. if (data.IsNull) 
55. \{
56. return new Facade \{ isNull = true \}; 
57. \}
58. 
59. Facade result;
60. string\[\] tmpData = data.Value.Split(';'); 
61. result = new Facade(int.Parse(tmpData\[0\]), int.Parse(tmpData\[1\]), int.Parse(tmpData\[2\]), int.Parse(tmpData\[3\])); 
62. 
63. return result; 
64. \}
65. 
66. 
67. public bool IsNull 
68. \{
69. get \{ return isNull; \} 
70. \}
71. \}

####    部署步骤 ####

**\[sql\]** [view plain][] [copy][view plain] [print][view plain] [?][view plain]

1.  1.编译项目，获取生成的DLL文件。
2.  
3.  2.在数据库中输入命令：
4.  sp\_configure \[clr enabled\],1 reconfigure \--开启对程序集的信任权限。 
5.  
6.  3.输入命令:
7.  create assembly chapter34\_UDT from 'c:\\chapter34\_UDT.dll' 注册程序集 
8.  \--如果上述步骤已经做了就忽略 
9.  4.输入命令：
10. create type Facade external name 
11. \[chapter34\_UDT\].Facade

#### 执行结果 ####

![20383_1331527553UgI3.jpg][]

## 小结 ##

CLR Sql Server 的推出大大的提高了Sql Server的脚本编程效率问题，并且这项技术给了我们很大的相信空间。现在我们就来用有限的手段实现无限的可能吧！

reference from : [http://blog.csdn.net/ghostbear/article/details/7333189][view plain]

转载于:https://www.cnblogs.com/zhangchenliang/p/4118237.html

[view plain]: http://blog.csdn.net/ghostbear/article/details/7333189
[20383_1331527551R92r.jpg]: /images/20211227/dd238aff7c414c3f94495ed7d310ceab.png
[20383_1331527552r8zw.jpg]: /images/20211227/a2f4a4bac12c429bb52cda4f354752b7.png
[20383_1331527553ESt4.jpg]: /images/20211227/84abae2969374a5cab47da284eaeb702.png
[20383_13315275546Csw.jpg]: /images/20211227/905c1a8abad546949ebaa4f5afe6594e.png
[20383_13315275540ta2.jpg]: /images/20211227/393d18c1fdbe43e4b3ea925506895cd5.png
[20383_1331527552JcY7.jpg]: /images/20211227/d53c180e9f184a26a15ed63ebf004f00.png
[20383_1331527553UgI3.jpg]: /images/20211227/f4ede6effde042c6917ddda5c8b2640d.png