次の方法で共有

ADO.NET と SQLCLR を使用した SQL Server 内部でのマネージ データ アクセス

  • [アーティクル]

Pablo Castro
Microsoft Corporation

April 2005

対象 :
   Microsoft SQL Server 2005
   Microsoft .NET Framework 2.0
   ADO.NET

概要 : マネージ コードで新しい SQLCLR 機能を使用すると、SQL Server 2005 内部で実行する際に ADO.NET を使用できます。インプロセス データ アクセス、SQLCLR 構成要素、およびその相互作用の基本的なシナリオを使って、SQLCLR について説明します。

目次

はじめに
第 1 部 : 基本事項
第 2 部 : 高度なトピック
まとめ

はじめに

この資料では、マネージ コードで新しい SQLCLR 機能を使用して、SQL Server 2005 内部で実行する際に ADO.NET を使用する方法について説明します。

第 1 部では、インプロセス データ アクセスが必要になる可能性のある基本的なシナリオ、およびローカル接続とリモート接続の相異点について説明します。ストアド プロシージャや関数など、多種多様な SQLCLR 構成要素に加え、データ アクセス インフラストラクチャとその構成要素との相互作用の興味深い側面について説明します。

第 2 部では、インプロセス接続、および SQLCLR 内部で実行中に ADO.NET に適用される制限事項についてさらに詳しく説明します。最後に、SQLCLR 内部のデータ アクセス コードでのトランザクションの意味合いと、トランザクション API と暗黙および明示的に相互作用する方法について詳しく説明します。

第 1 部 : 基本事項

SQLCLR 内部でデータ アクセスを行う理由

SQL Server 2005 は .NET Framework と完全に統合されているので、好みの .NET プログラミング言語を使用して、ストアド プロシージャ、関数、ユーザー定義型、およびユーザー定義集計を作成できます。これらすべての構成要素では、.NET Framework インフラストラクチャ、基本クラス ライブラリ、およびサードパーティのマネージ ライブラリの大部分を利用できます。

多くの場合、マネージ コード部分は、計算を集中的に行う機能に使用されます。早期導入ユーザーが SQLCLR を使用して作成したアプリケーションの多くは、一般的に、文字列の解析や科学計算などにマネージ コードを使用しています。

しかし、大量の演算を行うアルゴリズムや文字列操作のアルゴリズムを単独に実行することはありません。ある時点では、入力を取得したり、結果を返す必要があります。その情報が比較的少なく細かい場合は、入力パラメータや出力パラメータを使用したり、値を返すことができます。ただし、大量の情報を処理している場合は、インメモリ構造は適切な表現や変換メカニズムにはなりません。このようなシナリオでは、データベースの方が適切な場合があります。情報をデータベースに格納することを選択する場合は、ツールとして SQLCLR やデータ アクセス インフラストラクチャが必要になります。

SQLCLR 内部で実行する際にデータベース アクセスが必要になるシナリオは多数あります。1 つは先に説明したシナリオです。このようなシナリオでは、大きなデータ セットになる可能性のあるデータで複数の計算を実行する必要があります。もう 1 つはシステム間の統合のシナリオです。このようなシナリオでは、データベース関連の操作を続行するために、さまざまなサーバーと対話して中間応答を得る必要があります。

注意    SQLCLR 全般の概要と詳細については、「SQL Server 2005 における CLR 統合の使用」 を参照してください。

ここで、マネージ コードを記述してデータ アクセスを行う場合は、ADO.NET が必要になります。

SQLCLR 内部の ADO.NET の概要

幸いなことに ADO.NET は SQLCLR 内部で "正しく機能" します。そのため、ADO.NET に関して現在持っているすべての知識を利用できます。

たとえば、次のコードを見てみましょう。このコードはクライアント側アプリケーション、Web アプリケーション、または中間層コンポーネントで適切に機能します。ということは、このコードは SQLCLR 内部でも適切に機能します。

C#
// Connection strings shouldn't be hardcoded for production code
using(SqlConnection conn = new SqlConnection(
  "server=MyServer; database=AdventureWorks; user id=MyUser; password=MyPassword")) {
  conn.Open();
  SqlCommand cmd = new SqlCommand(
                     "SELECT Name, GroupName FROM HumanResources.Department", conn);
    SqlDataReader r = cmd.ExecuteReader();
  while(r.Read()) {
    // Consume the data from the reader and perform some computation with it
  }
}
Visual Basic .NET
Dim cmd as SqlCommand
Dim r as SqlDataReader

' Connection strings shouldn't be hardcoded for production code
Using conn As New SqlConnection( _
     "server=MyServer; database=AdventureWorks; user id=MyUser; password=MyPassword")
  conn.Open()
  
  cmd = New SqlCommand("SELECT Name, GroupName FROM HumanResources.Department", conn)

  r = cmd.ExecuteReader()
  Do While r.Read()
    ' Consume the data from the reader and perform some computation with it
  Loop
End Using

このサンプルでは、System.Data.SqlClient プロバイダを使用して SQL Server に接続しています。このコードが SQLCLR 内部で実行される場合は、SQLCLR をホストする SQL Server から別の SQL Server に接続することになるので注意が必要です。また、別のデータ ソースに接続することもできます。たとえば、System.Data.OracleClient プロバイダを使用して、SQL Server 内部から直接 Oracle サーバーに接続することができます。

一般に、SQLCLR 内部から ADO.NET を使用するからといって大きな違いがあるわけではありません。ただし、やや注意が必要なシナリオが 1 つあります。それは、コードが実行されるのと同じサーバーに接続してデータを取得または変更する場合です。ADO.NET でこの問題を解決する方法については、「コンテキスト接続」を参照してください。

詳細を説明する前に、SQLCLR 内部でコードを実行する基本的な手順について説明します。既に SQLCLR ストアド プロシージャを作成した経験があれば、この部分を読み飛ばしてください。

ADO.NET を使用するマネージ ストアド プロシージャを Visual Studio で作成する

Visual Studio 2005 では SQL Server 2005 との統合が大きく進められたので、実際に、SQLCLR プロジェクトを簡単に作成および配置できるようになりました。Visual Studio 2005 を使用して、ADO.NET を使用する新しいマネージ ストアド プロシージャを作成しましょう。

  1. **SQLCLR プロジェクトを作成する。**Visual Studio で SQLCLR プロジェクトを作成する場合は、まず、データベース プロジェクトを作成します。好みの言語の下にある [データベース] というプロジェクトの種類を選択して、新しいプロジェクトを作成します。次に、[SQL Server プロジェクト] というテンプレートを選択して名前を指定すると、プロジェクトが作成されます。

  2. **アクセス許可を EXTERNAL_ACCESS に設定する。**プロジェクトのプロパティに移動 (プロジェクト ノードを右クリックして [プロパティ] を選択) し、[データベース] タブをクリックして、[アクセス許可のレベル] ボックスの [外部] を選択します。

  3. **プロジェクトにストアド プロシージャを追加する。**プロジェクトを作成したら、プロジェクト ノードを右クリックして、[追加]、[新しい項目] の順にクリックします。ポップアップ表示されるダイアログ ボックスには、作成できるさまざまな SQLCLR オブジェクトがすべて表示されます。[ストアド プロシージャ] を選択して名前を指定すると、ストアド プロシージャが作成されます。Visual Studio によって、ストアド プロシージャのテンプレートが作成されます。

  4. Visual Studio **テンプレートをカスタマイズする。**Visual Studio では、マネージ ストアド プロシージャのテンプレートが生成されます。このサンプルでは、SqlClient (SQL Server 用の .NET データ アクセス プロバイダ) を使用して別の SQL Server に接続するので、System.Data.SqlClient 用に少なくとも 1 つ以上の using ステートメント (C# の場合) や imports ステートメント (Visual Basic の場合) を追加する必要があります。

  5. **ストアド プロシージャ本体をコーディングする。**ここで、ストアド プロシージャのコードが必要になります。たとえば、別の SQL Server に接続して (コードは SQL Server 内部で実行されることを覚えておいてください)、入力データに基づいた情報を入手し、結果を処理するとしましょう。Visual Studio によってストアド プロシージャのメソッドに SqlProcedure 属性が生成されることに注意してください。この属性は、Visual Studio の配置インフラストラクチャで使用されるので、そのままにしておきます。次のコードを見ると、クライアントや中間層で実行される従来の ADO.NET コードと異なる点がないことがわかります。その部分が気に入っています。

    6. 



C#
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures {
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void SampleSP() {
// as usual, connection strings shouldn't be hardcoded for production code
using(SqlConnection conn = new SqlConnection(
"server=MyServer; database=AdventureWorks; " +
"user id=MyUser; password=MyPassword")) {
conn.Open();
SqlCommand cmd = new SqlCommand(
"SELECT Name, GroupName FROM HumanResources.Department", conn);
SqlDataReader r = cmd.ExecuteReader();
while(r.Read()) {
// consume the data from the reader and perform some processing
}
}
}
}
Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub SampleSP()
Dim cmd As SqlCommand
Dim r As SqlDataReader
' as usual, connection strings shouldn't be hardcoded for production code
Using conn As New SqlConnection( _
"server=MyServer; database=AdventureWorks; user id=MyUser; password=MyPassword")
conn.Open()
cmd = New SqlCommand( _
"SELECT Name, GroupName FROM HumanResources.Department", conn)
r = cmd.ExecuteReader()
Do While r.Read()
' consume the data from the reader and perform some processing
Loop
End Using
End Sub
End Class



    

  1. **アセンブリを配置する。**ここで、SQL Server にこのストアド プロシージャを配置する必要があります。Visual Studio では、SQL Server にアセンブリを配置して、アセンブリ内の各オブジェクトをサーバーに登録する処理が簡単に行えます。プロジェクトをビルドした後、[ビルド] メニューの [ソリューションの配置] を選択します。Visual Studio は、SQL Server に接続して、必要な場合は以前のバージョンのアセンブリを削除します。その後、新しいアセンブリをサーバーに送信して登録し、アセンブリに追加したストアド プロシージャを登録します。
    

    2. 

  2. テストする。"Test Scripts" プロジェクト フォルダの下に生成されている "test.sql" ファイルをカスタマイズして、使用しているストアド プロシージャを実行することもできます。Visual Studio では、Ctrl キーを押しながら F5 キーを押すか、F5 キーだけを押すと、そのストアド プロシージャが実行されます (F5 キーによりデバッガが起動され、SQLCLR 内部の T-SQL コードと CLR コードの両方をデバッグできます。すばらしいですよね)。
    

    3. 



ADO.NET を使用するマネージ ストアド プロシージャを SDK のみを使用して作成する


Visual Studio 2005 が手元にない場合、または Visual Studio で実行する前に最初に動作を確認しておく場合に、手動で SQLCLR ストアド プロシージャを作成する方法を次に示します。


まず、ストアド プロシージャのコードが必要になります。たとえば、Visual Studio の例と同様に、別の SQL Server に接続し、入力データに基づいた情報を入手して、結果を処理するとしましょう。


C#
using System.Data;
using System.Data.SqlClient;
public class SP {
  public static void SampleSP() {
    // as usual, connection strings shouldn't be hardcoded for production code
    using(SqlConnection conn = new SqlConnection(
                                      "server=MyServer; database=AdventureWorks; " +
                                      "user id=MyUser; password=MyPassword")) {
      conn.Open();
      SqlCommand cmd = new SqlCommand(
                     "SELECT Name, GroupName FROM HumanResources.Department", conn);
        SqlDataReader r = cmd.ExecuteReader();
      while(r.Read()) {
        // consume the data from the reader and perform some processing
      }
    }
  }
}
Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Public Class SP
  Public Shared Sub SampleSP()
    Dim cmd As SqlCommand
    Dim r As SqlDataReader
    ' as usual, connection strings shouldn't be hardcoded for production code
    Using conn As New SqlConnection( _
     "server=MyServer; database=AdventureWorks; user id=MyUser; password=MyPassword")
      conn.Open()
      cmd = New SqlCommand( _
                   "SELECT Name, GroupName FROM HumanResources.Department", conn)
      r = cmd.ExecuteReader()
      Do While r.Read()
        ' consume the data from the reader and perform some processing
      Loop
    End Using
  End Sub
End Class



これもまた、従来の ADO.NET と異なる点はありません。


ここで、コードをコンパイルして、ストアド プロシージャを含む DLL アセンブリを作成する必要があります。次のコマンドでこの処理を実行します (ただし、ファイルに myprocs.cs または myprocs.vb という名前を付けたこと、.NET Framework 2.0 がパスに含まれていることが前提です)。


C#
csc /t:library myprocs.cs
VB
vbc /t:library myprocs.vb



これにより、コードがコンパイルされ、myprocs.dll という新しい DLL が作成されます。この DLL をサーバーに登録する必要があります。たとえば、myprocs.dll を c:\temp に配置するとしましょう。そのパスからストアド プロシージャを SQL Server にインストールするには、次の SQL ステートメントが必要です。このステートメントは、SQL Server Management Studio または sqlcmd コマンド ライン ユーティリティのいずれかから実行できます。


-- Register the assembly
CREATE ASSEMBLY myprocs FROM 'c:\temp\myprocs.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
GO
-- Register the stored-procedure
CREATE PROCEDURE SampleSP AS EXTERNAL NAME myprocs.SP.SampleSP



コードから外部リソース (この場合は別の SQL Server) にアクセスしているので、EXTERNAL_ACCESS アクセス許可セットが必要になります。既定のアクセス許可セット (SAFE) では、外部アクセスが許可されません。


後からストアド プロシージャを変更する場合は、すべて削除して再作成することなく、SQL Server のアセンブリを更新できます。ただし、パブリック インターフェイスを変更 (たとえば、パラメータの型や数の変更) している場合は除きます。ここで説明したシナリオでは、DLL を再コンパイル後、次のコードを単純に実行できます。


-- Refresh assembly from the file-system
ALTER ASSEMBLY myprocs FROM 'c:\temp\myprocs.dll'



コンテキスト接続


比較的一般的なデータ アクセス シナリオでは、CRL ストアド プロシージャや CLR 関数が実行されているのと同じサーバーにアクセスします。


そのための 1 つの選択肢として、SqlClient を使用して通常の接続を作成し、ローカル サーバーを示す接続文字列を指定して接続を開きます。


これで接続が確立されます。ただし、これは独立した接続なので、ログイン用の資格情報を指定する必要があります。独立した接続であるということは、これ以外にデータベース セッションが異なる、さまざまな SET オプションを設定できる、個別のトランザクションに含まれる、一時テーブルが参照されないなど、さまざまなことを意味します。


つまり、ストアド プロシージャや関数コードが SQLCLR 内部で実行される場合は、誰かがこの SQL Server に接続し、これらのコードを呼び出す SQL ステートメントを実行したことを意味します。おそらく、その接続では、そのトランザクション、SET オプションなどが合わせて必要になるでしょう。それらの作業を行って初めて、この接続を使用できるようになります。このような接続を "コンテキスト接続" と呼びます。


コンテキスト接続では、最初にコードが呼び出されたときと同じコンテキストで SQL ステートメントを実行できます。コンテキスト接続を取得するには、次の例のように context connection という新しい接続文字列キーワードを使用する必要があります。


C#
using(SqlConnection c = new SqlConnection("context connection=true")) {
    c.Open();
    // do something with the connection
}
Visual Basic .NET
Using c as new SqlConnection("context connection=true")
    c.Open()
    ' do something with the connection
End Using 



実際にコードが呼び出し元と同じ接続で実行されているかどうかを確認するには、次のテストを実行します。このテストでは、SqlConnection オブジェクトを使用し、呼び出し元と接続内の SPID (SQL Server セッション識別子) を比較します。プロシージャのコードは次のようになります。


C#
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures {
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void SampleSP(string connstring, out int spid) {
        using (SqlConnection conn = new SqlConnection(connstring)) {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT @@SPID", conn);
            spid = (int)cmd.ExecuteScalar();
        }
    }
}
Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
   <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SampleSP(ByVal connstring As String, ByRef spid As Integer)
        Using conn As New SqlConnection(connstring)
            conn.Open()

            Dim cmd As New SqlCommand("SELECT @@SPID", conn)
            spid = CType(cmd.ExecuteScalar(), Integer)
        End Using
    End Sub
End Class



SQL Server でこのコードをコンパイルして配置した後、テストを実行できます。


-- Print the SPID as seen by this connection
PRINT @@SPID
-- Now call the stored proc and see what SPID we get for a regular connection
DECLARE @id INT
EXEC SampleSP 'server=.;user id=MyUser; password=MyPassword', @id OUTPUT
PRINT @id
-- Call the stored proc again, but now use the context connection
EXEC SampleSP 'context connection=true', @id OUTPUT
PRINT @id



最初と最後の SPID が一致することがわかります。これは、事実上、これらの SPID が同じ接続であるためです。2 番目の SPID は異なります。これは、サーバーへの 2 番目の接続 (完全に新しい接続) が確立されたためです。


さまざまな SQLCLR オブジェクトでの ADO.NET の使用


"コンテキスト" オブジェクト


以下でさまざまな SQLCLR オブジェクトについて説明しているように、各オブジェクトは特定のサーバー "コンテキスト" で実行されます。コンテキストは SQLCLR コードがアクティブになった環境を表し、SQLCLR 内部で実行しているコードがその SQLCLR オブジェクトの種類に基づいて適切なランタイム情報にアクセスできるようにします。


コンテキストの最上位オブジェクトは、Microsoft.SqlServer.Server 名前空間で定義される SqlContext クラスです。


ほとんどの場合に使用できるもう 1 つのオブジェクトは、クライアントへの接続を表す pipe オブジェクトです。たとえば、T-SQL では、PRINT ステートメントを使用してメッセージをクライアントに戻すことができます (クライアントが SqlClient の場合、SqlConnection.InfoMessage イベントとして表示されます)。SQLCLR では、SqlPipe オブジェクトを使用して同じ処理を実行できます。


C#
SqlContext.Pipe.Send("Hello, World! (from SQLCLR)");
Visual Basic .NET
SqlContext.Pipe.Send("Hello, World! (from SQLCLR)")



ストアド プロシージャ


上記で使用したすべてのサンプルは、ストアド プロシージャを基礎としていました。ストアド プロシージャを使用すると、ローカル サーバーとリモート データ ソースの両方のデータを取得および変更できます。


また、ストアド プロシージャは、T-SQL ストアド プロシージャと同様に、結果をクライアントに送信することもできます。たとえば、T-SQL では、次のようなストアド プロシージャを使用できます。


CREATE PROCEDURE GetVendorsMinRating(@rating INT)
AS
SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor
WHERE CreditRating <= @rating



このストアド プロシージャを実行するクライアントには、クライアントに返された結果セットが表示されます (つまり、同様にクライアントで ADO.NET を使用している場合は、ExecuteReader を使用して SqlDataReader を取得することになります)。


マネージ ストアド プロシージャも結果セットを返すことができます。上記の例のようなセット指向のステートメントが多数を占めるストアド プロシージャでは、常に T-SQL を使用するほうが適しています。ただし、演算処理の多い作業を実行したり、マネージ ライブラリを使用してから、複数の結果を返すストアド プロシージャを使用する場合は、SQLCLR を使用するほうが適している場合があります。SQLCLR で書き直した同じプロシージャを次に示します。


C#
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures {
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void SampleSP(int rating) {
        using (SqlConnection conn = new SqlConnection("context connection=true")) {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor " +
                "WHERE CreditRating <= @rating", conn);
            cmd.Parameters.AddWithValue("@rating", rating);
            // execute the command and send the results directly to the client
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
    }
}
Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SampleSP(ByVal rating As Integer)
        Dim cmd As SqlCommand

        ' connect to the context connection
        Using conn As New SqlConnection("context connection=true")
            conn.Open()
            cmd = New SqlCommand( _
                "SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor " & _
                "WHERE CreditRating <= @rating", conn)
            cmd.Parameters.AddWithValue("@rating", rating)

            ' execute the command and send the results directly to the client
            SqlContext.Pipe.ExecuteAndSend(cmd)
        End Using
    End Sub
End Class



上記の例では、SQL クエリの結果をクライアントに送信する方法を示しています。ただし、(たとえば、計算をローカルで実行したり、Web サービスを呼び出すことにより) 独自のデータを作成するストアド プロシージャを使用して、そのデータを結果セットとしてクライアントに返す可能性もあります。SQLCLR を使用すればこれも可能になります。簡単な例を次に示します。


C#
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures {
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void SampleSP() {
        // simply produce a 10-row result-set with 2 columns, an int and a string
        // first, create the record and specify the metadata for the results
        SqlDataRecord rec = new SqlDataRecord(
            new SqlMetaData("col1", SqlDbType.NVarChar, 100),
            new SqlMetaData("col2", SqlDbType.Int));
        // start a new result-set
        SqlContext.Pipe.SendResultsStart(rec);
        // send rows
        for(int i = 0; i < 10; i++) {
            // set values for each column for this row
            // This data would presumably come from a more "interesting" computation
            rec.SetString(0, "row " + i.ToString());
            rec.SetInt32(1, i);
            SqlContext.Pipe.SendResultsRow(rec);
        }
        // complete the result-set
        SqlContext.Pipe.SendResultsEnd();
    }
}
Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SampleSP()
        ' simply produce a 10-row result-set with 2 columns, an int and a string
        ' first, create the record and specify the metadata for the results
        Dim rec As New SqlDataRecord( _
            New SqlMetaData("col1", SqlDbType.NVarChar, 100), _
            New SqlMetaData("col2", SqlDbType.Int))
        ' start a new result-set
        SqlContext.Pipe.SendResultsStart(rec)
        ' send rows
        Dim i As Integer
        For i = 0 To 9
            ' set values for each column for this row
            ' This data would presumably come from a more "interesting" computation
            rec.SetString(0, "row " & i.ToString())
            rec.SetInt32(1, i)
            SqlContext.Pipe.SendResultsRow(rec)
        Next
        ' complete the result-set
        SqlContext.Pipe.SendResultsEnd()
    End Sub
End Class



ユーザー定義関数


ユーザー定義のスカラ関数は、既に以前のバージョンの SQL Server にもありました。SQL Server 2005 では、スカラ関数は既存の T-SQL を使用するオプションだけでなく、マネージ コードを使用しても作成できます。どちらの場合も、関数は 1 つのスカラ値を返します。


SQL Server では、関数によって副作用が発生しないことが前提になります。つまり、関数でデータベースの状態を変更してはいけません (データもメタデータも変更しないでください)。T-SQL 関数の場合、実際にサーバーによってこの制限が課せられているので、副作用を生じる操作 (たとえば、UPDATE ステートメントの実行) を行うと、ランタイム エラーが発生します。


同じ制限 (副作用なし) がマネージ関数にも当てはまります。ただし、サーバーでこの制限が課せられることはありません。コンテキスト接続を使用し、その接続経由で副作用のある T-SQL ステートメント (たとえば、UPDATE ステートメント) を実行すると、ADO.NET から SqlException が返されます。しかし、通常の (コンテキスト接続以外の) 接続経由で副作用のある操作を実行するとこれを検出できません。一般に、影響を明確に把握していない限り、安全を期して、副作用のある操作を関数から実行しないことをお勧めします。


また、関数は、ストアド プロシージャのように結果セットをクライアントに返すことはできません。


テーブル値ユーザー定義関数


T-SQL テーブル値関数 (TVF) は、以前のバージョンの SQL Server にもありました。SQL Server 2005 では、マネージ コードを使用した TVF の作成をサポートします。マネージ コードを使用して作成されたテーブル値関数は "ストリーミング テーブル値関数" (略してストリーミング TVF) と呼びます。


    

  • この関数は、スカラ値ではなくリレーション (結果セット) を返すので、"テーブル値" といいます。つまり、この関数は、SELECT ステートメントの FROM 部分などで使用できます。
    

    • 

  • "ストリーミング" と呼ぶのは、初期化手順後、サーバーがオブジェクトを呼び出して行を取得するためです。そのため、サーバーからの要求に応じて結果行を作成できます。最初にすべての結果をメモリ内に作成し、結果全体をデータベースに返す必要はありません。
    

    • 



単語をコンマで区切ったリストを含む 1 つの文字列を受け取り、単語ごとに 1 行、1 列の結果セットを返す関数の簡単な例を次に示します。


C#
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.SqlServer.Server;
public partial class Functions {
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow")]
    // if you're using VS then add the following property setter to
    // the attribute above: TableDefinition="s NVARCHAR(4000)"
    public static IEnumerable ParseString(string str) {
        // Split() returns an array, which in turn 
        // implements IEnumerable, so we're done :)
        return str.Split(','); 
    }
    public static void FillRow(object row, out string str) {
        // "crack" the row into its parts. this case is trivial
        // because the row is only made of a single string
        str = (string)row;
    }
}
Visual Basic .NET
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server
Partial Public Class Functions
   <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow")> _
    ' if you're using VS then add the following property setter to
    ' the attribute above: TableDefinition:="s NVARCHAR(4000)"
    Public Shared Function ParseString(ByVal str As String) As IEnumerable
        ' Split() returns an array, which in turn 
        ' implements IEnumerable, so we're done :)
        Return Split(str, ",")
    End Function
    Public Shared Sub FillRow(ByVal row As Object, <Out()> ByRef str As String)
        ' "crack" the row into its parts. this case is trivial
        ' because the row is only made of a single string
        str = CType(row, String)
    End Sub
End Class



Visual Studio を使用している場合は、単に TVF を含むアセンブリを配置します。手動でこの操作を行う場合は、次のステートメントを実行して TVF を登録します (ただし、既にアセンブリを登録していることが前提です)。


-- register the managed TVF
CREATE FUNCTION ParseString(@str NVARCHAR(4000))
RETURNS TABLE (s NVARCHAR(4000))
AS EXTERNAL NAME myprocs.Functions.ParseString



登録後に、次の T-SQL ステートメントを実行して登録を確認できます。


-- Use the TVF in a SELECT statement, throwing-in an "order by" just because
SELECT s FROM dbo.ParseString('a,b,c') ORDER BY s DESC



この段階で、データ アクセスでは何を実行する必要があるでしょうか。TVF から ADO.NET を使用する際に注意すべき制限事項がいくつかあることがわかります。


    

  • TVF は関数なので、副作用の制限も適用されます。
    

    • 

  • コンテキスト接続は、初期化メソッド (たとえば、上記の例の ParseString) では使用できますが、行を設定するメソッド (FillRowMethodName 属性プロパティによって示されるメソッド) では使用できません。
    

    • 

  • 初期化メソッドでも行設定メソッドでも、通常 (コンテキスト以外) の接続で ADO.NET を使用できます。行設定メソッドでクエリや実行時間の長い他の操作を実行すると、TVF を使用する SELECT ステートメントのパフォーマンスに深刻な影響をもたらす場合があることに注意してください。
    

    • 



トリガ


トリガの作成は、多くの点でストアド プロシージャの作成に似ています。ADO.NET を使用すると、ストアド プロシージャからと同様に、トリガからデータ アクセスを実行できます。


ただし、一般的にトリガの場合は、追加の要件がいくつかあります。


    

  • トリガが起動される原因となった変更を "確認" できます。T-SQL トリガでは、通常、INSERTED テーブルと DELETED テーブルを使用してこの操作を行います。マネージ トリガの場合も、同じことが当てはまります。つまり、コンテキスト接続を使用している限り、SqlCommand オブジェクトを使用してトリガで実行する SQL ステートメントから INSERTED テーブルと DELETED テーブルを参照できます。
    

    • 

  • 変更された列を確認できます。SqlTriggerContext クラスの IsUpdatedColumn() メソッドを使用して、特定の列が変更されたかどうかを確認できます。SqlTriggerContext のインスタンスは、コードがトリガ内で実行されている場合、SqlContext クラスから使用できます。つまり、SqlContext.TriggerContext プロパティを使用して、このインスタンスにアクセスできます。
    

    • 



もう 1 つの一般的な方法として、トリガを使用して入力データを検証し、検証の基準に合格しなければ操作を中断できます。この操作は、次のステートメントを使用するだけで、マネージ コードから行うこともできます。


C#
System.Transactions.Transaction.Current.Rollback();
Visual Basic .NET
System.Transactions.Transaction.Current.Rollback()



さて、ここでは何が行われたのでしょうか。これはまさに、SQLCLR と .NET Framework との緊密な統合によって可能になります。詳細については、「第 2 部 : 高度なトピック」の「トランザクション」を参照してください。


SQLCLR と ADO.NET を併用しない場合


SQL をラップしない


クエリを実行するだけのストアド プロシージャの場合は、常に T-SQL で記述することをお勧めします。SQLCLR 内で記述すると、開発により多くの時間がかかり (クエリの T-SQL コードとプロシージャのマネージ コードを記述する必要があります)、実行時に速度が低下します。


SQLCLR を使用して、T-SQL コードの比較的簡単な部分をラップすると、必ず、パフォーマンスが低下し、余分な保守コストが発生します。SQLCLR は、ストアド プロシージャや関数で実行されるセット指向の操作以外の作業が実際に必要な場合に適しています。




注意   この資料のサンプルは、ストアド プロシージャや関数の一部であり、実稼動レベルのデータベースに実在するコンポーネントではありません。ここで説明している ADO.NET API を試すのに必要な最低限のコードのみ記載しています。そのため、サンプルの多くにはデータ アクセス コードしか含まれていません。実際に、ストアド プロシージャや関数にデータ アクセス コードしか含まれない場合は、T-SQL で記述できるかどうかを再度チェックしてください。




セット指向の操作で実行できる場合はプロシージャで行を処理しない


セット指向の操作は非常に強力です。場合によっては、このような操作を正確に理解するのは難しいことがありますが、いったんこの操作を行えば、ユーザーが指定する SQL ステートメントに基づいた実行内容をデータベース エンジンが把握できる機会が増え、ユーザーに代わって詳細で高度な最適化を実行できるようになります。


そのため、一般的には、セット指向のステートメント (UPDATE、INSERT、DELETE など) を使用して行を処理することをお勧めします。


この適切な例を次に示します。


    

  • 行単位のスキャンや更新は避けます。できる限り、より洗練された UPDATE ステートメントを記述することをお勧めします。
    

    • 

  • 明示的に SqlDataReader を開いて、値を反復処理することで値のカスタム集計を行うことを避けます。組み込みの集計関数 (SUM、AVG、MIN、MAX など) を使用するか、ユーザー定義集計を作成してください。
    

    • 



もちろん、手続き型のロジックを使用して、行単位に処理することが役立つシナリオもいくつかあります。ほとんどの場合、1 つの SQL ステートメントで表記できることには、行単位の処理を行わないようにします。


第 2 部 : 高度なトピック


接続の詳細


通常の接続とコンテキスト接続のいずれかを選択する


リモート サーバーに接続している場合は、常に通常の接続が使用されます。一方、関数やストアド プロシージャを実行しているサーバーと同じサーバーへの接続が必要な場合は、コンテキスト接続を使用することがほとんどです。既に説明したとおり、これにはいくつかの理由があります。たとえば、同じトランザクション空間で実行される点や再認証が不要な点などが挙げられます。


さらに、コンテキスト接続を使用すると、通常、パフォーマンスが向上し、リソースの使用率が低下します。コンテキスト接続はインプロセスのみの接続なので、サーバーと "直接" 対話できます。つまり、コンテキスト接続は、SQL ステートメントを送信したり、結果を受け取るのに、ネットワーク プロトコルやトランスポート層を経由する必要がありません。また、認証プロセスを経由する必要もありません。


同じサーバーに通常の接続を個別に開く必要がある場合もあります。たとえば、コンテキスト接続を使用するには、「コンテキスト接続の制限事項」で説明している特定の制限事項があります。


サーバーへの "直接" 接続とは


コンテキスト接続は、サーバーに "直接" 接続し、ネットワーク プロトコルやトランスポート層を使用しないことを既に説明しました。図 1 では、SqlClient マネージ プロバイダの主要なコンポーネントと、通常接続の使用時とコンテキスト接続の使用時のさまざまなコンポーネントの相互作用を表します。


ms345135.mandataaccess_01(ja-jp,SQL.90).gif


図 1. 接続プロセス


ご覧のとおり、コンテキスト接続では、必要なコード パスが短くなり、関与するコンポーネントが少なくなります。そのため、コンテキスト接続は、通常の接続よりも高速にサーバーに到達し、戻ると予測できます。もちろん、クエリの実行時間は同じです。クエリの実行に必要な時間は SQL ステートメントがサーバーに到達するしくみとは無関係です。


コンテキスト接続の制限事項


アプリケーションで使用する際に考慮にしておく必要のある、コンテキスト接続に適用される制限事項を次に示します。


    

  • 特定の接続に対して同時に開くことができるコンテキスト接続は 1 つだけです。
    

      

    • もちろん、複数のステートメントを個別の接続で同時に実行している場合は、各ステートメントがそれぞれ独自のコンテキスト接続を取得できます。この制限は、異なる接続からの同時要求には影響しません。つまり、特定の接続の特定の要求のみに影響します。
      • 

    

    • 

  • コンテキスト接続では、複数のアクティブな結果セット (MARS) がサポートされません。
    

    • 

  • コンテキスト接続では SqlBulkCopy クラスが機能しません。
    

    • 

  • コンテキスト接続では、更新のバッチ処理がサポートされません。
    

    • 

  • コンテキスト接続に対して実行するコマンドと SqlNotificationRequest を併用できません。
    

    • 

  • コンテキスト接続に対して実行しているコマンドのキャンセルはサポートされません。SqlCommand.Cancel() は警告しないで要求を無視します。
    

    • 

  • context connection=true を使用する場合は他の接続文字列キーワードを使用できません。
    

    • 



これらの制限事項には、仕様によるものやコンテキスト接続のセマンティクスの結果によるものがあります。また、今回のリリースではこれらの制限事項のうち実際に実装が決定したものもあります。また、ユーザーのフィードバックに基づいて、今後のバージョンでは制限が緩和される場合もあります。


SQLCLR 内部の通常の接続に関する制限事項


コンテキスト接続ではなく通常の接続を使用することを決めた場合に、注意すべき制限事項がいくつかあります。


ADO.NET のほとんどすべての機能を SQLCLR 内部で使用できますが、今回のリリースでは、適用されない機能や、サポートしないことが決まった機能がいくつかあります。具体的には、非同期コマンドの実行、SqlDependecy オブジェクト、および関連するインフラストラクチャはサポートされません。


接続の資格情報


これまで使用してきたすべてのサンプルでは、統合認証ではなく SQL 認証 (ユーザー ID とパスワード) を使用していることに気付いた方もいるでしょう。常に、統合認証を使用することを強く推奨しているのに SQL 認証を使用するのはなぜか不思議に思っているかもしれません。


SQLCLR 内部で統合認証を使用することはそれほど簡単ではありません。統合認証を使用する前に注意しておく必要のある考慮事項がいくつかあります。


まず、既定では、クライアント権限の借用が行われません。つまり、SQL Server から CLR コードを呼び出すと、SQL Server サービスのアカウントで実行されます。統合認証を使用すると、接続で使用する "ID" は接続元クライアントの ID ではなく、サービスの ID になります。シナリオによっては、実際にこのことを意図し、うまく機能するものもあります。しかし、多くのシナリオではうまく機能しません。たとえば、SQL Server が "ローカル システム" で実行されていると、統合認証を使用してリモート サーバーにログインすることはできなくなります。




注意   頭痛に見舞われたくない場合は、次の 2 つの段落を読み飛ばしてください。




場合によっては、サービス アカウントとして実行するのではなく、SqlContext.WindowsIdentity プロパティを使用して、呼び出し元の権限を借用することがあります。このような場合、呼び出し元のコードを呼び出したクライアントの ID を表す WindowsIdentity インスタンスを公開します。このインスタンスは、クライアントが最初から統合認証を使用している場合のみ使用できます (それ以外の場合は、クライアントの Windows ID はわかりません)。WindowsIdentity インスタンスを取得したら、Impersonate を呼び出してスレッドのセキュリティ トークンを変更し、クライアントに代わって ADO.NET 接続を開くことができます。


さらに複雑なことに、インスタンスを取得した場合でも、既定ではそのインスタンスを別のコンピュータに反映できません。つまり、既定では、Windows セキュリティ インフラストラクチャでこの反映が制限されています。複数の信頼関係のあるコンピュータ間で Windows ID の反映を有効にするメカニズムを "委任" といいます。委任の詳細については、TechNet の「Kerberos のプロトコル遷移と制約付き委任」を参照してください。


トランザクション


たとえば、次のコードを含む、SampleSP というマネージ ストアド プロシージャがあるとしましょう。


C#
// as usual, connection strings shouldn't be hardcoded for production code
using(SqlConnection conn = new SqlConnection(
  "server=MyServer; database=AdventureWorks; user id=MyUser; password=MyPassword")) {
  conn.Open();
  // insert a hardcoded row for this sample
  SqlCommand cmd = new SqlCommand("INSERT INTO HumanResources.Department " +
     "(Name, GroupName) VALUES ('Databases', 'IT'); SELECT SCOPE_IDENTITY()", conn);
  outputId = (int)cmd.ExecuteScalar();
}
Visual Basic .NET
Dim cmd as SqlCommand
' as usual, connection strings shouldn't be hardcoded for production code
Using conn As New SqlConnection( _
     "server=MyServer; database=AdventureWorks; user id=MyUser; password=MyPassword")
  conn.Open()
    ' insert a hardcoded row for this sample
  cmd = New SqlCommand("INSERT INTO HumanResources.Department " _ &
      "(Name, GroupName) VALUES ('Databases', 'IT'); SELECT SCOPE_IDENTITY()", conn)
  outputId = CType(cmd.ExecuteScalar(), Integer)
End Using



このプロシージャを T-SQL で実行したらどうなるでしょうか。


BEGIN TRAN
DECLARE @id INT
-- create a new department and get its ID
EXEC SampleSP @id OUTPUT
-- move employees from department 1 to the new department
UPDATE Employees SET DepartmentID = @id WHERE DepartmentID = @id
-- now undo the entire operation
ROLLBACK



最初に BEGIN TRAN を実行しているので、ROLLBACK ステートメントは、T-SQL の UPDATE によって行われた操作を元に戻します。ただし、ストアド プロシージャでは、別のサーバーへの新しい ADO.NET 接続を作成し、そこで変更を行いました。その変更はどうなるでしょうか。心配することはありません。コードでリモート サーバーへの ADO.NET 接続が確立されたことを検出します。また、既定では、その接続で既存のトランザクションを意識せずに取得し、コードで接続するすべてのサーバーを分散トランザクションに参加させます。これは、SQL Server 以外の接続でも機能します。


このために、System.Transactions との優れた統合が行われています。


System.Transactions と ADO.NET と SQLCLR


System.Transactions は、.NET Framework 2.0 のリリースに含まれる新しい名前空間です。この名前空間には、新しいトランザクション フレームワークがあります。このフレームワークは、マネージ アプリケーションでのローカル トランザクションと分散トランザクションの使用を大幅に拡張および簡略化します。


System.Transactions と ADO.NET の概要については、MSDN Magazine の「Data Points: ADO.NET and System.Transactions」 (英語)、および MSDN TV Episode の「.NET Framework 2.0 における System.Transactions の紹介」を参照してください。


ADO.NET と SQLCLR は、.NET Framework 間で統一されたトランザクション API を提供するために、System.Transactions と密接に統合されています。


トランザクションの昇格


プロシージャの分散トランザクションに関するさまざまなしかけについて説明したので、これにかかわる大きなオーバーヘッドがあると考えるかもしれません。これについては、それほど問題にはなりません。


データベース トランザクション内でマネージ ストアド プロシージャを呼び出すと、トランザクション コンテキストが CLR コードに移行します。


既に説明したように、コンテキスト接続はまったく同じ接続なので、同じトランザクションが適用され、オーバーヘッドは追加されません。


それに対して、リモート サーバーに接続を行っている場合、同じ接続ではないことは明らかです。ADO.NET 接続を開くと、自動的にコンテキストを伴うデータベース トランザクションであることが検出され、そのデータベース トランザクションを分散トランザクションに "昇格" します。その後、リモート サーバーへの接続をその分散トランザクションに参加させるので、この段階ですべてが調整されます。このような場合のみ追加コストがかかります。それ以外の場合は、通常のデータベース トランザクションのコストしか必要ありません。すばらしいでしょう。




注意   クライアントや中間層のシナリオで使用する場合も、同様のトランザクション昇格機能を ADO.NET と System.Transactions で使用できます。詳細については、MSDN のドキュメントを参照してください。




現在のトランザクションにアクセスする


ここで、"ADO.NET 接続を自動的に参加させるアクティブなトランザクションが、SQLCLR コード内に存在することはどのようにわかるのか" 不思議に思うかもしれません。さらに詳細な統合が行われることがわかります。


System.Transactions フレームワークが System.Transaction.Current によって使用できる "現在のトランザクション" の概念を SQL Server の外部に公開します。基本的に、サーバー内部でも同じことを実行されます。


SQLCLR コードに処理が移動する時点でトランザクションがアクティブだった場合、そのトランザクションが System.Transactions.Transaction クラスによって SQLCLR API に返されます。具体的には、Transaction.Current が Null 以外になります。


ほとんどの場合、明示的にトランザクションにアクセスする必要はありません。データベース接続の場合、ADO.NET は、接続の Open() で Transaction.Current を自動的に確認し、意識する必要なしに、その接続をトランザクションに参加させます (ただし、enlist=false を接続文字列に指定している場合を除きます)。


トランザクション オブジェクトを直接使用する可能性のあるシナリオがいくつかあります。


    

  • ストアド プロシージャや関数の内部から外部トランザクションをアボートする場合。この場合は、単に Transaction.Current.Rollback() を呼び出すことができます。
    

    • 

  • 自動参加を実行しないリソース、または何らかの理由で初期化中に参加しなかったリソースを参加させる場合。
    

    • 

  • ユーザー自身をトランザクションに参加させ、選択処理に関与させたり、単に選択を行うタイミングを通知する場合。
    

    • 



ここでは BEGIN TRAN を実行する明示的な例を使用しましたが、SQLCLR をトランザクション内部で呼び出すことができたり、Transaction.Current が Null 以外になるシナリオが他にもあることに注意してください。たとえば、UPDATE ステートメント内部でユーザー定義マネージ関数を呼び出す場合、トランザクションは明示的に開始されませんが、呼び出しはトランザクション内で行われます。


System.Transactions を明示的に使用する


呼び出し元がトランザクションを開始しなかった場合でも、トランザクション内部で実行すべきコード ブロックがある場合は、System.Transactions API を使用できます。これは、クライアントや中間層でトランザクションを管理する際に使用するコードでも同じです。次に例を示します。


C#
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures {
  [Microsoft.SqlServer.Server.SqlProcedure()]
  public static void SampleSP() {
    // start a transaction block
    using(TransactionScope tx = new TransactionScope()) {
      // connect to the context connection
      using(SqlConnection conn = new SqlConnection("context connection=true")) {
        conn.Open();
        // do some changes to the local database
      }
      // connect to the remote database
      using(SqlConnection conn = new SqlConnection(
                                  "server=MyServer; database=AdventureWorks;" +
                                  "user id=MyUser; password=MyPassword")) {
        conn.Open();
        // do some changes to the remote database
      }
      // mark the transaction as complete
      tx.Complete();
    }
  }
}
Visual Basic .NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
  <Microsoft.SqlServer.Server.SqlProcedure()> _
  Public Shared Sub SampleSP()
   ' start a transaction block
    Using tx As New TransactionScope()
      ' connect to the context connection
      Using conn As New SqlConnection("context connection=true")
        conn.Open()
        ' do some changes to the local database
      End Using
      ' connect to a remote server (don't hardcode the conn string in real code)
      Using conn As New SqlConnection("server=MyServer; database=AdventureWorks;" & _
                                      "user id=MyUser; password=MyPassword")
        conn.Open()
        ' do some changes to the remote database
      End Using
      ' mark the transaction as completed
      tx.Complete()
    End Using
  End Sub
End Class



上記のサンプルでは、System.Transactions の最も簡単な使用方法を示しています。トランザクション処理すべきコードの周囲にトランザクションのスコープを配置するだけです。ブロックの終盤で、スコープに対して Complete メソッドを呼び出していることに注意してください。このメソッドは、このコード部分が正常に実行されたので、トランザクションをコミットできることを示しています。トランザクションをアボートする場合は、単に Complete を呼び出しません。


TransactionScope オブジェクトは、既定で、"適切な処理" を実行します。つまり、既にアクティブなトランザクションがあった場合、スコープはそのトランザクション内部で行われます。それ以外の場合は、新しいトランザクションが開始されます。この動作をカスタマイズできる他のオーバーロードもあります。


パターンは非常に単純です。トランザクションのスコープは、既にアクティブなトランザクションを取得するか、新しいトランザクションを開始するかのいずれかです。どちらの場合も、"using" ブロック内にあるので、コンパイラはブロックの最後に Dispose への呼び出しを導入します。スコープがブロックの最後に達する前に Complete の呼び出しを検出すると、トランザクションに対して commit を選択します。それに対して、Complete の呼び出しを検出できなかった場合 (たとえば、ブロックの中間辺りで例外がスローされた場合)、トランザクションは自動的にロールバックされます。




注意   SQL Server 2005 リリースでは、TransactionScope オブジェクトは SQLCLR 内部で実行される場合、常に分散トランザクションを使用します。つまり、まだ分散トランザクションが存在しない場合は、スコープによってトランザクションが昇格されます。その結果、ローカル サーバーのみに接続する場合にオーバーヘッドが生じます。その場合、SQL トランザクションのほうが軽量になります。一方、複数のリソース (たとえば、リモート データベースへの接続) を使用するシナリオでは、どのような場合でもトランザクションを昇格させる必要があります。そのため、追加のオーバーヘッドはありません。




コンテキスト接続のみを使用して接続する場合は、TransactionScope を使用しないことをお勧めします。


SQLCLR コード内で SQL トランザクションを使用する


もう 1 つの方法として、ローカル トランザクションしか処理しない、通常の SQL トランザクションを使用できます。


既存の SQL トランザクション API の使用は、クライアントまたは中間層での SQL トランザクションの動作と同等です。SQL ステートメント (たとえば、BEGIN TRAN) を使用するか、接続オブジェクトの BeginTransaction メソッドを呼び出すことができます。これにより、トランザクションのコミットやロールバックに使用できるトランザクション オブジェクト (たとえば、SqlTransaction) が返されます。


ストアド プロシージャや関数がトランザクション内から呼び出される可能性があるという意味で、このようなトランザクションを入れ子にできます。つまり、トランザクション内部から BeginTransaction を呼び出すことができます (これは "実際に" 入れ子になったトランザクションにすることを意味していません。T-SQL で BEGIN TRAN ステートメントを入れ子にしたときとまったく同じ動作になります)。


トランザクションの有効期間


T-SQL ストアド プロシージャで開始したトランザクションと SQLCLR コードで (上記のメソッドのいずれかを使用して) 開始したトランザクションには違いがあります。SQLCLR コードは、SQLCLR 呼び出しの開始時と終了時にトランザクションの状態を変えることができません。これにより、次に示すようないくつかの影響が出ます。


    

  • SQLCLR フレーム内部でトランザクションを開始できません。また、トランザクションをコミットまたはロールバックできません。これを行うと、SQL Server はフレームの終了時にエラーを生成します。
    

    • 

  • 同様に、SQLCLR コード内で外側のトランザクションをコミットまたはロールバックできません。
    

      

    • 同じプロシージャで開始していないトランザクションをコミットしようとすると、ランタイム エラーが発生します。
      

      • 

    • 同じプロシージャで開始していないトランザクションをロールバックしようとすると、トランザクションが失敗して、副作用のある他の操作が発生するのを防ぎますが、そのトランザクションは SQLCLR コードがアンワインドされるまで消去されません。この場合は実際には適切に処理が行われ、プロシージャ内部のエラーを検出したり、トランザクション全体がアボートするのを確認する場合に役立ちます。
      

      • 

    

    • 



まとめ


SQLCLR は優れたテクノロジで、多数の新しいシナリオを可能にします。SQLCLR 内部で ADO.NET を使用することは強力な構成で、ローカル サーバーとリモート サーバーの両方に対するデータ アクセスと負荷の高い処理を組み合わせることができ、トランザクション処理の正確性を完全に維持できるようになります。


その他のテクノロジと同様に、このテクノロジが役に立つ具体的なアプリケーション領域があります。すべてのプロシージャを SQLCLR を使用するように再作成する必要があるわけではなく、また、すべてのプロシージャから ADO.NET を使用してデータベースにアクセスする必要があるわけでもありません。逆に、ほとんどの場合、T-SQL で適切に機能します。ただし、高度なロジックや機能豊富なライブラリが SQL Server 内部で必要な場合には、SQLCLR と ADO.NET でこれらを実行します。


謝辞


この資料をレビューして役立つフィードバックを提供するために時間を割いてくださった Acey Bunch、Alazel Acheson、Alyssa Henry、Angel Saenz-Badillos、Chris Lee、Jian Zeng、Mary Chipman、および Steve Starck に感謝の意を表します。