首页 > 代码库 > 提高你的数据库编程效率:Microsoft CLR Via Sql Server
提高你的数据库编程效率:Microsoft CLR Via Sql Server
你还在为数据库编程而抓狂吗?那些恶心的脚本拼接,低效的脚本调试的日子将会与我们越来越远啦。现在我们能用支持.NET的语言来开发数据库中的对象,如:存储过程,函数,触发器,集合函数已及复杂的类型。看到这些你还能淡定吗?哈哈,不仅仅是这些。那些能被.NET支持的第三方扩展通过该技术统统都能应用在数据库编程上,如:正则表达式,.NET庞大的加密解密库,以及各种.NET集成的排序和搜索算法。
下面我就来一一介绍怎么使用该技术来解放我们的双手!
实现存储过程
[csharp] view plaincopyprint?
- 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=http://www.mamicode.com/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
- {
- }
- }
- };
部署步骤
[sql] view plaincopyprint?
- 1.编译项目,获取生成的DLL文件。
- 2.在数据库中输入命令sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。
- 3.输入命令:create assembly chapter34_UDT from ‘c:\chapter34_UDT.dll‘ 注册程序集
- 4.输入命令:
- <p>--注册存储过程
- create procedure PrintStudentDetail
- as
- external name chapter34_UDT.SampleStoreProcedure.PrintStudentDetail</p><p>--注册带参数的存储过程
- create procedure GetStudentDetail
- (
- @Id int
- )
- as
- external name chapter34_UDT.SampleStoreProcedure.GetStudentDetail</p>
执行结果
[sql] view plaincopyprint?
- exec PrintStudentDetail
- exec GetStudentDetail 1
存储过程PrintStudentDetail执行结果
存储过程GetStudentDetail执行的结果
实现函数
[csharp] view plaincopyprint?
- 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);
- }
- }
部署步骤
[sql] view plaincopyprint?
- 1.编译项目,获取生成的DLL文件。
- 2.在数据库中输入命令:
- sp_configure [clr enabled],1 reconfigure --开启对程序集的信任权限。
- 3.输入命令:
- create assembly chapter34_UDT from ‘c:\chapter34_UDT.dll‘ 注册程序集
- --如果上述步骤已经做了就忽略
- <p>4.输入命令:</p>
- --注册函数
- create function HashSomeThing(@data nvarchar) returns nvarchar
- as
- external name chapter34_UDT.SampleFunction.[Hash]
执行结果
[sql] view plaincopyprint?
- <p>输入调用命令:</p>select dbo.HashSomeThing(name) from Student
实现表值函数
[csharp] view plaincopyprint?
- 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());
- }
- }
部署步骤
[sql] view plaincopyprint?
- 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
执行结果
实现触发器
[csharp] view plaincopyprint?
- 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();
- }
- }
- }
部署步骤
[sql] view plaincopyprint?
- 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
执行结果
[sql] view plaincopyprint?
- 输入命令:
- 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
实现聚合函数
[csharp] view plaincopyprint?
- 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);
- }
- }
部署步骤
[sql] view plaincopyprint?
- 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
执行结果
[sql] view plaincopyprint?
- 输入命令:
- select dbo.SampleSum(TAggregate) from TAggregate
- select Sum(TAggregate) from TAggregate
实现类型
[csharp] view plaincopyprint?
- 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; }
- }
- }
部署步骤
[sql] view plaincopyprint?
- 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的脚本编程效率问题,并且这项技术给了我们很大的相信空间。现在我们就来用有限的手段实现无限的可能吧!
reference from : http://blog.csdn.net/ghostbear/article/details/7333189
提高你的数据库编程效率:Microsoft CLR Via Sql Server
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。