C#からSQLServerのストアドプロシージャを呼び出す方法を紹介していきます。
「結果セットの受け取り方」
「パラメータの設定方法」
「OUTPUTパラメータの設定方法」
「return_valueの受け取り方」
「複数結果セットの受け取り方」
使用するテーブルはこちらになります。
テーブル:UserMst
id |name|animal|height|weight
-----+----+------+------+------
00001|伊藤|いぬ |150 |55.5
00002|青木|ねこ |160 |66.6
それでは早速やっていきましょう。
結果セットの受け取り方
まずは、最もシンプルな、結果セットを受け取る方法から紹介していきます。
呼び出し元のプロシージャ(GetUserMstData)は、パラメータがなく「UserMst」全件を取得するものにしています。
CREATE PROCEDURE [dbo].[GetUserMstData]
AS
BEGIN
SELECT
*
FROM
UserMst;
RETURN 0;
END;
そもそもストアドプロシージャの作り方がわからない方はこちらの記事を参考にしてみてください。
続いて、呼び出し側のC#を見てみましょう。
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlCon = new SqlConnection(Properties.Settings.Default.DB))
{
sqlCon.Open();
// 任意のストアドプロシージャを指定
SqlCommand cmd = new SqlCommand("GetUserMstData", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
// ストアドプロシージャ実行
using (SqlDataReader sdr = cmd.ExecuteReader())
{
// 行を読み込む
while (sdr.Read())
{
// 列を読み込む
for (int i = 0; i < sdr.FieldCount; i++)
{
Console.Write(sdr[i] + " ");
}
Console.WriteLine();
}
// SqlDataReaderをクローズ
sdr.Close();
}
sqlCon.Close();
}
MessageBox.Show("処理終了");
}
解説
3行目:SQLServerへの接続
5行目:SQLConnectionをオープン
7-9行目:SQLCommandの設定 ここでストアドプロシージャを指定する。
12行目:ストアドプロシージャを実行する。実行して作成された結果セットがSqlDataReaderに格納される。
14-23行目:取得した結果をコンソールへ表示
結果
id |name|animal|height|weight
-----+----+------+------+------
00001|伊藤|いぬ |150 |55.5
00002|青木|ねこ |160 |66.6
UserMstすべての値がコンソールに出力されました。
ストアドプロシージャの結果が取得できています。
パラメータの設定方法
ストアドプロシージャにパラメータを設定する場合を紹介していきます。
下記のように、入力パラメータが1つあるストアドプロシージャの呼び出し方を紹介します。
ALTER PROCEDURE [dbo].[GetUserMstData]
@in_userid VARCHAR(5)
AS
BEGIN
SELECT
*
FROM
UserMst
WHERE
UserMst.id = @in_userid;
RETURN 0;
END;
呼び出し側のC#を見てみましょう。
ストアドプロシージャを実行する前に、パラメータを設定します。
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlCon = new SqlConnection(Properties.Settings.Default.DB))
{
sqlCon.Open();
// 任意のストアドプロシージャを指定
SqlCommand cmd = new SqlCommand("GetUserMstData", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
//パラメータを設定
cmd.Parameters.AddWithValue("@in_userid", "00001");
// ストアドプロシージャ実行
using (SqlDataReader sdr = cmd.ExecuteReader())
{
// 行を読み込む
while (sdr.Read())
{
// 列を読み込む
for (int i = 0; i < sdr.FieldCount; i++)
{
Console.Write(sdr[i] + " ");
}
Console.WriteLine();
}
// SqlDataReaderをクローズ
sdr.Close();
}
sqlCon.Close();
}
MessageBox.Show("処理終了");
}
解説
11-12行目:パラメータに値を代入しています。ここでは、@in_useridというパラメータに、00001を代入しています。
結果
id |name|animal|height|weight
-----+----+------+------+------
00001|伊藤|いぬ |150 |55.5
パラメータで、指定した(id=00001)のデータのみ取得できていることが確認できました。
OUTPUT 出力パラメータの設定方法
出力パラメータの取得方法を紹介していきます。
先ほどのSQLにOUTPUTパラメータを追加しました。OUTPUTパラメータに[name]の値が格納されます。
ALTER PROCEDURE [dbo].[GetUserMstData]
@in_userid VARCHAR(5)
,@out_username NVARCHAR(30) OUTPUT
AS
BEGIN
SELECT
@out_username = [name]
FROM
UserMst
WHERE
UserMst.id = @in_userid;
RETURN 0;
END;
呼び出し側のC#を見てみましょう。
ストアドプロシージャを実行する前に、OUTPUTパラメータを設定します。
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlCon = new SqlConnection(Properties.Settings.Default.DB))
{
sqlCon.Open();
// 任意のストアドプロシージャを指定
SqlCommand cmd = new SqlCommand("GetUserMstData", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
//パラメータを設定
cmd.Parameters.AddWithValue("@in_userid", "00001");
// OUTPUTパラメータを設定
cmd.Parameters.Add(new SqlParameter("@out_username", SqlDbType.NVarChar, 30));
cmd.Parameters["@out_username"].Direction = ParameterDirection.Output;
// ストアドプロシージャ実行
using (SqlDataReader sdr = cmd.ExecuteReader())
{
// OUTPUT値取得
object output_value = cmd.Parameters["@out_username"].Value;
Console.WriteLine("@out_username:" + output_value);
// SqlDataReaderをクローズ
sdr.Close();
}
sqlCon.Close();
}
MessageBox.Show("処理終了");
}
解説
15行目:型の設定を行います。OUTPUTパラメータの型が文字列の場合は、最大文字数を忘れないようにしましょう。
16行目:Direction ここでOUTPUTのパラメータと指定していきます。
23行目:ストアドプロシージャで出力されたOUTPUTパラメータの値を取得する。
結果
@out_username:伊藤
ストアドプロシージャで設定した、@out_usernameの値を取得することができていますね。
return_valueの受け取り方
ストアドプロシージャのRETURNの値を取得することができます。
主にストアドプロシージャが正常終了したのか異常終了したのか結果として使用されます。
先ほどと同じストアドプロシージャですが、14行目のRETURNの値を1にしています。
この値を取得してみましょう。
ALTER PROCEDURE [dbo].[GetUserMstData]
@in_userid VARCHAR(5)
,@out_username NVARCHAR(30) OUTPUT
AS
BEGIN
SELECT
@out_username = [name]
FROM
UserMst
WHERE
UserMst.id = @in_userid;
RETURN 1;
END;
呼び出し側のC#を見てみましょう。
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlCon = new SqlConnection(Properties.Settings.Default.DB))
{
sqlCon.Open();
// 任意のストアドプロシージャを指定
SqlCommand cmd = new SqlCommand("GetUserMstData", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
//パラメータを設定
cmd.Parameters.AddWithValue("@in_userid", "00001");
// OUTPUTパラメータを設定
cmd.Parameters.Add(new SqlParameter("@out_username", SqlDbType.NVarChar, 30));
cmd.Parameters["@out_username"].Direction = ParameterDirection.Output;
// @return_value 取得
cmd.Parameters.Add(new SqlParameter("@return_value", SqlDbType.Int));
cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
// ストアドプロシージャ実行
using (SqlDataReader sdr = cmd.ExecuteReader())
{
// OUTPUT値取得
object output_value = cmd.Parameters["@out_username"].Value;
Console.WriteLine("@out_username:" + output_value);
// 戻り値取得
object return_value = cmd.Parameters["@return_value"].Value;
Console.WriteLine("@return_value:" + return_value);
// SqlDataReaderをクローズ
sdr.Close();
}
sqlCon.Close();
}
MessageBox.Show("処理終了");
}
解説
19行目:@return_valueでRETURNの値を取得することができます。型はInt型にして下さい。
20行目:Directionは、ParameterDirection.ReturnValue を指定しましょう。
結果
@out_username:伊藤
@return_value:1
ストアドプロシージャのRETURN値である、1が取得できました。
複数結果セットの受け取り方
最後に複数回SQLを実行し、複数の結果を取得する方法を紹介します。
ストアドプロシージャで、2つのSQLを実行してみました。
1回目は、全件
2回目は、1件
取得されます。
ALTER PROCEDURE [dbo].[GetUserMstData]
@in_userid VARCHAR(5)
AS
BEGIN
-- 1回目
SELECT
*
FROM
UserMst;
-- 2回目
SELECT
*
FROM
UserMst
WHERE
UserMst.id = @in_userid;
RETURN 0;
END;
呼び出し側のC#を見てみましょう。
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlCon = new SqlConnection(Properties.Settings.Default.DB))
{
sqlCon.Open();
// 任意のストアドプロシージャを指定
SqlCommand cmd = new SqlCommand("GetUserMstData", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
//パラメータを設定
cmd.Parameters.AddWithValue("@in_userid", "00001");
int cnt = 0;
// ストアドプロシージャ実行
using (SqlDataReader sdr = cmd.ExecuteReader())
{
// SqlDataReaderがクローズされるまでループ
while (!sdr.IsClosed)
{
cnt = cnt + 1;
Console.WriteLine("結果" + cnt.ToString());
// 行を読み込む
while (sdr.Read())
{
// 列を読み込む
for (int i = 0; i < sdr.FieldCount; i++)
{
Console.Write(sdr[i] + " ");
}
Console.WriteLine();
}
// 次の結果セットがなければ終了(複数結果セット対応)
if (!sdr.NextResult())
{
// SqlDataReaderをクローズ
sdr.Close();
}
}
}
sqlCon.Close();
}
MessageBox.Show("処理終了");
}
20行目:SqlDatareaderが閉じられるまで、ループを行う。(41行目で閉じる)
38-42行目:複数結果があれば次のループし、なければSqlDataReaderを閉じループが終了する。
結果
結果1
00001 伊藤 いぬ 150 55.5
00002 青木 ねこ 160 66.6
結果2
00001 伊藤 いぬ 150 55.5
結果が2回取得できており、1回目が、全件2回目が[id=00001]のデータが取得できています。
サンプルソース
最後にサンプルソースを載せておきます。
SQLServer側
ALTER PROCEDURE [dbo].[GetUserMstData]
@in_userid VARCHAR(5)
,@out_username NVARCHAR(30) OUTPUT
AS
BEGIN
-- 1回目
SELECT
*
FROM
UserMst;
-- 2回目
SELECT
*
FROM
UserMst
WHERE
UserMst.id = @in_userid;
-- 3回目
SELECT
@out_username = [name]
FROM
UserMst
WHERE
UserMst.id = @in_userid;
RETURN 0;
END;
C#側
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace c_sharp
{
public partial class frmProcedure : Form
{
public frmProcedure()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlCon = new SqlConnection(Properties.Settings.Default.DB))
{
sqlCon.Open();
// 任意のストアドプロシージャを指定
SqlCommand cmd = new SqlCommand("GetUserMstData", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
//パラメータを設定
cmd.Parameters.AddWithValue("@in_userid", "00001");
// OUTPUTパラメータを設定
cmd.Parameters.Add(new SqlParameter("@out_username", SqlDbType.NVarChar, 30));
cmd.Parameters["@out_username"].Direction = ParameterDirection.Output;
// @return_value 取得
cmd.Parameters.Add(new SqlParameter("@return_value", SqlDbType.Int));
cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
int cnt = 0;
// ストアドプロシージャ実行
using (SqlDataReader sdr = cmd.ExecuteReader())
{
// SqlDataReaderがクローズされるまでループ
while (!sdr.IsClosed)
{
cnt = cnt + 1;
Console.WriteLine("結果" + cnt.ToString());
// 行を読み込む
while (sdr.Read())
{
// 列を読み込む
for (int i = 0; i < sdr.FieldCount; i++)
{
Console.Write(sdr[i] + " ");
}
Console.WriteLine();
}
// 次の結果セットがなければ終了(複数結果セット対応)
if (!sdr.NextResult())
{
// OUTPUT値取得
object output_value = cmd.Parameters["@out_username"].Value;
Console.WriteLine("@out_username:" + output_value);
// 戻り値取得
object return_value = cmd.Parameters["@return_value"].Value;
Console.WriteLine("@return_value:" + return_value);
// SqlDataReaderをクローズ
sdr.Close();
}
}
}
sqlCon.Close();
}
MessageBox.Show("処理終了");
}
}
}
結果
結果1
00001 伊藤 いぬ 150 55.5
00002 青木 ねこ 160 66.6
結果2
00001 伊藤 いぬ 150 55.5
@out_username:伊藤
@return_value:0
まとめ
C#からストアドプロシージャを行う方法の備忘録を作成しました。
このやり方を見ておけば、C#からストアドプロシージャを実行し、結果を取得することができるようになると思います。
ストアドプロシージャを利用することでパフォーマンスの向上ができる場合があるので、是非利用してみてください。