Microsoft.Data.SqlClient でパラメーターを構成する

パラメータはコマンドの Transact-SQL(T-SQL)テキストとは別に値を送ります。 SQL文に値を連結または補間する代わりに、 SqlParameter オブジェクトを使いましょう。 パラメータはSQLインジェクションから守り、値のデータベース型を保持するのに役立ちます。

この記事の例では、connection という名前のオープン SqlConnection が AdventureWorks サンプル データベース をターゲットとしていることを前提としています。

C#の例は using Microsoft.Data.SqlClient;を必要とします。 SqlDbTypeを使う例もまた、using System.Data;が必要です。

入力パラメータの追加

コマンドテキスト内の各値プレースホルダーごとにパラメータを追加してください。 パラメータのSQL Server型を指定し、その値を割り当てます:

const string sql = """
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
      AND ListPrice >= @minimumPrice
    ORDER BY ListPrice;
    """;

using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;
command.Parameters.Add("@minimumPrice", SqlDbType.Money).Value = 1_000m;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)}: {reader.GetString(1)} ({reader.GetDecimal(2):C})");
}

コマンド テキストと SqlParameter の両方で、パラメーター名の表記を同じにします。 SQL Serverはサーバー コレーションを使って変数名を照合するため、大文字と小文字を区別するコレーションを持つサーバーでは、一致しないと「スカラー変数を宣言しなければならない」エラーになります。

パラメーターのプレースホルダーを操作する

Microsoft.Data.SqlClientは@parameterNameという形式の名前付きパラメーターを使用します。 位置指定の疑問符(?)プレースホルダーはサポートされていません。

パラメータプレースホルダーは値を表し、SQL識別子や構文ではありません。 テーブル名、列名、ソート方向、あるいはSQLフラグメント全体にパラメータを使うことはできません。 アプリケーションが動的に識別子を選択した場合は、許可リストに対して検証を行い、検証後にのみコマンドテキストに追加してください。 すべてのデータ値をパラメータとして渡し続けてください。

テキストコマンドについては、Microsoft.Data.SqlClient はパラメータ化された文を sp_executesql を介して SQL Server に送ります。 文のテキストとパラメータ定義が同じままであれば、SQL Serverは実行計画を再利用できます。

パラメーターのデータ型を指定する

アプリケーションがターゲットSQL Server型を認識したときに明示的にSqlDbTypeを設定します。 また、該当する場合は、たとえば Size、Precision、Scale のようなタイプ固有のメタデータも設定してください。

この例は、Person.Person 内の nvarchar(50) および datetime 列に一致します:

const string sql = """
    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE LastName = @lastName
      AND ModifiedDate >= @modifiedSince
    ORDER BY BusinessEntityID;
    """;

using var command = new SqlCommand(sql, connection);

SqlParameter lastName = command.Parameters.Add(
    "@lastName",
    SqlDbType.NVarChar,
    50);
lastName.Value = "O'Brien";

SqlParameter modifiedSince = command.Parameters.Add(
    "@modifiedSince",
    SqlDbType.DateTime);
modifiedSince.Value = new DateTime(2013, 1, 1);

パラメータメタデータをターゲット列にマッチさせることで暗黙の変換を避け、文字列長や小数スケールの変更によってパラメータ定義が変わるのを防ぎます。

小数値は、精度とスケールを明示的に設定してください。 Production.ProductのWeight列はdecimal(8, 2)を使用します:

const string sql = """
    SELECT ProductID, Name, Weight
    FROM Production.Product
    WHERE Weight >= @minimumWeight
    ORDER BY Weight;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter minimumWeight = command.Parameters.Add(
    "@minimumWeight",
    SqlDbType.Decimal);
minimumWeight.Precision = 8;
minimumWeight.Scale = 2;
minimumWeight.Value = 10.25m;

データベースのNULL値を渡します

データベース NULL を送信するには、Value を割り当ててください。 C# null は、通常のスカラーパラメーターに対応する SQL Server の NULL を表しません:

const string sql = """
    SELECT SalesOrderID, OrderDate, SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE (@salesPersonId IS NULL OR SalesPersonID = @salesPersonId)
    ORDER BY SalesOrderID;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter salesPersonId = command.Parameters.Add(
    "@salesPersonId",
    SqlDbType.Int);

int? selectedSalesPersonId = null;
salesPersonId.Value = selectedSalesPersonId.HasValue
    ? selectedSalesPersonId.Value
    : DBNull.Value;

推論されたメタデータは必要に応じて使用します

AddWithValueメソッドは実行時の値からパラメータのメタデータを推論します。 推論は、推論されたメタデータが安定していてデータベースの種類と一致している場合に便利です。 文字列の長さ、小数点の精度、その他の値特性が変化すると、異なるパラメータ定義を生成することができます。

ターゲットスキーマが既知の本番コマンドには、明示的なAddを持つSqlDbTypeを優先します。 C# null を AddWithValueに渡さず、型を指定して DBNull.Value を割り当ててください。

コマンドパラメータを再利用

パラメーターを一度作成し、異なる値で同じコマンドを実行する際は、その Value のみを変更するようにします:

const string sql = """
    SELECT TOP (1) Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ListPrice DESC;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter subcategoryId = command.Parameters.Add(
    "@subcategoryId",
    SqlDbType.Int);

foreach (int id in new[] { 1, 2, 3 })
{
    subcategoryId.Value = id;

    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        Console.WriteLine($"{id}: {reader.GetString(0)}");
    }
}

同じ名前のパラメータを各実行ごとに追加しないでください。 既存のパラメータを再利用するか、コレクションをクリアしてから再構築してください。

パラメータの方向を設定する

Directionプロパティはコマンドがパラメータをどのように使用するかを示します。 ParameterDirection値に設定します。

出力、入出力、戻り値のパラメーターについては、コマンドの実行が完了してから Value を参照します。 コマンドが行を返した場合は、その値を読む前に SqlDataReader を閉じるか廃棄してください。

ストアドプロシージャの完全な例については、「ストアドプロシージャでデータを変更する」を参照してください。

ストアドプロシージャでパラメーターを使用する

StoredProcedureを設定し、プロシージャのパラメータ名を使用します。 この例は、AdventureWorksに含まれる dbo.uspGetEmployeeManagers 手続きを次のように呼び出しています:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
    "@BusinessEntityID",
    SqlDbType.Int).Value = 6;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetString(2)} {reader.GetString(3)}");
}

パラメータ名はストアドプロシージャで宣言された名前と一致しなければなりません。

テーブル値パラメーターの構成

テーブル値パラメータは、ストアドプロシージャやパラメーター化されたステートメントに複数行を送ります。 構成方法は以下の通りです:

1. SqlDbType.Structuredを設定します。
2. TypeNameをデータベースのユーザー定義テーブルタイプに設定します。
3. DataTable、DbDataReader、または IEnumerable<SqlDataRecord> の行ソースを割り当てます。

列が定義されているが行がない DataTable は、空のテーブルを送信します。 空の IEnumerable<SqlDataRecord> は ArgumentException をスローします。その入力フォームに対して 0 行を送信するには、C# の null を割り当てます。 テーブル値パラメータに DBNull.Value を割り当てないでください。

型の作成、行マッピング、完全な例については、「テーブル値パラメータ」を参照してください。

ストリーミング値を設定

Value に Stream、TextReader、XmlReader を割り当てることで、段階的に大きな値を設定します。 メモリ容量に制限のある転送を行う場合は、(max)、xml、または json のパラメーター型を使用します。 Microsoft.Data.SqlClient は、(max) 以外の文字型およびバイナリ型、ならびにレガシーな大容量オブジェクト型について、実行前にソースをバッファリングします。

パラメーターの型は値と一致しなければなりません:

Microsoft.Data.SqlClientは、オブジェクト型、SqlTypeException、または宣言サイズに互換性がない場合、SqlDbType、ArgumentException、またはInvalidCastExceptionをスローします。 アップロードおよびダウンロードの完全な例については、 SqlClientストリーミングサポートをご覧ください。

パラメーター情報を派生させる

DeriveParametersSQL Serverでストアドプロシージャのパラメータメタデータをクエリし、コマンドの既存のパラメータコレクションを置き換えます:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(command);
command.Parameters["@BusinessEntityID"].Value = 6;

DeriveParameters を呼び出すたびに、データベースとの追加の往復通信が必要です。 必要に応じて開発時にメタデータを導き出し、パフォーマンスに敏感なパスでパラメータを明示的に定義します。

関連するコンテンツ

• コマンドを実行する
• ストアド プロシージャによるデータの変更
• テーブル値パラメーター
• SqlClient ストリーミング サポート