vb 用HASHTABLE批量增删改

 

Public Sub FGNarashiShinseshaUpdate(ByVal LaParam As List(Of Hashtable))

 

        Dim LtDb As New Kobetsu.Common.DBAccess

        Try

            '' 処理開始ログ出力

            MtLogger.Beginning()

 

            Dim LvUpdateTable As String = "UPDATE [経営安定＿ナラシ申請者] "

            Dim LvWhere As String = ""

            Dim LvSet As String = ""

 

            '' トランザクションを開始する。

            LtDb.beginTransaction()

 

            For Each LzHash As Hashtable In LaParam

                LvWhere = ""

                LvSet = ""

                For Each LtDE As DictionaryEntry In LzHash

                    If "申請年度".Equals(LtDE.Key) OrElse "加入者コード".Equals(LtDE.Key) Then

                        If String.IsNullOrEmpty(LvWhere) Then

                            LvWhere = LvWhere + " [" + LtDE.Key.ToString + "] = '" + LtDE.Value.ToString + "' "

                        Else

                            LvWhere = LvWhere + " AND [" + LtDE.Key.ToString + "] = '" + LtDE.Value.ToString + "' "

                        End If

                    Else

                        If String.IsNullOrEmpty(LvSet) Then

                            LvSet = LvSet + " [" + LtDE.Key.ToString + "] = '" + LtDE.Value.ToString + "' "

                        Else

                            LvSet = LvSet + ", [" + LtDE.Key.ToString + "] = '" + LtDE.Value.ToString + "' "

                        End If

                    End If

                Next

 

                Dim LvUpdateSQL As String = LvUpdateTable + " SET " + LvSet + " WHERE " + LvWhere

 

                '' SQL文出力

                Me.MtLogger.SGDebug(LvUpdateSQL)

                '' SQL文の実行

                LtDb.ExecuteNonQueryInTansaction(LvUpdateSQL)

            Next

 

            '' トランザクション終了

            LtDb.commitTansaction()

 

        Catch ex As Exception

            LtDb.rollbackTansaction()

            MtLogger.Fatal(ex)

            Throw New Common.CKobetsuException("ナラシ申請者の登録処理は失敗しました。", ex)

        Finally

            '' 処理終了ログ出力

            MtLogger.Ending()

        End Try

    End Sub

 

    ' @(f)

    '

    ' 機能     :    ナラシ申請者の削除

    '

    ' 返り値   :    なし

    '

    ' 引き数   :    LaParam - 削除用条件

    '

    ' 機能説明 :    ナラシ申請者を削除する

    '

    ' 備考     :   

    '

    Public Function FGNarashiShinseshaDelete(ByVal LaParam As List(Of Hashtable)) As Integer

 

        Dim LtDb As New Kobetsu.Common.DBAccess

        Try

            '' 処理開始ログ出力

            MtLogger.Beginning()

 

            Dim LvDeleteTable As String = "DELETE FROM [経営安定＿ナラシ申請者] "

            Dim LvWhere As String = ""

 

            '' トランザクションを開始する。

            LtDb.beginTransaction()

 

            For Each LzHash As Hashtable In LaParam

                LvWhere = ""

                For Each LtDE As DictionaryEntry In LzHash

                    If String.IsNullOrEmpty(LvWhere) Then

                        LvWhere = LvWhere + " [" + LtDE.Key.ToString + "] = '" + LtDE.Value.ToString + "' "

                    Else

                        LvWhere = LvWhere + " AND [" + LtDE.Key.ToString + "] = '" + LtDE.Value.ToString + "' "

                    End If

                Next

 

                Dim LvDeleteSQL As String = LvDeleteTable + " WHERE " + LvWhere

 

                '' SQL文出力

                Me.MtLogger.SGDebug(LvDeleteSQL)

                '' SQL文の実行

                LtDb.ExecuteNonQueryInTansaction(LvDeleteSQL)

            Next

 

            '' トランザクション終了

            LtDb.commitTansaction()

 

        Catch ex As Exception

            LtDb.rollbackTansaction()

            MtLogger.Fatal(ex)

            Throw New Common.CKobetsuException("ナラシ申請者の削除処理は失敗しました。", ex)

        Finally

            '' 処理終了ログ出力

            MtLogger.Ending()

        End Try

    End Function

 

 

    ' @(f)

    '

    ' 機能     :    ナラシ申請者の追加

    '

    ' 返り値   :    なし

    '

    ' 引き数   :    LaParam - 追加用条件

    '

    ' 機能説明 :    ナラシ申請者を追加する

    '

    ' 備考     :   

    '

    Public Function FGNarashiShinseshaInsert(ByVal LaParam As List(Of Hashtable)) As Integer

 

        Dim LtDb As New Kobetsu.Common.DBAccess

        Try

            '' 処理開始ログ出力

            MtLogger.Beginning()

            Dim LvInsertTable As String = "INSERT INTO [経営安定＿ナラシ申請者]  ("

 

            '' トランザクションを開始する。

            LtDb.beginTransaction()

 

            For Each LzHash As Hashtable In LaParam

                Dim LvInsertNames As String = ""

                Dim LvInsertValues As String = ""

                For Each LtDE As DictionaryEntry In LzHash

                    If String.IsNullOrEmpty(LvInsertNames) Then

                        LvInsertNames = LvInsertNames + " [" + LtDE.Key.ToString + "] "

                        LvInsertValues = LvInsertValues + " VALUES ('" + LtDE.Value.ToString + "' "

                    Else

                        LvInsertNames = LvInsertNames + ", [" + LtDE.Key.ToString + "] "

                        LvInsertValues = LvInsertValues + " ,'" + LtDE.Value.ToString + "' "

                    End If

                Next

 

                Dim LvInsertSQL As String = LvInsertTable + LvInsertNames + ") " + LvInsertValues + ") "

 

                '' SQL文出力

                Me.MtLogger.SGDebug(LvInsertSQL)

                '' SQL文の実行

                LtDb.ExecuteNonQueryInTansaction(LvInsertSQL)

            Next

 

            '' トランザクション終了

            LtDb.commitTansaction()

 

        Catch ex As Exception

            LtDb.rollbackTansaction()

            MtLogger.Fatal(ex)

            Throw New Common.CKobetsuException("ナラシ申請者の追加処理は失敗しました。", ex)

        Finally

            '' 処理終了ログ出力

            MtLogger.Ending()

        End Try

    End Function

 

#End Region

 

End Class