質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.19%

DataTableをSQLServerにInsertする

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 1,206

pon108

score 15

前提・実現したいこと

Visual Studio2019を使用して
在庫管理Windowsアプリケーションを作成しております。

その中でExcelファイルを読み込み、Formに配置しているDataGridViewに表示を行い
表示された内容をSQLServerに用意している2つのTABLEにInsertするようにしたいと考えております。

登録部分で躓いてしまったため書けておりませんが登録するデータの重複チェックを行い、
重複していない時は両TableにINSERT、
重複しているようであれば
 ・mst_tableにはINSERTしない
 ・DATA_tableは重複しているデータの数量の部分に足す(UPDATE)
行うようにしたいと考えております。

■Formに配置されているコントロール
 ・Button1 ⇒ OpenFileDialogを使用してExcelファイルを選択してDataGridView1に表示
 ・Button2 ⇒ DataGridView1に表示されている内容をSQLServerにInsert
 ・DataGridView1 ⇒ 読み込んだExcelファイルの表示

読み込むExcelファイルは8列×3000行ぐらいです。
列数は固定で、行数は可変となります。

■INSERTするTable(データ型)
 ・mst_table ⇒ 品物マスター
   ・列名1 … nvarchar(50)
   ・列名2 … nvarchar(50)
   ・列名3 … nvarchar(50)
   ・列名4 … decimal(8, 0)
   ・列名5 … varbinary(MAX)
   ・列名6 … nvarchar(50)
   ・列名7 … date
   ・列名8 … nvarchar(50)

 ・DATA_table ⇒ 品物の在庫数量管理
   ・列名1 … nvarchar(50)
   ・列名2 … int
   ・列名3 … datetime

発生している問題・エラーメッセージ

DataGridViewに表示した内容をDBへ登録する際に下記エラーが発生します。
・エラーメッセージ
 「実行タイムアウトの期限が切れました。操作完了前にタイムアウト期間が過ぎたか、サーバーが応答していません。」
DataTableの内容を順番に確認をしながらInsertしているので発生しているのだと思うのですが
今の私ではこれ以外に良い方法が見つかりませんでした。

該当のソースコード

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim dt As New DataTable

        dt = DataGridView1.DataSource

        Call insert_Table(dt)

    End Sub

    Sub insert_Table(ByVal dt As DataTable)

        Dim check_Sql As String = Nothing
        Dim Insert_Sql_mst As String = Nothing
        Dim Insert_Sql_DATA As String = Nothing
        Dim sinamono As String = Nothing
        Dim used As Boolean = True


        Using db_cn As New SqlConnection(接続文字列)
            Using db_cmd As SqlCommand = db_cn.CreateCommand

                db_cn.Open()

                Try

                    db_cmd.Transaction = db_cn.BeginTransaction()

                    For i As Integer = 0 To dt.Rows.Count - 1

                        sinamono = dt.Rows(i)("EXCEL列名2").ToString

                        If sinamono <> Nothing Then

                            mySql_check = "SELECT COUNT(*) "
                            mySql_check &= "FROM mst_table "
                            mySql_check &= "WHERE EXCEL列名2 = '" & sinamono & "'"


                            '******【すでに登録されているか確認】*********

                            ml_function.UsedCode(check_Sql)

                            '*****************************************


                            '登録がなかった場合、【新規登録(INSERT)】
                            If used = True Then

                                Insert_Sql_mst = "INSERT INTO mst_table "
                                Insert_Sql_mst &= "(列名1, 列名2, 列名3, 列名4, 列名5, 列名6, 列名7, 列名8)"
                                Insert_Sql_mst &= "VALUES("
                                Insert_Sql_mst &= "'" & dt.Rows(i)("EXCEL列名1").ToString & "', "
                                Insert_Sql_mst &= "'" & dt.Rows(i)("EXCEL列名2").ToString & "', "
                                Insert_Sql_mst &= "'" & dt.Rows(i)("EXCEL列名3").ToString & "', "
                                Insert_Sql_mst &= "'" & CInt(dt.Rows(i)("EXCEL列名4").ToString) & "', "
                                Insert_Sql_mst &= "NULL, "
                                Insert_Sql_mst &= "'" & dt.Rows(i)("EXCEL列名6").ToString & "', "
                                Insert_Sql_mst &= "'" & DateTime.Now() & "', "
                                Insert_Sql_mst &= "'" & My.Settings.変数 & "'"
                                Insert_Sql_mst &= ")"

                                Insert_Sql_DATA = "INSERT INTO DATA_table "
                                Insert_Sql_DATA &= "(列名1, 列名2, 列名3) "
                                Insert_Sql_DATA &= "VALUES("
                                Insert_Sql_DATA &= "'" & dt.Rows(i)("EXCEL列名2").ToString & "', "
                                Insert_Sql_DATA &= "'" & CInt(dt.Rows(i)("EXCEL列名7").ToString) & "', "
                                Insert_Sql_DATA &= "'" & DateTime.Now() & "'"
                                Insert_Sql_DATA &= ")"


                                db_cmd.CommandText = Insert_Sql_mst
                                db_cmd.ExecuteNonQuery()

                                db_cmd.CommandText = Insert_Sql_DATA
                                db_cmd.ExecuteNonQuery()

                            End If

                        End If

                    Next

                    db_cmd.Transaction.Commit()

                    MessageBox.Show("登録完了。")

                Catch ex As Exception

                    MessageBox.Show(ex.Message, "エラー")
                    db_cmd.Transaction.Rollback()

                End Try

            End Using
        End Using
    End Sub

    Public Function UsedCode(check_Sql As String) As Boolean

        '※存在している時はTrue
        Dim used As Boolean = True


        Using db_con As New SqlConnection(接続文字列)

            Using db_cmd As SqlCommand = db_con.CreateCommand()

                db_con.Open()

                Dim db_dr As SqlDataReader

                db_cmd.CommandText = check_Sql

                db_dr = db_cmd.ExecuteReader()

                If db_dr.HasRows Then
                    db_dr.Read()
                    If db_dr(0) = 0 Then
                        'カウント0で存在なしはFalse
                        used = False

                    End If
                End If

            End Using
        End Using

        Return used

    End Function

試したこと

下記サイトを参考にDataTableを一括登録する方法など考えましたがDataGridViewに表示した内容を
二つのDataTableに分けて登録するような応用が出来ませんでした。

参考サイト
リンク
 

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

Visual Studio2019
SQLServer2016
開発言語 VB

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+2

追加処理のトランザクションと存在有無のチェック内のSELECTコマンドが別トランザクションとなっているために、ロックがかかっているのではないでしょうか?

    Public Function UsedCode(con As SqlConnection, trn As SqlTransaction, check_Sql As String) As Boolean

        '※存在している時はTrue
        Dim used As Boolean = True

        Using db_cmd As SqlCommand = con.CreateCommand()

            db_cmd.Transaction = trn
            db_cmd.CommandText = check_Sql

            Dim db_dr As SqlDataReader = db_cmd.ExecuteReader()

            If db_dr.HasRows Then
                db_dr.Read()
                If db_dr(0) = 0 Then
                    'カウント0で存在なしはFalse
                    used = False

                End If
            End If

        End Using

        Return used

    End Function


といった感じで、同じコネクション、同じトランザクションを使用するようにしてはどうでしょうか?

追記
リンク先の方法ですと、ストアドを作成しての3番の方法がいいのではないでしょうか?
そのストアドの中で存在チェック等を行い、INSERT、UPDATEを実行すればいいでしょう。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/07/27 11:21

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

    教えていただいたように同じコネクション、トランザクションを使用するように変更してみたのですが
    今度は「この Command に関連付けられている DataReader が既に開かれています。このコマンドを最初に閉じる必要があります。」とエラーが出ます。
    調べたところ、「DataReaderはひとつの接続に対して1つしか開くことができません。」とあったのですが、DataReader は一度しか使っていないと思っているのですが…
    何か他に原因があるのでしょうか?

    キャンセル

  • 2019/07/27 12:16

    SqlDataReaderは使い終わったらCloseしてください。
    また、Countを取るだけであれば、ExecuteReaderを使用するのではなくExecuteScalarを使用したほうがよろしいかと

    キャンセル

  • 2019/07/28 01:40

    ありがとうございます。本当に助かりました。
    教えていただきました内容で無事に解決することが出来ました。

    またアドバイスいただきましたようにExecuteScalarを使用するように変更しました。

    キャンセル

+2

DataTableをSQLServerにInsertする

登録するデータの重複チェックを行い云々のあたりが理解できていませんが、もし、最終的な INSERT 用の DataTable が完成できているのであれば、以下の記事に書いてあるような手段は使えないでしょうか?

DataAdapter を使用したバッチ操作の実行
https://docs.microsoft.com/ja-jp/dotnet/framework/data/adonet/performing-batch-operations-using-dataadapters

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/07/27 11:23

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

    教えていただきましたサイトを確認しました。
    ただ読み解くのに少し時間が掛かりそうなので参考にさせていただき、勉強してみます。

    キャンセル

+1

DataTableを一括登録する方法など考えましたがDataGridViewに表示した内容を
二つのDataTableに分けて登録するような応用が出来ませんでした。

リンク先のサンプルだとFROMは同じで出力するテーブルやselect項目を変更した、SQLをそれぞれ発行するようにすれば良いのでは。

ところで、データをマスタとトランザクションに分けて追加する場合、外部参照の状態でしょうから、マスターに追加する部分は重複を避けたり、登録済みの除外とか不要でないのかが気になります。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/07/28 01:44

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

    参考にしているサイトの内容をもう一度読み直し、別の書き方も学んでみます。

    また追加のアドバイスをいただき、ありがとうございます。
    確かに必要な部分なので追加してみます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.19%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る