同處理序資料存取範例

發行項
05/07/2013

InProcessDataAccess 範例包含許多簡單的函數，這些函數會示範 SQL Server CLR 同處理序資料存取提供者的各種功能。

必要條件

若要建立並執行這個專案，您必須安裝下列軟體：

SQL Server 或 SQL Server Express。您可以從 SQL Server Express 文件集和範例網站免費取得 SQL Server Express。
您可以從 SQL Server 開發人員網站 (英文) 取得 AdventureWorks 資料庫。
.NET Framework SDK 2.0 或更新版本或是 Microsoft Visual Studio 2005 或更新版本。您可以免費取得 .NET Framework SDK。
此外，您也必須符合下列條件：
您所使用的 SQL Server 執行個體必須啟用 CLR 整合。
若要啟用 CLR 整合，請執行下列步驟：

啟用 CLR 整合
- 執行下列 Transact-SQL 命令：
sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

[!附註]

若要啟用 CLR 整合，您必須擁有 ALTER SETTINGS 伺服器層級權限，此權限是由系統管理員 (sysadmin) 和伺服器管理員 (serveradmin) 固定伺服器角色的成員以隱含方式持有。
AdventureWorks 資料庫必須安裝在您所使用的 SQL Server 執行個體上。
如果您不是正在使用之 SQL Server 執行個體的管理員，則必須讓管理員授與您 CreateAssembly 權限來完成安裝。

建立範例

使用下列指示來建立並執行範例：

開啟 Visual Studio 或 .NET Framework 命令提示字元。
必要時，請建立範例的目錄。在此範例中，我們將使用 C:\MySample。
在 c:\MySample 中，建立 inprocda.vb (適用於 Visual Basic 範例) 或 inprocda.cs (適用於 C# 範例) 並將適當的 Visual Basic 或 C# 範例程式碼 (下面) 複製到檔案中。
根據您選擇的語言，在命令列提示字元中執行下列其中一段程式碼，藉以將範例程式碼編譯成必要的組件。
- Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library InProcDA.vb
- Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /target:library inprocda.cs
將 Transact-SQL 安裝程式碼複製到檔案中，並將它儲存成範例目錄中的 Install.sql。
如果此範例安裝在 C:\MySample\ 以外的目錄中，請依照指示編輯 Install.sql 檔案，以便指向該位置。
部署組件、預存程序和函數，方法是執行
- sqlcmd -E -I -i install.sql
將 Transact-SQL 安裝程式碼複製到檔案中，並將它儲存成範例目錄中的 test.sql。
在命令提示字元中執行下列程式碼行，藉以測試應用程式：
- sqlcmd -E -I -i test.sql
將 Transact-SQL 清除指令碼複製到檔案中，並將它儲存成範例目錄中的 cleanup.sql。
使用下列命令來執行指令碼
- sqlcmd -E -I -i cleanup.sql

範例程式碼

下面是此範例的程式碼清單。

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public sealed class DataAccessDemo
{
        private DataAccessDemo()
        {
        }

        /// <summary>
/// Simple example to send a message to the client.
/// </summary>
public static void SendMessage(string msg)
{
SqlContext.Pipe.Send("Message from server: " + msg);
}

/// <summary>
/// Simple example of performing data access within
/// a function
/// </summary>
/// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
public static string ReportSqlVersion()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                //create a command from the current context
                SqlCommand cmd = conn.CreateCommand();

                //execute something
                cmd.CommandText = "select @@version";

                conn.Open();
                //return results as scalar
                return (string)cmd.ExecuteScalar();
            }
}


/// <summary>
/// Create a result set on the fly and send it to the client.
/// </summary>
public static void SendTransientResultSet()
{
//create the metadata for the columns
            Microsoft.SqlServer.Server.SqlMetaData[] columnSchema 
                = new Microsoft.SqlServer.Server.SqlMetaData[] {
new Microsoft.SqlServer.Server.SqlMetaData("stringcol", SqlDbType.NVarChar, 128)
};

//create a record based on that metadata
            SqlDataRecord newRecord = new SqlDataRecord(columnSchema);

//populate it
newRecord.SetString(0, "Hello World!");

//send it
SqlContext.Pipe.Send(newRecord);
}

/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
public static void ExecuteToClient()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "select @@version";
                conn.Open();
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
}

/// <summary>
/// Execute a command and send the resultig reader to the client
/// </summary>
public static void SendReaderToClient()
{
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "select @@version";
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                try
                {
                    SqlContext.Pipe.Send(rdr);
                }
                finally
                {
                    rdr.Close();
                }
            }
}

};

Visual Basic

Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Diagnostics
Partial Public NotInheritable Class DataAccessDemo
    Private Sub New()
    End Sub

    ''' <summary>
    ''' Simple example of performing data access within a function
    ''' </summary>
    ''' <returns></returns>
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReportSqlVersion() As SqlString
        Using conn As New SqlConnection("context connection=true")
            'create a command from the current context
            Dim cmd As SqlCommand = conn.CreateCommand()

            'execute something
            cmd.CommandText = "SELECT @@VERSION"

            conn.Open()

            'return results as scalar
            Return CType(cmd.ExecuteScalar(), String)
        End Using
    End Function

    ''' <summary>
    ''' Simple example to send a message to the client.
    ''' </summary>
    Public Shared Sub SendMessage(ByVal msg As String)
        SqlContext.Pipe.Send(("Message from server: " & msg))
    End Sub

    ''' <summary>
    ''' Create a result set on the fly and send it to the client.
    ''' </summary>
    Public Shared Sub SendTransientResultSet()
        'create the metadata for the columns
        Dim columnSchema() As Microsoft.SqlServer.Server.SqlMetaData _
            = {New SqlMetaData("stringcol", SqlDbType.NVarChar, 128)}

        'create a record based on that metadata
        Dim newRecord As New SqlDataRecord(columnSchema)

        'populate it
        newRecord.SetString(0, "Hello World!")

        'send it
        SqlContext.Pipe.Send(newRecord)
    End Sub

    ''' <summary>
    ''' Execute a command and send the results to the client directly.
    ''' </summary>
    Public Shared Sub ExecuteToClient()
        Using conn As New SqlConnection("context connection=true")
            Dim cmd As SqlCommand = conn.CreateCommand()

            cmd.CommandText = "SELECT @@VERSION"
            conn.Open()
            SqlContext.Pipe.ExecuteAndSend(cmd)
        End Using
    End Sub

    ''' <summary>
    ''' Execute a command and send the resulting reader to the client
    ''' </summary>
    Public Shared Sub SendReaderToClient()
        Using conn As New SqlConnection("context connection=true")
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT @@VERSION"
            conn.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            Try
                SqlContext.Pipe.Send(rdr)
            Finally
                rdr.Close()
            End Try
        End Using
    End Sub

End Class

這是 Transact-SQL 安裝指令碼 (Install.sql)，它會部署組件並建立此範例所需的預存程序和函數。

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))  
DROP FUNCTION [ReportSqlVersion];
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO
DECLARE @SamplesPath nvarchar(1024)

-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
set @SamplesPath = N'C:\MySample\'
CREATE ASSEMBLY InProcDA FROM @SamplesPath + 'InProcDA.dll'
WITH permission_set = SAFE;
GO

CREATE PROCEDURE [SendMessage] @msg nvarchar(4000)
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendMessage];
GO

CREATE FUNCTION [ReportSqlVersion]() RETURNS nvarchar(4000)
AS EXTERNAL NAME [InProcDA].[DataAccessDemo].[ReportSqlVersion];
GO

CREATE PROCEDURE [SendTransientResultSet]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendTransientResultSet];
GO

CREATE PROCEDURE [ExecuteToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[ExecuteToClient];
GO

CREATE PROCEDURE [SendReaderToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendReaderToClient];
GO

下列 Transact-SQL (test.sql) 會執行此範例中定義的預存程序和函數，藉以測試範例。

USE AdventureWorks;
GO

-- send a message to the client
EXEC SendMessage  N'This is a test message.';

-- exec a function that does data access
SELECT dbo.ReportSqlVersion();

-- exec the proc that sends a result set to the client
EXEC SendTransientResultSet;

EXEC ExecuteToClient;

EXEC SendReaderToClient;

USE master;
GO

下列 Transact-SQL 會從資料庫中移除組件、函數和預存程序。

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))  
DROP FUNCTION [ReportSqlVersion];
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO

請參閱

概念

Common Language Runtime (CLR) 整合的使用案例和範例

共用方式為

同處理序資料存取範例

必要條件

啟用 CLR 整合

建立範例

使用下列指示來建立並執行範例：

範例程式碼

請參閱

概念

其他資源