ストアドプロシージャを作成する意味とは？ストアドプロシージャを呼び出す方法
データベースにSQL Serverを導入していれば、Transact-SQLという言語を使用してデータベースサイドでプログラミングを行うことが可能です。これをストアドプロシージャと呼びます。ストアドプロシージャは多くの現場で活用されていますので、Entity Frameworkから呼び出せると作業の幅も広がります。ここではEntity Frameworkとストアドプロシージャについて確認していきましょう。
- SE
- ストアドプロシージャをEntity Frameworkから呼び出すことができたら、データ取得などストアドプロシージャサイドでコーディングする選択肢も増えますね。でもどうやって実装すれば良いのでしょうか？
- PL
- 実装の選択肢が増えることはとても良いことですね。しかし、ストアドプロシージャを呼び出すためには、Visual Studioでの作業が少しばかり必要です。早速その手順を一緒に確認していきましょう。
この記事でわかること
そもそもストアドプロシージャとは何か
ストアドプロシージャとは、簡潔に言うと、データベースに対する幾つもの処理を1つにまとめた関数のことを言います。1つの関数にまとめることで関数を1度呼び出すだけで複数の処理を実行できるようになります。ストアドプロシージャは「戻り値の無い」関数であることもポイントです。その逆で「戻り値のある」ものはストアドファンクションと言います。
ストアドプロシージャを作成する意味とは？
複雑な条件によるSQL問い合わせを、アプリでは無くデータベース側で実行することで、クライアントとデータベース間の通信回数・通信量を抑えることでレスポンス時間を短縮させることが出来ます。また、ビジネスロジックをアプリと分離することで保守性を向上させることも出来ます。
ストアドプロシージャを作成する3つのメリット
ストアドプロシージャを作成するメリットで、「セキュリティの向上に繋げること」「パフォーマンスの向上に繋げること」「保守性の向上に繋げること」の3つがあります。詳しく解説します。
1：セキュリティの向上に繋げることが可能
ストアドプロシージャにすることで、ユーザがデータベース内のテーブルを直接操作することを制限することが出来ます。そのため、不用意なデータへの操作を防ぐことができセキュリティ向上に繋げることが出来ます。
2：パフォーマンスの向上に繋げることが可能
「データ検索」や「データ更新」を行おうとした際、埋め込みSQLの場合、「データ検索」と「データ更新」を行うのにデータベースとの通信を2度する必要があります。それに対してストアドプロシージャを採用した場合、「データ検索」と「データ更新」をひとまとめに処理するため、データベースへの通信は1度で済み、パフォーマンスの向上に繋がります。
3：保守性の向上に繋げることが可能
ストアドプロシージャは、データベースに対する手続きをアプリと別で管理することが出来ます。そうすることで、業務改善によりビジネスロジックへ変更を行う場合、対象のストアドプロシージャのみを変更するだけで対応が可能なため、アプリの保守性が向上します。
ストアドプロシージャの作成をするSQL文の例5つ
ストアドプロシージャ内で使う条件分岐である「IF」文と「CASE」文の2つと、繰り返し処理である「LOOP」文、「WHILE」文、「REPEAT」文の3つを解説していきます。
1：if
条件の真偽を評価します。条件に当てはまる処理を実行します。
2：case
条件の等価性を評価します。条件に当てはまる処理を実行します。
3：while
指定された条件が真（true）の間は、繰り返し処理します。（指定されている条件が真（true）になった場合のみ、最初とその後の繰り返しを処理します。）
4：LOOP
定義されている繰り返し文の中に組み込まれている1つまたは複数の文を繰り返し実行します。
5：repeat
指定された条件が真と評価されるまで、文または文のリストを繰り返し実行します。（最初の繰り返しは無条件で実行します。）
| SQLの制御文 | 概要 | 例 | |
|---|---|---|---|
| 条件分岐 | if | 条件の真偽を評価します。条件に当てはまる処理を実行します。 | IF ‘xxxx’ = 1 THEN …; ELSE….; END IF; |
| case | 条件の等価性を評価します。条件に当てはまる処理を実行します。 | CASE ‘xxxx’ WHEN ‘A’ THEN…; ELSE END CASE; | |
| 繰り返し処理 | while | 指令された条件が真と評価されている間は、文または文のリストを繰り返し実行。（指定されている条件が真になった場合のみ、最初の繰り返しとその後の繰り返しを実行） | WHILE ‘XXXX’ > 0 DO ・ ・ END WHILE; |
| LOOP | 定義されている繰り返し文の中に組み込まれている1つまたは複数の文を繰り返し実行。 | L1: LOOP ・ ・ END LOOP L1; | |
| repeat | 指定された条件が真と評価されるまで、文または文のリストを繰り返し実行。（最初の繰り返しは無条件で実行） | REPEAT ・ ・ UNTIL…. END REPEAT; |
埋め込みSQLとの違いとは
埋め込みSQLはVB.NETやPHPなどで作成するプログラム内にSQL文を埋め込むやり方です。これによりクライアントアプリの中からSQLを実行することが可能になります。一方、ストアドプロシージャは、SQL文をさまざまな制御文や条件文と組み合わせてコンパイルしたものをデータベースに格納しそれを呼び出して使います。この点が埋め込みSQLとの違いになります。
ストアドプロシージャを呼び出すための前準備
1.まずは使用するストアドプロシージャをデータベース内に作成しましょう。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
USE [DB_TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Test001]
@Id nvarchar(10)
,@City nvarchar(10)
AS
BEGIN
INSERT INTO [dbo].[City]
([Id]
,[CityName])
VALUES
(@Id
,@City)
END;
|
ソリューションエクスプローラー内で右クリック→「追加」→「新しい項目」→「Data」タブ→「ADO.NET Entity Data Model」を選択します。
3.「データベースからEFDesigner」を選択します。
4.データベースの接続先を設定します。
入力できたらテスト接続で接続確認を実施し、確認ができたら「OK」ボタンを押下します。
5.「次へ」を押下します。
6.Entity Frameworkのバージョンを選択します。
7.ストアドプロシージャを含めて「完了」ボタンを押下します。
ストアドプロシージャを呼び出すサンプルプログラム
Entity Frameworkからストアドプロシージャを呼び出し、データの登録を行うサンプルプログラムです。Window Fromsでテスト実行を行います。ID:002, CityName:Osakaを設定するようにします。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.EntityClient;
namespace WindowsFormsApp9
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
using (EntityConnection conn = new EntityConnection("name = DB_TESTEntities"))
{
//開く
conn.Open();
using (EntityCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = "DB_TESTEntities.sp_Test001";
cmd.CommandType = CommandType.StoredProcedure;
EntityParameter param = new EntityParameter();
param.Value = "002";
param.ParameterName = "Id";
EntityParameter param2 = new EntityParameter();
param2.Value = "Osaka";
param2.ParameterName = "City";
//ストアドプロシージャに渡すパラメータを追加する
cmd.Parameters.Add(param);
cmd.Parameters.Add(param2);
// ストアドプロシージャ実行
using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
}
//閉じる
conn.Close();
//破棄
conn.Dispose();
}
}
}
}
|
実行結果前
実行結果後
ストアドプロシージャを作成する時の注意点
ストアドプロシージャはプログラムのように処理を組めます。処理の中で別のストアドプロシージャを呼び出すこともできてしまいます。しっかりと設計せずに既存のストアドプロシージャを組み合わせてしまうとトランザクションを保持したまま、別のストアドプロシージャを呼び出し、その中でも更新処理を行うという場合に、想定されないエラーが発生してしまうことがあります。
なぜかと言うと、呼び出された側のトランザクションで、もしもエラーが起きてロールバックが発生した際に、その場で強制終了されます。しかし、呼び出した側のトランザクションが残ったままになるため、想定されないエラーが発生するという事象が起きてしまいます。
ストアドプロシージャを積極的に導入してみましょう
ストアドプロシージャは、データベースサイドでコンパイルされるので、C#サイドでコンパイルした後の本番環境へのDLLの置き換えなどが不要です。また、検索処理速度もC#サイドでSQLクエリをコーディングするよりスピードアップできますので、Entity Frameworkとストアドプロシージャの両方を活用できるプログラマーを目指してみても良いのではないでしょうか。
ストアドプロシージャについて理解しておこう
ストアドプロシージャのメリットや基本的な使い方について解説してきました。パフォーマンスやセキュリティや保守性など多くのメリットがある反面、色々できるためメリットを潰しかねない作りにもなってしまう可能性があります。ストアドプロシージャはデータベースへの処理がメインということを念頭に、正しい設計と実装を理解しながら開発を進めていってください。
Search キーワード検索
Popular 人気の記事
-
【VB.NET入門】DataGridViewの使い方まとめ
公開: 更新:
reccomended おすすめ記事
-
【.NETが統合】.NET 5の概要と今後のリリース予定
公開: 更新:
Categories 連載一覧
Tags タグ一覧
Jobs 新着案件
-
開発エンジニア/東京都品川区/【WEB面談可】/在宅ワーク
月給29万～30万円東京都品川区(大崎駅) -
遠隔テストサービス機能改修/JavaScript/東京都港区/【WEB面談可】/テレワーク
月給45万～60万円東京都港区(六本木駅) -
病院内システムの不具合対応、保守/東京都豊島区/【WEB面談可】/テレワーク
月給30万～30万円東京都豊島区(池袋駅) -
開発/JavaScript/東京都豊島区/【WEB面談可】/テレワーク
月給50万～50万円東京都豊島区(大塚駅) -
債権債務システム追加開発/東京都文京区/【WEB面談可】/在宅勤務
月給62万～67万円東京都文京区(後楽園駅) -
PMO/東京都豊島区/【WEB面談可】/在宅勤務
月給55万～55万円東京都豊島区(池袋駅)