C# SQLServerでデータ更新する(insert/update/delete)

nas2020/10/02

C#とSQLServerでデータ更新するサンプルと説明です。
(確認環境:Microsoft Visual Studio Community 2019,SQL Server 2019 Express Edition)

目次

SQLのサンプル insertのサンプル
説明 バインド変数
  Connectionクラスのインスタンスを生成
  接続とトランザクションの開始
  Commandクラスのインスタンスを生成
  CommandクラスのParametersプロパティに値をセット
  SQLの実行とコミット
  例外のCatchとロールバック
  updateの場合
  deleteの場合
サンプル 更新のsqlを複数回実行する例

insertのサンプル

SQLServerに接続してインサートを実行するサンプルです。Windows認証です。

using System;
using System.Data;
using System.Data.SqlClient;
class Test1
{
	static void Main()
	{
		string sql = "insert into employee(id,name,romaji) VALUES(@ID,@name,@romaji)";
		try
		{
			using (SqlConnection conn = new SqlConnection())
			{
				conn.ConnectionString =
				@"Data Source=localhost\SQLEXPRESS;" +
				"Initial Catalog=testDB1;" +
				 "Integrated Security=SSPI;";

				conn.Open();
				using (SqlTransaction transaction = conn.BeginTransaction())
				{
					try
					{
						using (SqlCommand cmd = new SqlCommand(sql, conn, transaction))
						{
							cmd.Parameters.Add(new SqlParameter(
								"@ID", SqlDbType.Int)).Value = 5;
							cmd.Parameters.Add(new SqlParameter(
								"@name", SqlDbType.VarChar)).Value = "高橋";
							cmd.Parameters.Add(new SqlParameter(
								"@romaji", SqlDbType.VarChar)).Value = "takahashi";

							cmd.ExecuteNonQuery();

							transaction.Commit();
						}
					}
					catch (Exception ex)
					{
						transaction.Rollback();
						Console.WriteLine(ex.ToString());
					}
				}
			}
		}
		catch (Exception ex)
		{
			Console.WriteLine(ex.Message.ToString());
		}
	}
}

バインド変数

string sql = "insert into employee(id,name,romaji) VALUES(@ID,@name,@romaji)";

8行目は、SQLのinsert文です。valuesにあるアットマーク(@)がついている項目はバインド変数です。パラメータで値を設定します。バインド変数は、プレースホルダとも呼ばれます。
入力される不正な文字を抑止できるのでSQLインジェクション対策になります。
固定文字列のSQLの部分がプリコンパイルされるので実行速度が上がります。

Connectionクラスのインスタンスを生成

	using (SqlConnection conn = new SqlConnection())
	{
		conn.ConnectionString =
		@"Data Source=localhost\SQLEXPRESS;" +
		"Initial Catalog=testDB1;" +
		 "Integrated Security=SSPI;";

11行目は、SqlConnection のインスタンスを生成しています。Usingなので自動でcloseします。
13行目からは、インスタンスにDBへの接続情報をセットしています。testDB1はデータベース名です。

接続とトランザクションの開始

		conn.Open();
		using (SqlTransaction transaction = conn.BeginTransaction())

16行目は、openメソッドでDBへ接続しています。
17行目は、SqlTransactionクラスのBeginTransactionメソッドでトランザクションを開始します。ここもUsingなので自動でcloseします。
変数の型はSqlTransactionクラスです。

Commandクラスのインスタンスを生成

	try
	{
		using (SqlCommand cmd = new SqlCommand(sql, conn, transaction))

21行目のTryは、例外があったときに後述するCatchに処理が移動します。
23行目は、SqlCommandクラスのインスタンスを生成しています。コンストラクタの引数に上記のinsert文のSQLとconnとtransactionを指定しています。

CommandクラスのParametersプロパティに値をセット

		cmd.Parameters.Add(new SqlParameter(
			"@ID", SqlDbType.Int)).Value = 5;
		cmd.Parameters.Add(new SqlParameter(
			"@name", SqlDbType.VarChar)).Value = "高橋";
		cmd.Parameters.Add(new SqlParameter(
			"@romaji", SqlDbType.VarChar)).Value = "takahashi";

25行目からは、SqlCommandクラスのParametersプロパティに値をセットしています。SqlParameterの引数の"ID"や”name"がSQL文のバインド変数と紐づきます。
26行目は、DBのテーブルのデータ型がint型なのでSqlDbTypeは、Intにしています。
28,30行目は、Oracleのテーブルのデータ型はVARCHAR型なのでvarCharにしています。

SQLの実行とコミット

			cmd.ExecuteNonQuery();

			transaction.Commit();

32行目は、SqlCommandクラスのExecuteNonQueryメソッドでSQLを実行します。
34行目は、DBでのSQLの処理が成功した場合、SqlTransactionクラスのCommitメソッドで確定されます。
ここでエラーがあった場合は、34行目は実行されず、後述のCatchに処理が移動します。

例外のCatchとロールバック

		catch (Exception ex)
		{
			transaction.Rollback();
			Console.WriteLine(ex.ToString());
		}

37行目のCatchは21行目のtry以降の実行でエラーが発生したときに処理が行われます。
エラーをキャッチするとも言われます。
Exceptionはクラスでexは変数です。
39行目は、SqlTransactionクラスのRollbackメソッドでデータをロールバックします。更新しません。例えばinsert文を2つ発行したとして一つは成功しもう一つは失敗した場合、2つともデータ更新はしません。

updateの場合

= "update employee Set name = @name ,romaji = @romaji where id = @ID";

updeteを行う場合は、8行目のSQL文を変更します。
cmd.Parametersの箇所では、キーと更新したい項目を指定します。

deleteの場合

= "delete from employee where id = @ID";

deleteを行う場合は、8行目のSQL文を変更します。
cmd.Parametersの箇所では、キーを指定します。

更新のsqlを複数回実行する例

更新のsqlを複数回実行する場合は、SqlParameterCollectionクラスのClearメソッドを使用します。

	using (SqlCommand cmd = new SqlCommand(sql, conn, transaction))
	{
		cmd.Parameters.Add(new SqlParameter(
			"@ID", SqlDbType.Int)).Value = 5;
		cmd.Parameters.Add(new SqlParameter(
			"@name", SqlDbType.VarChar)).Value = "高橋";
		cmd.Parameters.Add(new SqlParameter(
			"@romaji", SqlDbType.VarChar)).Value = "takahashi";

		cmd.ExecuteNonQuery();

		cmd.Parameters.Clear();

		cmd.Parameters.Add(new SqlParameter(
			"@ID", SqlDbType.Int)).Value = 6;
		cmd.Parameters.Add(new SqlParameter(
			"@name", SqlDbType.VarChar)).Value = "武田";
		cmd.Parameters.Add(new SqlParameter(
			"@romaji", SqlDbType.VarChar)).Value = "takeda";

		cmd.ExecuteNonQuery();

		transaction.Commit();
	}

32行目は、ExecuteNonQueryメソッドで1回目の更新処理を行います。
34行目は、Clearメソッドでパラメータをクリアします。
43行目は、2回目の更新処理を行います。
45行目は、上記2つの更新処理が成功した場合にコミットします。

関連の記事

C# SQLServerに接続してselectするサンプル

△上に戻る