次の方法で共有


パラメータおよびパラメータのデータ型の構成 (ADO.NET)

更新 : November 2007

コマンド オブジェクトは、パラメータを使用して SQL ステートメントまたはストアド プロシージャに値を渡すことによって、型チェックと検証の機能を実現します。コマンド テキストとは異なり、パラメータの入力は実行可能なコードとしてではなく、リテラル値として扱われます。これにより、攻撃者がサーバーのセキュリティを侵害するコマンドを SQL ステートメントに "注入" する SQL インジェクション攻撃を防ぐことができます。

パラメータ化コマンドによりクエリ実行パフォーマンスも向上します。これは、データベース サーバーが入力コマンドを適切なキャッシュ済みクエリ プランに正確に一致させるのに役立つためです。詳細については、SQL Server オンライン ブックの「実行プランのキャッシュと再利用」および「パラメータと実行プランの再利用」を参照してください。セキュリティおよびパフォーマンス上の利点に加え、パラメータ化コマンドを使用すると、データ ソースに渡す値を簡単に扱うことができます。

DbParameter オブジェクトは、コンストラクタを使って作成できるほか、DbParameterCollection コレクションの Add メソッドを呼び出し、DbParameterCollection にオブジェクトを追加することによって作成することもできます。Add メソッドは、コンストラクタ引数または既存のパラメータ オブジェクトを入力として受け取ります。この点はデータ プロバイダによっても異なります。

ParameterDirection プロパティの指定

パラメータを追加する際は、入力パラメータとは別に、パラメータの ParameterDirection プロパティを指定する必要があります。ParameterDirection で使用できる ParameterDirection の値を次の表に示します。

メンバ名

説明

Input

このパラメータは入力パラメータです。これは、既定の設定です。

InputOutput

このパラメータは入力と出力の両方の機能を持っています。

Output

このパラメータは出力パラメータです。

ReturnValue

パラメータは、ストアド プロシージャ、組み込み関数、ユーザー定義関数などの操作からの戻り値を表します。

パラメータのプレースホルダの使用

パラメータのプレースホルダの構文はデータ ソースに依存します。.NET Framework のデータ プロバイダによって、パラメータおよびパラメータのプレースホルダの名前付けや指定方法が異なります。次の表に示すように、データ ソースごとに固有の構文が採用されています。

データ プロバイダ

パラメータの名前付け構文

System.Data.SqlClient

@parametername 形式の名前付きパラメータが使用されます。

System.Data.OleDb

疑問符 (?) で指定される位置パラメータ マーカーが使用されます。

System.Data.Odbc

疑問符 (?) で指定される位置パラメータ マーカーが使用されます。

System.Data.OracleClient

:parmname (または parmname) 形式の名前付きパラメータが使用されます。

パラメータのデータ型の指定

パラメータのデータ型は .NET Framework データ プロバイダに固有の属性です。型が指定されている場合は、その値がデータ ソースに渡される前に Parameter の値が .NET Framework データ プロバイダ型に変換されます。Parameter オブジェクトの DbType プロパティを特定の DbType に設定する一般的な方法で Parameter の型を指定することもできます。

Parameter オブジェクトの .NET Framework データ プロバイダ型は、Parameter オブジェクトの Value の .NET Framework 型、または Parameter オブジェクトの DbType から推論されます。Parameter 値として渡されるオブジェクトまたは指定された DbType に基づいて推論される Parameter 型を、次の表に示します。

.NET Framework 型

DbType

SqlDbType

OleDbType

OdbcType

OracleType

bool

Boolean

Bit

Boolean

Bit

Byte

byte

Byte

TinyInt

UnsignedTinyInt

TinyInt

Byte

byte[]

Binary

VarBinary。バイト配列が VarBinary の最大サイズ (8000 バイト) より大きい場合、この暗黙の変換はエラーになります。8000 バイトを超えるバイト配列の場合は、明示的に SqlDbType を設定してください。

VarBinary

Binary

Raw

char

 

char から SqlDbType への推論はサポートされていません。

Char

Char

Byte

DateTime

DateTime

DateTime

DBTimeStamp

DateTime

DateTime

DateTimeOffset

DateTimeOffset

SQL Server 2008 の DateTimeOffset。SQL Server 2008 より前のバージョンの SQL Server では、DateTimeOffset から SqlDbType への推論はサポートされていません。

DateTime

Decimal

Decimal

Decimal

Decimal

Numeric

Number

double

Double

Float

Double

Double

Double

float

Single

Real

Single

Real

Float

Guid

Guid

UniqueIdentifier

Guid

UniqueIdentifier

Raw

Int16

Int16

SmallInt

SmallInt

SmallInt

Int16

Int32

Int32

Int

Int

Int

Int32

Int64

Int64

BigInt

BigInt

BigInt

Number

object

Object

Variant

Variant

Object から OdbcType への推論はサポートされていません。

Blob

string

String

NVarChar。文字列が NVarChar の最大サイズ (4000 文字) より大きい場合、この暗黙の変換はエラーになります。4000 文字を超える文字列の場合は、明示的に SqlDbType を設定してください。

VarWChar

NVarChar

NVarChar

TimeSpan

Time

SQL Server 2008 の Time。SQL Server 2008 より前のバージョンの SQL Server では、TimeSpan から SqlDbType への推論はサポートされていません。

DBTime

Time

DateTime

UInt16

UInt16

UInt16 から SqlDbType への推論はサポートされていません。

UnsignedSmallInt

Int

UInt16

UInt32

UInt32

UInt32 から SqlDbType への推論はサポートされていません。

UnsignedInt

BigInt

UInt32

UInt64

UInt64

UInt64 から SqlDbType への推論はサポートされていません。

UnsignedBigInt

Numeric

Number

 

AnsiString

VarChar

VarChar

VarChar

VarChar

 

AnsiStringFixedLength

Char

Char

Char

Char

 

Currency

Money

Currency

Currency から OdbcType への推論はサポートされていません。

Number

 

Date

SQL Server 2008 の Date。SQL Server 2008 より前のバージョンの SQL Server では、Date から SqlDbType への推論はサポートされていません。

DBDate

Date

DateTime

 

SByte

SByte から SqlDbType への推論はサポートされていません。

TinyInt

SByte から OdbcType への推論はサポートされていません。

SByte

 

StringFixedLength

NChar

WChar

NChar

NChar

 

Time

SQL Server 2008 の Time。SQL Server 2008 より前のバージョンの SQL Server では、Time から SqlDbType への推論はサポートされていません。

DBTime

Time

DateTime

 

VarNumeric

VarNumeric から SqlDbType への推論はサポートされていません。

VarNumeric

VarNumeric から OdbcType への推論はサポートされていません。

Number

yy6y35y8.alert_note(ja-jp,VS.90).gifメモ :

decimal から他の型への変換は縮小変換になるため、decimal 値は最も近い整数値に切り捨てられます。変換結果が対象の型にならなかった場合、OverflowException がスローされます。

yy6y35y8.alert_note(ja-jp,VS.90).gifメモ :

サーバーに NULL パラメータ値を送信する場合は、null (Visual Basic の場合は Nothing) ではなく、DBNull を指定する必要があります。システムの NULL 値は、値のない空オブジェクトです。DBNull は、NULL 値を表すために使用されます。データベースの NULL 値の詳細については、「NULL 値の処理 (ADO.NET)」を参照してください。

パラメータ情報の派生

DbCommandBuilder クラスを使用してストアド プロシージャからパラメータを派生させることができます。SqlCommandBuilder クラスと OleDbCommandBuilder クラスはどちらも静的メソッド DeriveParameters を提供します。このメソッドは、ストアド プロシージャから得られたパラメータ情報を使用して、コマンド オブジェクトのパラメータ コレクションを設定します。DeriveParameters はコマンドの既存のパラメータ情報を上書きします。

yy6y35y8.alert_note(ja-jp,VS.90).gifメモ :

パラメータ情報を派生させた場合、情報を取得するためにデータ ソースへのラウンド トリップが 1 つ増えるため、パフォーマンスが低下します。パラメータ情報がデザイン時にわかっている場合は、パラメータを明示的に設定することでアプリケーションのパフォーマンスを改善できます。

詳細については、「CommandBuilder でのコマンドの生成 (ADO.NET)」を参照してください。

SqlCommand およびストアド プロシージャでのパラメータの使用

ストアド プロシージャは、データドリブンのアプリケーションに多くの利点を提供します。ストアド プロシージャを使用すると、データベースの操作を単一のコマンドにカプセル化し、最大のパフォーマンスが得られるように最適化し、さらに追加のセキュリティ機能を使用して、セキュリティを強化することができます。ストアド プロシージャは、ストアド プロシージャ名の後にパラメータ引数を記述して SQL ステートメントとして渡すことで呼び出すことができますが、ADO.NET の DbCommand オブジェクトの Parameters コレクションを使用すると、ストアド プロシージャ パラメータをより明示的に定義でき、出力パラメータや戻り値にもアクセスできます。

yy6y35y8.alert_note(ja-jp,VS.90).gifメモ :

パラメータ化ステートメントは、sp_executesql を使ってサーバー上で実行されるため、クエリ プランの再利用が可能になります。sp_executesql バッチ内のローカル カーソルまたはローカル変数は、sp_executesql を呼び出すバッチでは認識されません。データベース コンテキストの変更は、sp_executesql ステートメント終了時まで有効です。詳細については、SQL Server オンライン ブックを参照してください。

SqlCommand でパラメータを使用して SQL Server のストアド プロシージャを実行する場合は、Parameters コレクションに追加したパラメータの名前が、ストアド プロシージャ内のパラメータ マーカーの名前と一致している必要があります。.NET Framework Data Provider for SQL Server は、SQL ステートメントまたはストアド プロシージャにパラメータを渡す場合の疑問符 (?) プレースホルダをサポートしていません。ストアド プロシージャ内のパラメータは名前付きのパラメータと見なされ、一致するパラメータ マーカーが検索されます。たとえば、CustOrderHist ストアド プロシージャが、@CustomerID という名前のパラメータで定義されているとします。このストアド プロシージャを実行する場合、実行元のコードでも @CustomerID という名前のパラメータを使用する必要があります。

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

次の例では、Northwind サンプル データベースにある SQL Server ストアド プロシージャを呼び出す方法を説明します。ストアド プロシージャの名前は dbo.SalesByCategory で、nvarchar(15) データ型の @CategoryName という名前の入力パラメータを持ちます。このコードでは、プロシージャの終了時に接続が破棄されるように、using ブロック内で新しい SqlConnection を作成しています。SqlCommand オブジェクトおよび SqlParameter オブジェクトが作成され、それぞれのプロパティが設定されます。SqlDataReader によって SqlCommand が実行された後、ストアド プロシージャから結果セットが返されて、出力がコンソール ウィンドウに表示されます。

yy6y35y8.alert_note(ja-jp,VS.90).gifメモ :

SqlCommand オブジェクトと SqlParameter オブジェクトを作成してから別個のステートメントでプロパティを設定する代わりに、オーバーロード コンストラクタを使用して複数のプロパティを 1 つのステートメントで設定することもできます。

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub
static void GetSalesByCategory(string connectionString, 
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection. 
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

OleDbCommand または OdbcCommand によるパラメータの使用

OleDbCommand または OdbcCommand でパラメータを使用するときは、Parameters コレクションにパラメータが追加されている順序が、ストアド プロシージャ内でパラメータが定義されている順序と一致している必要があります。.NET Framework Data Provider for OLE DB と .NET Framework Data Provider for ODBC は、ストアド プロシージャ内のパラメータをプレースホルダとして処理し、順にパラメータ値を適用します。また、戻り値パラメータは、Parameters コレクションに最初に追加されたパラメータにする必要があります。

.NET Framework Data Provider for OLE DB と .NET Framework Data Provider for ODBC は、SQL ステートメントまたはストアド プロシージャにパラメータを渡す場合の名前付きのパラメータをサポートしていません。この場合は、次の例に示すように疑問符 (?) プレースホルダを使用する必要があります。

SELECT * FROM Customers WHERE CustomerID = ?

したがって、Parameters コレクションに Parameter オブジェクトを追加する順序は、パラメータの疑問符 (?) プレースホルダの位置と完全に対応している必要があります。

OleDb の例

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output
OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

OleDbParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Odbc の例

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output
OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

参照

概念

DataAdapter パラメータ (ADO.NET)

その他の技術情報

コマンドとパラメータ (ADO.NET)

ADO.NET でのデータ型のマッピング