VB.NET SQLServerでデータ更新(insert/update/delete)

nas2021/03/01

VB.NETとSQLServerでデータ更新するサンプルと説明です。

確認環境
・Microsoft Visual Studio Community 2019
・SQL Server 2019 Express Edition

目次

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

insertのサンプル

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

Imports System.Data.SqlClient

Module Module1
	Sub Main()
		Dim Sql As String _
			= "insert into syain (id,name,romaji) VALUES (@ID,@name,@romaji)"
		Try
			Using Conn As New SqlConnection
				Conn.ConnectionString =
				   ("Data Source=localhost\SQLEXPRESS;" &
					"Initial Catalog=testDB1;" &
					"Integrated Security=SSPI;")

				Conn.Open()
				Using transaction As SqlTransaction = Conn.BeginTransaction()
					Try
						Using cmd As 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()
						End Using
					Catch ex As Exception
						transaction.Rollback()
						Console.WriteLine(ex.Message)
					End Try
				End Using
			End Using
		Catch ex As Exception
			Console.WriteLine(ex.Message)
		End Try
	End Sub
End Module

バインド変数

	Dim Sql As String _
		= "insert into syain (id,name,romaji) VALUES (@ID,@name,@romaji)"

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

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

	Using Conn As New SqlConnection
		Conn.ConnectionString =
			 ("Data Source=localhost\SQLEXPRESS;" &
			"Initial Catalog=testDB1;" &
			"Integrated Security=SSPI;")

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

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

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

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

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

	Try
		Using cmd As 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"

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

SQLの実行とコミット

	cmd.ExecuteNonQuery()

	transaction.Commit()

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

例外のCatchとロールバック

	Catch ex As Exception
		transaction.Rollback()
		Console.WriteLine(ex.Message)
	End Try

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

updateの場合

= "update syain Set name = @name ,romaji = @romaji where id = @ID"

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

deleteの場合

= "delete from syain where id = @ID"

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

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

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

	Using cmd As 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()
	End Using

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

関連の記事

VB.NET SQLServerに接続してselectするサンプル

△上に戻る