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

最新推荐文章于 2016-07-21 16:05:09 发布
最新推荐文章于 2016-07-21 16:05:09 发布
阅读量4.2k 收藏 4
点赞数 3
分类专栏： D365 CRM & Power Platform 常用算法贴（C#） 文章标签： 数据库 sql server microsoft 编程 assembly insert
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ghostbear/article/details/7333189
版权

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

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

实现存储过程

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;

    public class SampleStoreProcedure
    {
        [SqlProcedure]
        public static void PrintStudentDetail()
        {
            SqlConnection conn = new SqlConnection("Context connection=true");
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from student", conn);
            SqlCommand cmd2 = new SqlCommand("insert into studentdetail values(@detail)");

            SqlDataReader reader;
            string tmpData=string.Empty;
            ArrayList tmpDataArray=new ArrayList();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    tmpData += reader[i].ToString();
                    
                }
                tmpDataArray.Add(tmpData);

            }
            reader.Close();
            cmd2.Connection = conn;
            foreach (string tmp in tmpDataArray)
            {
                cmd2.Parameters.Clear();
                cmd2.Parameters.AddWithValue("@detail", tmp);
                cmd2.ExecuteNonQuery();
            }

            conn.Close();
            //conn2.Close();

        }

        [SqlProcedure]
        public static void GetStudentDetail(int id)
        {
            SqlConnection conn = new SqlConnection("Context connection=true");
            SqlCommand cmd = new SqlCommand("select * from student where id=@id", conn);
            SqlDataReader reader;

            cmd.Parameters.AddWithValue("@id", id);
            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                SqlPipe pipe = SqlContext.Pipe;
                pipe.Send(reader);
                reader.Close();
            }
            catch
            {
                conn.Close();
            }
            finally
            { 
            
            }
            
        }
    };

 

部署步骤
1.编译项目，获取生成的DLL文件。

2.在数据库中输入命令sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。

3.输入命令:create assembly chapter34_UDT from 'c:\chapter34_UDT.dll' 注册程序集

4.输入命令：

--注册存储过程
create procedure PrintStudentDetail
as
external name chapter34_UDT.SampleStoreProcedure.PrintStudentDetail
--注册带参数的存储过程
create procedure GetStudentDetail
(
    @Id int
)
as
external name chapter34_UDT.SampleStoreProcedure.GetStudentDetail

执行结果
exec PrintStudentDetail
exec GetStudentDetail 1

 存储过程PrintStudentDetail执行结果

存储过程GetStudentDetail执行的结果


 

 

实现函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Security;
using System.Security.Cryptography;



    public class SampleFunction
    {
        public SampleFunction()
        { 
        
        }

        [SqlFunction]
        public static SqlString Hash(SqlString data)
        {
            SHA1 sha1 = SHA1.Create();
            byte[] tmp = Encoding.ASCII.GetBytes(data.Value);
            string result= Convert.ToBase64String(sha1.ComputeHash(tmp));
            return new SqlString(result);
        }

    }
部署步骤

 

1.编译项目，获取生成的DLL文件。

2.在数据库中输入命令：
sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。

3.输入命令:
create assembly chapter34_UDT from 'c:\chapter34_UDT.dll' 注册程序集
--如果上述步骤已经做了就忽略


4.输入命令：

--注册函数
create function HashSomeThing(@data nvarchar) returns nvarchar
as
external name chapter34_UDT.SampleFunction.[Hash]


执行结果
输入调用命令:
select dbo.HashSomeThing(name) from Student


 

实现表值函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Xml.Linq;
using System.Xml;
using System.IO;
using System.Collections;


public class SampleTableValueFunction
    {
        /// 
        /// 表值函数的主体，该函数需要结合“内容填充函数”才能发挥功能。这里的“内容填充函数”是通过
        /// 属性“FillRowMethodName”属性来指定的。属性“TableDefinition”用来定义返回表格的格式。
        /// 
        [SqlFunction(TableDefinition = "tmp_value nvarchar(max)", FillRowMethodName = "FillRow")]
        public static IEnumerable PrintOneToTen()
        {
            IList<string> result2 = new List<string>();
            var matches = new string[]{
                                          "one",
                                          "two",
                                          "three",
                                          "four",
                                          "five",
                                          "six",
                                          "seven",
                                          "eight",
                                          "nine",
                                          "ten"
                                     };

            return matches.AsEnumerable();
        }

        public static void FillRow(object obj, out SqlString tmp)
        {
            tmp = new SqlString(obj.ToString());
        }
    }


 

部署步骤
1.编译项目，获取生成的DLL文件。

2.在数据库中输入命令：
sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。

3.输入命令:
create assembly chapter34_UDT from 'c:\chapter34_UDT.dll' 注册程序集
--如果上述步骤已经做了就忽略
4.输入命令：
create function SampleTableValueFunction() returns table(tmp_value nvarchar(max) null)
as
external name chapter34_UDT.SampleTableValueFunction.PrintOneToTen


 

执行结果

 

实现触发器

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;


    public class SampleTrigger
    {
        public SampleTrigger()
        { 
        
        }
        [SqlTrigger(Event = "For Insert,Update,Delete", Name = "PrintInfo", Target = "Student")]
        public static void PrintInfo()
        {
            SqlTriggerContext triggerContext = SqlContext.TriggerContext;
            SqlConnection conn = new SqlConnection("Context connection=true");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
           
            switch (triggerContext.TriggerAction)
            { 
                case TriggerAction.Insert:
                    cmd.CommandText = "insert into StudentDetail values('insert operation!')";
                    break;
                case TriggerAction.Delete:
                    cmd.CommandText = "insert into StudentDetail values('delete operation!')";
                    break;
                case TriggerAction.Update:
                    cmd.CommandText = "insert into StudentDetail values('update operation!')";
                    break;
                default:
                    break;
            }

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }
        }

        [SqlTrigger(Name="InsertSomething",Target="chapter30.dbo.Student",Event="FOR INSERT")]
        public static void InsertSomething()
        {
            SqlTriggerContext triggerContext = SqlContext.TriggerContext;
            if (triggerContext.TriggerAction == TriggerAction.Insert)
            {
                var conn = new SqlConnection("Context connection=true");
                var cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Insert into StudentDetail values('insert event')";
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

 

部署步骤
1.编译项目，获取生成的DLL文件。

2.在数据库中输入命令：
sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。

3.输入命令:
create assembly chapter34_UDT from 'c:\chapter34_UDT.dll' 注册程序集
--如果上述步骤已经做了就忽略
4.输入命令：
--注册触发器
create trigger PrintSomething on Student
for insert,update,delete
as
external name  chapter34_UDT.SampleTrigger.PrintInfo

执行结果
输入命令：
insert into Student values(12345,'tmp','11','11')
update Student set Name='new'+Name where Id=12345
delete from Student where Id=12345

select * from StudentDetail


 

实现聚合函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;



    [Serializable]
    [SqlUserDefinedAggregate(Format.Native)]
    public struct SampleSum
    {
        private int sum;
        public void Init()
        {
            sum = 0;
        }

        public void Accumulate(SqlInt32 Value)
        {
            sum += Value.Value;
        }

        public void Merge(SampleSum Group)
        {
            sum += Group.sum;
        }

        public SqlInt32 Terminate()
        {
            return new SqlInt32(sum);
        }

    }

部署步骤

 

1.编译项目，获取生成的DLL文件。

2.在数据库中输入命令：
sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。

3.输入命令:
create assembly chapter34_UDT from 'c:\chapter34_UDT.dll' 注册程序集
--如果上述步骤已经做了就忽略
4.输入命令：
--注册聚合函数
create aggregate SampleSum(@value int) returns int
external name [chapter34_UDT].SampleSum
执行结果

 

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


 

 

 

实现类型

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data;
using System.Data.SqlClient;


    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
    public struct Facade : INullable
    {
        public bool isNull;
        int hairColor;
        int tall;
        int skin;
        int country;

        public Facade(int hairColor, int tall, int skin, int country)
        {
            isNull = false;
            this.hairColor = hairColor;
            this.tall = tall;
            this.skin = skin;
            this.country = country;
        }

        public static Facade Null
        {
            get
            {
                return new Facade { isNull = true };
            }
        }

        public override string ToString()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("{0};", hairColor);
            sb.AppendFormat("{0};", tall);
            sb.AppendFormat("{0};", skin);
            sb.AppendFormat("{0}", country);
            return sb.ToString();

        }



        public static Facade Parse(SqlString data)
        {
            if (data.IsNull)
            {
                return new Facade { isNull = true };
            }

            Facade result;
            string[] tmpData = data.Value.Split(';');
            result = new Facade(int.Parse(tmpData[0]), int.Parse(tmpData[1]), int.Parse(tmpData[2]), int.Parse(tmpData[3]));

            return result;
        }


        public bool IsNull
        {
            get { return isNull; }
        }
    }

部署步骤

 

1.编译项目，获取生成的DLL文件。

2.在数据库中输入命令：
sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。

3.输入命令:
create assembly chapter34_UDT from 'c:\chapter34_UDT.dll' 注册程序集
--如果上述步骤已经做了就忽略
4.输入命令：
create type Facade external name
[chapter34_UDT].Facade


执行结果

 

 

 

小结

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

 

 

 

 

 

 

 

代码下载

 本章使用的数据库脚本

 

 

 

 

 

 

 

 

 

 

Jeff Xiong
关注
  • 3
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 6
    评论
专栏目录
microsoft system clr types for sql server 2014
10-19
microsoft system clr types for sql server 2014 VS2017 开发报表时使用。
Microsoft CLR
LinuxBoy1985
06-19 792
Common Language Runtime 公共语言运行时. 简单的理解，就是一个翻译，把.NET的语言翻译为机器可以执行的语言。 首先，先由c#编译器，把c#编译成托管代码（包括IL），然后再由clr中的JIT去编译成机器语言来执行。
如何提高代码可读性、可维护性
热门推荐
zm1_1zm
07-21 2万+
高质量代码的三大要素： 可读性、可维护性和可变更性 做好代码规范、提高代码质量，能显著增强代码的可读性、可维护性和可变更性。努力提高代码的读写可维护性，是做好代码规范的必要非充分条件。代码规范和架构设计是软件的灵魂所在，代码质量偏低，就像是人失去了三魂七魄中的一魄，就会丧失活力，影响正常运行，增加软件交付后维护成本，出现推迟完成、超出预算、特性缺失等现象。 任何语言都需要强调编码风格的一致性
微软说明文档CLR
10-02
微软说明文档CLR,详实的介绍
提高你的数据库编程效率_Microsoft CLR Via Sql Server
03-12
提高你的数据库编程效率_Microsoft CLR Via Sql Server 文章中的数据库脚本。详细情况请参阅博客：http://blog.csdn.net/ghostbear
Microsoft System CLR Types for Microsoft SQL Server 2014
03-27
"Microsoft System CLR Types for Microsoft SQL Server 2014" 是一个重要的组件，它包含了运行SQL Server 2014所需的一系列公共语言运行时（Common Language Runtime, CLR）类型。这些类型使得开发者能够使用.NET ...
microsoft system clr types for sql server 2012.7z
03-03
标题中的“microsoft system clr types for sql server 2012.7z”指的是微软为SQL Server 2012发布的Microsoft System CLR Types组件的压缩包。System CLR Types，全称为Common Language Runtime (CLR) Types，是...
Microsoft sql server system clr types 2012
03-31
标题“Microsoft SQL Server System CLR Types 2012”指的是微软SQL Server数据库管理系统的一个关键组件，主要用于处理与.NET Framework交互的系统级CLR（Common Language Runtime）类型。在SQL Server中，CLR集成...
探讨SQLServer2005.NETCLR编程
02-04
北京火龙果软件工程技术中心本文将解释如何在SQLServer2005中以尽可能最简单的方法创建基于.NET的CLR子例程。本文主要针对在服务器端使用SQLServer2005的.NET开发者。一.创建SQLServer2005数据库这一节主要讨论创建...
tsql 和 clr 的性能实测比对
weixin_30409849的博客
10-31 89
这两天一直被一个问题困扰, 就是到底是tsql的性能好, 还是 CLR user-defined function 的性能好. MSDN上是这么说的, Microsoft对 CLR和sql 的集成做了很多优化, 所以, CLR user-defined function 的性能要好于 常规的 user defined function, 而且是好不少. 不过也有人说, 还是应当尽量使用TSQL. ...
.Net插件编程模型：MEF和MAF
GhostHouse
03-08 1万+
.Net插件编程模型：MEF和MAF       MEF和MAF都是C#下的插件编程框架，我们通过它们只需简单的配置下源代码就能轻松的实现插件编程概念，设计出可扩展的程序。这真是件美妙的事情！ MEF（Managed Extensibility Framework）             MEF的工作原理大概是这样的：首先定义一个接口，用这个接口来约束插件需要具备的职责；然后在实现接口的程序方法
.NET下的加密解密大全（3）：非对称加密
GhostHouse
03-17 1万+
本博文列出了.NET下常用的非对称加密算法，并将它们制作成小DEMO，希望能对大家有所帮助。RSA /* 作者：GhostBear 博客地址：Http://blog.csdn.net/ghostbear */ static string EnRSA(string data,string publickey)
c#中的线程，任务，并发编程
GhostHouse
03-10 8943
线程，任务，并发编程异步委托异步调用有返回值的委托和没返回值的委托。 异步调用无返回值的委托   PrintSomething printFruit = (d) => { Thread.Sleep(3000); Console.WriteLine("\n\ryou input this fruit:"+d); };
.NET下的加密解密大全（2）：对称加密
GhostHouse
03-16 8532
本博文列出了.NET下常用的对称加密算法，并将它们制作成小DEMO，希望能对大家有所帮助。 公共代码 static byte[] CreateKey(int num) { byte[] result = new byte[num]; Random rand = new Random();
.NET下的加密解密大全（1）:哈希加密
GhostHouse
03-15 5429
.NET有丰富的加密解密API库供我们使用，本博文总结了.NET下的Hash散列算法，并制作成简单的DEMO，希望能对大家有所帮助。     MD5/*      作者：GhostBear  *   博客地址：Http://blog.csdn.net/ghostbera  */ using System; using System.Collections.Generic; using
.Net中处理XML的常用API
GhostHouse
03-09 4557
.Net中处理XML的常用API    .Net对XML提供了丰富的API，但大多数都在日常开发中涉及不到。先总结下一些使用频繁的API与大家分享。测试数据bookstore.xml 十万个为什么 30 为什么先生 火星出版社 2012年12月12日 客官不要嘛
多核编程深度解析：CLR via C# 第四版
"CLR via C# 4th edition" 《CLR via C# 4th edition》是Jeffrey Richter所著的一本深入解析.NET Common Language Runtime (CLR) 的经典书籍，针对的是C#语言的第四版。这本书全面覆盖了多核编程技术，并提供了对...
评论 6
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值