VB.NETでAccessのデータベースファイルへ書き込むための接続・切断

やっと DB の中身を変えていくぞ！　という段階まで来たのですが、まず各 SQL を書く前に、何度も使う接続や切断などお決まりの流れを先に作っておきます。

関連記事

1. DataGridViewへAccessのデータベースファイルを読み込む
2. AccessDBテーブルの主キー情報を取得する
3. DataGridViewに読み込んだDB情報を再取得する
4. DataGridViewをセル編集したときの行数を格納する
5. Accessのデータベースファイルへ書き込むための接続・切断 ←NOW!
6. Accessデータベースのレコードを削除する
7. Accessデータベースを更新する

ガッツリ続き物になってしまいました…。過去の分と合わせて順番に読んでいただけると分かりやすいかと思います。

なるべく簡素に書いているので、例外処理は甘いと思われます。ご参考にする際は、ご自分の環境に合わせてご修正ください。

解説のためツギハギしちゃったので、最後の記事（7回め）に全コードまとめてあります。

書いたときの環境

• Visual Studio 2010
• .NET Framework 4.0

です。

汎用プロシージャを作る

今回は、任意の SQL 文を引数として持っていって、コネクション開く → トランザクション開始 → 実行 → 確定処理 → コネクション閉じる、という流れを実装したrunSQLというプロシージャを作ります。

引数が文字列型（1つのSQL文）の場合

まず、単一のSQL文を引数にして持っていく処理。

Form2.vb

とりあえず、削除ボタン（Button3）クリック、22～34行目のプロシージャから走るようにしてみます。

'### Form2.vb ###
Public Class Form2
	'略

	'----------------------------------------------------
	'　　　　メソッド
	'----------------------------------------------------
	'略

	'----------------------------------------------------
	'　　Formイベント
	'----------------------------------------------------
	'略

	'----------------------------------------------------
	'　　Buttonクリックイベント
	'----------------------------------------------------
	Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
		'再読込ボタン（Button1）クリック時（略）
	End Sub

	Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
		'削除ボタン（Button3）クリック時

		Dim strSQL = "DELETE FROM table1 WHERE id=1;" '任意のSQL文
		'SQLを実行
		If db.runSQL(strSQL) = True Then '成功したら
			'再読込
			db.Dispose()
			Call setTable()
			'メッセージ
			MessageBox.Show("成功しました。")
		End If
	End Sub

	'----------------------------------------------------
	'　　DataGridViewイベント
	'----------------------------------------------------
	'略
End Class

25行目で任意のSQL文を変数に入れて、それを引数にしてrunSQLを実行します。関数にする予定なので、True が返ってきたら（成功したら）、再読込してメッセージを出します。

ここでは例として DELETE 文を書いていますが、もちろん INSERT でも UPDATE でもいけます。

DBtable.vb

汎用で使うためのrunSQLプロシージャを、DBtable.vb クラスの方に作ります。25～50行目です。

'### DBtable.vb ###
Imports System.Data.OleDb

Class DBtable
	'略

	'----------------------------------------------------
	'　　テーブルに関すること
	'----------------------------------------------------
	Private dbCnc As OleDbConnection
	Private dbAdp As OleDbDataAdapter
	Private tableData As DataTable
	Private primaryKeyArray() As DataColumn

	Public Sub setDataSource()
		'テーブルデータ読込（略）
	End Sub

	Private Function getTableData(ByVal strSQL As String) As DataTable
		'テーブルを接続（略）
	End Function

	'略

	Public Function runSQL(strSQL As String) As Boolean
		'テーブル情報の変更
		Dim checkFlg As Boolean

		dbCnc.Open() 'コネクションオープン

		Dim dbCmd As OleDbCommand = dbCnc.CreateCommand
		Dim dbTrz As OleDbTransaction = dbCnc.BeginTransaction
		dbCmd.Connection = dbCnc 'コマンド接続
		dbCmd.Transaction = dbTrz	'トランザクション開始

		Try
			dbCmd.CommandText = strSQL 'リスト内のSQL文をセット
			dbCmd.ExecuteNonQuery()	'実行
			dbTrz.Commit() 'コミット（確定）
			checkFlg = True
		Catch ex As Exception
			MessageBox.Show(dbCmd.CommandText & vbCrLf & vbCrLf & ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error) 'エラー内容表示
			dbTrz.Rollback() 'ロールバック
		Finally
			dbCmd.Dispose()	'コマンドを破棄
			dbCnc.Close()	'コネクションを閉じる
		End Try

		Return checkFlg
	End Function
End Class

29行目のコネクションは、テーブルを読み込む時点でもう出来ているので、それをそのまま使います。引数としてもってきたSQLを文を実行して、成功だったらフラグを立てて、失敗だったらエラーメッセージを出してロールバック。

引数がリスト型（複数のSQL文リスト）の場合

今回の例では、UPDATE や INSERT の行数をリストで保持しておいて、複数のSQL文を生成して一度に処理したい、という構想なので、上記のコードのrunSQLメソッドへ渡す引数を、単一のSQL文ではなく、複数のSQL文がリスト形式になっているものとします。

Form2.vb

引数が String から List になっただけです。

'### Form2.vb ###
Public Class Form2
	'略

	'----------------------------------------------------
	'　　　　メソッド
	'----------------------------------------------------
	'略

	'----------------------------------------------------
	'　　Formイベント
	'----------------------------------------------------
	'略

	'----------------------------------------------------
	'　　Buttonクリックイベント
	'----------------------------------------------------
	Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
		'再読込ボタン（Button1）クリック時（略）
	End Sub

	Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
		'削除ボタン（Button3）クリック時

		'SQL文リストを取得
		Dim sqlList As New List(Of String)
		sqlList.Add("SQL1") 'ここでSQL文リストを作る（1つでも2つ以上でもOK）
		sqlList.Add("SQL2")
		sqlList.Add("SQL3")
		'SQLを実行
		If db.runSQL(sqlList) = True Then '成功したら
			'再読込
			db.Dispose()
			Call setTable()
			'メッセージ
			MessageBox.Show("成功しました。")
		End If
	End Sub

	'----------------------------------------------------
	'　　DataGridViewイベント
	'----------------------------------------------------
	'略
End Class

SQL文をリスト化して、31行目でrunSQLに引き渡します。

Form2.vb

25行目の受け取る引数を List 型にしておいて、

'### DBtable.vb ###
Imports System.Data.OleDb

Class DBtable
	'略

	'----------------------------------------------------
	'　　テーブルに関すること
	'----------------------------------------------------
	Private dbCnc As OleDbConnection
	Private dbAdp As OleDbDataAdapter
	Private tableData As DataTable
	Private primaryKeyArray() As DataColumn

	Public Sub setDataSource()
		'テーブルデータ読込（略）
	End Sub

	Private Function getTableData(ByVal strSQL As String) As DataTable
		'テーブルを接続（略）
	End Function

	'略

	Public Function runSQL(sqlList As List(Of String)) As Boolean
		'テーブル情報の変更
		Dim checkFlg As Boolean

		dbCnc.Open() 'コネクションオープン

		Dim dbCmd As OleDbCommand = dbCnc.CreateCommand
		Dim dbTrz As OleDbTransaction = dbCnc.BeginTransaction
		dbCmd.Connection = dbCnc 'コマンド接続
		dbCmd.Transaction = dbTrz	'トランザクション開始

		Try
			For Each strSQL As String In sqlList
				dbCmd.CommandText = strSQL 'リスト内のSQL文をセット
				dbCmd.ExecuteNonQuery()	'実行
			Next
			dbTrz.Commit() 'コミット（確定）
			checkFlg = True
		Catch ex As Exception
			MessageBox.Show(dbCmd.CommandText & vbCrLf & vbCrLf & ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error) 'エラー内容表示
			dbTrz.Rollback() 'ロールバック
		Finally
			dbCmd.Dispose()	'コマンドを破棄
			dbCnc.Close()	'コネクションを閉じる
		End Try

		Return checkFlg
	End Function
End Class

37～40行でリスト内をループさせて、全部OKだったらコミットします。途中でエラーが起きたらロールバック。

今回はここまで。次回からようやく DB を書き換えていきますー！