C# ストアドプロシージャを呼び出す方法 SQLServer

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#からストアドプロシージャを実行し、結果を取得することができるようになると思います。
ストアドプロシージャを利用することでパフォーマンスの向上ができる場合があるので、是非利用してみてください。

 

SQLServerの記事一覧

おすすめの記事