Imports System.Collections
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Transactions

'**************************************************************************
' Name       :MyDataBase
'--------------------------------------------------------------------------
' Comment    :DataBase連携クラス
'             ※SQLServerとAccessMDBの両方に対応しています。
'             ！実装時の注意：否定「!=」演算子はAccessでは無効なため、「<>」を使用すること。
'                             SQLを修正する際は、SQL用だけでなくAccess用のパラメータ設定記述も忘れず同期をとること。
'--------------------------------------------------------------------------
' Design On  :2013/01/31
'        By  :NCS)T.Kikuchi
'--------------------------------------------------------------------------
' Update On  :
'        By  :
' Comment    :
'**************************************************************************
Public Class MyDataBase
    Implements IDisposable

    Private Const CON_CONNECTION As String = "eTellaDbConnection"

    Public Sub New()
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
    End Sub

#Region "Fill/Get"
    Public Function FillシステムMDataTable() As DataSet1.システムMDataTable
        Using ds As DataSet1 = New DataSet1
            Fill共通(ds.システムM, "SELECT * FROM システムM ORDER BY 表示順")
            Return ds.システムM
        End Using
    End Function

    Public Sub FillシステムM(ByVal ds As DataSet1)
        Fill共通(ds.システムM, "SELECT * FROM システムM WHERE 項目名 <> '" + MyConstant.投票制御フラグ項目名 + "' ORDER BY 表示順")
    End Sub

    Public Sub Fill選挙M(ByVal ds As DataSet1)
        Fill共通(ds.選挙M, "SELECT * FROM 選挙M WHERE (表示フラグ = 1) ORDER BY 表示順")
    End Sub

    Public Sub Fill選挙MBy選挙ID(ByVal ds As DataSet1, ByVal senkyoId As String)
        Fill共通(ds.選挙M, "SELECT * FROM 選挙M WHERE 選挙ID='" + senkyoId + "'")
    End Sub

    Public Sub Fill選挙M全件(ByVal ds As DataSet1)
        Fill共通(ds.選挙M, "SELECT * FROM 選挙M")
    End Sub

    Public Sub Fill政党M(ByVal ds As DataSet1)
        Fill共通(ds.政党M, "SELECT * FROM 政党M WHERE (表示フラグ = 1) ORDER BY 表示順")
    End Sub

    Public Sub Fill政党M全件(ByVal ds As DataSet1)
        Fill共通(ds.政党M, "SELECT * FROM 政党M")
    End Sub

    Public Sub Fill候補者MBy選挙ID(ByVal ds As DataSet1, ByVal senkyoId As String)
        Fill共通(ds.候補者M, "SELECT * FROM 候補者M WHERE 選挙ID='" + senkyoId + "' AND (表示フラグ = 1) ORDER BY 表示順")
    End Sub

    Public Sub Fill候補者MBy選挙ID候補者ID(ByVal ds As DataSet1, ByVal senkyoId As String, ByVal kohoId As String)
        Fill共通(ds.候補者M, "SELECT * FROM 候補者M WHERE 選挙ID='" + senkyoId + "' AND 候補者ID='" + kohoId + "' AND (表示フラグ = 1)")
    End Sub

    Public Sub Fill候補者M全件(ByVal ds As DataSet1)
        Fill共通(ds.候補者M, "SELECT * FROM 候補者M ORDER BY 選挙ID, 表示順")
    End Sub

    Public Sub Fill投票券T(ByVal ds As DataSet1)
        Fill共通(ds.投票券T, "SELECT * FROM 投票券T")
    End Sub

    Public Sub Fill投票券TBy投票券番号(ByVal ds As DataSet1, ByVal no As String)
        Fill共通(ds.投票券T, "SELECT * FROM 投票券T WHERE 投票券番号='" + no + "' AND 発券取消日時=''")
    End Sub

    Public Sub Fill会員M(ByVal ds As DataSet1)
        Fill共通(ds.会員M, "SELECT * FROM 会員M")
    End Sub

    Public Sub Fill会員MByユーザID(ByVal ds As DataSet1, ByVal id As String)
        Fill共通(ds.会員M, "SELECT * FROM 会員M WHERE ユーザID='" + id + "'")
    End Sub

    Public Sub Fill投票T(ByVal ds As DataSet1)
        Fill共通(ds.投票T, "SELECT * FROM 投票T")
    End Sub

    Public Sub Fill投票TBy選挙ID投票券番号(ByVal ds As DataSet1, ByVal senkyoId As String, ByVal no As String)
        Fill共通(ds.投票T, "SELECT * FROM 投票T WHERE 選挙ID='" + senkyoId + "' AND 投票券番号='" + no + "'")
    End Sub

    Public Function Get選挙名称By選挙ID(ByVal senkyoId As String) As String
        Return Get共通("SELECT 選挙名称 FROM 選挙M WHERE 選挙ID='" + senkyoId + "'")
    End Function

    Public Function Get政党名称By政党ID(ByVal seitoId As String) As String
        Return Get共通("SELECT 政党名称 FROM 政党M WHERE 政党ID='" + seitoId + "'")
    End Function

    Public Sub Fill投票所M全件(ByVal ds As DataSet1)
        Fill共通(ds.投票所M, "SELECT * FROM 投票所M")
    End Sub

#End Region

#Region "Insert/Updateメソッド"
    Public Function Insert投票T(ByVal senkyoId As String, ByVal no As String, ByVal loginTime As String, _
                              ByVal _投票日時 As DateTime, ByVal internetFlag As Integer) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "INSERT INTO 投票T VALUES(@a, @b, @c, @d, @e, @f, @g, @h)"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)
            Using command As New SqlCommand
                command.CommandText = sql
                ' 引数設定
                command.Parameters.Clear()
                command.Parameters.Add(New SqlParameter("@a", SqlDbType.Char, 20))
                command.Parameters.Add(New SqlParameter("@b", SqlDbType.Char, 20))
                command.Parameters.Add(New SqlParameter("@c", SqlDbType.DateTime))
                command.Parameters.Add(New SqlParameter("@d", SqlDbType.DateTime))
                command.Parameters.Add(New SqlParameter("@e", SqlDbType.SmallInt))
                command.Parameters.Add(New SqlParameter("@f", SqlDbType.VarChar, 50))
                command.Parameters.Add(New SqlParameter("@g", SqlDbType.VarChar, 50))
                command.Parameters.Add(New SqlParameter("@h", SqlDbType.VarChar, 50))

                ' 引数に値を設定
                command.Parameters("@a").Value = senkyoId
                command.Parameters("@b").Value = no
                command.Parameters("@c").Value = Convert.ToDateTime(loginTime)
                command.Parameters("@d").Value = _投票日時
                command.Parameters("@e").Value = internetFlag
                command.Parameters("@f").Value = String.Empty
                command.Parameters("@g").Value = String.Empty
                command.Parameters("@h").Value = String.Empty
                commands.Add(command)
            End Using

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)
            Dim command As New OleDbCommand
            command.CommandText = sql
            ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
            command.Parameters.Clear()
            command.Parameters.Add(New OleDbParameter("@a", OleDbType.Char, 20))
            command.Parameters.Add(New OleDbParameter("@b", OleDbType.Char, 20))
            command.Parameters.Add(New OleDbParameter("@c", OleDbType.Date))
            command.Parameters.Add(New OleDbParameter("@d", OleDbType.Date))
            command.Parameters.Add(New OleDbParameter("@e", OleDbType.SmallInt))
            command.Parameters.Add(New OleDbParameter("@f", OleDbType.VarChar, 50))
            command.Parameters.Add(New OleDbParameter("@g", OleDbType.VarChar, 50))
            command.Parameters.Add(New OleDbParameter("@h", OleDbType.VarChar, 50))

            ' 引数に値を設定
            command.Parameters("@a").Value = senkyoId
            command.Parameters("@b").Value = no
            command.Parameters("@c").Value = Convert.ToDateTime(loginTime)
            command.Parameters("@d").Value = _投票日時
            command.Parameters("@e").Value = internetFlag
            command.Parameters("@f").Value = String.Empty
            command.Parameters("@g").Value = String.Empty
            command.Parameters("@h").Value = String.Empty
            commands.Add(command)

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

    Public Function UpdateシステムM投票制御フラグ(ByVal val As String) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "UPDATE システムM SET 値=@a WHERE 項目名='投票制御フラグ'"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)
            Using command As New SqlCommand
                command.CommandText = sql
                ' 引数設定
                command.Parameters.Clear()
                command.Parameters.Add(New SqlParameter("@a", SqlDbType.NVarChar, 50))

                ' 引数に値を設定
                command.Parameters("@a").Value = val
                commands.Add(command)
            End Using

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)
            Dim command As New OleDbCommand
            command.CommandText = sql
            ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
            command.Parameters.Clear()
            command.Parameters.Add(New OleDbParameter("@a", OleDbType.VarWChar, 50))

            ' 引数に値を設定
            command.Parameters("@a").Value = val
            commands.Add(command)

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

#End Region

#Region "DeleteInsertメソッド"
    Public Function DeleteInsertシステムM(ByVal ds As DataSet1) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "INSERT INTO システムM VALUES(@a, @b, @c, @d)"
        Dim sqldel As String = "DELETE FROM システムM WHERE 項目名 <> '" + MyConstant.投票制御フラグ項目名 + "'"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            Using command As New SqlCommand
                command.CommandText = sqldel
                commands.Add(command)
            End Using

            ' 全インポート
            For Each row As DataSet1.システムMRow In ds.システムM
                Using command As New SqlCommand
                    command.CommandText = sql
                    ' 引数設定
                    command.Parameters.Clear()
                    command.Parameters.Add(New SqlParameter("@a", SqlDbType.NChar, 30))
                    command.Parameters.Add(New SqlParameter("@b", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@c", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@d", SqlDbType.Int))

                    ' 引数に値を設定
                    command.Parameters("@a").Value = row.項目名
                    command.Parameters("@b").Value = row.値
                    command.Parameters("@c").Value = row.備考
                    command.Parameters("@d").Value = row.表示順
                    commands.Add(command)
                End Using
            Next

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            Dim command1 As New OleDbCommand
            command1.CommandText = sqldel
            commands.Add(command1)

            ' 全インポート
            For Each row As DataSet1.システムMRow In ds.システムM
                Dim command As New OleDbCommand
                command.CommandText = sql
                ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
                command.Parameters.Clear()
                command.Parameters.Add(New OleDbParameter("@a", OleDbType.WChar, 30))
                command.Parameters.Add(New OleDbParameter("@b", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@c", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@d", OleDbType.Integer))
                
                ' 引数に値を設定
                command.Parameters("@a").Value = row.項目名
                command.Parameters("@b").Value = row.値
                command.Parameters("@c").Value = row.備考
                command.Parameters("@d").Value = row.表示順
                commands.Add(command)
            Next

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

    Public Function DeleteInsert選挙M(ByVal ds As DataSet1) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "INSERT INTO 選挙M VALUES(@a, @b, @c, @d, @e, @f, @g, @h, @i)"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            Using command As New SqlCommand
                command.CommandText = "DELETE FROM 選挙M"
                commands.Add(command)
            End Using

            ' 全インポート
            For Each row As DataSet1.選挙MRow In ds.選挙M
                Using command As New SqlCommand
                    command.CommandText = sql
                    ' 引数設定
                    command.Parameters.Clear()
                    command.Parameters.Add(New SqlParameter("@a", SqlDbType.Char, 20))
                    command.Parameters.Add(New SqlParameter("@b", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@c", SqlDbType.SmallInt))
                    command.Parameters.Add(New SqlParameter("@d", SqlDbType.Int))
                    command.Parameters.Add(New SqlParameter("@e", SqlDbType.Int))
                    command.Parameters.Add(New SqlParameter("@f", SqlDbType.Int))
                    command.Parameters.Add(New SqlParameter("@g", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@h", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@i", SqlDbType.VarChar, 50))

                    ' 引数に値を設定
                    command.Parameters("@a").Value = row.選挙ID.TrimEnd
                    command.Parameters("@b").Value = row.選挙名称
                    command.Parameters("@c").Value = row.表示フラグ
                    command.Parameters("@d").Value = row.表示順
                    command.Parameters("@e").Value = row.一覧表示パターン
                    command.Parameters("@f").Value = row.単票表示パターン
                    command.Parameters("@g").Value = row.予備１
                    command.Parameters("@h").Value = row.予備２
                    command.Parameters("@i").Value = row.予備３
                    commands.Add(command)
                End Using
            Next

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            Dim command1 As New OleDbCommand
            command1.CommandText = "DELETE FROM 選挙M"
            commands.Add(command1)

            ' 全インポート
            For Each row As DataSet1.選挙MRow In ds.選挙M
                Dim command As New OleDbCommand
                command.CommandText = sql
                ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
                command.Parameters.Clear()
                command.Parameters.Add(New OleDbParameter("@a", OleDbType.Char, 20))
                command.Parameters.Add(New OleDbParameter("@b", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@c", OleDbType.SmallInt))
                command.Parameters.Add(New OleDbParameter("@d", OleDbType.Integer))
                command.Parameters.Add(New OleDbParameter("@e", OleDbType.Integer))
                command.Parameters.Add(New OleDbParameter("@f", OleDbType.Integer))
                command.Parameters.Add(New OleDbParameter("@g", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@h", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@i", OleDbType.VarChar, 50))

                ' 引数に値を設定
                command.Parameters("@a").Value = row.選挙ID.TrimEnd
                command.Parameters("@b").Value = row.選挙名称
                command.Parameters("@c").Value = row.表示フラグ
                command.Parameters("@d").Value = row.表示順
                command.Parameters("@e").Value = row.一覧表示パターン
                command.Parameters("@f").Value = row.単票表示パターン
                command.Parameters("@g").Value = row.予備１
                command.Parameters("@h").Value = row.予備２
                command.Parameters("@i").Value = row.予備３
                commands.Add(command)
            Next

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

    Public Function DeleteInsert政党M(ByVal ds As DataSet1) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "INSERT INTO 政党M VALUES(@a, @b, @c, @d, @e, @f, @g, @h, @i)"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            Using command As New SqlCommand
                command.CommandText = "DELETE FROM 政党M"
                commands.Add(command)
            End Using

            ' 全インポート
            For Each row As DataSet1.政党MRow In ds.政党M
                Using command As New SqlCommand
                    command.CommandText = sql
                    ' 引数設定
                    command.Parameters.Clear()
                    command.Parameters.Add(New SqlParameter("@a", SqlDbType.Char, 20))
                    command.Parameters.Add(New SqlParameter("@b", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@c", SqlDbType.NVarChar, 20))
                    command.Parameters.Add(New SqlParameter("@d", SqlDbType.SmallInt))
                    command.Parameters.Add(New SqlParameter("@e", SqlDbType.Int))
                    command.Parameters.Add(New SqlParameter("@f", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@g", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@h", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@i", SqlDbType.VarChar, 50))

                    ' 引数に値を設定
                    command.Parameters("@a").Value = row.政党ID.TrimEnd
                    command.Parameters("@b").Value = row.政党名称
                    command.Parameters("@c").Value = row.政党名略称
                    command.Parameters("@d").Value = row.表示フラグ
                    command.Parameters("@e").Value = row.表示順
                    command.Parameters("@f").Value = row.画像ファイル名
                    command.Parameters("@g").Value = row.予備１
                    command.Parameters("@h").Value = row.予備２
                    command.Parameters("@i").Value = row.予備３
                    commands.Add(command)
                End Using
            Next

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            Dim command1 As New OleDbCommand
            command1.CommandText = "DELETE FROM 政党M"
            commands.Add(command1)

            ' 全インポート
            For Each row As DataSet1.政党MRow In ds.政党M
                Dim command As New OleDbCommand
                command.CommandText = sql
                ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
                command.Parameters.Clear()
                command.Parameters.Add(New OleDbParameter("@a", OleDbType.Char, 20))
                command.Parameters.Add(New OleDbParameter("@b", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@c", OleDbType.VarWChar, 20))
                command.Parameters.Add(New OleDbParameter("@d", OleDbType.SmallInt))
                command.Parameters.Add(New OleDbParameter("@e", OleDbType.Integer))
                command.Parameters.Add(New OleDbParameter("@f", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@g", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@h", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@i", OleDbType.VarChar, 50))

                ' 引数に値を設定
                command.Parameters("@a").Value = row.政党ID.TrimEnd
                command.Parameters("@b").Value = row.政党名称
                command.Parameters("@c").Value = row.政党名略称
                command.Parameters("@d").Value = row.表示フラグ
                command.Parameters("@e").Value = row.表示順
                command.Parameters("@f").Value = row.画像ファイル名
                command.Parameters("@g").Value = row.予備１
                command.Parameters("@h").Value = row.予備２
                command.Parameters("@i").Value = row.予備３
                commands.Add(command)
            Next

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

    Public Function DeleteInsert候補者M(ByVal ds As DataSet1) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "INSERT INTO 候補者M VALUES(@a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k, @l, @m, @n, @o)"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            Using command As New SqlCommand
                command.CommandText = "DELETE FROM 候補者M"
                commands.Add(command)
            End Using

            ' 全インポート
            For Each row As DataSet1.候補者MRow In ds.候補者M
                Using command As New SqlCommand
                    command.CommandText = sql
                    ' 引数設定
                    command.Parameters.Clear()
                    command.Parameters.Add(New SqlParameter("@a", SqlDbType.Char, 20))
                    command.Parameters.Add(New SqlParameter("@b", SqlDbType.Char, 30))
                    command.Parameters.Add(New SqlParameter("@c", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@d", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@e", SqlDbType.Char, 20))
                    command.Parameters.Add(New SqlParameter("@f", SqlDbType.SmallInt))
                    command.Parameters.Add(New SqlParameter("@g", SqlDbType.Int))
                    command.Parameters.Add(New SqlParameter("@h", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@i", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@j", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@k", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@l", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@m", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@n", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@o", SqlDbType.VarChar, 50))

                    ' 引数に値を設定
                    command.Parameters("@a").Value = row.選挙ID.TrimEnd
                    command.Parameters("@b").Value = row.候補者ID.TrimEnd
                    command.Parameters("@c").Value = row.候補者名称
                    command.Parameters("@d").Value = row.候補者名称かな
                    command.Parameters("@e").Value = row.政党ID.TrimEnd
                    command.Parameters("@f").Value = row.表示フラグ
                    command.Parameters("@g").Value = row.表示順
                    command.Parameters("@h").Value = row.画像ファイル名
                    command.Parameters("@i").Value = row.画像ファイル名２
                    command.Parameters("@j").Value = row.画像ファイル名３
                    command.Parameters("@k").Value = row.候補者名称２
                    command.Parameters("@l").Value = row.候補者名称３
                    command.Parameters("@m").Value = row.予備１
                    command.Parameters("@n").Value = row.予備２
                    command.Parameters("@o").Value = row.予備３
                    commands.Add(command)
                End Using
            Next

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            Dim command1 As New OleDbCommand
            command1.CommandText = "DELETE FROM 候補者M"
            commands.Add(command1)

            ' 全インポート
            For Each row As DataSet1.候補者MRow In ds.候補者M
                Dim command As New OleDbCommand
                command.CommandText = sql
                ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
                command.Parameters.Clear()
                command.Parameters.Add(New OleDbParameter("@a", OleDbType.Char, 20))
                command.Parameters.Add(New OleDbParameter("@b", OleDbType.Char, 30))
                command.Parameters.Add(New OleDbParameter("@c", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@d", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@e", OleDbType.Char, 20))
                command.Parameters.Add(New OleDbParameter("@f", OleDbType.SmallInt))
                command.Parameters.Add(New OleDbParameter("@g", OleDbType.Integer))
                command.Parameters.Add(New OleDbParameter("@h", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@i", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@j", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@k", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@l", OleDbType.VarWChar, 50))
                command.Parameters.Add(New OleDbParameter("@m", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@n", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@o", OleDbType.VarChar, 50))

                ' 引数に値を設定
                command.Parameters("@a").Value = row.選挙ID.TrimEnd
                command.Parameters("@b").Value = row.候補者ID.TrimEnd
                command.Parameters("@c").Value = row.候補者名称
                command.Parameters("@d").Value = row.候補者名称かな
                command.Parameters("@e").Value = row.政党ID.TrimEnd
                command.Parameters("@f").Value = row.表示フラグ
                command.Parameters("@g").Value = row.表示順
                command.Parameters("@h").Value = row.画像ファイル名
                command.Parameters("@i").Value = row.画像ファイル名２
                command.Parameters("@j").Value = row.画像ファイル名３
                command.Parameters("@k").Value = row.候補者名称２
                command.Parameters("@l").Value = row.候補者名称３
                command.Parameters("@m").Value = row.予備１
                command.Parameters("@n").Value = row.予備２
                command.Parameters("@o").Value = row.予備３
                commands.Add(command)
            Next

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

    Public Function DeleteInsert会員M(ByVal ds As DataSet1) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "INSERT INTO 会員M VALUES(@a, @b, @c, @d, @e, @f)"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            Using command As New SqlCommand
                command.CommandText = "DELETE FROM 会員M"
                commands.Add(command)
            End Using

            ' 全インポート
            For Each row As DataSet1.会員MRow In ds.会員M
                Using command As New SqlCommand
                    command.CommandText = sql
                    ' 引数設定
                    command.Parameters.Clear()
                    command.Parameters.Add(New SqlParameter("@a", SqlDbType.Char, 20))
                    command.Parameters.Add(New SqlParameter("@b", SqlDbType.VarChar, 30))
                    command.Parameters.Add(New SqlParameter("@c", SqlDbType.Int))
                    command.Parameters.Add(New SqlParameter("@d", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@e", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@f", SqlDbType.VarChar, 50))

                    ' 引数に値を設定
                    command.Parameters("@a").Value = row.ユーザID.TrimEnd
                    command.Parameters("@b").Value = row.パスワード
                    command.Parameters("@c").Value = row.投票数
                    command.Parameters("@d").Value = row.予備１
                    command.Parameters("@e").Value = row.予備２
                    command.Parameters("@f").Value = row.予備３
                    commands.Add(command)
                End Using
            Next

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            Dim command1 As New OleDbCommand
            command1.CommandText = "DELETE FROM 会員M"
            commands.Add(command1)

            ' 全インポート
            For Each row As DataSet1.会員MRow In ds.会員M
                Dim command As New OleDbCommand
                command.CommandText = sql
                ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
                command.Parameters.Clear()
                command.Parameters.Add(New OleDbParameter("@a", OleDbType.Char, 20))
                command.Parameters.Add(New OleDbParameter("@b", OleDbType.VarChar, 30))
                command.Parameters.Add(New OleDbParameter("@c", OleDbType.Integer))
                command.Parameters.Add(New OleDbParameter("@d", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@e", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@f", OleDbType.VarChar, 50))

                ' 引数に値を設定
                command.Parameters("@a").Value = row.ユーザID.TrimEnd
                command.Parameters("@b").Value = row.パスワード
                command.Parameters("@c").Value = row.投票数
                command.Parameters("@d").Value = row.予備１
                command.Parameters("@e").Value = row.予備２
                command.Parameters("@f").Value = row.予備３
                commands.Add(command)
            Next

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

    Public Function DeleteInsert投票所M(ByVal ds As DataSet1) As Integer
        Dim cnt As Integer = 0

        Dim sql As String = "INSERT INTO 投票所M VALUES(@a, @b, @c, @d, @e)"

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            Using command As New SqlCommand
                command.CommandText = "DELETE FROM 投票所M"
                commands.Add(command)
            End Using

            ' 全インポート
            For Each row As DataSet1.投票所MRow In ds.投票所M
                Using command As New SqlCommand
                    command.CommandText = sql
                    ' 引数設定
                    command.Parameters.Clear()
                    command.Parameters.Add(New SqlParameter("@a", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@b", SqlDbType.NVarChar, 50))
                    command.Parameters.Add(New SqlParameter("@c", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@d", SqlDbType.VarChar, 50))
                    command.Parameters.Add(New SqlParameter("@e", SqlDbType.VarChar, 50))

                    ' 引数に値を設定
                    command.Parameters("@a").Value = row.投票所コード.TrimEnd
                    command.Parameters("@b").Value = row.投票所名
                    command.Parameters("@c").Value = row.予備１
                    command.Parameters("@d").Value = row.予備２
                    command.Parameters("@e").Value = row.予備３
                    commands.Add(command)
                End Using
            Next

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            Dim command1 As New OleDbCommand
            command1.CommandText = "DELETE FROM 投票所M"
            commands.Add(command1)

            ' 全インポート
            For Each row As DataSet1.投票所MRow In ds.投票所M
                Dim command As New OleDbCommand
                command.CommandText = sql
                ' 引数設定(！OleDbの場合はパラメータの出現順番が正値でないと更新されないことに注意)
                command.Parameters.Clear()
                command.Parameters.Add(New OleDbParameter("@a", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@b", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@c", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@d", OleDbType.VarChar, 50))
                command.Parameters.Add(New OleDbParameter("@e", OleDbType.VarChar, 50))

                ' 引数に値を設定
                command.Parameters("@a").Value = row.投票所コード.TrimEnd
                command.Parameters("@b").Value = row.投票所名
                command.Parameters("@c").Value = row.予備１
                command.Parameters("@d").Value = row.予備２
                command.Parameters("@e").Value = row.予備３
                commands.Add(command)
            Next

            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function


#End Region

#Region "Deleteメソッド"
    Public Function Delete投票券T投票T() As Integer
        Dim cnt As Integer = 0

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            commands.Add(New SqlCommand("DELETE FROM 投票券T"))
            commands.Add(New SqlCommand("DELETE FROM 投票T"))

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            commands.Add(New OleDbCommand("DELETE FROM 投票券T"))
            commands.Add(New OleDbCommand("DELETE FROM 投票T"))
            
            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function

    Public Function DeleteAll() As Integer
        Dim cnt As Integer = 0

        If IsSqlServer() Then
            ' SQLの場合
            Dim commands As New List(Of SqlCommand)

            ' 全削除
            commands.Add(New SqlCommand("DELETE FROM 選挙M"))
            commands.Add(New SqlCommand("DELETE FROM 政党M"))
            commands.Add(New SqlCommand("DELETE FROM 候補者M"))
            commands.Add(New SqlCommand("DELETE FROM 会員M"))
            commands.Add(New SqlCommand("DELETE FROM 投票券T"))
            commands.Add(New SqlCommand("DELETE FROM 投票T"))

            cnt = ExecuteNonQuery共通forSQL(commands)
        Else
            ' OLEDBの場合
            Dim commands As New List(Of OleDbCommand)

            ' 全削除
            commands.Add(New OleDbCommand("DELETE FROM 選挙M"))
            commands.Add(New OleDbCommand("DELETE FROM 政党M"))
            commands.Add(New OleDbCommand("DELETE FROM 候補者M"))
            commands.Add(New OleDbCommand("DELETE FROM 会員M"))
            commands.Add(New OleDbCommand("DELETE FROM 投票券T"))
            commands.Add(New OleDbCommand("DELETE FROM 投票T"))
            
            cnt = ExecuteNonQuery共通forOLE(commands)
        End If

        Return cnt
    End Function
#End Region

#Region "Privateメソッド"

    Private Function IsSqlServer() As Boolean
        If ConfigurationManager.ConnectionStrings(CON_CONNECTION).ProviderName = MyConstant.Accessプロバイダ名 Then
            IsSqlServer = False
        Else
            IsSqlServer = True
        End If
    End Function

    Private Sub Fill共通(ByVal dt As DataTable, ByVal sql As String)
        dt.Clear()
        If IsSqlServer() Then
            Using con As New SqlConnection
                con.ConnectionString = ConfigurationManager.ConnectionStrings(CON_CONNECTION).ConnectionString
                con.Open()
                Dim da As New SqlDataAdapter(sql, con)
                da.Fill(dt)
            End Using
        Else
            Using con As New OleDb.OleDbConnection
                con.ConnectionString = ConfigurationManager.ConnectionStrings(CON_CONNECTION).ConnectionString
                con.Open()
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, con)
                da.Fill(dt)
            End Using
        End If
    End Sub

    Private Function Get共通(ByVal sql As String) As Object
        Dim val As Object = Nothing
        If IsSqlServer() Then
            Using con As New SqlConnection
                con.ConnectionString = ConfigurationManager.ConnectionStrings(CON_CONNECTION).ConnectionString
                con.Open()
                Using command As New SqlCommand()
                    command.Connection = con
                    command.CommandText = sql
                    val = command.ExecuteScalar()
                End Using
            End Using
        Else
            Using con As New OleDb.OleDbConnection
                con.ConnectionString = ConfigurationManager.ConnectionStrings(CON_CONNECTION).ConnectionString
                con.Open()
                Using command As New OleDbCommand()
                    command.Connection = con
                    command.CommandText = sql
                    val = command.ExecuteScalar()
                End Using
            End Using
        End If
        If Not IsDBNull(val) Then
            Return val
        Else
            Return Nothing
        End If
    End Function

    Private Function ExecuteNonQuery共通forSQL(ByVal commands As List(Of SqlCommand)) As Integer
        Dim cnt As Integer = 0

        ' トランザクション開始（Usingの場合Close,Disposeは自動で呼び出される）
        Using scope As New TransactionScope
            Using con As New SqlConnection
                con.ConnectionString = ConfigurationManager.ConnectionStrings(CON_CONNECTION).ConnectionString
                con.Open()
                For Each command As SqlCommand In commands
                    Using command
                        command.Connection = con

                        ' データベースの更新を実行
                        cnt += command.ExecuteNonQuery()
                    End Using
                Next
            End Using

            ' トランザクションコミット
            scope.Complete()
        End Using

        Return cnt
    End Function

    Private Function ExecuteNonQuery共通forOLE(ByVal commands As List(Of OleDbCommand)) As Integer
        Dim cnt As Integer = 0

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = ConfigurationManager.ConnectionStrings(CON_CONNECTION).ConnectionString
        con.Open()
        ' トランザクション開始
        Dim trans As OleDb.OleDbTransaction = con.BeginTransaction()
        Try
            For Each command As OleDbCommand In commands
                command.Connection = con
                command.Transaction = trans

                ' データベースの更新を実行
                cnt += command.ExecuteNonQuery()
            Next

            ' トランザクションコミット
            trans.Commit()
        Catch ex As Exception
            ' トランザクションロールバック
            trans.Rollback()
            Throw ex
        Finally
            con.Close()
        End Try

        Return cnt
    End Function
#End Region

End Class
