• 提高你的数据库编程效率：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
  • 相关阅读:
    jenkins1—docker快速搭建jenkins环境
    UPC-6616 Small Multiple(BFS广搜&双向队列)
    UPC-5502 打地鼠游戏(贪心&优先队列)
    UPC-5500 经营与开发(贪心&逆推)
    NBUT
    UPC-6690 Transit Tree Path(树上最短路径SPFA)
    UPC-6359 售票(字典树)
    UPC-6358 庭师的利刃(两数与运算最大值)
    HDU-6308 Time Zone(时区转换)
    欧拉函数模板及拓展
  • 原文地址：https://www.cnblogs.com/zhangchenliang/p/4118237.html
Copyright © 2020-2023  润新知