Class Module1

    Private Shared _instance As New Module1
    Public Shared Function GetInstance() As Module1
        Return _instance
    End Function
    Private Sub New()
    End Sub
    '設定

    Public Property mdbFileInfo As MDBFileInfo
    Private Cn_txt As String
    Private Adapter As System.Data.OleDb.OleDbDataAdapter
    Private Table1 As DataTable
    Private Cn As System.Data.OleDb.OleDbConnection
    Private SQL As System.Data.OleDb.OleDbCommand
    Private Trz As System.Data.OleDb.OleDbTransaction
    Private WithEvents form2 As Form2

    '成功フラグ
    Public result_flg As Boolean

    Sub DBread(ByVal table_name As String)
        '接続文字列
        Cn_txt = mdbFileInfo.CreateConectionString()
        'SQL文
        Dim SQL_txt As String = "SELECT * FROM " & table_name

        'データアダプターを生成
        Adapter = New System.Data.OleDb.OleDbDataAdapter(SQL_txt, Cn_txt)

        'データの読み込み
        Table1 = New DataTable()
        Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey '既定の情報と共に主キーメタデータも読み込む
        Adapter.Fill(Table1)

    End Sub

    Sub show_form2() 'Form2表示
        Dim param As New Form2Param
        param.Title = Table1.TableName
        param.Table = Table1
        form2 = New Form2
        form2.Param = param
        form2.Show() 'Fomr2を表示
    End Sub

    Sub form2_close(sender As Object, e As FormClosedEventArgs) Handles form2.FormClosed
        DGVdispose()
    End Sub

    Sub DGVdispose() 'DGVに読み込んだDBを破棄
        Table1.Dispose()
        Adapter.Dispose()
    End Sub
    Sub DBconnect() 'DB接続
        'データベース指定
        Cn = New System.Data.OleDb.OleDbConnection
        Cn.ConnectionString = Cn_txt
        Cn.Open() 'オープン

        'トランザクション開始
        Trz = Cn.BeginTransaction

        'コマンドのインスタンスを生成
        SQL = Cn.CreateCommand
        SQL.Connection = Cn
        SQL.Transaction = Trz
    End Sub
    Sub DBdispose() 'DB切断
        Cn.Close() 'データベースを閉じる
        SQL.Dispose() 'コマンドを破棄
        Cn.Dispose() 'データベースを破棄
    End Sub
    Sub DBrefresh()
        Dim tableName As String = Table1.TableName
        DGVdispose()
        DBread(tableName)
        Dim param As New Form2Param
        param.Title = Table1.TableName
        param.Table = Table1
        form2.Param = param
    End Sub

    Sub DBdelete(ByVal table_name As String, row_n As Integer)

        Call DBconnect() 'DB接続

        Try
            'WHERE句生成
            Dim where_txt As String = MAKE_where_txt(row_n)

            'DELETE文生成
            SQL.CommandText = "DELETE FROM " & table_name & where_txt 'WHERE句を足す
            SQL.ExecuteNonQuery() '実行

            Trz.Commit() 'コミット（確定）
            result_flg = True '成功フラグを立てる
        Catch ex As Exception
            MessageBox.Show(SQL.CommandText) 'SQL表示（デバッグ用）
            MessageBox.Show(ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Trz.Rollback() 'ロールバック
        Finally
            Call DBdispose() 'DB切断
        End Try

    End Sub

    Function MAKE_where_txt(ByVal row_n As Integer) As String
        'WHERE句を生成する関数

        Dim Dgv1 As DataGridView = form2.dgv1
        Dim where_txt As String = ""
        Dim n As Integer = 0 'カウント用

        For Each key_clm As Integer In form2.ListBox1.Items '主キーの列番号をループ
            If n = 0 Then
                where_txt = " WHERE "
            Else '主キーがふたつ以上ある場合
                where_txt &= " AND "
            End If
            Dim field_txt As String = MAKE_field_txt(row_n, key_clm, form2.ListBox2.Items(key_clm)) '囲み文字を含めたフィールド文字列を生成
            where_txt &= Dgv1.Columns(key_clm).HeaderCell.Value & "=" & field_txt
            n = n + 1 'カウントアップ
        Next

        Return where_txt 'WHERE句を返す
    End Function

    Sub DBsave(table_name As String)
        Dim Dgv1 As DataGridView = form2.dgv1
        Dim key_flg As Boolean

        Call DBconnect() 'DB接続

        Try
            'UPDATEの処理
            For Each row_n As Integer In form2.ListBox3.Items
                'UPDATE文生成
                SQL.CommandText = "UPDATE " & table_name & " SET"
                For i As Integer = 0 To Dgv1.Columns.Count - 1
                    key_flg = False 'フラグリセット
                    For Each key_clm As Integer In form2.ListBox1.Items
                        If i = key_clm Then key_flg = True '主キーだったらTrue
                    Next
                    If form2.ListBox2.Items(i).Substring(0, 2) <> "A_" And key_flg = False Then 'オートインクリメント&主キーじゃない場合
                        Dim field_txt As String = MAKE_field_txt(row_n, i, form2.ListBox2.Items(i)) '囲み文字を含めたフィールド文字列を生成
                        SQL.CommandText &= " " & Dgv1.Columns(i).HeaderCell.Value & "=" & field_txt & ","
                    End If
                Next i
                SQL.CommandText = SQL.CommandText.Remove(SQL.CommandText.Length - 1) '最後の一文字（,）を削除

                'WHERE句生成
                Dim where_txt As String = MAKE_where_txt(row_n)

                SQL.CommandText &= where_txt 'WHERE句を足す
                SQL.ExecuteNonQuery() '実行
            Next
            'INSERTの処理
            For Each row_n As Integer In form2.ListBox4.Items
                'INSERT文生成
                SQL.CommandText = "INSERT INTO " & table_name & "("

                'フィールド名を列挙
                For i As Integer = 0 To Dgv1.Columns.Count - 1
                    If form2.ListBox2.Items(i).Substring(0, 2) <> "A_" Then 'オートインクリメントじゃない場合
                        SQL.CommandText &= " " & Dgv1.Columns(i).HeaderCell.Value & ","
                    End If
                Next i
                SQL.CommandText = SQL.CommandText.Remove(SQL.CommandText.Length - 1) '最後の一文字（,）を削除
                SQL.CommandText &= ") VALUES("

                '中身を生成
                For i As Integer = 0 To Dgv1.Columns.Count - 1
                    If form2.ListBox2.Items(i).Substring(0, 2) <> "A_" Then 'オートインクリメントじゃない場合
                        Dim field_txt As String = MAKE_field_txt(row_n, i, form2.ListBox2.Items(i)) '囲み文字を含めたフィールド文字列を生成
                        SQL.CommandText &= " " & field_txt & ","
                    End If
                Next i
                SQL.CommandText = SQL.CommandText.Remove(SQL.CommandText.Length - 1) '最後の一文字（,）を削除
                SQL.CommandText &= " )"

                SQL.ExecuteNonQuery() '実行
            Next
            Trz.Commit() '確定する
            result_flg = True 'フラグを立てる
        Catch ex As Exception
            MessageBox.Show(SQL.CommandText) 'SQL表示（デバッグ用）
            MessageBox.Show(ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Trz.Rollback() 'ロールバック
        Finally
            Call DBdispose() 'DB切断
        End Try
    End Sub

    Function MAKE_field_txt(ByVal row_n As Integer, i As Integer, pattern As String) As String
        'フィールドの中身を生成する関数

        Dim Dgv1 As DataGridView = form2.dgv1
        Dim txt As String
        Dim field_txt As String

        If Dgv1.Rows(row_n).Cells(i).Value IsNot DBNull.Value Then 'Nullじゃない場合
            Select Case pattern
                Case "String"
                    txt = "'"
                Case "DateTime"
                    txt = "#"
                Case Else
                    txt = ""
            End Select
            field_txt = txt & Dgv1.Rows(row_n).Cells(i).Value & txt
        Else
            If pattern = "Boolean" Then 'BooleanでNULL判定されたときはfalseにする
                field_txt = "false"
            Else
                field_txt = "NULL"
            End If
        End If

        Return field_txt
    End Function
End Class