方法: ADO.NET でオプション値を指定してストアドプロシージャを呼び出す

技法の説明

SQL Server でストアドプロシージャを設計するときは、通常、入力パラメーターに既定値を指定することをお勧めします。 たとえば、 year パラメーターを受け取り、指定した年に顧客が配置した注文を返すストアドプロシージャがある場合は、今年度を既定値にすることができます。 ユーザーが year パラメーターの値を入力しなかった場合、ストアドプロシージャは適切な結果を返します。 ADO.NET と SQL Server .NET マネージドプロバイダーでは、次の2つの方法で、ストアドプロシージャで入力パラメーターの既定値を使うことができます。

• 既定値を受け取るパラメーターには、 SqlParameter オブジェクトを定義しないでください。 SQL Server .NET マネージプロバイダーは名前付きパラメーターを使うため、パラメーターを任意の順序で定義できます。また、コードですべてのパラメーターを定義する必要はありません。

• SqlParameterオブジェクトが定義されてParametersコレクションに追加されている場合は、パラメーターの値としてNothingを渡します。

要件

推奨するハードウェア、ソフトウェア、ネットワーク インフラストラクチャ、および必要な Service Pack の概略の一覧を以下に示します。

• Microsoft Visual Studio .NET は、互換性のある Microsoft Windows オペレーティングシステムにインストールされている

• Microsoft SQL Server 2000 または Microsoft SQL Server 7.0 の使用可能なインスタンス

この資料は、次のトピックについて詳しい知識のあるユーザーを対象としています。

• Microsoft Visual Basic .NET プログラミング

• ADO.NET データアクセス

• パラメーター化された SQL Server ストアドプロシージャ

サンプルを作成する

1. SQL Server クエリアナライザー (または SQL Server Enterprise Manager) を起動し、次のように Northwind サンプルデータベースで新しいストアドプロシージャを作成します。

   USE NorthwindGOCREATE PROCEDURE usp_OptionalYear@CustID NCHAR(5),@Year CHAR(4) = '1997',@ShipCountry NVARCHAR(15)ASIF @Year IS NULLBEGINSELECT * FROM ORDERSWHERE CustomerID = @CustIDAND ShipCountry = @ShipCountryORDER BY OrderDateENDELSEBEGINSELECT * FROM ORDERSWHERE CustomerID = @CustIDAND DATEPART(year, OrderDate) = @YearAND ShipCountry = @ShipCountryORDER BY OrderDateEND

2. Visual Basic 本体の新しいアプリケーションを次のように作成します。

   1. Microsoft Visual Studio .NET を起動します。

   2. [ ファイル ] メニューの [ 新規作成] をポイントし、[ プロジェクト] をクリックします。

   3. [プロジェクトの種類] で [ Visual Basic プロジェクト] をクリックし、[テンプレート] の [コンソールアプリケーション] をクリックします。

3. Module1 のコードウィンドウの上部に、次のステートメントを追加します。

   Imports System.DataImports System.Data.SqlClient

4. Sub Mainプロシージャに次のコードを追加します。

           'Declare and open Connection.        Dim cn As SqlConnection = _            New SqlConnection("Data Source=<server>;" & _            "Initial Catalog=Northwind;" & _            "User ID=<user>;Password=<password>;")        cn.Open()        'Declare Command.        Dim cmd As SqlCommand = _            New SqlCommand("usp_OptionalYear", cn)        cmd.CommandType = CommandType.StoredProcedure        'Declare three Parameters.        Dim prm As SqlParameter = _            New SqlParameter("@CustID", SqlDbType.NChar, 5)        cmd.Parameters.Add(prm)        prm = New SqlParameter("@Year", SqlDbType.Char, 4)        cmd.Parameters.Add(prm)        prm = New SqlParameter("@ShipCountry", SqlDbType.NVarChar, 15)        cmd.Parameters.Add(prm)        'Set Parameter values.        With cmd            .Parameters("@CustID").Value = "ALFKI"            .Parameters("@Year").Value = Nothing            .Parameters("@ShipCountry").Value = "Germany"        End With        'Execute stored procedure.        Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)        Do While dr.Read            Console.WriteLine("Order ID {0} placed on {1}", dr.GetInt32(0), dr.GetDateTime(3))        Loop        'Keep window open to view results.        Console.ReadLine()

5. 環境に必要に応じて SQL Server の接続文字列を変更します。

6. プロジェクトを実行します。 @Yearパラメーターの値に対してNothingを渡し、そのパラメーターの値を指定しなかったため、 1997の既定値が使用されていることに注意してください。 コンソールウィンドウには、顧客が ALFKI を1997に配置した3つの注文が表示されます。

7. 次の3行をコメントにして、 @Year パラメーターが宣言されなくなったため、値が提供されないようにします。

           'prm = New SqlParameter("@Year", SqlDbType.Char, 4)        'cmd.Parameters.Add(prm)        ...           '.Parameters("@Year").Value = Nothing

8. プロジェクトをもう一度実行します。 @Yearパラメーターを定義していないため、 1997の既定値が再使用されます。 さらに、コンソールウィンドウには、顧客が ALFKI を1997に配置した3つの注文が表示されます。