生成clr库_如何使用CLR存储过程发送数据库邮件

生成clr库

In this article, I am going to explain how we can send an email from SQL Server express edition using a CLR stored procedure. First, let me explain about the CLR stored procedures.

在本文中，我将解释如何使用CLR存储过程从SQL Server Express Edition发送电子邮件。 首先，让我解释一下CLR存储过程。

什么是CLR存储过程 (What are the CLR Stored procedures)

The CLR is a common language runtime, and the SQL Server stored procedures are a collection of the SQL Queries and the command logic. The stored procedures are compiled and stored in the database. The CLR stored procedures are the combination of the CLR and stored procedure. CLR SPs are the .Net objects which run in the SQL Server memory of the SQL Server database.

CLR是公共语言运行库，而SQL Server存储过程是SQL查询和命令逻辑的集合。 存储过程被编译并存储在数据库中。 CLR存储过程是CLR和存储过程的组合。 CLR SP是运行在SQL Server数据库SQL Server内存中的.Net对象。

Both CLR SPs and extended stored procedures (Stored procedures, functions, and triggers) accesses the system resources and both runs under the database memory. CLR SPs can perform the same tasks that can be performed by the extended stored procedures, but the only difference is that the extended stored procedures are the unmanaged codes and CLR SPs contains the managed code.

CLR SP和扩展存储过程（存储过程，函数和触发器）都访问系统资源，并且两者都在数据库内存下运行。 CLR SP可以执行与扩展存储过程可以执行的相同的任务，但是唯一的区别是扩展存储过程是非托管代码，而CLR SP包含托管代码。

The extended stored procedures use the same process space as a database engine that can affect the performance of the database, but standard stored procedures and functions are considered the best for the data-oriented tasks. CLR SPs are managed objects; hence it runs as per the specification of the common language runtime (CLR). It uses the .NET classes, which makes the implementation of the complex logic, complex string operations iterations and data encryption much easier. It can also replace the complex business logic which is difficult to implement using standard stored procedures or functions. Moreover, CLR SPs are compiled and managed code; hence it gives better performance.

扩展存储过程使用与数据库引擎相同的处理空间，这可能会影响数据库的性能，但是标准存储过程和功能被认为是最佳的面向数据的任务。 CLR SP是托管对象； 因此，它按照公共语言运行库（CLR）的规范运行。 它使用.NET类，这使得复杂逻辑的实现，复杂字符串操作的迭代和数据加密变得更加容易。 它还可以代替使用标准存储过程或功能难以实现的复杂业务逻辑。 此外，CLR SP是经过编译和托管的代码。 因此，它具有更好的性能。

以下是CLR SP的好处： (Following are the benefits of the CLR SPs:)

1. It’s a managed code; it ensures memory management and type safety
   这是一个托管代码； 确保内存管理和类型安全
2. It uses the .Net classes; it is easy to implement the complex logic
   它使用.Net类； 容易实现复杂的逻辑
3. It provides the object-oriented programming capabilities enables polymorphism, encapsulations, and inheritance
   它提供了面向对象的编程功能，可以实现多态性，封装和继承
4. It can be written in any language, supported by the Microsoft.NET framework, thus easy for the programmer to develop it
   它可以用Microsoft.NET框架支持的任何语言编写，从而使程序员易于开发
5. It can be used with the Oracle 10g R2 and later database version
   可以与Oracle 10g R2和更高版本的数据库一起使用

以下是CLR SP的缺点 (The following are the drawbacks of the CLR SPs)

1. It is not useful to execute simple queries. In such cases, the standard stored procedures are a better option
   执行简单查询没有用。 在这种情况下，标准存储过程是一个更好的选择
2. • We cannot drop and create the schema bounded assemblies
     我们不能删除和创建模式绑定程序集
   • Difficulties in changing or alter the file path due to permission issues
     由于权限问题而难以更改或更改文件路径

可以在以下情况下使用CLR SP： (CLR SPs can be used under the following scenarios:)

1. When we want to implement the complex business logic
   当我们想实现复杂的业务逻辑时
2. The application flow is CPU intensive. As I mentioned that the CLR SPs are compiled and managed; hence it gives better performance
   应用程序流占用大量CPU。 如前所述，CLR SP是经过编译和管理的； 因此它提供了更好的性能
3. If you want to perform the tasks that are not possible by using T-SQL. For example, accessing the system resources, implement the cryptography and access web services
   如果要执行使用T-SQL无法完成的任务。 例如，访问系统资源，实施加密和访问Web服务
4. Any operation or task which requires higher safety
   任何需要更高安全性的操作或任务

To create the CLR Store procedures, we must create a Microsoft.Net class library and SQL Server assembly. Considering the topic of the article, I am not going to include detailed information on the .Net class library and SQL Server assemblies. Following gives a basic idea of Microsoft.Net class library and SQL Server assemblies.

若要创建CLR存储过程，我们必须创建一个Microsoft.Net类库和SQL Server程序集。 考虑到本文的主题，我将不包括有关.Net类库和SQL Server程序集的详细信息。 下面给出了Microsoft.Net类库和SQL Server程序集的基本概念。

什么是Microsoft.Net类库？ (What is Microsoft.Net class library?)

Microsoft.Net framework class library is the collection of the various classes, interfaces, namespaces, and values type which can be used in .Net application or program. You can read more about the Microsoft.Net class library here.

Microsoft.Net框架类库是可以在.Net应用程序或程序中使用的各种类，接口，名称空间和值类型的集合。 您可以在此处阅读有关Microsoft.Net类库的更多信息。

什么是SQL Server程序集？ (What are SQL Server assemblies?)

The SQL Server assemblies are the DLL files that are imported in the SQL Server database. Once we import the DLL and register it within the SQL Server stored procedures. The assemblies are a great way to expand the functionality of the native functionalities of the SQL Server. You can read more about the SQL Server assemblies here.

SQL Server程序集是在SQL Server数据库中导入的DLL文件。 导入DLL并将其注册到SQL Server存储过程中之后。 程序集是扩展SQL Server本机功能的一种好方法。 您可以在此处阅读有关SQL Server程序集的更多信息。

示范 (Demonstration)

Now, as I mentioned that to explain the usage of the CLR SPs, we are going to use it to send the email from the SQL Server.

现在，正如我提到的那样，为了解释CLR SP的用法，我们将使用它来从SQL Server发送电子邮件。

As we know, the SQL Server express edition does not provide the database mail feature; we can use the CLR Store procedure to send the email.

众所周知，SQL Server速成版不提供数据库邮件功能。 我们可以使用CLR存储过程来发送电子邮件。

In the demonstration, I am going to create a CLR SP which is going to use to perform the following steps:

在演示中，我将创建一个CLR SP，它将用于执行以下步骤：

1. Create a Microsoft.Net class library. The class library contains the code which sends the email to the specified sender
   创建一个Microsoft.Net类库。 类库包含将电子邮件发送到指定发件人的代码
2. Build the Microsoft.Net class library to generate the dynamic link library (DLL) file
   生成Microsoft.Net类库以生成动态链接库（DLL）文件
3. Create a SQL Server assembly using the DLL file that is generated by the Microsoft.NET class library
   使用Microsoft.NET类库生成的DLL文件创建SQL Server程序集
4. Create a CLR SP using the SQL Server assembly
   使用SQL Server程序集创建CLR SP

创建Microsoft.Net类库 (Create Microsoft.Net class library)

To create a .Net class library, open Visual studio 2017 Click on files Hover on New select “New Project.” See the following image:

若要创建.Net类库，请打开Visual Studio2017。单击“将鼠标悬停在“ 新建 ”上，选择“ 新建项目”。 ”，请参见下图：

In the New Project dialog box, select the Class Library (.NET Framework). In the Name box provide the desired name and in the Location box enter the location of the visual studio project and click OK:

在“ 新建项目”对话框中，选择“ 类库（.NET Framework）”。 在名称框中 提供所需的名称，然后在“ 位置”框中输入Visual Studio项目的位置，然后单击“ 确定” ：

The System.Net and System.Net namespaces contain the classes which can be used to send the email. To send the mail using the class library, we must import the System.Net and System.Net.Mail namespaces in the cs file. To import the namespace, we must use the following code.

System.Net和System.Net命名空间包含可用于发送电子邮件的类。 要使用类库发送邮件，我们必须在cs文件中导入System.Net和System.Net.Mail命名空间。 要导入名称空间，我们必须使用以下代码。

using System.Net;
using System.Net.Mail;

In the SendDatabaseMail namespace, rename the class from Class1 to Sendmail_ClassLib. See the following code:

在SendDatabaseMail命名空间中，将类从Class1重命名为Sendmail_ClassLib 。 请参见以下代码：

namespace SendDatabaseMail
{
    public class SendMail_ClassLib
    {
    }
}

Now, lets create the method named SendEmailUsingCLR() in Sendmail_ClassLib. This method will be used to send the email. Following is the code:

现在，让我们在Sendmail_ClassLib中创建名为SendEmailUsingCLR（）的方法。 此方法将用于发送电子邮件。 以下是代码：

public static void SendEmailUsingCLR()
{
        string _sender = "nisargupadhyay87@outlook.com";
        string _password = "YourPassword";
        string _Receiver = "nisargupadhyay87@gmail.com";
        SmtpClient client = new SmtpClient("smtp-mail.outlook.com");
        client.Port = 587;
        client.DeliveryMethod = SmtpDeliveryMethod.Network;
        client.UseDefaultCredentials = false;
        System.Net.NetworkCredential credentials =
             new System.Net.NetworkCredential(_sender, _password);
        client.EnableSsl = true;
        client.Credentials = credentials;
        try
        {
            var mail = new MailMessage(_sender,_Receiver);
            mail.Subject = "Voila..!! This email has been send using CLR Assembly.";
            mail.Body = "Voila..!! This email has been send using CLR Assembly.";
            client.Send(mail);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            throw ex;
        }
}

In the image below is the entire code:

下图中是完整的代码：

建立课程库 (Build the class library)

Now to build the class library, in the Solution Explorer, right-click on the SendDatabaseMail project and from the context menu, choose the Build command:

现在要构建类库，在解决方案资源管理器中，右键单击SendDatabaseMail项目，然后从上下文菜单中选择Build命令：

Alternatively, you can build the solution by pressing the Ctrl+B shortcut.

或者，您可以通过按Ctrl + B快捷键来构建解决方案。

Once the project is built successfully, the DLL file is generated on the C:\Visual Studio Projects\SendDatabaseMail\SendDatabaseMail\bin\Debug location. See the image:

成功构建项目后，将在C：\ Visual Studio Projects \ SendDatabaseMail \ SendDatabaseMail \ bin \ Debug位置上生成DLL文件。 见图片：

在SQL Server数据库中创建程序集 (Create Assembly in SQL Server database)

Once the class library has been built successfully, and the DLL file is generated, we will use it to create a SQL Server assembly. To do that, open SQL Server Management Studio, connect to the database engine. In Object Explorer, expand the DBA database, under the Programmability folder, right click on the Assemblies folder and from the context menu, choose the New Assembly command:

一旦成功构建了类库并生成了DLL文件，我们将使用它来创建SQL Server程序集。 为此，请打开SQL Server Management Studio，连接到数据库引擎。 在对象资源管理器中，展开DBA数据库， 可编程文件夹下，在组件上的文件夹，然后从上下文菜单中点击右键，选择 新装配命令：

The New Assembly window opens. In the Path to the assembly text box, provide the location of the DLL file, which is generated by building the class library. We want to keep this assembly as unsafe hence, choose Unrestricted from the Permission set drop-down box:

将打开“ 新装配”窗口。 在“ 程序集的路径”文本框中，提供DLL文件的位置，该文​​件是通过构建类库生成的。 我们希望将此程序集保持不安全，因此，从“ 权限集”下拉框中选择“ 不受限制 ”：

Click OK to close the dialog box.

单击“ 确定”关闭对话框。

创建CLR存储过程 (Create CLR Stored procedure)

Once the assembly has been created, we will create a CLR SP. Please note that the EXTERNAL NAME must be set in the following way:

创建装配后，我们将创建CLR SP。 请注意，必须以以下方式设置外部名称：

AssemblyName.[AssemblyName.ClassNameInProgram].MethodNameInProgram

To create a CLR SP, use the code below:

要创建CLR SP，请使用以下代码：

CREATE PROCEDURE spSendEmail       
AS    
EXTERNAL NAME SendDatabaseMail.[SendDatabaseMail.SendMail_ClassLib].SendEmailUsingCLR

Once the CLR SP is created, we must deploy it to use it. For deployment, you must enable the CLR integration on the database to do that, execute the following code:

创建CLR SP后，我们必须部署它才能使用它。 为了进行部署，必须在数据库上启用CLR集成，然后执行以下代码：

use master
go
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO

Once CLR integration is enabled, we must set the database TRUSTWORTHY. To do that, execute the following code:

启用CLR集成后，我们必须将数据库设置为TRUSTWORTHY。 为此，执行以下代码：

use master
go
alter database [DBA] set trustworthy on
GO

发送测试电子邮件 (Send Test email)

Once CLR SP has been created, execute the following code to test it.

创建CLR SP后，请执行以下代码对其进行测试。

use DBA
go
exec spSendEmail

The procedure has been executed successfully. The following is the screenshot of the email.

该过程已成功执行。 以下是电子邮件的屏幕截图。

摘要 (Summary)

In this article, I have explained about the SQL Server CLR Stored procedure and how we can use it to send the database email.

在本文中，我已经解释了有关SQL Server CLR存储过程以及如何使用它来发送数据库电子邮件的方法。

翻译自: https://www.sqlshack.com/how-to-send-a-database-mail-using-a-clr-stored-procedure/

生成clr库