跟小D每日学口语

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

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

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

实现存储过程

[csharp] view plaincopyprint?
 
  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 plaincopyprint?
 
  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. <p>--注册存储过程  
  9. create procedure PrintStudentDetail  
  10. as  
  11. external name chapter34_UDT.SampleStoreProcedure.PrintStudentDetail</p><p>--注册带参数的存储过程  
  12. create procedure GetStudentDetail  
  13. (  
  14.     @Id int  
  15. )  
  16. as  
  17. external name chapter34_UDT.SampleStoreProcedure.GetStudentDetail</p>  


执行结果

[sql] view plaincopyprint?
 
  1. exec PrintStudentDetail  
  2. exec GetStudentDetail 1  

 存储过程PrintStudentDetail执行结果

存储过程GetStudentDetail执行的结果


 

 

实现函数

[csharp] view plaincopyprint?
 
  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 plaincopyprint?
 
  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. <p>4.输入命令：</p>  
  11. --注册函数  
  12. create function HashSomeThing(@data nvarchar) returns nvarchar  
  13. as  
  14. external name chapter34_UDT.SampleFunction.[Hash]  

 

执行结果

[sql] view plaincopyprint?
 
  1. <p>输入调用命令:</p>select dbo.HashSomeThing(name) from Student  


 

实现表值函数

[csharp] view plaincopyprint?
 
  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 plaincopyprint?
 
  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  


 

执行结果

 

实现触发器

[csharp] view plaincopyprint?
 
  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 plaincopyprint?
 
  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 plaincopyprint?
 
  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  

 

实现聚合函数

[csharp] view plaincopyprint?
 
  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 plaincopyprint?
 
  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 plaincopyprint?
 
  1. 输入命令：  
  2. select dbo.SampleSum(TAggregate) from TAggregate  
  3. select Sum(TAggregate) from TAggregate  

 

 

 

 

实现类型

[csharp] view plaincopyprint?
 
  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 plaincopyprint?
 
  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  

 

执行结果

 

 

 

小结

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

 

reference from : http://blog.csdn.net/ghostbear/article/details/7333189

posted @ 2014-11-24 11:19  Danny Chen  阅读(315)  评论(0)  编辑  收藏  举报