1つ。前書き:
ストアド プロシージャは、特定の関数を完了するように設計された一連の SQL ステートメントであり、コンパイルされてデータベースに保存されます。ユーザーは、ストアド プロシージャの名前を指定し、パラメーターを指定することによって (ストアド プロシージャにパラメーターがある場合)、ストアド プロシージャを実行します。ストアド プロシージャはデータベース内の重要なオブジェクトであり、適切に設計されたデータベース アプリケーションではストアド プロシージャを使用する必要があります。一般に、ストアド プロシージャには次の利点があります。
◆ ストアド プロシージャにより、標準的なコンポーネント プログラミングが可能になります
。◆ ストアド プロシージャにより、より高速な実行速度を実現できます
。 ◆ストアド プロシージャにより、ネットワーク トラフィックを削減できます
。 ◆ストアド
プロシージャをセキュリティ メカニズムとして最大限に活用できます。
.NET データベース アプリケーションでのストアド プロシージャのアプリケーションと、それを ADO.NET の SqlDataAdapter オブジェクト、DataSet オブジェクトなどと組み合わせて使用して、.NET データベース アプリケーションの全体的なパフォーマンスを向上させる方法について説明します。
二。システム要件：
開発ツール：Visual Studio.NET
データベース管理システム：SQL Server 2000（サンプルプログラムで使用するPubsデータベースを含む）
３．簡単なストアド プロシージャを作成する:
ここでは、Visual Studio.NET IDE を使用してストアド プロシージャを作成する方法を紹介します。 Visual Studio.NET IDE を使用してストアド プロシージャを作成するのは非常に簡単かつ直観的です。サーバー エクスプローラーで Pubs データベースに移動し、ノードを展開すると、図 1 に示すように、ストアド プロシージャを含むさまざまなデータベース オブジェクトが表示されます。見せる。
ストアド プロシージャ ノードを右クリックして、[新規ストアド プロシージャ] コマンドを含むメニューをポップアップ表示します。新しいストアド プロシージャを作成すると、以下に示すコード テンプレートが IDE のコード編集ウィンドウに表示されます。
プロシージャの作成 dbo.StoredProcedure1
/*
(
@parameter1 データ型 = デフォルト値、
@parameter2 データ型 OUTPUT )
*/
として
/* NOCOUNT をオンに設定します */
戻る
ストアド
プロシージャを作成するための簡略化された構文規則に準拠しています。完全な構文規則は次のとおりです。
[ { @parameter data_type }
[ 可変 ] [ = デフォルト ] [ 出力 ]
] [ ,...n ]
[ と
{ 再コンパイル | 再コンパイル 、 暗号化 } ]
[ レプリケーション用 ]
AS sql_statement [ ...n ]
スペースの制限のため、ここでは各パラメータの意味は紹介しません。興味のある読者は、SQL Server 2000 データベース管理システムに関する情報を参照してください。
以下に、このコード テンプレートの各文法コンポーネントを簡単に紹介します。 CREATE PROCEDURE ステートメントはストアド プロシージャを作成し、その後にストアド プロシージャの名前を指定します。 「/*...*/」内のコンポーネントはストアド プロシージャのパラメータであり、入力パラメータと出力パラメータを含めることができます。 AS キーワードに続く内容はストアド プロシージャの本体であり、ストアド プロシージャに含まれる任意の数および種類の SQL ステートメントです。 RETURN キーワードはストアド プロシージャの終了を示し、整数のステータス値を呼び出し元に返すことができます。パラメーターのない単純なストアド プロシージャを作成して使用してみましょう:
CREATE PROCEDURE dbo.up_GetPublisherInfo
として
SELECT pub_id、pub_name、市、州、国
出版社から
戻る
上記のストアド プロシージャを作成したら、保存します。保存すると、ストアド プロシージャに対応するノードがサーバー エクスプローラーに表示されます。コード編集ウィンドウの CREATE キーワードが、既存のストアド プロシージャを変更するために使用される ALTER キーワードに変更されたことにも注意してください。上記のストアド プロシージャを実行するには、そのノードをクリックし、右クリック ポップアップ メニューで [ストアド プロシージャの実行] を選択するだけです。操作の結果は次のようになります。
4つ目。パラメーター付きストアド プロシージャを作成する:
上記ではパラメーターなしの単純なストアド プロシージャを作成しましたが、実際のアプリケーションではパラメーター付きストアド プロシージャが多数使用されることがよくあります。パラメーターを含むストアド プロシージャは通常、データの更新またはデータの挿入に使用されます。以下では、同じ操作メソッドを使用して、パラメーターを含むストアド プロシージャを作成できます。
プロシージャの作成 dbo.up_UpdatePublisherInfo
(
@pub_id 文字 (4)、
@pub_name varchar (40)、
@cityvarchar(20)、
@状態文字(2)、
@country varchar (30)
)
として
発行者を更新する
SET pub_name = @pub_name、city = @city、state = @state、
国 = @country
WHERE ( pub_id = @pub_id )
戻る
ストアド プロシージャを作成する上記のコードでは、名前の前に「@」記号を追加してストアド プロシージャのローカル変数パラメータを宣言し、各パラメータの型も宣言し、各パラメータの方向値を決定します。 is、パラメータが入力型か出力型か入出力型か戻り値型かを示します。ユーザーは、対応するストアド プロシージャ名と正しい有効なパラメータを使用してストアド プロシージャを呼び出すことができます。また、OUTPUT キーワードを使用してパラメータに出力パラメータを追加することもできます。具体的な方法については、上記の構文規則を参照してください。出力パラメータは、呼び出し元に関連情報を返すことができます。
上記のストアド プロシージャは、発行者テーブル内の対応する発行者情報を更新できます。ストアド プロシージャのノードをクリックし、右クリック ポップアップ メニューで [ストアド プロシージャの実行] を選択すると、ストアド プロシージャを実行できます。実行すると、発行者情報を入力するためのダイアログ ボックスが IDE にポップアップ表示されます (図 3 を参照)。このダイアログ ボックスに正しく有効な更新情報を入力し、pub_id の値が元のテーブルに存在する必要があることに注意して、[OK] ボタンをクリックしてデータを更新します。
五。単純なストアド プロシージャを使用してデータベース アプリケーションを作成する:
次に、パラメーターなしで上記のストアド プロシージャを使用してデータベース アプリケーションを作成します。このアプリケーションでは、ADO.NET の SqlDataAdapter オブジェクトと DataSet オブジェクトも使用します。 SqlDataAdapter オブジェクトは、SQL Server データベースと DataSet オブジェクトの間のブリッジとして機能し、この 2 つを接続します。 SqlDataAdapter オブジェクトには、よく使用される 2 つのメソッド、Fill() メソッドと Update() メソッドが含まれています。 Fill() メソッドは、データベースから対応するデータを取得して DataSet オブジェクトに入力でき、Update() メソッドは、名前が示すように、データ セットを更新します。 Fill() メソッドを呼び出す前に、SqlDataAdapter オブジェクト (実際には SqlCommand オブジェクト) の SelectCommand プロパティを設定する必要があります。 SelectCommand プロパティには有効な SQL ステートメントが含まれており、対応するデータをデータベースから取得して DataSet オブジェクトに入力できます。
まず、Windows フォーム アプリケーションを作成します。プログラミング言語は C# です。 Visual Studio.NET で新しいプロジェクトを作成した後、新しいクラスをプロジェクトに追加します。Publishers クラスは、バックエンド データベースに接続してデータ セット オブジェクトを取得するビジネス ロジックをカプセル化します。手順は次のとおりです。
1.必要な名前空間参照を追加します: System.Data.SqlClient を使用します
。次の必要な変数をこのクラスに追加します
。
プライベート SqlCommand cmdPubs;
プライベート SqlDataAdapter daPubs;
プライベート DataSet dsPubs;
3．このクラスのコンストラクターで、バックエンド データベースへの接続を完了し、SqlDataAdapter オブジェクトとその他のビジネス ロジックを取得します:
public Publishers()
{
試す
{
//データベース接続オブジェクトを作成する
cnPubs = new SqlConnection( "server=localhost;integrated security=true;database=pubs" );
// SqlCommand オブジェクトを作成し、そのコマンド タイプをストアド プロシージャとして指定します
cmdPubs = 新しい SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
// SqlDataAdapter オブジェクトを作成し、その SelectCommand プロパティを上記の SqlCommand オブジェクトに設定します
daPubs = 新しい SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
//DataSet オブジェクトを作成する
dsPubs = 新しい DataSet();
}
キャッチ(例外) {}
}
4.最後に、このクラスには GetPublisherInfo() メソッドが提供されており、DataSet オブジェクトに SqlDataAdapter オブジェクトを埋め込み、埋め込まれた DataSet オブジェクトを返します。メソッドは次のとおりです (SqlDataAdapter オブジェクトが暗黙的にデータベース接続を開き、データを取得した後にデータベース接続を暗黙的に開きます。接続を正式に閉じます。つまり、DataSet オブジェクトは非接続モードで動作し、データベース接続を明示的に開いてデータを取得しても、SqlDataAdapter オブジェクトは接続を閉じません。
public DataSet GetPublisherInfo()。
{
// SqlDataAdapter オブジェクトの Fill() メソッドを呼び出し、データセット オブジェクトを返します。
daPubs.Fill(dsPubs);
dsPub を返します。
}
Publishers クラスの設計が完了したら、DataGrid コントロールをメイン フォームに追加し、それを使用して DataSet オブジェクト内のデータを表示します。まず、次のメンバー変数をメイン フォーム クラスに追加します
。
プライベート データセット ds;
その後、メインフォームクラスのコンストラクターを次のように変更します。
パブリック Form1()
{
//
// Windows フォーム デザイナーのサポートに必要
//
InitializeComponent();
//
// TODO: InitializeComponent 呼び出しの後にコンストラクター コードを追加します。
// pubs = new Publishers();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
このようにして、アプリケーションが開始されるとすぐに、パラメーターなしで上記のストアド プロシージャを使用して Pubs データベースから取得した対応するデータが、メイン フォームの DataGrid コントロールに表示されます。プログラム実行図は次のとおりです
。パラメーター付きのストアド プロシージャを使用したデータベース アプリケーションを作成する:
上ではパラメーターなしのストアド プロシージャを使用したアプリケーションを作成しましたが、ここではより複雑なデータベース アプリケーションを作成します。実際のデータベース アプリケーションでは、データを取得し、データを更新、挿入、削除する必要があることがよくありますが、このとき、SqlDataAdapter オブジェクトを使用するときに、その Update() を呼び出す必要があります。方法。 Update() メソッドは、DataSet オブジェクトの DataTable オブジェクトの各レコードの変更に基づいて、対応する操作を自動的に完了します。 SqlDataAdapter オブジェクトには、UpdateCommand、InsertCommand、DeleteCommand などのプロパティも含まれています。これらのプロパティは、実際には SqlCommand オブジェクトです。 Update() メソッドは、操作のタイプに基づいて適切な属性を選択します。
パラメーターを含むストアド プロシージャを使用してデータベース アプリケーションを構築する場合、通常は、Sql パラメーターに関連するさまざまなプロパティとメソッドをカプセル化する SqlParameter クラスを使用します。プロパティには、ParameterName、SqlDBType、Direction、Size、Value、SourceColumn、SourceVersion などが含まれます。このうち、ParameterName、SqlDBType、Direction、Size およびその他の属性は、ストアド プロシージャで定義されたパラメーターと一致するために使用されます。たとえば、以下に定義されている SqlParameter オブジェクトは、以前に定義された up_UpdatePublisherInfo ストアド プロシージャの"@pub_id " パラメータと一致するために使用されます。
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
上記の定義では、Direction 属性は明示的に指定されていませんが、そのデフォルト値は Input であるため、ニーズを満たしています。また、SqlParameter オブジェクトの Direction プロパティが InputOutput、Output、または ReturnValue である場合、その Direction プロパティを明確に記述する必要があります。たとえば、次のコードは、SqlParameter オブジェクトの Direction プロパティが Output であることを明確に宣言します。
oParam.Direction = ParameterDirection.Output;
SourceColumn プロパティは、DataTable オブジェクトの DataColumn オブジェクトを照合するために使用され、Update() メソッドが呼び出されて DataTable オブジェクトが更新されるときに、必要な SqlParameter オブジェクトを暗黙的にインポートできます。定義時にこのプロパティが宣言されていない場合は、コード内で SqlParameter オブジェクトの SourceColumn プロパティを明示的に記述する必要があります。
SourceVersion プロパティのデフォルト値は、DataRow オブジェクトの対応するフィールドの現在の値であり、データベースに更新される値です。もちろん、SourceVersion プロパティは、DataRow オブジェクトの対応するフィールドの元の値、つまりデータベースから取得した初期値を指すこともできます。データベース トランザクション処理システムでは、データの同期の問題は非常に重要です。データの同期を検出できるストアド プロシージャを構築してみましょう。
CREATE PROCEDURE dbo.up_UpdatePublisherName
(
@pub_id char(4)、
@pub_name varchar(40)、
@Original_pub_name varchar(40)
)
として
存在する場合(pub_idを選択)
出版社からの
ここで、(pub_id = @pub_id) AND (pub_name = @Original_pub_name))
始める
パブリッシャーを更新 SET pub_name = @pub_name
WHERE (pub_id = @pub_id)
終わり
戻る
次に、上記のアプリケーションのストアド プロシージャを呼び出して、発行者の名前を更新します。まず、元のアプリケーションに基づいてビジネス ロジック クラス (Publishers クラス) を改善します。
1. SqlDataAdapter オブジェクトの UpdateCommand プロパティとして使用できる新しい SqlCommand オブジェクトを追加します。
プライベート SqlCommand cmdUpdPubs;
2.このクラスのコンストラクター Publishers() 関数を更新して、以下を追加します。
// パブリッシャー名を更新するストアド プロシージャを参照する別の SqlCommand オブジェクトを作成します
cmdUpdPubs = 新しい SqlCommand();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//上記のSqlCommandオブジェクトに必要なパラメータを追加します
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id" );
cmdUpdPubs.Parameters.Add( "@pub_name", SqlDbType.VarChar, 40, "pub_name" );
SqlParameter updParam = 新しい SqlParameter
( "@Original_pub_name", SqlDbType.VarChar, 40, "pub_name" );
updParam.SourceVersion = DataRowVersion.Original;
cmdUpdPubs.Parameters.Add( updParam );
3． SqlDataAdapter オブジェクトの UpdateCommand プロパティを、上で定義した SqlCommand オブジェクトとして指定します。
daPubs.UpdateCommand = cmdUpdPubs;
4. UpdatePublisherName() メソッドを追加します。
public void UpdatePublisherName(DataSet dsChanges)
{
// すべての変更を更新します
daPubs.Update(dsChanges);
}
アプリケーションのビジネス ロジック クラスが完了したら、メイン フォームに「データ セットの更新」という名前のボタンを追加し、次のようにボタンのイベント応答関数を追加します。
private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds = pubs.GetPublisherInfo();
これまでのところ、アプリケーションのビジネス ロジック クラスとメイン フォーム クラスが更新され、ユーザーの変更に応じてデータベース内の関連コンテンツを更新できるようになりました。
セブン。概要:
この記事では、ストアド プロシージャの基本的な知識と、SqlDataAdapter オブジェクトや DataSet オブジェクトなどを組み合わせて .NET データベース アプリケーションでデータ駆動型アプリケーションを構築する方法を紹介します。この記事では、2 種類のストアド プロシージャを使用しました。1 つはパラメータのない単純なストアド プロシージャで、比較的簡単に使用できます。もう 1 つはパラメータ付きのストアド プロシージャであり、このタイプのストアド プロシージャを呼び出す必要があります。 SqlParameter オブジェクトに。同時に、データ更新のビジネス ロジックをストアド プロシージャにカプセル化することが、アプリケーションの管理性、スケーラビリティ、データベース セキュリティを向上できる優れた設計方法であることを見つけるのは難しくありません。同様に、データを挿入および削除するビジネス ロジックをストアド プロシージャにカプセル化し、同様の方法でアプリケーションで使用できます。最後に、この記事が皆様のお役に立てれば幸いです。