トランザクション文中でSELECT、Fillを使いたい。

環境/言語:[XP、VB.NET2003]
分類:[.NET]



お世話になります。
以下、お伺いさせて頂きます。
実際に作成しているコードは、複雑ですので、
同構造の、簡単なコードを使いまして、
ご質問させて頂きます。

SQLServerに、２つのテーブルがあります。

TEST1
氏名（テキスト型）

TEST2
氏名2（テキスト型）

フォームにボタンが１つ、あります。
ボタンを押して、
TESTにデータがない場合、
TESTの氏名に、AをINSERTします。
TESTにデータがある場合、
TESTの氏名を、TEST2の氏名2で、更新します。

以下、コードです。

-----------------------------------------------------------

Private Sub btnTest_Click(以下、略）

Dim cn As New SqlClient.SqlConnection(接続文字列)
Dim cmd As New SqlClient.SqlCommand

Dim ts As SqlClient.SqlTransaction
Dim da As SqlClient.SqlDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim dr() As DataRow

Dim da2 As SqlClient.SqlDataAdapter
Dim ds2 As DataSet
Dim dt2 As DataTable
Dim dr2() As DataRow

Dim strSQL As String
Dim strSQL2 As String
Dim strUpdate As String
Dim strInsert As String

Dim strName As String

Try
cn.Open()・･････注１

strSQL = "select * FROM TEST"
da = New SqlClient.SqlDataAdapter(strSQL, cn)
ds = New DataSet
da.Fill(ds, "TEST")･･････注２
dt = ds.Tables("TEST")
dr = dt.Select

ts = cn.BeginTransaction･･････注３
cmd.Transaction = ts･･････注３

If dr.Length = 0 Then
strInsert = "insert into TEST(氏名) values ('A')"
cmd.Connection = cn
cmd.CommandText = strInsert
cmd.ExecuteNonQuery()
Else
strSQL2 = "select 氏名2 FROM TEST2"
da2 = New SqlClient.SqlDataAdapter(strSQL2, cn)
ds2 = New DataSet
da2.Fill(ds, "TEST")･･････注４
dt2 = ds.Tables("TEST")
dr2 = dt.Select

strName = CStr(dr2(0)("氏名2"))
strUpdate = "update TEST set 氏名 = '" & strName & "'"
cmd.Connection = cn
cmd.CommandText = strUpdate
cmd.ExecuteNonQuery()
End If

ts.Commit()

Catch ex As Exception
（略）
Finally
（略）
End Try

End Sub
-----------------------------------------------------------

上記を実行しますと、
注４の行で、エラーが発生します。

「Executeは、コマンドに割り当てられた接続が保留状態であるローカルのトランザクションにあるとき、
トランザクションオブジェクトを持つコマンドが必要です。
コマンドのTransactionプロパティまで初期化されていません。」

これと、同じ内容のエラーが、注３を、注１の直下に置いても、
注２の行で、発生します。

この場合は、他のサイトを調べまして、注３を、注２の後に置けばいい、と、分かりまして、
そのように、致しました。

ただ、注４の場合、注３を、注４の下に置くことが、出来ません。

da2.Fillを使う代わりに、

cmd.Connection = cn
cmd.CommandText = strSQL2
strName = CStr(cmd.ExecuteScalar)

と、置き換えますと、エラーは出ないのですが、

実際に作成しているコードでは、ここで、Fillを使いたい状況が、ございます。

この、Fillの部分を、そのまま残した上で、

このエラーを回避するための方法が、ございましたら、
ご教示頂きたいと、存じます。

どうぞよろしくお願い申し上げます。

エラー回避のため、テーブル名やフィールド名の[]を、外しております。

2013/05/18(Sat) 18:41:45 編集(投稿者)

この掲示板には
　『半角カナは使用しないでください。文字化けの原因になります。』
という注意書きがあったかと思います。

元の投稿に半角の「・」が含まれていたため、引用時に別の文字に
置き換えてさせて頂きました。次回以降の投稿ではご注意ください。


■No31548に返信(ERIさんの記事)
> 環境/言語:[XP、VB.NET2003]　
懐…(^^;


> TEST1
> 氏名（テキスト型）
TEST1 ではなく、TEST なのでは。


> TESTにデータがない場合、
> TESTの氏名に、AをINSERTします。
> TESTにデータがある場合、
> TESTの氏名を、TEST2の氏名2で、更新します。
MERGE ステートメントでは駄目なのでしょうか。
http://d.hatena.ne.jp/matu_tak/20100124/1264361341


> 以下、コードです。
コードを投稿する際には、「図表モード」を指定しておくと
インデントが失われずに済みますよ。


> da.Fill(ds, "TEST")・・・・・・注２
この部分はトランザクションに含めなくてよいのですか?


> ds2 = New DataSet
> da2.Fill(ds, "TEST")……注４
> dt2 = ds.Tables("TEST")
あれ? ds2 が使われていないような…。
それに、DataTable の名前も "TEST" で良いのでしょうか?

別テーブルである以上、普通は
　da2.Fill(ds, "TEST2")
もしくは
　da2.Fill(ds2, 何某)
のいずれかで実装するのが自然かと思いますよ。

まぁ、マージしたい場合には同名テーブルに Fill することもありますが
元々のテーブル構造(主キー構成など)が分からないので、同じテーブルに
続けて Fill して良いのかどうかは、何とも言えないのですけれど。


> da2.Fill(ds, "TEST")……注４
> 注４の行で、エラーが発生します。
トランザクション「ts」を割り当てていないからだと思います。。

Fill する前に、da2.SelectCommand.Transaction = ts を
実行しておいてみてください。これで解決できるのでは無いでしょうか。

もしも 更新処理(da2.Update) も行う予定がある場合には、
DeleteCommand/InserCommand/UpdateCommand も同様にして
トランザクションの範囲に含めておいてください。


> dr = dt.Select
> If dr.Length = 0 Then
件数を見るだけなら、Select メソッドの呼び出しは不要で、
　If dt.Rows.Count = 0 Then
で良いと思います。

そもそも、変数 dr や dt の中身は参照されていないようですが、
今のコードだと、TEST テーブルに数十万件のデータがあったとしても
無駄にデータの取り込みが行われてしまいますが、問題ないのでしょうか?

もしも中身は参照せず、単に 0 件かどうかの判定を行いたいだけなのであれば、
"SELECT TOP 1 'X' AS DUMMY FROM TEST"
を取得した方が効率が良くなります。あるいは件数そのものを得たいのであれば
"SELECT COUNT(*) FROM TEST" を ExecuteScalar するのがよろしいかと。


> dr2 = dt.Select
ここでは、Select メソッドの呼び出しに括弧をつけていないのに
> cmd.ExecuteNonQuery()
> ts.Commit()
これらでは、括弧を付与していますね。
実行結果は同じことですが、記述を揃えておいた方がスマートですよ。


> この場合は、他のサイトを調べまして、
具体的な URL を教えてもらえますか?

> 注３を、注２の後に置けばいい、と、分かりまして、
『何故』そうすれば良いのかも分かりましたか?

■No31550に返信(魔界の仮面弁士さんの記事)

ありがとうございます。

>>TEST1
>>氏名（テキスト型）
> TEST1 ではなく、TEST なのでは。

大変失礼致しました。
TEST、でした。

>>TESTにデータがない場合、
>>TESTの氏名に、AをINSERTします。
>>TESTにデータがある場合、
>>TESTの氏名を、TEST2の氏名2で、更新します。
> MERGE ステートメントでは駄目なのでしょうか。
> http://d.hatena.ne.jp/matu_tak/20100124/1264361341

調べてみます。

> コードを投稿する際には、「図表モード」を指定しておくと
> インデントが失われずに済みます。

そうなのですね。
今後、そのように、致します。

>>da.Fill(ds, "TEST")・・・・・・注２
> この部分はトランザクションに含めなくてよいのですか?

本当は、含めたいのですが、エラーが出ると困るので、
外しておりました。

>>ds2 = New DataSet
>>da2.Fill(ds, "TEST")……注４
>>dt2 = ds.Tables("TEST")
> あれ? ds2 が使われていないような…。
> それに、DataTable の名前も "TEST" で良いのでしょうか?
>
> 別テーブルである以上、普通は
> 　da2.Fill(ds, "TEST2")
> もしくは
> 　da2.Fill(ds2, 何某)
> のいずれかで実装するのが自然かと思いますよ。
>
> まぁ、マージしたい場合には同名テーブルに Fill することもありますが
> 元々のテーブル構造(主キー構成など)が分からないので、同じテーブルに
> 続けて Fill して良いのかどうかは、何とも言えないのですけれど。

大変失礼致しました。
>>ds2 = New DataSet
>>da2.Fill(ds2, "TEST2")……注４
>>dt2 = ds.Tables("TEST2")
でした。

>>注４の行で、エラーが発生します。
> トランザクション「ts」を割り当てていないからだと思います。。
>
> Fill する前に、da2.SelectCommand.Transaction = ts を
> 実行しておいてみてください。これで解決できるのでは無いでしょうか。
>
> もしも 更新処理(da2.Update) も行う予定がある場合には、
> DeleteCommand/InserCommand/UpdateCommand も同様にして
> トランザクションの範囲に含めておいてください。

ありがとうございます。
やってみます。

>>dr = dt.Select
>>If dr.Length = 0 Then
> 件数を見るだけなら、Select メソッドの呼び出しは不要で、
> 　If dt.Rows.Count = 0 Then
> で良いと思います。
>
> そもそも、変数 dr や dt の中身は参照されていないようですが、
> 今のコードだと、TEST テーブルに数十万件のデータがあったとしても
> 無駄にデータの取り込みが行われてしまいますが、問題ないのでしょうか?
>
> もしも中身は参照せず、単に 0 件かどうかの判定を行いたいだけなのであれば、
> "SELECT TOP 1 'X' AS DUMMY FROM TEST"
> を取得した方が効率が良くなります。あるいは件数そのものを得たいのであれば
> "SELECT COUNT(*) FROM TEST" を ExecuteScalar するのがよろしいかと。

ありがとうございます。
実際のコードでは、drの内容を参照する部分がありまして。
ただ、そうではない部分もありますので、教えて頂きました方法で、
改善してみます。

>>dr2 = dt.Select
> ここでは、Select メソッドの呼び出しに括弧をつけていないのに
>>cmd.ExecuteNonQuery()
>>ts.Commit()
> これらでは、括弧を付与していますね。
> 実行結果は同じことですが、記述を揃えておいた方がスマートですよ。

ありがとうございます。

>>この場合は、他のサイトを調べまして、
> 具体的な URL を教えてもらえますか?

こちらです。
http://homepage1.nifty.com/rucio/commu/ThreadDetail_ThreadId_9035.htm

>
>>注３を、注２の後に置けばいい、と、分かりまして、
> 『何故』そうすれば良いのかも分かりましたか?

今回のご教示で、理解できました。

検証いたしまして、再度、ご報告させていただきます。
ありがとうございます＾＾。

■No31550に返信(魔界の仮面弁士さんの記事)
> 2013/05/18(Sat) 18:41:45 編集(投稿者)

教えて頂きました方法で、解決できました。
ありがとうございました。

>
> この掲示板には
> 　『半角カナは使用しないでください。文字化けの原因になります。』
> という注意書きがあったかと思います。
>
> 元の投稿に半角の「・」が含まれていたため、引用時に別の文字に
> 置き換えてさせて頂きました。次回以降の投稿ではご注意ください。

大変失礼致しました。
投稿時、注意書きが出たのですが、半角カタカナを見つけられませんでした。
「・」が、そうだったのですね。
今後、気をつけます。

ありがとうございました。