DataSetをデータベースに更新させる

環境/言語:[VB.NET2005]
分類:[.NET]

MDBのテーブルをSQLserverのテーブルに移す作業をしていて、MDBのテーブルとSQL
serverのテーブルをDataSetに入れて、行を移し。そのデータをSQLserverのテーブルに入れて更新するという事をやっているのですが、データが更新されません。

行を移す事はできていると思うので、DataSetの更新がうまくいかないと思っているのですがどうでしょうか？

以下はそのソースです。

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim MDB_DB As New DataTable
Dim SQL_DB As New DataTable

Dim M_Set As New DataSet
Dim S_Set As New DataSet
Dim row As DataRow
Try

'MDBのデータをセット
M_Set = GetMDBDataSet()
MDB_DB = M_Set.Tables(0)


'SQLのデータをセット
S_Set = GetSQLDataSet()
SQL_DB = S_Set.Tables(0)

Using da As SqlDataAdapter = New SqlDataAdapter
For i As Integer = 0 To 4
row = MDB_DB.Rows(i)
'Next
Dim Newrow As DataRow = SQL_DB.NewRow
Newrow = row
SQL_DB.ImportRow(Newrow)
Next

da.Update(S_Set,"SQLテーブル")
End Using

Catch ex As Exception
MsgBox(ex.Message.ToString)

Finally
SQL_DB.Dispose()
MDB_DB.Dispose()
End Try

Public Function GetSQLDataSet() As DataSet
Dim retDt As New DataSet
Try
'** SQL SERVER接続
Using con As New SqlConnection
Dim cmd As New SqlCommand

'接続関連プロパティ設定
Me.SetConnection(con, cmd)

'ＳＱＬ文設定
cmd.CommandText = ("SELECT * FROM SQLテーブル")

'テーブルからレコード取得
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
da.Fill(retDt, "SQLテーブル")

End Using

Catch ex As Exception
Throw New Exception _
("clsSQLDBIO.GetSQLDatSetで例外発生：" & ex.ToString)

End Try

'----------< ◆戻値を設定してリターン >----------
Return retDt

End Function

Public Function GetMDBDataSet() As DataSet
Dim retDt As New DataSet
Try
'** MDB接続
Using con As New OleDbConnection
Dim cmd As New OleDbCommand

'接続関連プロパティ設定
Me.mdbConnection(con, cmd)

'ＳＱＬ文設定
cmd.CommandText = ("SELECT * FROM MDBテーブル")

'テーブルからレコード取得
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
da.Fill(retDt, "MDBテーブル")

End Using

Catch ex As Exception
Throw New Exception _
("clsSQLDBIO.GetSQLDatSetで例外発生：" & ex.ToString)

End Try

'----------< ◆戻値を設定してリターン >----------
Return retDt

End Function
Public Sub SetConnection(ByRef con As SqlConnection, _
ByRef cmd As SqlCommand)

Try
'----------< 接続関連プロパティ設定 >----------
'変数の宣言
Dim settings As String
'接続する端末名
Dim ServerName As String = "SQLSERVER"
'接続するデータベース名
Dim DBName As String = "SQLデータベース"
'ユーザ名
Dim UserID As String = "*"
'パスワード
Dim Password As String = "**"

'接続文字列をapp.configファイルから取得
'** データベース接続情報
settings = "Server =" & ServerName & ";" & _
"User ID =" & UserID & ";" & _
"Password =" & Password & ";" & _
"Initial Catalog =" & DBName

If settings Is Nothing Then
'接続文字列取得エラー
Throw New Exception _
("接続文字列がapp.configに未登録.")
Else
'接続文字列の設定
con.ConnectionString = settings

'SqlCommand.Connectionプロパティの設定
cmd.Connection = con

End If

Catch ex As Exception
Throw New Exception _
("SetConnectionで例外発生." & ex.ToString)
End Try

End Sub

Public Sub mdbConnection(ByRef con As OleDbConnection, _
ByRef cmd As OleDbCommand)

Try
'----------< 接続関連プロパティ設定 >----------
'接続するMDB名
Dim MDBName As String = "MDB"

' DB接続文字列の設定
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & MDBName

' コネクションの設定
cmd.Connection = con

' DB接続を開く
con.Open()


Catch ex As Exception
Throw New Exception _
("mdbConnectionで例外発生." & ex.ToString)
End Try

End Sub

■No26059に返信(勉強中さんの記事)
DataAdapter.Update メソッドの解説に CommandBuilder 使うように
書いてあると思います。
リンクで「DataAdapter によるデータ ソースの更新」のページなども
読んでみて下さい。

■No26059に返信(勉強中さんの記事)
勉強中さん、こんにちは。

問題のおこる所は、この場所でいいでしょうか？

 1> Using da As SqlDataAdapter = New SqlDataAdapter
 2>     For i As Integer = 0 To 4
 3>         row = MDB_DB.Rows(i)
 4>         'Next
 5>         Dim Newrow As DataRow = SQL_DB.NewRow
 6>         Newrow = row
 7>         SQL_DB.ImportRow(Newrow)
 8>     Next
 9>     da.Update(S_Set,"SQLテーブル")
10> End Using

他人のコードをデバックする気はないのでヒントだけ。

1. SqlDataAdapter の da に設定が足りない。

2. 上記 5行目で新しいDataRowが作られていますが、
   6行目で3行目に指定されたrowで上書きされ5行目の意味を失っている。

3. データは全部で５行しか追加されない。
   逆に言うと、取得してきたデータが５行未満なら例外が発生する。

こんな所でしょうか。
( ある意味デバックしてるか…(汗 )

あと、ここの解説に重要なヒントがあります。
http://msdn.microsoft.com/ja-jp/library/system.data.sqlclient.sqldataadapter.insertcommand(VS.80).aspx

まずは、めんどくさがらずに「隅から隅までMSDNを読めるように努力」してください。
いっぱい重要なヒントが隠れています。

■No26059に返信(勉強中さんの記事)
> データが更新されません。

更新すべきデータを渡していないからだと思います。

DataSet の各行(DataRow)には、「行の状態」を表す RowState プロパティがあり、
それぞれ、以下の状態を持っています。
　Added     (追加)    　…　追加された行
　Deleted   (削除)    　…　削除された行
　Modified  (変更)    　…　変更された行
　Unchanged (変更なし)　…　サーバーに更新する必要のない行

DataAdapter.Fill メソッドにてデータを取得する際には、
DataAdapter.SelectCommand に設定された「SELECT」の SQL が使われますが、
DataAdapter.Update メソッドでは、これらの各行の状態を見て、
変更のあったデータのみが更新されるというわけです。

追加行では、.InsertCommand に設定された「INSERT」の SQL が利用され、
削除行では、.DeleteCommand に設定された「DELETE」の SQL が利用され、
変更行では、.UpdateCommand に設定された「UPDATE」の SQL が利用されます。
（Unchanged の行は単に無視され、何も行われません。）


なお、InsertCommand 等がそもそも割り当てられていない場合も
更新処理は行えません。これらのプロパティを手動で設定することもできますが、
この場合には CommandBuilder を使うと楽でしょう。CommandBuilder は
（SelectCommand を元にして）更新系のコマンドを自動的に生成してくれます。


-----------
> M_Set = GetMDBDataSet()
> MDB_DB = M_Set.Tables(0)
この時点では、DataAdapter.Fill した直後なので、各行の状態は Unchanged です。

> S_Set = GetSQLDataSet()
> SQL_DB = S_Set.Tables(0)
この時点でも同様に、各行の状態は Unchanged です。


さて、問題はこの後のループ処理です。

>    row = MDB_DB.Rows(i)
各テーブルはまだ未加工ですから、ここで取得した
mdb テーブルの i 行目も、もちろん Unchanged です。


>    Dim Newrow As DataRow = SQL_DB.NewRow
ここで生成した行は、まだ DataTable に登録されていません。
この行は、Detached という状態になっています。

この Detached な行を、.Rows.Add で登録することで、
追加行(Added)の状態になります。


>    Newrow = row
これはマズイです。

この行により、先ほど生成した行は一度も使われる事無く破棄され、
代わりに、この変数に mdb 側の i 行目が格納されています。
（そのため、直前の NewRow が無意味な物になってしまっています）


>    SQL_DB.ImportRow(Newrow)
ImportRow では、RowState もそのまま引き継がれます。

現時点での Newrow 変数は row であり、つまり MDB_DB.Rows(i) なのですから、
変更されていない行(Unchanged)として登録されることになります。
残念ながら、追加行(Added)ではありません。

ですから、DataAdapter.Update に渡したとしても、単に無視されてしまい、
更新される事は無いというわけです。


SQL_DB に行を追加したいなら、
　SQL_DB.Rows.Add(row(列1), row(列2), row(列3), …)
とか、
　Dim Newrow As DataRow = SQL_DB.NewRow()
　Newrow(列1) = row(列1)
　Newrow(列2) = row(列2)
　Newrow(列3) = row(列3)
　SQL_DB.Rows.Add(Newrow)
とか、
　Dim Newrow As DataRow = SQL_DB.NewRow()
　Newrow.ItemArray = row.ItemArray
　SQL_DB.Rows.Add(Newrow)
とか、
　SQL_DB.Rows.Add(row.ItemArray)
などのように記述してみてください。
これらの方法で登録された行は、RowState が Added になってくれます。

■No26061に返信(るしぇさんの記事)
> ■No26059に返信(勉強中さんの記事)
> DataAdapter.Update メソッドの解説に CommandBuilder 使うように
> 書いてあると思います。
> リンクで「DataAdapter によるデータ ソースの更新」のページなども
> 読んでみて下さい。

ちょうど似たような話が MSDN フォーラムに上がってました。
こちらを参考にされてもいいかと思います。

http://social.msdn.microsoft.com/Forums/ja-JP/sqlserverja/thread/a39f9f41-68db-43f3-a715-76b5b49ccee1

たくさんのアドバイスありがとうございます。

やはりInsertとか必要だったんですね、ＳＱＬ構文なしでももしかしたら…と思っていたのですが。
色々と覚えなきゃいけないことも多いですね。頑張らないと。

■No26070に返信(勉強中さんの記事)
DataSetにデータを入れる事はできたのですが、それをInsertするとき。
「InsertCommandのプロパティがありません」　みたいなエラーが出てきました。
DataAdapterには入れているのですが、何が足りないのでしょうか？

以下、ソース
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim MDB_DB As New DataTable
Dim SQL_DB As New DataTable

Dim M_Set As New DataSet
Dim S_Set As New DataSet
Dim row As DataRow

Try

'MDBのデータをセット
M_Set = GetMDBDataSet()
MDB_DB = M_Set.Tables(0)

'SQLのデータをセット
S_Set = GetSQLDataSet()
SQL_DB = S_Set.Tables(0)

Using da As SqlDataAdapter = New SqlDataAdapter
'MDB_DBの内容をSQL_DBに格納
For i As Integer = 0 To 4
row = MDB_DB.Rows(i)
Dim Newrow As DataRow = SQL_DB.NewRow
Newrow.ItemArray = row.ItemArray
SQL_DB.Rows.Add(Newrow)
Next
'SQL_DBからSQLServerにデータの更新する
Dim insertCmd As New SqlCommand()
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Me.SetConnection(con, cmd)

Dim insertStr As String = "INSERT INTO SQLテーブル 　　　　　　　　　　　Select * From MDBテーブル"
insertCmd.CommandText = insertStr
da.InsertCommand = insertCmd
da.Update(S_Set, "ＳＱＬテーブル")
End Using

End Sub

■No26081に返信(勉強中さんの記事)

> Dim insertStr As String =
> "INSERT INTO SQLテーブル Select * From MDBテーブル"
> insertCmd.CommandText = insertStr
> da.InsertCommand = insertCmd
> da.Update(S_Set, "ＳＱＬテーブル")

そもそも SqlDataAdapter.Update() は、
パラメータに渡された DataSet や DataTable の内容を DB へ更新させるためのメソッドです。

> "INSERT INTO SQLテーブル Select * From MDBテーブル"

の場合、SqlDataAdapter を使わずに SqlCommand.ExecuteNonQuery メソッドを使うことを推奨します。

■No26083に返信(ひらぽんさんの記事)
なるほど、この場合はdataAdapterは使わないのですね。

使うとすればDataAdapterに合うようなＳＱＬ文を書けという事でしょうか。

■No26084に返信(勉強中さんの記事)
> ■No26083に返信(ひらぽんさんの記事)
> なるほど、この場合はdataAdapterは使わないのですね。
>
> 使うとすればDataAdapterに合うようなＳＱＬ文を書けという事でしょうか。

上でも るしぇさんが書かれてますが、
DataAdapter に合うようなクエリを生成するには、
CommandBuilder を使うべきだと思います。

INSERT 文を生成するにも、まず SELECT 文を SqlCommand に設定し、
CommandBuilder で INSERT 文を自動生成させるようにします。
また SELECT 文は更新対象となる DataTable と同じ列を Select します。

上のレスでも書きましたが、MSDN フォーラムのスレッドに
サンプルを書いておきましたので、こちらを参考にしてください。

http://social.msdn.microsoft.com/Forums/ja-JP/sqlserverja/thread/a39f9f41-68db-43f3-a715-76b5b49ccee1

■No26086に返信(ひらぽんさんの記事)

ありがとうございます、そういう便利な機能があったんですね。

今回のプログラムは、他の人（ここではない）のアドバイスもありまだＤａｔａＳｅｔを使うには技術と知識が足りないとの事で、完成かけのファイルをいただきどうにか仕上げる事ができました。
当然、本当に解決してはいないのですか。区切りとしては一区切りつきましたので。締めたいと思います。

もう一度、DataSet（というかADO.NETか)類を勉強し直していこうと思います。
アドバイスをくれた方々ありがとうございました。