sql clr_2015年SQL CLR .Net集成（年份不是产品版本）

sql clr

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still read this article from 

很久以前（2011年5月），我写了一篇文章，向您展示如何使用要在SQL Server 2005中托管的Visual Studio 2005创建DLL。该设置当时是有效的，如果仍在使用这些功能，则仍然有效。版本。 您仍然可以从阅读本文

here. 在这里 。

Creating a DLL to be hosted in SQL Server let you run code directly in the database. You may think of it as a Stored Procedure written in VB or C#. it might be helpful to prevent doing large processing on the client after having transited a lot of data on the network wire.

通过创建要在SQL Server中托管的DLL，您可以直接在数据库中运行代码。 您可能会认为它是用VB或C＃编写的存储过程。 在网络线上传输大量数据后，防止对客户端进行大量处理可能会有所帮助。

Lately, I was reviewing training material on that topic to see if it was still accurate and found out that many things have changed since then. We are now using Visual Studio 2013 (or 2015 in CTP) and SQL Server 2014.

最近，我正在查看有关该主题的培训材料，以查看它是否仍然准确，并发现此后许多事情已经改变。 我们现在使用的是Visual Studio 2013（或CTP中的2015）和SQL Server 2014。

可下载的代码 (
Downloadable code)

This month code is available in both VB and C#. The solution has been created using Visual Studio 2105 CTP 5 but would also work the same way if you are using VS 2013.

VB和C＃中都提供了本月的代码。 该解决方案是使用Visual Studio 2105 CTP 5创建的，但是如果您使用VS 2013，也可以以相同的方式工作。

You will also need Microsoft SQL Server. I have used SQL Server 2014 but should also work with SQL Server 2012.

您还将需要Microsoft SQL Server。 我使用过SQL Server 2014，但也应使用SQL Server 2012。

You will also need to install Microsoft SQL Server Data Tools (aka SSDT) which are tools added to Visual Studio to provide a better interaction between the 2 products. If you don’t already have it, you can get it from https://msdn.microsoft.com/en-us/data/hh297027. SSDT is also installable right from within Visual Studio. The code can be downloaded from here.

您还需要安装Microsoft SQL Server数据工具（又名SSDT），这是添加到Visual Studio中的工具，以提供两种产品之间的更好交互。 如果尚未安装，则可以从https://msdn.microsoft.com获得 / zh-TW / dat a / hh297027 。 SSDT也可以直接在Visual Studio中安装。 可以从这里下载代码。

()

Simple method

 

简单的方法

We will start by creating a very simple method that adds 2 digits together just to get our feet wet with the process and we will build up from there on a real re-usable CLR procedures.

我们将从创建一个非常简单的方法开始，将两个数字加起来只是为了弄清该过程，然后我们将在此基础上建立一个真正可重复使用的CLR程序。

So start your Visual Studio 2013 or 2015 (with SSDT installed) and create a new project. But not just any kind of project, a “SQL Server Database Project” that you will find under the SQL Server templates folder. As opposed to any other projects you created, there is no template letting you choose VB or C#. At this point, you are just creating a database project.

因此，启动Visual Studio 2013或2015（安装了SSDT）并创建一个新项目。 但不仅仅是任何类型的项目，您都可以在SQL Server模板文件夹下找到“ SQL Server数据库项目”。 与您创建的任何其他项目相反，没有模板可让您选择VB或C＃。 此时，您只是在创建一个数据库项目。

Figure 1: Creating a database project 

图1：创建数据库项目

Before going further with this project, we need set some of the project properties. So open the project’s properties and go to the project Settings tab. The most important setting on that tab is the target platform. Ensure you are targeting the version of SQL Server you will be running your code from. You have a wide range of version from 2005 to 2014 and even Azure.  

在继续进行此项目之前，我们需要设置一些项目属性。 因此，打开项目的属性，然后转到项目的“设置”选项卡。 该选项卡上最重要的设置是目标平台。 确保您以将要运行代码SQL Server为目标。 从2005到2014，甚至还有Azure，您都有各种各样的版本。

Figure 2: Ensuring the targeted platform 

图2：确保目标平台

Now that we have the database version set properly, you can open the SQLCLR tab. There are 2 important settings here. The first one is the Target framework which you need to set to the version installed on your server. Any version from the .Net framework 2.0 up to the most current one are available.

现在我们已经正确设置了数据库版本，您可以打开“ SQLCLR”选项卡。 这里有2个重要设置。 第一个是目标框架，您需要将其设置为服务器上安装的版本。 从.Net framework 2.0到最新版本的任何版本均可用。

The second important property that you can set here is the language. By default, a new project will be created to use C# but you can change it to Visual Basic here. I strongly suggest that you change it right now if it is your intent to use VB.  

您可以在此处设置的第二个重要属性是语言。 默认情况下，将创建一个新项目以使用C＃，但是您可以在此处将其更改为Visual Basic。 如果您打算使用VB，我强烈建议您立即进行更改。

Figure 3: Setting the Language 

图3：设置语言

We have now customized the most important properties and are ready to add our little function.

现在，我们已经定制了最重要的属性，并准备添加我们的小功能。

Lets now create a new function. Right-click the project, select “Add” and then “New item…”.  

现在让我们创建一个新函数。 右键单击项目，选择“添加”，然后选择“新建项目…”。

Figure 4: Adding a new item 

图4：添加一个新项目

From the “Add New Item” dialog, be sure to select “SQL CLR [VB|C#]” (depending on the language you selected in the properties) from the left pane. Then select the type of object you want to create. For my demo, I have selected a User Defined Function. This is also the place where you should set the name of your new class.

在“添加新项”对话框中，确保从左窗格中选择“ SQL CLR [VB | C＃]”（取决于在属性中选择的语言）。 然后选择您要创建的对象的类型。 对于我的演示，我选择了一个用户定义函数。 这也是您应设置新班级名称的地方。

Figure 5: Adding a new UDF to the project 

图5：向项目添加新的UDF

After you clicked the Add button, a new class is added to your project with a sample function returning a string. I have replaced this sample method with this code:  

单击“添加”按钮后，新类将通过返回字符串的示例函数添加到您的项目中。 我已将以下示例方法替换为以下代码：

<Microsoft.SqlServer.Server.SqlFunction()>
Public Shared Function EM_Add(p1 As SqlInt32, p2 As SqlInt32) As SqlInt32
	Return p1.Value + p2.Value
End Function

You will observe 2 things. The first thing is the attribute indicating that this method is special, it is a SqlFunction. The second thing is the data type used. We are using SQL types instead of .Net native types.

您将观察到2件事。 首先是属性，指示该方法很特殊，它是一个SqlFunction。 第二件事是使用的数据类型。 我们使用SQL类型而不是.Net本机类型。

In my demo project, I have also created in the same class, a second method using the same template called EM_Sub in which I have changed operation from a + to a -. 

在演示项目中，我还使用相同的类创建了第二个方法，该方法使用相同的模板EM_Sub，其中将操作从+更改为-。

将其部署到数据库 (Deploying it to the database)

Now build your project to ensure your don’t have issues. When you will have your project building, right-click your project and select Publish.  

现在构建您的项目，以确保您没有问题。 建立项目后，请右键单击您的项目，然后选择“发布”。

Figure 6: Publishing the project 

图6：发布项目

This dialog will show up but the target database connection will be empty. You can type the connection string that fit your environment or you can click the Edit button to have another dialog letting you enter the values that generates the connection string. You can click the Publish button to have your new database created (named DemoSqlClr as per the publish dialog) and your project gets deployed.

将显示此对话框，但目标数据库连接为空。 您可以键入适合您的环境的连接字符串，也可以单击“编辑”按钮以显示另一个对话框，让您输入生成连接字符串的值。 您可以单击“发布”按钮以创建新数据库（根据“发布”对话框命名为DemoSqlClr），然后部署项目。

If everything went well, you will get the results shown in figure 7.

如果一切顺利，您将得到如图7所示的结果。

Figure 7: Project deployed 

图7：部署的项目

At this point, your database has been created and your new functions are ready to be used. If you now open SQL Server Management Studio (SSMS), you will find your new database. 

至此，您的数据库已创建完毕，您可以使用新功能了。 如果现在打开SQL Server Management Studio（SSMS），则将找到新数据库。

Figure 8: Your new database from SSMS 

图8：来自SSMS的新数据库

If you start drilling into it and open the Programmability folder, you will find a name you should be familiar with (DemoSqlClr in my case) and your new functions under the Scalar-value Functions folder.

如果您开始钻研它并打开Programmability文件夹，则会在Scalar-value Functions文件夹下找到一个应该熟悉的名称（在我的情况下为DemoSqlClr）和新函数。

You might be tempted to right-click one of your function and select Modify. You can do it but don’t expect to see VB (or C#) code in there. Instead, you will find this code:  

您可能会想右键单击您的函数之一，然后选择“修改”。 您可以执行此操作，但是不要期望在那里看到VB（或C＃）代码。 相反，您将找到以下代码：

ALTER FUNCTION [dbo].[EM_Add](@p1 [int], @p2 [int])
RETURNS [int] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [DemoSqlClr].[DemoSqlClr.UserDefinedFunctions].[EM_Add]

What does this code means? This code acts like a proxy (or a pointer) to another method using that signature found in the name following the EXTERNAL NAME. That means that whenever you will call this SQL Scalar-value function, the external .Net code will gets called.

此代码是什么意思？ 此代码就像使用另一个签名（在外部名称后面的名称中找到的）的另一个方法的代理（或指针）一样。 这意味着无论何时调用此SQL标量值函数，都会调用外部.Net代码。

To try it, from a query window in SSMS, you can enter a queries like these: 

要尝试，请从SSMS的查询窗口中输入以下查询：

SELECT dbo.EM_Add(5,13)
SELECT dbo.EM_Sub(22,14)

If it is the first time ever you are trying to run CLR code on your server, you might get an error.

如果这是您第一次尝试在服务器上运行CLR代码，则可能会收到错误消息。

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

This is because CLR is off by default on SQL Server. To enable it, just run these statements from SSMS: 

这是因为默认情况下，在SQL Server上CLR是关闭的。 要启用它，只需从SSMS运行以下语句：

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE 

()

Debugging the method

调试方法

You might now want to debug your .Net code. I doubt this simple method has an error but if your method is not as trivial as mine, chances are that a debugging session would be helpful. We cannot just run into code like this, we need to create a SQL query that will call our new function.

您现在可能要调试.Net代码。 我怀疑这个简单的方法有错误，但是如果您的方法不像我的那么简单，那么调试会话可能会有所帮助。 我们不能只是碰到这样的代码，我们需要创建一个SQL查询来调用我们的新函数。

Go back to your Visual Studio project, right-click on your project, click Add and New Item … (as shown in figure 4). This time, select the User Scripts tab and select “Script (Not in build)” item.  

返回到Visual Studio项目，右键单击您的项目，单击Add and New Item…（如图4所示）。 这次，选择“用户脚本”选项卡，然后选择“脚本（不在构建中）”项。

Figure 9: Adding test script 

图9：添加测试脚本

That will generate a new empty .sql file. You can enter queries in that file just like we did in SSMS. For example: 

这将生成一个新的空.sql文件。 您可以像在SSMS中一样在该文件中输入查询。 例如：

select dbo.EM_Add(5,13)

You might get used to hit F5 at this point but don’t. Hitting F5 will just deploy your project to the server, it won’t run the query. Instead, you need to hit CTRL-SHIFT-E or hit the green arrow from the top left of you query editor. 

此时您可能已经习惯击中F5了，但是还没有。 点击F5只会将您的项目部署到服务器，它将不会运行查询。 相反，您需要按CTRL-SHIFT-E或单击查询编辑器左上方的绿色箭头。

Figure 10: Executing a query from Visual Studio 

图10：从Visual Studio执行查询

After it has been executed correctly, results will show up just underneath. This is not really what I call debugging! Just to the right of green arrow, there is a small arrow meaning that this command is in fact a drop down of commands: 

正确执行后，结果将显示在下面。 这真的不是我所说的调试！ 在绿色箭头的右边，有一个小箭头，表示该命令实际上是命令的下拉列表：

Figure 11: More commands available 

图11：更多可用命令

If you pick “Execute With Debugger”, you should be entered to the real debugging mode you are used to from which you can hit F11 until you get in your .Net code. Notice that most .Net debugging are fully working (Tooltips, Call stack, Locals, Watch Windows, …). Some features like setting the next line to be executed are not working. Edit-and-continue is also not working. 

如果选择“ Execute with Debugger”，则应进入习惯的调试模式，在此模式下您可以按F11键，直到获得.Net代码为止。 请注意，大多数.Net调试都可以正常运行（工具提示，调用堆栈，本地，观察Windows等）。 某些功能（如设置要执行的下一行）不起作用。 编辑并继续操作也不起作用。

()

Adding new functions

增加新功能

After you have created your first version of your assemble, you will probably find out more features to put into it. This is quite simple. Just continue adding methods to your class (or create a new class in the same project) and re-publish it just like we did before (see figure 6). 

创建程序集的第一个版本后，您可能会发现更多功能。 这很简单。 只需继续向您的类添加方法（或在同一项目中创建一个新类），然后像以前一样重新发布它即可（请参见图6）。

()

Deploying to the production SQL Server

部署到生产SQL Server

Chances are that you cannot connect to your SQL Server using Visual Studio just like we did here. Your DBA might not really to copy DLLs to the server.

可能无法像我们在此处那样使用Visual Studio连接到SQL Server。 您的DBA可能不会真正将DLL复制到服务器。

What we’ve done here on a test server can all be scripted. You first need to create a script of the assembly itself as shown in figure 12. 

我们在测试服务器上所做的一切都可以编写成脚本。 首先，您需要创建程序集本身的脚本，如图12所示。

Figure 12: Scripting the assembly 

图12：编写程序集脚本

You also need to create scripts for each functions you created.

您还需要为创建的每个功能创建脚本。

You finally need to append all the scripts (assembly and each function) to a big script that you add to your deployment scripts just like any database update scripts. Remember that you might have to enable the CLR on the production server as well. 

最后，您需要像所有数据库更新脚本一样，将所有脚本（程序集和每个函数）附加到添加到部署脚本中的大脚本中。 请记住，您可能还必须在生产服务器上启用CLR。

()

Replacing existing SQL features

替换现有SQL功能

I have always been one of those saying that, if the feature exists natively in SQL, you should never rewrite it. Lately, I was proven wrong on a specific feature. It is probably due to the fact that XML is used and this usage of XML is very not optimized. I would never rewrite simple T-SQL features but I might do some performance tests when dealing with more complex features.

我一直都是这样说的人之一：如果该功能固有地存在于SQL中，则永远不要重写它。 最近，我被证明在特定功能上是错误的。 可能是由于使用了XML的事实，并且XML的这种用法没有得到优化。 我永远不会重写简单的T-SQL功能，但在处理更复杂的功能时可能会做一些性能测试。

Microsoft documented some advantages of using SQL-CLR integration here. 

Microsoft 在此处记录了使用SQL-CLR集成的一些优点。

()

Concatenating strings by group

按组串联字符串

This is something we often have to do. We want to concatenate in a single string all the values for a group. Using the STUFF T-SQL function and a bit of XML, you can achieve that. I even blogged about it here.

这是我们经常要做的事情。 我们想将一个组的所有值连接在一个字符串中。 使用STUFF T-SQL函数和一些XML，您可以实现这一目标。 我什至在这里写过博客。

Somebody pointed me out to a CLR method somebody created and publicly available on CodePlex. I first argue that the STUFF T-SQL feature was surely faster be I was told that it was surprisingly fast. I was not convinced! I created a test database with a table of 1,000,000 rows and created to queries, one with STUFF and another one with the CLR function. I was surprised to find out that the CLR was running quite faster (3 seconds compared to 9 seconds for the STUFF method).

有人向我指出了有人创建并在CodePlex上公开可用的CLR方法。 首先，我被告知STUFF T-SQL功能肯定更快，因为有人告诉我它非常快。 我不服气！ 我创建了一个包含1,000,000行表的测试数据库，并创建了要查询的数据库，其中一个数据库使用STUFF，另一个数据库使用CLR函数。 我惊讶地发现CLR的运行速度相当快（3秒，而STUFF方法为9秒）。

So I have first apologize. Now I will setup a test database to compare both methods.

所以我首先道歉。 现在，我将建立一个测试数据库来比较这两种方法。

()

Conclusion

结论

Over the years things are changing a bit but good stuff stick around. This article is an example. Using older versions of software, you were able to do it but this feature is now still working a bit differently.

多年来，情况有所变化，但好东西仍然存在。 本文是一个示例。 使用较旧版本的软件，您可以执行此操作，但是此功能现在仍会有所不同。

I really think it was worth revisiting this article 5 years later because being able to run .Net code in a database is still a very valid solution especially that it can give you a performance boost in some scenario!

我真的认为5年后值得回顾本文，因为能够在数据库中运行.Net代码仍然是一个非常有效的解决方案，尤其是在某些情况下可以提高性能！

翻译自: https://www.experts-exchange.com/articles/18299/SQL-CLR-Net-Integration-in-2015-year-not-product-version.html

sql clr