在SQL Server2005中使用.Net代码-技术开发专区

在SQL Server2005中使用.Net代码

作者：IT168 微山编辑：胡铭娅 2008-01-14 00:00

介绍

    首先要感谢微软的开发小组，因为大家可以利用他们高超而创新的技术来在Ms SQL Server 2005存储过程任何支持语言的.Net中编写受控代码了。简单地说，可以调用SQL Server 存储过程中的.net类库中的任何函数了。这项技术使我们可以使用.net语言所具有的特征—扩展性。

描述

   我从codeproject中的SQL Server获得了一篇关于使用MQSM的文章，作者是一位专门致力于MQSM和SQL SP中的Messaging的先生。我设法暴露使用SQL server 的汇编和结构的限制性和有限性，所以以后开发者就可以致力于这方面的工作。我将和大家分享我在实际执行时所碰到的问题和经验。

   在开始之前，你得使SQL Server执行属性为工作状态(能够执行受控代码)，因为在默认的状态下它是不工作的。使其处于SQL Server查询编辑器中的查询状态并能执行如下代码。

sp_configure 'clr enable', 1 
GO

现在，服务器就开始在runtime的状态下运行受控代码。但是知道这不是什么魔术，而是我们在.net构架中用到的一样的技术。这里只有一点非常重要，那就是SQL Server也可以通过CLR执行代码了，这是不是很伟大？在写这篇文章之前做个很多的关于这方面的调查，发现很多资料都表达了相同的观点。但是我碰到了很多的困难，因为资料太零散了。在这篇文章尽量仿照整个过程重新叙述一遍。我做这项研究是有明确的要求。我一直使用微软的C# .net中的 Message Queuing Service。但一段时间以后，我收到一个命令，要求我从存储过程发一条信息到MSMQ Queue.中去。到那时为止，对于我来说，很有可能使用系统。这样，只用.net runtime的名空间发送信息。我相信大多数开发人员很想知道MSMQ是什么东西。不要担心我会马上进入细节。我将首先从一个简单的应用的实例着手，那就是利用一个简单的字符串返回C# .net类库的函数。让我们从类库着手吧！

启动.net 2005 studio。打开一个新的类库。默认时，class自动生成名为Class1.cs，重新命名它为BaseFunctionClass.cs，类中创建一个如下的代码：

Collapse 
using System; 
using System.Collections.Generic; 
using System.Text; 
using System.IO; 
using System.Data.SqlTypes; 
using System.Data.SqlClient; 
using Microsoft.SqlServer.Server; 

namespace ManagedCodeAndSQLServer 
{ 
public class BaseFunctionClass 
{ 
#region "Default Constructor" 
public BaseFunctionClass() 
{ 
#endregion 
} 
} 

} 
#endregion 

#region "Welcome Function" 
[SqlProcedure] 
public static void GetMessage(SqlString strName, out SqlString strMessge) 
{ 
strMessge = "Welcome," + strName + ", " + "your code is getting executed under CLR !"; 
}

重点

    希望从上面的代码中发现一点新的东西。首先所有的[SqlProcedure]是附加于GetMessage功函数上的，这暗示着CLR可以从SQL Server存储过程中调用函数。我要告诉你的是我自己的一些体会和经验。这些都是你不可能在出版的刊物和互联网上看到的。你必须记住，在调用CLR某一个函数时，SQL server nvarchar等同于System.String。但最重要一点是CLR使程序只能返回Int32, Int和无效数据类型。那就是我为什么使用GetMessage函数的输出参数了。我也用了SqlString代替System.String。请把上面所有的东西给牢牢记住。

    现在，我们来创建工程并且以后不去理会它了，因为从.net类库的角度来看，你的工作已经结束了。现在，我们再来看看SQL Server。我希望你还记着，在文章的开头我们能在服务器中使CLR集成。

    在数据库中，大家应把ManagedCode（受控代码）和SQLServer.dl注册一个集，为了注册这个集，你应在数据库中拥有主动权或者你应成为此地的系统和服务器的管理者。这个将要注册的集应在不安全的方式下注册。否则，它们将无法使源接近SQL Server 2005外部。还有，为了注册一个不安全的集，你应该能在你的角色或用户名里行使你的“不安全”的权利。以上所讲到的都是非常重要的，你应多加注意，否则就无法注册集。接下来，创建一个名为TestingCLR的数据库。在注册数据库中的.net集之前，你必须使用如下代码设置值得信赖的数据库选项。

ALTER DATABASE TestingCLR SET TRUSTWORTHY ON 
GO

现在使用如下代码来注册ManagedCodeAndSQLServer.dll。

CREATE ASSEMBLY HelloDotNet 
AUTHORIZATION dbo 
FROM 'E:\Important\SQL\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.dll' 
WITH PERMISSION_SET = UNSAFE 
GO

我在重申一遍，dbo要是没有“不安全集的”权利，它是不会运行的。同样，如果我要在dll中使用.net某个特定的类的话，我必须以同样的方法在注册dll之前注册。例如，如果集中使用System.Messaging，将使用如下代码来注册：

CREATE ASSEMBLY Messaging 
AUTHORIZATION dbo 
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' 
WITH PERMISSION_SET = UNSAFE 
GO

在资源管理器中，不必为了使用正常的泛函的特性而去注册dll，到TestingCLR database中打开Programmability选项，然后你将会在程序集中发现有一个已经用i.e. "HelloDotNet给定的名注册的集。到现在为止，做的已经差不多了。你必须知道你将要从集中调用到的函数的整个途径。在本文的例子中：ManagedCodeAndSQLServer.BaseFunctionClass 和 GetMessage都是我们要调用到的函数。现在，我用下面的程序集来创建一个简单的存储过程。

CREATE PROCEDURE usp_UseHelloDotNetAssembly 
@name nvarchar(200), 
@msg nvarchar(MAX)OUTPUT 
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage 
GO

运行如下程序：

DECLARE @msg varchar(MAX) 
EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output 
PRINT @msg

接着，会输出这样的信息：Welcome,Kittu And Tannu, your code is getting executed under CLR !

尝试编写这样的程序，你会乐在其中。祝编程愉快。

关注我们