SQLデータベースへ重複読み込み? への返答

投稿で使用できる特殊コードの説明。(別タブで開きます。)
本名は入力しないようにしましょう。
投稿した後で削除するときに使うパスワードです。返答があった後は削除できません。
返答する人が目安にします。相手が小学生か社会人かで返答の仕方も変わります。
最初の投稿が質問の場合、質問者が解決時にチェックしてください。(以降も追加書き込み・返信は可能です。)
※「過去ログ」について書くときはその過去ログのURLも書いてください。

以下の返答は逆順(新しい順)に並んでいます。

投稿者 まる  (社会人) 投稿日時 2009/3/2 19:52:53
るきおさん、返事が遅れてすみませんでした。
そして大変親切な回答・解説、ありがとうございます!
おかげで無事問題が解決できました。

DataReaderは「接続を占有して、値の更新もできない」、DataTableは「データを一気に取得するので、パフォーマンスが悪い」ということが勉強になりました。

これからも少しずつ使用して、自分のものにできたらなと思います。
るきおさん、本当にありがとうございました!
投稿者 るきお  (社会人) 投稿日時 2009/2/28 07:30:55
>今コネクションを
>Dim Cn As SqlClient.SqlConnection
>で宣言しているのですが、もう1つ別名で同じ宣言をすればいいということなのですか?
宣言も必要ですが、それだけでいいわけではありませんしここは問題の本質ではありません。

>別クラスが読まれたときに「Cn」をNewしているのですが、それでもだめなのでしょうか?
Newして使いまわしているのならOKです。
けれど同じ変数で複数の接続を表現するようなコードを書くと、わかりにくくなって大変だと思います。

以下の例では同様のエラーが発生します。
        '▼接続を確立 
        Dim cn1 As New SqlConnection
        cn1.ConnectionString = "Server=.;Integrated Security = True"
        cn1.Open()

        '▼クエリ生成 
        Dim sqlCm1 As New SqlCommand("SELECT * FROM sys.databases", cn1)

        '▼DataReader生成 
        Dim reader As SqlDataReader
        reader = sqlCm1.ExecuteReader

        '▼ループを回しつつ、同じ接続でExecuteNonQuery 
        While reader.Read
            Dim sqlCm2 As New SqlCommand("SELECT * FROM sys.objects", cn1)

            'ここでエラー 
            sqlCm2.ExecuteNonQuery()
        End While

        '▼後処理 
        reader.Close()
        cn1.Close()

        MsgBox("OK")


これを接続を2つ使ってエラーがでないように書き換えると次のようになります。
        '▼接続を確立 
        Dim cn1 As New SqlConnection
        cn1.ConnectionString = "Server=.;Integrated Security = True"
        cn1.Open()

        '▼クエリ生成 
        Dim sqlCm1 As New SqlCommand("SELECT * FROM sys.databases", cn1)

        '▼DataReader生成 
        Dim reader As SqlDataReader
        reader = sqlCm1.ExecuteReader

        '▼2つ目の接続を確立 
        Dim cn2 As New SqlConnection("Server=.;Integrated Security = True")
        cn2.Open()

        '▼1つ目の接続でループを回しつつ、2つ目の接続でExecuteNonQuery 
        While reader.Read
            Dim sqlCm2 As New SqlCommand("SELECT * FROM sys.objects", cn2)
            sqlCm2.ExecuteNonQuery()
        End While

        '▼後処理 
        cn2.Close()
        reader.Close()
        cn1.Close()

        MsgBox("OK")


ただし、同時にデータベースに接続するのはできるだけさけるべきであり、このような修正はあまりお勧めしません。
DataTableを使えば同時にデータベースに接続する必要はなくなります。

DataTableを使って書き換えると次のようになります。
        '▼接続を確立 
        Dim cn1 As New SqlConnection
        cn1.ConnectionString = "Server=.;Integrated Security = True"
        cn1.Open()

        '▼クエリ生成 
        Dim sqlCm1 As New SqlCommand("SELECT * FROM sys.databases", cn1)

        '▼DataTableにデータを格納する 
        Dim adapter As New SqlDataAdapter(sqlCm1)
        Dim table As New DataTable
        adapter.Fill(table)

        '▼DataTableでループを回しつつ、1つ目の接続でExecuteNonQuery 
        For Each row As DataRow In table.Rows
            Dim sqlCm2 As New SqlCommand("SELECT * FROM sys.objects", cn1)
            sqlCm2.ExecuteNonQuery()
        Next

        '▼後処理 
        cn1.Close()

        MsgBox("OK")


ただし、いつでもDataTableを使えばよいというわけではなく、DataReaderなどとの適切な使い分けは必要です。
たとえばDataTableはデータをクライアント側にごっそりもってくるためデータの量が多い場合はリソースを消費しパフォーマンスが悪化します。
DataReaderはサーバー側との接続を開いた状態にしておく必要があるので、今回の事例のように接続を占有してしまいます。また前方にしか移動できず値の更新もできません。

ちなみに私はほとんどの場面でDataReaderではなくDataTableを使用しています。
投稿者 (削除されました)  () 投稿日時 2009/2/28 07:14:14
(削除されました)
投稿者 まる  (社会人) 投稿日時 2009/2/27 22:27:43
るきおさん、ご返答ありがとうございます。

今コネクションを
Dim Cn As SqlClient.SqlConnection
で宣言しているのですが、もう1つ別名で同じ宣言をすればいいということなのですか?
別クラスが読まれたときに「Cn」をNewしているのですが、それでもだめなのでしょうか?

DataTableに関しては、使い方がわからないので調べてみます。

接続についての宣言などはこのようになっています。

メインクラス
Dim Cn As SqlClient.SqlConnection
Dim Command As New SqlClient.SqlCommand
Dim MyClass As 別クラス
Public Sub New()
    MyClass=New 別クラス
    Cn=MyClass.接続のテキスト
    Command.Connection=Cn
End Sub

別クラス
Dim Cn As SqlClient.SqlConnection
Dim Command As New SqlClient.SqlCommand
Public Sub New()
    MyBase.New()
    Cn=データベースの接続のためのText
    Command.Connection=Cn
End Sub
投稿者 (削除されました)  () 投稿日時 2009/2/27 22:24:42
(削除されました)
投稿者 るきお  (社会人) 投稿日時 2009/2/27 21:20:59
提示されたコードだけからははっきりとはいえませんが、
恐らくDataReaderを開いた状態で、同じ接続を使ってExecuteNonQueryを実行しているのが原因と思います。

DataReaderは接続を占有しますので、DataReaderを開いているときにその接続を使ってExecuteNonQueryなどの別処理を行うことはできません。

回避策としてこの「接続」とはSqlConnectionクラスのことですので、同じデータベースに対して2つのSqlConnectionを作成すれば片方ではDataReaderを実行し、他方ではExecuteNonQueryを発行することが可能になります。
これはプログラム的には大して難しくはないのですが、トランザクションの排他制御などデータベースの読み書きに関する通常の同時実行制御について気をつけることが増えてしまうので、ある程度重要な業務で使用する場合は十分に設計・テストを行ってください。

もう1つの回避策としてはDataReaderを使うのを止めるということが考えられます。
たとえば、代わりにDataTableを使用することはできませんか?
投稿者 まる  (社会人) 投稿日時 2009/2/27 18:46:05
いつもお世話になっております。

今1つのデータベースから2つのストアドプロシージャで読み込もうとしているのですが、うまくいきません・・・。

「このコマンドに関連付けられている DataReader が既に開かれています。このコマンドを最初に閉じる必要があります。」というエラーです。
でも今自分がやろうとしているものは、1つめのストアドプロシージャ実行して、データを取得しつつ、2つめのストアドプロシージャを実行して、データを取得したいのです。

誰か解決法があればお願いいたします。

データベースへは接続されているものとします。

メインクラス
Dim Reader As SqlClient.SqlDataReader
Dim Command As New SqlClient.SqlCommand

With Command
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Stored1"
    .Parameters.Clear()
End With

Reader=Command.ExecuteReader()

While Reader.Read()
    Call 別クラス.Test()
End While

別クラス
Dim Command As New SqlClient.SqlCommand

With Command
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Stored2"
    .Parameters.Clear()
    .Parameters.AddWithValue("@x", x)
    .Parameters.Add("Return", SqlDbType.Decimal)
    .Parameters("Return").Direction = ParameterDirection.ReturnValue
    .ExecuteNonQuery() ←ここでエラーになります。
End With