C#、SQL Server、T-SQL を使ったストアドプロシージャ開発のベストプラクティス

2024-04-02

C#、SQL Server、T-SQL を使って配列を SQL Server ストアド プロシージャに渡す方法

前提条件

  • Visual Studio 2019 または 2022
  • SQL Server Management Studio (SSMS)
  • SQL Server データベース

手順

  1. C# プロジェクトの作成

  2. NuGet パッケージの追加

    ソリューション エクスプローラーでプロジェクトを右クリックし、NuGet パッケージの管理 を選択します。NuGet パッケージ マネージャー で、以下のパッケージを検索してインストールします。

    • System.Data.SqlClient
    • Microsoft.Extensions.Configuration

  3. App.config ファイルの編集

<configuration>
  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;" />
  </connectionStrings>
</configuration>

  1. ストアド プロシージャの作成

CREATE PROCEDURE [dbo].[GetNames]
  @names VARCHAR(MAX)

AS
BEGIN
  SELECT Name
  FROM dbo.People
  WHERE Name IN (SELECT Value FROM dbo.SplitString(@names, ','))
END

  1. C# コードの作成

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            // 配列を初期化します
            string[] names = new string[] { "John", "Jane", "Michael" };

            // 接続文字列を取得します
            string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            // SqlConnection オブジェクトを作成します
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // SqlCommand オブジェクトを作成します
                using (SqlCommand command = new SqlCommand("dbo.GetNames", connection))
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    // パラメータを追加します
                    command.Parameters.AddWithValue("@names", string.Join(",", names));

                    // SqlCommand を実行します
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    // 結果を読み取ります
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["Name"]);
                    }
                }
            }
        }
    }
}

  1. コードの実行

John
Jane
Michael

説明

このコードでは、以下の方法で配列を SQL Server ストアド プロシージャに渡しています。

  1. C# コードで配列を初期化します。
  2. SqlCommand オブジェクトを作成し、ストアド プロシージャの名前を指定します。
  3. AddWithValue メソッドを使って、ストアド プロシージャのパラメータに配列を渡します。
  4. SqlCommand オブジェクトを実行します。
  • SQL Server には、テーブル値パラメータと呼ばれる、ストアド プロシージャに配列を渡す別の方法もあります。


// C# コード

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            // 配列を初期化します
            string[] names = new string[] { "John", "Jane", "Michael" };

            // 接続文字列を取得します
            string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            // SqlConnection オブジェクトを作成します
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // SqlCommand オブジェクトを作成します
                using (SqlCommand command = new SqlCommand("dbo.GetNames", connection))
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    // パラメータを追加します
                    command.Parameters.AddWithValue("@names", string.Join(",", names));

                    // SqlCommand を実行します
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    // 結果を読み取ります
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["Name"]);
                    }
                }
            }
        }
    }
}

// SQL Server ストアド プロシージャ

CREATE PROCEDURE [dbo].[GetNames]
  @names VARCHAR(MAX)

AS
BEGIN
  SELECT Name
  FROM dbo.People
  WHERE Name IN (SELECT Value FROM dbo.SplitString(@names, ','))
END

// App.config ファイル

<configuration>
  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;" />
  </connectionStrings>
</configuration>

John
Jane
Michael

このサンプルコードは、C#、SQL Server、T-SQL を使って配列を SQL Server ストアド プロシージャに渡す方法を示しています。



SQL Server ストアド プロシージャに配列を渡す他の方法

XML を使う方法

配列を XML に変換し、ストアド プロシージャのパラメータとして渡すことができます。

// 配列を XML に変換します
string xml = string.Join(",", names.Select(x => $"<value>{x}</value>"));

// SqlCommand オブジェクトを作成します
using (SqlCommand command = new SqlCommand("dbo.GetNames", connection))
{
    command.CommandType = System.Data.CommandType.StoredProcedure;

    // パラメータを追加します
    command.Parameters.AddWithValue("@names", xml);

    // SqlCommand を実行します
    ...
}

SQL Server ストアド プロシージャ

CREATE PROCEDURE [dbo].[GetNames]
  @names XML

AS
BEGIN
  SELECT Name
  FROM dbo.People
  WHERE Name IN (SELECT value FROM @names.nodes('/values/value'))
END

テーブル変数を使う方法

一時テーブルを作成し、配列のデータを挿入してから、ストアド プロシージャのパラメータとして渡すことができます。

// テーブル変数を作成します
using (SqlCommand command = new SqlCommand("CREATE TABLE #Names (Name VARCHAR(MAX))", connection))
{
    command.ExecuteNonQuery();
}

// 配列のデータを挿入します
foreach (string name in names)
{
    using (SqlCommand command = new SqlCommand("INSERT INTO #Names (Name) VALUES (@name)", connection))
    {
        command.Parameters.AddWithValue("@name", name);
        command.ExecuteNonQuery();
    }
}

// SqlCommand オブジェクトを作成します
using (SqlCommand command = new SqlCommand("dbo.GetNames", connection))
{
    command.CommandType = System.Data.CommandType.StoredProcedure;

    // パラメータを追加します
    command.Parameters.AddWithValue("@names", "#Names");

    // SqlCommand を実行します
    ...
}

CREATE PROCEDURE [dbo].[GetNames]
  @names VARCHAR(MAX)

AS
BEGIN
  SELECT Name
  FROM dbo.People
  WHERE Name IN (SELECT Name FROM @names)
END

分割して渡す方法

// 配列を分割します
int chunkSize = 100;
for (int i = 0; i < names.Length; i += chunkSize)
{
    // パラメータを追加します
    command.Parameters.AddWithValue("@names" + i, string.Join(",", names.Skip(i).Take(chunkSize)));

    // SqlCommand を実行します
    ...
}

CREATE PROCEDURE [dbo].[GetNames]
  @names1 VARCHAR(MAX),
  @names2 VARCHAR(MAX),
  ...

AS
BEGIN
  SELECT Name
  FROM dbo.People
  WHERE Name IN (@names1, @names2, ...)
END

これらの方法にはそれぞれメリットとデメリットがあります。どの方法を使うかは、状況によって異なります。

C#、SQL Server、T-SQL を使って配列を SQL Server ストアド プロシージャに渡す方法はいくつかあります。どの方法を使うかは、状況によって異なります。


c# sql-server t-sql

データの重複を防ぎ、検索速度を向上させる: SQL Serverにおけるユニークキーとインデックスの役割

答え： はい、ユニークキーはインデックスの一種です。詳細解説：ユニークキー とは、テーブル内の各行を一意に識別する列または列の組み合わせです。インデックス は、テーブル内のデータを高速に検索するために使用されるデータ構造です。ユニークキーとインデックスの関係：...

 
sql server indexing unique key データの重複を防ぎ、検索速度を向上させる: SQL Serverにおけるユニークキーとインデックスの役割

EOMONTH関数とDAY関数で賢く使い分ける！SQL Serverで月の最大日数を取得する方法

方法1：EOMONTH関数を使うEOMONTH関数は、指定した月の日付のうち、最も最後の日の値を返します。この関数を使うには、以下の構文を使用します。date_expression: 対象となる月を含む日付を指定します。省略可。指定しない場合は、現在のシステム日付が使われます。...

 
sql server datetime EOMONTH関数とDAY関数で賢く使い分ける！SQL Serverで月の最大日数を取得する方法

SQL Server: DEFAULT 句、CONSTRAINT 句、トリガー、ビュー、ストアドプロシージャを使用したタイムスタンプフィールドのデフォルト値設定

このチュートリアルでは、SQL Server のタイムスタンプフィールドにデフォルト値として datetime フィールドを追加する方法について解説します。方法以下の方法で、datetime フィールドのデフォルト値をタイムスタンプフィールドに追加できます。...

 
sql server datetime SQL Server: DEFAULT 句、CONSTRAINT 句、トリガー、ビュー、ストアドプロシージャを使用したタイムスタンプフィールドのデフォルト値設定

【SQL Server初心者向け】「文字列またはバイナリ データが切り捨てられます」のエラーを理解して解決する

エラー概要このエラーは、SQL Serverでデータを挿入または更新しようとするときに発生します。挿入または更新しようとしているデータが、カラムに定義されているサイズよりも大きい場合に発生します。具体的には、以下の2つのケースが考えられます。...

 
sql server 【SQL Server初心者向け】「文字列またはバイナリ データが切り捨てられます」のエラーを理解して解決する

SQL Serverへの接続方法：Entity Framework Core、Dapper、LINQ to SQLなど

SQL ServerへのADO. NET接続が時々成功し、時々失敗する場合、様々な原因が考えられます。問題を特定し、解決するには、以下の点を確認する必要があります。接続文字列接続文字列に誤りがないか確認してください。サーバー名、データベース名、ユーザー名、パスワードなどが正しく設定されていることを確認します。...

 
sql server ado.net SQL Serverへの接続方法：Entity Framework Core、Dapper、LINQ to SQLなど

SQL SQL SQL SQL Amazon で見る