C# から SQL Server ストアドプロシージャに List<> を渡す方法

2024-04-07

C# から SQL ストアドプロシージャに List<> を渡す方法

Table-Valued Parameter (TVP) を使用する

TVP は、ストアドプロシージャに渡すデータ構造を事前に定義できる機能です。C# の List<> 型と同様の構造を持つ TVP を定義することで、List<> の内容を効率的にストアドプロシージャに渡すことができます。

TVP を使用する場合のメリット

  • データ構造を事前に定義することで、コードの可読性と保守性を向上させることができる
  • パラメータのサイズが大きくなっても、効率的にデータを渡すことができる
  • 事前に TVP を定義する必要があるため、開発の手間が増える
  • 古いバージョンの SQL Server では使用できない

TVP を使用したサンプルコード

// C#

// ストアドプロシージャで使用する TVP の定義
[TableValuedParameter("MyTable")]
public class MyTableType
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// ストアドプロシージャの呼び出し
using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var myTable = new List<MyTableType>
    {
        new MyTableType { Id = 1, Name = "John Doe" },
        new MyTableType { Id = 2, Name = "Jane Doe" }
    };

    var tvpParam = cmd.Parameters.AddWithValue("@MyTable", myTable);
    tvpParam.TypeName = "MyTableType";

    cmd.ExecuteNonQuery();
}

// SQL Server

CREATE TYPE MyTableType AS TABLE
(
    Id INT,
    Name VARCHAR(50)
)

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable MyTableType READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

SqlParameter は、ストアドプロシージャに個々のパラメータを渡すためのオブジェクトです。List<> 型のデータを SqlParameter に渡すには、AddWithValue メソッドを使用して、List<> を IEnumerable 型に変換する必要があります。

SqlParameter を使用する場合のメリット

  • TVP を使用するよりも開発の手間が軽い
  • データ構造を事前に定義できないため、コードの可読性と保守性が低くなる
  • パラメータのサイズが大きくなると、データ転送に時間がかかる
// C#

using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var myTable = new List<int> { 1, 2, 3 };

    var param = cmd.Parameters.AddWithValue("@MyTable", myTable);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "int";

    cmd.ExecuteNonQuery();
}

// SQL Server

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable INT READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

XML を使用する

List<> 型のデータを XML に変換し、ストアドプロシージャに渡す方法もあります。

  • データ構造を事前に定義する必要がない
  • コードが複雑になる
  • データ転送に時間がかかる
// C#

using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var myTable = new List<string> { "John Doe", "Jane Doe" };

    var xml = new XDocument(
        new XElement("MyTable",
            myTable.Select(x => new XElement("Item", x))
        )
    );

    var param = cmd.Parameters.AddWithValue("@MyTable", xml.ToString());
    param.


C# から SQL ストアドプロシージャに List<> を渡す方法のサンプルコード

Table-Valued Parameter (TVP) を使用する

// C#

// ストアドプロシージャで使用する TVP の定義
[TableValuedParameter("MyTable")]
public class MyTableType
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// ストアドプロシージャの呼び出し
using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var myTable = new List<MyTableType>
    {
        new MyTableType { Id = 1, Name = "John Doe" },
        new MyTableType { Id = 2, Name = "Jane Doe" }
    };

    var tvpParam = cmd.Parameters.AddWithValue("@MyTable", myTable);
    tvpParam.TypeName = "MyTableType";

    cmd.ExecuteNonQuery();
}

// SQL Server

CREATE TYPE MyTableType AS TABLE
(
    Id INT,
    Name VARCHAR(50)
)

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable MyTableType READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

SqlParameter を使用する

// C#

using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var myTable = new List<int> { 1, 2, 3 };

    var param = cmd.Parameters.AddWithValue("@MyTable", myTable);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "int";

    cmd.ExecuteNonQuery();
}

// SQL Server

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable INT READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

XML を使用する

// C#

using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var myTable = new List<string> { "John Doe", "Jane Doe" };

    var xml = new XDocument(
        new XElement("MyTable",
            myTable.Select(x => new XElement("Item", x))
        )
    );

    var param = cmd.Parameters.AddWithValue("@MyTable", xml.ToString());
    param.SqlDbType = SqlDbType.Xml;

    cmd.ExecuteNonQuery();
}

// SQL Server

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable XML READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

データテーブルを使用する

// C#

using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

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

    foreach (var item in myTable)
    {
        myTable.Rows.Add(item.Id, item.Name);
    }

    var param = cmd.Parameters.AddWithValue("@MyTable", myTable);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "MyTableType";

    cmd.ExecuteNonQuery();
}

// SQL Server

CREATE TYPE MyTableType AS TABLE
(
    Id INT,
    Name VARCHAR(50)
)

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable MyTableType READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END


JSON を使用する

JSON は、データ構造を記述するための軽量なフォーマットです。List<> 型のデータを JSON に変換し、ストアドプロシージャに渡すことができます。

// C#

using (var connection = new SqlConnection("connection string"))
{
    var cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var myTable = new List<string> { "John Doe", "Jane Doe" };

    var json = JsonConvert.SerializeObject(myTable);

    var param = cmd.Parameters.AddWithValue("@MyTable", json);
    param.SqlDbType = SqlDbType.NVarChar;

    cmd.ExecuteNonQuery();
}

// SQL Server

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable NVARCHAR(MAX) READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

Dapper を使用する

Dapper は、C# と SQL Server 間のデータアクセスを簡略化するオープンソースのライブラリです。Dapper を使用すると、List<> 型のデータを簡単にストアドプロシージャに渡すことができます。

  • パフォーマンスが向上する
  • Dapper の使い方を習得する必要がある
// C#

using (var connection = new SqlConnection("connection string"))
{
    var myTable = new List<string> { "John Doe", "Jane Doe" };

    var result = connection.Query<int>("MyStoredProcedure", new { MyTable = myTable });
}

// SQL Server

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable NVARCHAR(MAX) READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

Entity Framework は、C# と SQL Server 間のオブジェクト関係マッピング (ORM) を提供するオープンソースのフレームワークです。Entity Framework を使用すると、List<> 型のデータを簡単にストアドプロシージャに渡すことができます。

  • オブジェクト指向の開発が可能になる
  • Entity Framework の使い方を習得する必要がある
// C#

using (var context = new MyContext())
{
    var myTable = new List<string> { "John Doe", "Jane Doe" };

    var result = context.Database.SqlQuery<int>("MyStoredProcedure", new SqlParameter("@MyTable", myTable));
}

// SQL Server

CREATE PROCEDURE MyStoredProcedure
(
    @MyTable NVARCHAR(MAX) READONLY
)
AS
BEGIN
    -- MyTable の内容を処理
END

上記は、C# から SQL Server ストアドプロシージャに List<> を渡すその他の方法のサンプルコードです。どの方法を使用するかは、状況によって異なります。


c# sql sql-server

MySQLのパフォーマンスを劇的に向上させる！Profiler ツールの使い方

MySQL には、いくつかの Profiler ツールがあります。MySQL Profiler：MySQL に組み込まれたツールです。クエリの実行時間、スキャンされた行数、使用されたメモリなど、クエリのパフォーマンスに関する情報を収集します。...

 
mysql sql server database MySQLのパフォーマンスを劇的に向上させる！Profiler ツールの使い方

SQL Server Management Studio：GUIによるアクセス権限の設定

この解説では、SQL Serverデータベースのテーブルへの読み書きアクセスを許可するためのT-SQLコマンドについて説明します。T-SQLコマンドデータベースのテーブルへの読み書きアクセスを許可するには、GRANTステートメントを使用します。...

 
sql server t SQL Server Management Studio：GUIによるアクセス権限の設定

sys.sp_copy_data_in_batchesで大量データも楽々！SQL Serverテーブル間データ移行の超効率化

INSERT INTO ステートメント最も基本的な方法は、INSERT INTO ステートメントを使用して、元のテーブルからデータを新しいテーブルに挿入することです。構文は以下の通りです。例:この方法は、シンプルなデータ移動に適しています。...

 
sql server sys.sp_copy_data_in_batchesで大量データも楽々！SQL Serverテーブル間データ移行の超効率化

SQL Serverで複数列を一度に変更する方法：ALTER COLUMNコマンド

SQL Serverでテーブル構造を変更する場合、ALTER TABLEコマンドを使用します。このコマンドには、列を追加、削除、変更、名前変更など、さまざまな操作を実行するためのオプションが含まれています。本記事では、ALTER COLUMNオプションを使用して、複数の列を一度に変更する方法について解説します。...

 
sql server t alter column SQL Serverで複数列を一度に変更する方法：ALTER COLUMNコマンド

SQL SQL SQL SQL Amazon で見る