.net clr procedure能否与mysql集成_CLR 存储过程

若要将查询的结果直接发送到客户端，请对 SqlPipe 对象使用 Execute 方法的重载之一。这是将结果返回到客户端的最高效方法，因为数据不必复制到托管内存即传输到网络缓冲区。例如：

[C#]

using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

///

/// Execute a command and send the results to the client directly.

///

[Microsoft.SqlServer.Server.SqlProcedure]

public static void ExecuteToClient()

{

using(SqlConnection connection = new SqlConnection("context connection=true"))

{

connection.Open();

SqlCommand command = new SqlCommand("select @@version", connection);

SqlContext.Pipe.ExecuteAndSend(command);

}

}

}

[Visual Basic]

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

'''

''' Execute a command and send the results to the client directly.

'''

_

Public Shared Sub ExecuteToClient()

Using connection As New SqlConnection("context connection=true")

connection.Open()

Dim command As New SqlCommand("SELECT @@VERSION", connection)

SqlContext.Pipe.ExecuteAndSend(command)

End Using

End Sub

End Class

若要通过进程内提供程序发送以前已执行的查询结果(或者使用 SqlDataReader 的自定义实现对数据进行预处理)，则使用采用 SqlDataReader 的 Send 方法的重载。此方法比上述直接方法稍慢，但它提供更高的灵活性，以便在数据发送到客户端之前操纵数据。

using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

///

/// Execute a command and send the resulting reader to the client

///

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SendReaderToClient()

{

using(SqlConnection connection = new SqlConnection("context connection=true"))

{

connection.Open();

SqlCommand command = new SqlCommand("select @@version", connection);

SqlDataReader r = command.ExecuteReader();

SqlContext.Pipe.Send(r);

}

}

}

[Visual Basic]

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

'''

''' Execute a command and send the results to the client directly.

'''

_

Public Shared Sub SendReaderToClient()

Using connection As New SqlConnection("context connection=true")

connection.Open()

Dim command As New SqlCommand("SELECT @@VERSION", connection)

Dim reader As SqlDataReader

reader = command.ExecuteReader()

SqlContext.Pipe.Send(reader)

End Using

End Sub

End Class

若要创建动态结果集，请填充该结果集并将其发送到客户端，您可以通过当前连接创建记录并且使用 SqlPipe.Send 发送它们。

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

public class StoredProcedures

{

///

/// Create a result set on the fly and send it to the client.

///

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SendTransientResultSet()

{

// Create a record object that represents an individual row, including it's metadata.

SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));

// Populate the record.

record.SetSqlString(0, "Hello World!");

// Send the record to the client.

SqlContext.Pipe.Send(record);

}

}

[Visual Basic]

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

'''

''' Create a result set on the fly and send it to the client.

'''

_

Public Shared Sub SendTransientResultSet()

' Create a record object that represents an individual row, including it's metadata.

Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )

' Populate the record.

record.SetSqlString(0, "Hello World!")

' Send the record to the client.

SqlContext.Pipe.Send(record)

End Sub

End Class

下面是通过 SqlPipe 发送表格结果和消息的示例。

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void HelloWorld()

{

SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");

using(SqlConnection connection = new SqlConnection("context connection=true"))

{

connection.Open();

SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);

SqlDataReader reader = command.ExecuteReader();

SqlContext.Pipe.Send(reader);

}

}

}

[Visual Basic]

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlClient

'The Partial modifier is only required on one class definition per project.

Partial Public Class StoredProcedures

'''

''' Execute a command and send the results to the client directly.

'''

_

Public Shared Sub HelloWorld()

SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")

Using connection As New SqlConnection("context connection=true")

connection.Open()

Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)

Dim reader As SqlDataReader

reader = command.ExecuteReader()

SqlContext.Pipe.Send(reader)

End Using

End Sub

End Class

第一个 Send 将消息发送到客户端，第二个则使用 SqlDataReader 发送表格结果。

请注意，这些示例只用于说明用途。对于执行大量计算的应用程序，CLR 函数比简单的 Transact-SQL 语句更合适。与前一示例几乎等效的 Transact-SQL 存储过程是：

CREATE PROCEDURE HelloWorld() AS

BEGIN

PRINT('Hello world!')

SELECT ProductNumber FROM ProductMaster

END

注意

消息和结果集在客户端应用程序中以不同的方式检索。例如，SQL Server Management Studio 结果集出现在“结果”视图中，消息出现在“消息”窗格中。

如果以上 Visual C# 代码保存在 MyFirstUdp.cs 文件中并且使用以下语句编译：

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs

或者，如果以上 Visual Basic 代码保存在 MyFirstUdp.vb 文件中并且使用以下语句编译：

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb

注意

从 SQL Server 2005 开始，不再支持执行使用 /clr:pure 编译的 Visual C++ 数据库对象(例如存储过程)。

可以使用以下 DDL 注册最终生成的程序集，以及调用的入口点：

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll'

CREATE PROCEDURE HelloWorld

AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld

EXEC HelloWorld

注意

从 SQL Server 2005 开始，在兼容级别为“80”的 SQL Server 数据库上，您不能创建托管的用户定义类型、存储过程、函数、聚合或触发器。若要利用 SQL Server 的这些 CLR 集成功能，您必须使用 sp_dbcmptlevel 存储过程将数据库兼容级别设置为“100”。