C# からユーザー定義テーブル型変数をストアードプロシージャに渡す方法

ユーザー定義テーブル型変数

複数のレコードを一度にストアドプロシージャに渡して処理したい時には、SQL Server のユーザー定義テーブル型を使うと便利です。

SQL Server 入門の 「ユーザー定義テーブル型変数を使って、ストアドプロシージャに複数のレコードを一度に渡す」 では、ユーザー定義テーブル型変数と、それを引数として受け取るストアードプロシージャを作り、SQL Server Management Studio から実行してみました。

今回は、そこで作ったストアードプロシージャ uspStudentSave を C# から実行する方法をご紹介します。


C# からユーザー定義テーブル型変数をストアードプロシージャに渡す

おさらいですが、実行前の Student テーブルはこのようになっています。

C# からユーザー定義テーブル型変数をストアードプロシージャに渡す方法 1

ストアードプロシージャ uspStudentSave は、ユーザー定義のテーブル型変数 StudentTableType を引数として受けとって、Student テーブルの更新（Inesrt/Update/Delete）を行います。

StudentID は Identity カラムで、新規レコードには StudentID = 0 がクライアントプログラムで設定されているものとします。

C# からユーザー定義テーブル型変数をストアードプロシージャに渡す方法 2

生成するスクリプトは 「ユーザー定義テーブル型変数を使って、ストアドプロシージャに複数のレコードを一度に渡す」 を参照してください。

今回は Student テーブルから値を引いてきて、それを変更し、保存してみましょう。

まず、以下のストアドプロシージャ uspStudentSelectを実行して、Student テーブルから全レコードを取得します。 

CREATE PROCEDURE uspStudentSelect
AS
BEGIN

	SELECT	StudentID,
		FirstName,
		LastName,
		Birthday,
		Gender
	FROM	Student;

END

次に、StudentID = 1 の LastName を Yamamoto に変更し、StudentID = 2 のレコードを削除し、新しい Student レコードを一行追加します。

そして、最後に uspStudentSave を実行して変更を保存するコードは以下の通りです。 

// Student テーブルのデータを取得
DataTable dtStudent = new DataTable();
using (SqlConnection conn =
            new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
{
    conn.Open();

    SqlCommand cmd = new SqlCommand("uspStudentSelect", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    sda.Fill(dtStudent);
}

// Student テーブルのデータを変更・削除・新規挿入
DataRow[] dr1 = dtStudent.Select("StudentID = 1");
if(dr1.Length > 0)
{
    dr1[0]["LastName"] = "Yamamoto";
}

DataRow[] dr2 = dtStudent.Select("StudentID = 2");
if (dr2.Length > 0)
{
    dr2[0].Delete();
}

DataRow drS = dtStudent.NewRow();
drS["StudentID"] = 0;
drS["FirstName"] = "Ryoko";
drS["LastName"] = "Okamoto";
drS["Birthday"] = "1981-08-08";
drS["Gender"] = "F";
dtStudent.Rows.Add(drS);

dtStudent.AcceptChanges();

// Student テーブルのデータを保存
using (SqlConnection conn =
            new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
{
    conn.Open();

    SqlCommand cmd = new SqlCommand("uspStudentSave", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@StudentTable", dtStudent);
    tvpParam.SqlDbType = SqlDbType.Structured;

    cmd.ExecuteNonQuery();
}

C# からユーザー定義テーブル型変数をストアードプロシージャに渡す方法 3

ユーザー定義のテーブル型を uspStudentSave に渡しているのは緑で囲まれた部分です。

値の入った DataTable を AddWithValue で追加して、その SqlParameter の SqlDbType を Structured に設定しています。


注意する点は、値の入った DataTable はユーザー定義テーブル型と構造が同じでないとエラーになります。

今回の場合ですと、 uspStudentSelect が返すカラムがユーザー定義テーブル型と違うとエラーになります。

ユーザー定義テーブル型と同じカラム＆型であれば、以下のような感じで DataTable を作って渡しても大丈夫です。 

DataTable dtStudent = new DataTable();
dtStudent.Columns.Add("StudentID", typeof(Int32));
dtStudent.Columns.Add("FirstName", typeof(String));
dtStudent.Columns.Add("LastName", typeof(String));
dtStudent.Columns.Add("Birthday", typeof(DateTime));
dtStudent.Columns.Add("Gender", typeof(String));

DataRow drS1 = dtStudent.NewRow();
drS1["StudentID"] = 1;
drS1["FirstName"] = "Taro";
drS1["LastName"] = "Yamada";
drS1["Birthday"] = "1980-02-15";
drS1["Gender"] = "M";
dtStudent.Rows.Add(drS1);

上のコードを実行してみました。 こちらは保存前の dtStudent の値です。

C# からユーザー定義テーブル型変数をストアードプロシージャに渡す方法 4

実行後の Student テーブルの値は以下の通りです。 期待通りに更新されていますね。

C# からユーザー定義テーブル型変数をストアードプロシージャに渡す方法 5

© 2024 C# 入門