インプロセスデータアクセスサンプル

[アーティクル]
05/07/2013

InProcessDataAccess サンプルには、SQL Server CLR インプロセスデータアクセスプロバイダーのさまざまな機能を示す、多数の単純な関数が含まれています。

前提条件

このプロジェクトを作成して実行するには、次のソフトウェアがインストールされている必要があります。

SQL Server または SQL Server Express。 SQL Server Express は、SQL Server Express ドキュメントとサンプルの Web サイトから無償で入手できます。
SQL Server デベロッパー Web サイトから入手できる 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

RECONFIGURE

注
CLR 統合を有効にするには、サーバーレベルの ALTER SETTINGS 権限が必要です。この権限は、sysadmin 固定サーバーロールおよび serveradmin 固定サーバーロールのメンバーには暗黙に許可されています。

使用している SQL Server インスタンスに AdventureWorks データベースがインストールされている必要があります。
使用している SQL Server インスタンスの管理者でない場合、インストールを完了するには、権限が管理者から許可されている必要があります。

サンプルのビルド

次の手順に従ってサンプルを作成し、実行します。

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

次の方法で共有

インプロセスデータアクセスサンプル

前提条件

CLR 統合の有効化

サンプルのビルド

次の手順に従ってサンプルを作成し、実行します。

サンプルコード

関連項目

概念

その他のリソース

次の方法で共有

インプロセス データ アクセス サンプル

前提条件

CLR 統合の有効化

サンプルのビルド

次の手順に従ってサンプルを作成し、実行します。

サンプル コード

関連項目

概念

その他のリソース

インプロセスデータアクセスサンプル

サンプルコード