DataGridViewでDBの更新・追加・削除をしたい

Question

前提・実現したいこと

DataGridViewを使用しDBからデータを取得して、表示・更新・追加・削除を行いたいと考えております。
DataGridViewにDBからデータを取得して、表示するところまではできました。

'ﾌｫｰﾑﾛｰﾄﾞ
Private Sub Frm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   'DB情報
    Me.bindingSource = New BindingSource()
    Me.DataGridView1.DataSource = Me.bindingSource
    Me.Controls.Add(Me.DataGridView1)
    'ﾃﾞｰﾀ取得
    Me.bindingSource.DataSource = CreateDataTable()
End Sub

'ﾃﾞｰﾀ取得(表示)
Private Function CreateDataTable() As DataTable
   Dim dt As DataTable = New DataTable()
   'ﾃﾞｰﾀﾍﾞｰｽ
   Mdl_Gnl_objConnection.ConnectionString = "Database=" + ***** + ";Data Source=" + *****  + ";User Id=" + ***** + ";Password=" + *****
   '接続情報を使ってｺﾈｸｼｮﾝを生成
    Using conn As New MySqlConnection(Mdl_Gnl_objConnection.ConnectionString)
        'MySQL文とｺﾈｸｼｮﾝを設定
         Using cmd As New MySqlCommand("SELECT カラム1,カラム2,... FORM テーブル1 LEFT OUTER JOIN...", conn)
             'SELECTｺﾏﾝﾄﾞを設定
             Mdl_DataAdapter.SelectCommand = cmd
             'SELECTの実行及びﾌｪｯﾁ
             Mdl_DataAdapter.Fill(dt)
             'ｸﾞﾘｯﾄﾞに表示
             Grd_YoteiKubun.DataSource = dt
         End Using
     End Using
     Return dt
End Function

発生している問題

表示はできているのですが、更新・追加・削除ができていない状況です。
自分なりに調べてここまでは書きました。
これすらも当たっているのか正直分かりません…。
コードの書き方が分からない部分はソースコードに記述しました。

DataGridViewからデータを取得して、DBに反映する方法を教えていただきたいです。
また下記ソースコードじたいが間違っていればそちらの指摘もお願い致します。

'ﾃﾞｰﾀ更新
Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
    Dim objCommand As New MySqlCommand
    Dim SQL As String = ""
    Dim dt As DataTable = New DataTable()
    Dim r As DataRow
    dt = DataGridView1.DataSource
    
    'ﾃﾞｰﾀﾍﾞｰｽ
    Mdl_Gnl_objConnection = New MySqlConnection
    Mdl_Gnl_objConnection.ConnectionString = "Database=" + ***** + ";Data Source=" + *****  + ";User Id=" + ***** + ";Password=" + *****
    'ｵｰﾌﾟﾝ
    Call Mdl_Gnl_objConnection.Open()
    objCommand = Mdl_Main_Gnl_objConnection.CreateCommand
    'ﾃﾞｰﾀ更新
    For Each r In dt.Rows
        Select Case r.RowState
               Case DataRowState.Added     '新規追加
                    'ここの中身が分からない
               Case DataRowState.Modified  '修正
　　　　　　　　　　　'ここの中身が分からない
　　　　　　　　Case DataRowState.Deleted   '削除
　　　　　　　　　　　'ここの中身が分からない
               Case Else   '何もしない
                    Continue For
         End Select
         objCommand.CommandText = SQL
         Call objCommand.ExecuteNonQuery()
    Next
    'ｸﾛｰｽﾞ
    Call Mdl_Gnl_objConnection.Close()
    dt.Dispose()
End Sub

補足情報（FW/ツールのバージョンなど）

.Net 2010
業務上、MySqLを使用しています。

Comments

[退会済みユーザー]

TableAdapter 構成ウィザードは使えませんか？

[PgBeginners]

SurferOnWwwさん
>TableAdapter 構成ウィザードは使えませんか？
使用できなそうです、、、大変なのを覚悟でやっています、、、。

Answer 1

OleDbの例ですがこんな感じになるかと思います。
dtをgDTbl等へ読み替えて見て下さい。
builber定義後にデータ取得セット。画面入力で自動的にSQL文作成されます。
このSQL文をCommitにて更新します。

'(Test_Sample_Miniature)
'【定義部分】
Public gbindingSource As BindingSource
Public gConn As System.Data.OleDb.OleDbConnection
Public gCmd As System.Data.OleDb.OleDbCommand
Public gTran As System.Data.OleDb.OleDbTransaction
Public gAdp As System.Data.OleDb.OleDbDataAdapter
Public gCBui As System.Data.OleDb.OleDbCommandBuilder
Public gDTbl As DataTable


'【初期値セット(DataGridView=Me.DGV)】
gCmd = gConn.CreateCommand
gCmd.CommandText = strSQL 'SQL文セット
gAdp = New System.Data.OleDb.OleDbDataAdapter(gCmd)
gCBui = New System.Data.OleDb.OleDbCommandBuilder(gAdp)
gDTbl = New System.Data.DataTable

gAdp.Fill(gDTbl)
gbindingSource = New BindingSource
gbindingSource.DataSource = gDTbl
Me.DGV.DataSource = gbindingSource


’【Builder自動作成SQL文の実行処理】この前に画面入力作業が入ります。
'
'<BeginTransaction>
'
Dim result As Integer = 0
gTran = gConn.BeginTransaction()
gCmd.Transaction = gTran

Dim builder As System.Data.OleDb.OleDbCommandBuilder
builder = New System.Data.OleDb.OleDbCommandBuilder(gAdp)
builder.GetUpdateCommand()
result = gAdp.Update(gDTbl)
'
'<Commit>
'
gTran.Commit()

入力時にはDataGirdViewe_CellEndEdit等のイベントが沢山あります。
データチェック等処理を行い、OK後にCommit処理が走る形になるかと思います。
更新に付いてはデータセットまで出来ていますので、builberとCommit部分を追加していけば宜しいかと思います。

Comments

[PgBeginners]

ご回答ありがとうございます。
じっくり読み試験してみます。
返信等遅くなるかと思いますが、少々お待ちください。

[PgBeginners]

時間が空いてすみません。
お一つ質問なのですが、データセットまで出来ているとのことだったのですがFor Each～を削除して、Dim builder As System.Data.OleDb.OleDbCommandBuilder～を記述するという認識合っていますでしょうか？

[tosi]

はいそうです。
入力結果からSQL文(insert/update/delete)は勝手に作られます。
これをbuilderさせてCommitすれば更新完了です。
OleDbはAccessでの例です。
上手く行かない様でしたらAccesssで一度やってみてからでも良いかも知れません。

[PgBeginners]

返信ありがとうございます。
tosiさんに提示して頂いたソースを参考に修正してみたのですが、「複数のベーステーブルに対する動的SQLはサポートされません。」というエラーメッセージが出てしまいました。
解決策はありませんでしょうか？

[tosi]

まず、このエラーがでると言うことは、SQL自動作成から更新実行までは動いていると思われます。
従って、1テーブル(キー正常な表)をソースへ割り当てれば上手く動きそうです。
問題は、最初にDataGridViewへ渡したSQL表が更新可能なSQLソースであるかということです。
https://docs.microsoft.com/ja-jp/dotnet/framework/data/adonet/generating-commands-with-commandbuilders
では、
＞複数のテーブルを結合して作成したデータベース ビューは、
＞単一データベース テーブルとは見なされません。・・・・・・
とありますので、複数テーブルでの動作は対応しているのか確認する必要がありそうです。
私の場合は、複数テーブルSQL文を更新用DataGridViewへ割り当てたことはありません。更新では動作に不安感があるため、単独テーブルのみ割り当てて使っています。(私だけが不安に思っているだけかも知れませんが････････)

以下、私見です。
この様なやり方もあると言う程度に見てください。
複雑なSQL処理をする場合には、仮想のデータベース(表)を作成して、SQL文の結果を一旦この表へ書き込みます。
(例：仮想的な表の作成)
Public gTb As DataTable = New DataTable
Dim txtColumn As DataColumn
txtColumn = New DataColumn("textbox", GetType(String))
gTb.Columns.Add(txtColumn)
gTb.Rows.Add("111111")
gTb.Rows.Add("222222)
こんな感じでgTb表が出来上がり、DataGridViewへソースセットします。
DataGridViewへ入力した値とgTbとは先ほどの更新処理で一致させます。
本番のMySQLデータベースへは、これとは別にSQL文を作成し独自に更新を行います。
この更新時には、gTbは最新の状態となっていますので、この表を基にSQL文を作成すると楽かも。

全体の感じとしては、
(準備)
gTbを作成してDataGridViewへリンク。
(入力)
DataGridViewセル入力。イベント色々でデータチェック。
DataGridView入力終了。CellEndEditで入力完了を取得。
(更新開始)
gTbへのBuilder更新。
SQL文独自作成しMySQLを更新。
(終了)
という手順です。
MySQL更新部分はボタンにして、クリック実行でも良いかも知れません。
取り合えずこのやり方でそれなりに動いているので、私してきには良いのではないかと思っています。

[PgBeginners]

ご回答ありがとうございます。
早速やってみたいと思います。

結果は後程、少々お持ちください。

Answer 2

TableAdapter 構成ウィザードは使えませんか？

使用できなそうです、、、大変なのを覚悟でやっています、、、。

使用できるようにしましょう。MySQL でも MySQL For Visual Studio をインストールすればウィザードを使ってアプリが作れます。

DB が SQL Server の場合ですが、以下のチュートリアル、

チュートリアル : データベースへのデータの保存 (単一テーブル)
https://docs.microsoft.com/ja-jp/previous-versions/0f92s97z(v=vs.120)

10 行でズバリ !! 非接続型のデータ アクセス
https://github.com/microsoftarchive/msdn-code-gallery-community-0-9-non-alphabetic/tree/master/10%20%E8%A1%8C%E3%81%A7%E3%82%BA%E3%83%90%E3%83%AA%20!!%20%E9%9D%9E%E6%8E%A5%E7%B6%9A%E5%9E%8B%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%20%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%20(ADO.NET)%20(VB)

・・・のように Visual Studio のデータソース構成ウィザードを利用して型付 DataSet + TableAdapter を作って、それを利用してアプリを作ると、以下のページの図のような構造のアプリが、ほとんど自分でコードを書くこと無しに作れます。

Windows フォーム アプリケーションでのデータへの接続
https://docs.microsoft.com/ja-jp/previous-versions/wxt2cwcc(v=vs.120)

例えば、ウィザードを使って .xsd ファイル（型付 DataSet / DataTable + TableAdapter）を作ると、データソースウィンドウに作ったデータソースが表示されます。（以下は MySQL をインストールした特に付属してきた world データベースの country テーブルから作ったもの）

それをデザイン画面で Form にドラッグ＆ドロップすると以下の画像のような、一覧表示機能だけでなく、編集・更新機能を持ったアプリが自動生成されます。

操作に慣れると 10 分もかからず作れるはずです。自力で一からコードを書く場合のような種々の問題に悩むことはなくなり、開発工数は激減するはずです。保守工数も減るはずです。お試しください。

【追記】

どうしてもウィザードが使えなければ、以下の記事の「非接続型のデータ更新」のセクションにあるような CommandBuilder を使って更新系のメソッドを実装するなど、自力でコードを書く他手はなさそうです。

DB 設計者のための明解 ADO.NET 第 1 回
https://docs.microsoft.com/ja-jp/previous-versions/cc482903(v=msdn.10)?redirectedfrom=MSDN

Comments

[PgBeginners]

ご回答ありがとうございます。
一通り目を通してみますので、少しばかり返信が遅くなると思います。
ご了承ください。

[退会済みユーザー]

Visual Studio のウィザードが使う MySql.Data.dll のバージョンと、GAC の MySql.Data.dll のバージョンに不一致があって、それが原因でウィザードが期待通り動かないことがありますので注意してください。解決方法はあります。詳しくは以下の記事を見てください。

MySQL での TableAdapter 構成ウィザード不具合
http://surferonwww.info/BlogEngine/post/2018/02/10/tableadapter-configuration-wizard-does-not-work-properly-for-mysql.aspx

そういうことはありますが、それを直しても使う価値は十二分にあると思います。