SQL Server ストアド プロシージャのパフォーマンスを向上させる：テーブル値パラメータの威力

C# で SQL Server ストアド プロシージャにテーブル値パラメータを渡す方法

前提条件

• SQL Server 2008 以降
• .NET Framework 4.0 以降

手順

1. ストアド プロシージャを作成する

   ストアド プロシージャは、テーブル値パラメータを受け取るように作成する必要があります。テーブル値パラメータは、USERDEFINEDTABLE 型として定義されます。

   CREATE PROCEDURE dbo.MyStoredProcedure
       @MyTable TABLE (
           ID INT,
           Name NVARCHAR(50)
       )
   AS
   BEGIN
       -- ストアド プロシージャの処理
   END
   

2. C# コードでテーブル値パラメータを作成する

   テーブル値パラメータは、DataTable オブジェクトを使用して作成できます。

   DataTable myTable = new DataTable("MyTable");
   myTable.Columns.Add("ID", typeof(int));
   myTable.Columns.Add("Name", typeof(string));
   
   myTable.Rows.Add(1, "John Doe");
   myTable.Rows.Add(2, "Jane Doe");
   

3. SqlParameter オブジェクトを作成する

   SqlParameter オブジェクトを使用して、テーブル値パラメータをストアド プロシージャに渡します。

   SqlParameter mySqlParameter = new SqlParameter("@MyTable", SqlDbType.Structured);
   mySqlParameter.Value = myTable;
   

4. SqlCommand オブジェクトを作成してストアド プロシージャを実行する

   using (SqlConnection connection = new SqlConnection("connection string"))
   {
       connection.Open();
   
       using (SqlCommand command = new SqlCommand("dbo.MyStoredProcedure", connection))
       {
           command.CommandType = CommandType.StoredProcedure;
           command.Parameters.Add(mySqlParameter);
   
           command.ExecuteNonQuery();
       }
   }
   

• テーブル値パラメータは、パフォーマンスを向上させるために使用できますが、複雑なクエリを実行する場合には適していない場合があります。
• テーブル値パラメータを使用するには、SQL Server 2008 以降を使用する必要があります。
• .NET Framework 4.0 以降を使用する必要があります。

CREATE PROCEDURE dbo.MyStoredProcedure
       @MyTable TABLE (
           ID INT,
           Name NVARCHAR(50)
       )
   AS
   BEGIN
       -- ストアド プロシージャの処理
       SELECT * FROM @MyTable;
   END

DataTable myTable = new DataTable("MyTable");
myTable.Columns.Add("ID", typeof(int));
myTable.Columns.Add("Name", typeof(string));

myTable.Rows.Add(1, "John Doe");
myTable.Rows.Add(2, "Jane Doe");

SqlParameter mySqlParameter = new SqlParameter("@MyTable", SqlDbType.Structured);
mySqlParameter.Value = myTable;

using (SqlConnection connection = new SqlConnection("connection string"))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("dbo.MyStoredProcedure", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(mySqlParameter);

        command.ExecuteNonQuery();
    }
}

説明

このコードは、次のことを行います。

1. MyTable という名前の DataTable オブジェクトを作成します。
2. ID と Name という 2 つの列を DataTable に追加します。
3. SqlParameter の Value プロパティを DataTable オブジェクトに設定します。
4. dbo.MyStoredProcedure という名前の SqlCommand オブジェクトを作成します。
5. SqlCommand の CommandType プロパティを CommandType.StoredProcedure に設定します。
6. SqlParameter オブジェクトを SqlCommand の Parameters コレクションに追加します。
7. SqlCommand オブジェクトの ExecuteNonQuery メソッドを呼び出して、ストアド プロシージャを実行します。

このコードは、テーブル値パラメータを使用してストアド プロシージャにデータを渡す方法を示す基本的な例です。実際のコードは、ニーズに応じて変更する必要があります。

• このコードは、SQL Server 2008 以降と .NET Framework 4.0 以降が必要です。

SqlClient を使用する

SqlClient 名前空間を使用すると、テーブル値パラメータをより簡単に作成および渡すことができます。

using (SqlConnection connection = new SqlConnection("connection string"))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("dbo.MyStoredProcedure", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        // SqlClient を使用してテーブル値パラメータを作成する
        SqlParameter mySqlParameter = command.Parameters.Add("@MyTable", SqlDbType.Structured);
        mySqlParameter.TypeName = "MyTableType"; // ユーザー定義テーブル型の名前

        // DataTable を SqlParameter に追加する
        mySqlParameter.Value = myTable.CreateDataReader();

        command.ExecuteNonQuery();
    }
}

TVP を使用する

テーブル値パラメータ (TVP) は、SQL Server 2010 で導入された新しい機能です。 TVP を使用すると、ストアド プロシージャの引数としてテーブル型を定義できます。

CREATE TYPE MyTableType AS TABLE (
    ID INT,
    Name NVARCHAR(50)
)

using (SqlConnection connection = new SqlConnection("connection string"))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("dbo.MyStoredProcedure", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        // TVP を使用してストアド プロシージャの引数を定義する
        SqlParameter mySqlParameter = command.Parameters.Add("@MyTable", SqlDbType.Structured);
        mySqlParameter.TypeName = "MyTableType";

        // DataTable を SqlParameter に追加する
        using (SqlDataRecord dataRecord = new SqlDataRecord(mySqlParameter.TypeName))
        {
            foreach (DataRow row in myTable.Rows)
            {
                dataRecord.SetInt32(0, (int)row["ID"]);
                dataRecord.SetString(1, (string)row["Name"]);

                command.Parameters.AddWithValue("@MyTable", dataRecord);
            }
        }

        command.ExecuteNonQuery();
    }
}

.NET Framework Data Provider for SQL Server を使用する

.NET Framework Data Provider for SQL Server には、テーブル値パラメータを処理するためのいくつかのクラスが含まれています。

using (SqlConnection connection = new SqlConnection("connection string"))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("dbo.MyStoredProcedure", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        // SqlDataReader を使用してテーブル値パラメータを作成する
        SqlParameter mySqlParameter = command.Parameters.Add("@MyTable", SqlDbType.Structured);
        mySqlParameter.TypeName = "MyTableType";

        using (SqlDataReader reader = myTable.CreateDataReader())
        {
            // SqlDataReader を SqlParameter に追加する
            command.Parameters.AddWithValue("@MyTable", reader);
        }

        command.ExecuteNonQuery();
    }
}

Dapper を使用する

Dapper は、.NET Framework と .NET Core 用の軽量なオブジェクト マッパーです。 Dapper を使用すると、テーブル値パラメータを簡単に作成および渡すことができます。

using (var connection = new SqlConnection("connection string"))
{
    connection.Open();

    connection.Execute("dbo.MyStoredProcedure", new { MyTable = myTable });
}

上記の方法はそれぞれ長所と短所があります。最適な方法は、特定のニーズによって異なります。

• SqlClient は、最も単純でわかりやすい方法ですが、柔軟性に欠けます。
• TVP は、最も柔軟性がありますが、SQL Server 2010 以降が必要です。
• .NET Framework Data Provider for SQL Server は、TVP と同等の柔軟性を提供しますが、複雑でわかりにくいかもしれません。
• Dapper は、シンプルで使いやすいですが、サードパーティ製のライブラリを使用する必要があります。

• [https://www.mssqltips