2016年10月6日

SQL Serverのデータをストアドプロシージャを使って取得する

2016年10月6日 (木) 20:45 in , , ,



SQL Serverからストアドプロシージャを使ってデータを取得する方法です。

ちなみに今回もSQL Serverにあらかじめ以下のようなテーブルを作成しています。

テーブル名：T_Animals

列名
[ID] [int] NOT NULL
[Name] [nvarchar](50) NULL
[Type] [nvarchar](50) NULL

格納されてるデータ
ID Name Type
1 たま 猫
2 ピーター うさぎ
3 トム 猫
4 お父さん 犬
5 ふてニャン 猫
6 プー くま
7 ファーファ くま
8 ラスカル アライグマ
9 サトちゃん 象
10 チーバくん 犬
11 パトラッシュ 犬



また、usp_get_animals_dataという名前のストアドプロシージャを作っておきます。
1
2
3
4
5
6
7
8
9
10
11
12
USE [TESTDB]
GO
CREATE PROCEDURE [dbo].[usp_get_animals_data]
AS
BEGIN
 SET NOCOUNT ON;
 
 SELECT [ID]
  ,[Name]
  ,[Type]
 FROM [dbo].[T_Animals]
END
上記のストアドプロシージャは、T_Animalsの全レコードを取得するという単純なものです。

このストアドプロシージャを使ってデータを取得する方法です。
C#
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
string ConnectionString = string.Empty;
ConnectionString = @"Data Source=localhost\MSSQLSERVER;Initial Catalog=TESTDB;Integrated Security=True";
 
var cnn = new SqlConnection(ConnectionString);
 
cnn.Open();
 
var cmd = new SqlCommand();
SqlDataReader dtr = null;
 
cmd.Connection = cnn;
 
// コマンド文字列の解釈方法を指定
cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
// データソースで実行するストアドプロシージャを指定
cmd.CommandText = "[usp_get_animals_data]";
 
dtr = cmd.ExecuteReader();
 
if (dtr.HasRows)
{
    while(dtr.Read())
    {
        Console.WriteLine("{0},{1},{2}", dtr["ID"].ToString(), dtr["Name"], dtr["Type"]);
    }
}
 
dtr.Close();
cmd.Dispose();
cnn.Close();


VB
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
Dim ConnectionString As String = String.Empty
ConnectionString = "Data Source=localhost\MSSQLSERVER;Initial Catalog=TESTDB;Integrated Security=True"
 
Dim cnn = New SqlConnection(ConnectionString)
 
cnn.Open()
 
Dim cmd = New SqlCommand()
Dim dtr As SqlDataReader = Nothing
 
cmd.Connection = cnn
 
'コマンド文字列の解釈方法を指定
cmd.CommandType = System.Data.CommandType.StoredProcedure
 
'データソースで実行するストアドプロシージャを指定
cmd.CommandText = "[usp_get_animals_data]"
 
dtr = cmd.ExecuteReader()
 
If dtr.HasRows Then
    While dtr.Read()
        Console.WriteLine("{0},{1},{2}", dtr("ID").ToString(), dtr("Name"), dtr("Type"))
    End While
End If
 
dtr.Close()
cmd.Dispose()
cnn.Close()


ポイントは、CommandTypeで指定するタイプをStoredProcedureにすることと、CommandTextにストアドプロシージャ名を指定することです。
あとは、直接SQL文を記述する方法と変わりません。

このストアドプロシージャを使ってデータを取得するメリットとしては、SQL文をコードに記述しないのでメンテナンスがしやすいというのがあります。例えばデータを抽出するSELECT文のリレーションや抽出条件が変更になった場合、コードに直接SQL文を記述しているとプログラムの修正が発生してしまいます。しかし、ストアドプロシージャにしておけば変更があってもストアドプロシージャだけ直せば済みます。また、速度的にもストアドプロシージャにしたほうが速くなります。上記の例はとても単純なSELECT文なのでほとんど差がありませんが、もっと複雑なSQL文になると顕著な違いが表れてきます。また、アプリケーション作成部分とクエリ作成部分で作業を分担できるのもメリットです。たとえプログラマがSQLに詳しくなかったとしてもストアドプロシージャにすればクエリ部分をSQLに詳しい人間に作成を頼むこともできます。まあ、小さな会社ですと何から何まですべて一人でやらなきゃいけないってことがあるんですけどね。



スポンサーリンク