vb2008からデータベースのNULL禁止項目を含む変更したい

質問

vb2008とSQLServer2008Expressを勉強しております。

データベース「tblTest」を作成し、DataGridViewからNULLを禁止された項目を含む変更を行う処理で困っています。

tblTestの内容は

No|City
----------
0　|Tokyo
1　|Osaka
2　|Fukuoka
3　|Nagoya

としており、NoをNULL禁止にしております。

SQLDataAdapterを使用して
UpdateCommand、InsertCommand、DeleteCommandの設定、実行はうまくいきました。

困っている点は、

No |City
----------
0　|Tokyo
1　|Osaka
2　|Fukuoka
3　|Nagoya　　←ここを

No |City
----------
0　|Tokyo
1　|Osaka
2　|Fukuoka
4　|Hokkaido　←この様に変更したい

NoとCity両方を変更するような処理をしたい場合についてです。

DataGridView上では値の変更ですが、
処理的にはInsertCommandとDeleteCommandが一緒に実行されるような気がします。

このような場合、どのような処理を行えばいいのでしょうか？

For Each Row As DataRow In Table.Rows
で１行ずつ処理をかけるのでしょうか？

お分かりの方、アドバイスだけでもよろしくお願いいたします。

回答No.2

AKARI0418 · ベストアンサー

>TransactionScopeについてしらべてみました。
例外が発生したときに、変更を元に戻すものであると理解できました。

基本的にはそのとおりですが、
解釈としては、
複数のクエリの同時実行を保障してくれるものと考えてください。
AとBのクエリを同時に実行したい時に、
Aは成功したが、Bは失敗した場合、テーブル間の整合性を確保できなくなる場合に使用します。

例
商品Aを販売した場合
クエリA：商品テーブルからAの個数を1個減らす。
クエリB：売り上げテーブルにAの販売伝票を登録する。

この場合はどちらも同時に行われ、かつ両方のクエリが確実に成功しなければ整合性を保てませんね。
こういう場合に使用します。

本題に戻ります。

0　|Tokyo
1　|Osaka
2　|Fukuoka
3　|Nagoya　　←ここを

No |City
----------
0　|Tokyo
1　|Osaka
2　|Fukuoka
4　|Hokkaido　←この様に変更したい

とのことでした、
No,Cityを同時に変更したいわけですから、やり方は２種類です。

１．Updateで同時に変更する。
２．一回削除して、再度追加する。

１．の場合は、
SQL = "UPDATE tblTest SET City = @city WHERE No = @no"
では、Cityにしか値をセットしていないので、Noは変更されませんので、
SQL = "UPDATE tblTest SET No = @no, City = @city WHERE No = @no"

としなければいけません。

２．の場合は、
"DELETE FROM tblTest WHERE No = @no "
を実行後、
"INSERT INTO tblTest (No, City) VALUES (@no, @city)"
を実行すればよいのです。

ただし、同時に実行したいので、両方をトランザクションスコープ内で行うことが必要です。

お礼

おかげさまで、何十行もの余分な処理が削除できました。
お時間を頂き申し訳ありませんでした。
ありがとうございます。

補足

AKARI0418様、ご回答ありがとうございます。

>１．Updateで同時に変更する。
>２．一回削除して、再度追加する。

おかげさまで、１番の方法を利用し解決できました。
また、2番の方法でも正しく処理が行われました。
ありがとうございます。

>AとBのクエリを同時に実行したい時に、
Aは成功したが、Bは失敗した場合、テーブル間の整合性を確保できなくなる場合に使用します。

テスト環境でご指摘の現象が頻発しておりました。
こういった場合に使えばいいのですね。
ありがとうございます。

１ヶ月近く悩んでいたことが解決できました。
本当にうれしいです。ありがとうございます。

回答No.1

AKARI0418

TransactionScopeを使用して、
削除と追加を同時に行うようにします。

まずInsert文で、
4　|Hokkaido　
を追加し。

その後、
3　|Nagoya　
をDeleteします。

TransactionScopeを用いて、
片側のみ行われることを避けます。

補足

AKARI0418様、ご回答ありがとうございます。

TransactionScopeについてしらべてみました。
例外が発生したときに、変更を元に戻すものであると理解できました。

現在、Form_Loadの中にてSelect,Insert,Update,Deleteコマンドを設定しております。

　SQL = "SELECT * FROM tblTest"
　command = New SqlCommand(SQL, connection)
　dataAdapter.SelectCommand = command

　SQL = "INSERT INTO tblTest (No, City) VALUES (@no, @city)"
　command = New SqlCommand(SQL, Connection)
　command.Parameters.Add("@no", SqlDbType.Int, 32, "No")
　command.Parameters.Add("@city", SqlDbType.Text, 32, "City")
　dataAdapter.InsertCommand = command

　SQL = "UPDATE tblTest SET City = @city WHERE No = @no"
　command = New SqlCommand(SQL, connection)
　command.Parameters.Add("@city", SqlDbType.Text, 32, "City")
　Dim parameter As SqlParameter = command.Parameters.Add("@no", SqlDbType.Int, 32, "No")
　parameter.SourceVersion = DataRowVersion.Original
　dataAdapter.UpdateCommand = command

　SQL = "DELETE FROM tblTest WHERE No = @no "
　command = New SqlCommand(SQL, connection)
　command.Parameters.Add("@no", SqlDbType.Int, 32, "No")
　dataAdapter.DeleteCommand = command

これを、ボタン押下にて

　myDataAdapter.Update(myDataAdapter.Update(myDataSet.Tables(0))

を実行し、DataAdapterの内容をデータベースに反映させております。

これをご指摘頂いたTransactionScopeを用いて

　Using scope As New TransactionScope()
　　myDataAdapter.Update(myDataSet.Tables(0))
　　scope.Complete()
　End Using

としてみましたが、
「No」は更新されず、
「City」のみ更新されました。

これはUpdateコマンドしか実行されなかったと推測しました。
これを、ご指摘頂いた削除と追加を同時に行うにはどうすればよいのでしょうか？

もしくは、Form_Load内の各種Commandの記述がおかしいのでしょうか？

よろしくお願いいたします。