Private Function copyDBtest(ByVal CD As String,
ByVal NEWYMD As Integer,
ByVal OLDYMD As Integer) As String
copyDBtest = ""
Using SeisanConnect As New SqlConnection(SqlConnectionStringS)
SeisanConnect.Open()
trSQL = SeisanConnect.BeginTransaction
' 元のデータを取得
strSQL = "SELECT * FROM SE_SEH1TA WHERE CD = @cd AND YMD = @oymd AND HKBN = @hkbn"
Dim dtOriginal As New DataTable()
Using cmd As New SqlCommand(strSQL, SeisanConnect, trSQL)
cmd.Parameters.AddWithValue("@cd", CD)
cmd.Parameters.AddWithValue("@oymd", OLDYMD)
cmd.Parameters.AddWithValue("@hkbn", m_HKBN)
Using adapter As New SqlDataAdapter(cmd)
adapter.Fill(dtOriginal)
End Using
End Using
If dtOriginal.Rows.Count = 0 Then
Return "ERR NODATA"
End If
' 新しいデータを確認
strSQL = "SELECT * FROM SE_SEH1TA WHERE CD = @cd AND YMD = @nymd AND HKBN = @hkbn"
Dim dtNew As New DataTable()
Using cmd As New SqlCommand(strSQL, SeisanConnect, trSQL)
cmd.Parameters.AddWithValue("@cd", CD)
cmd.Parameters.AddWithValue("@nymd", NEWYMD)
cmd.Parameters.AddWithValue("@hkbn", m_HKBN)
Using adapter As New SqlDataAdapter(cmd)
adapter.Fill(dtNew)
End Using
End Using
If dtNew.Rows.Count <> 0 Then
strSQL = "delete FROM SE_SEH1TA WHERE CD = @cd AND YMD = @nymd AND HKBN = @hkbn"
Using deleteCmd As New SqlCommand(strSQL, SeisanConnect, trSQL)
deleteCmd.Parameters.AddWithValue("@cd", CD)
deleteCmd.Parameters.AddWithValue("@nymd", NEWYMD)
deleteCmd.Parameters.AddWithValue("@hkbn", m_HKBN)
deleteCmd.ExecuteNonQuery()
End Using
End If
' 新しい行を作成してデータベースに挿入
Dim newRow As DataRow = dtOriginal.Rows(0).Table.NewRow()
For Each column As DataColumn In dtOriginal.Columns
newRow(column.ColumnName) = dtOriginal.Rows(0)(column.ColumnName)
Next
newRow("cd") = CD
newRow("ymd") = NEWYMD
newRow("hkbn") = m_HKBN
' 列名とパラメータ名の文字列を生成
Dim columnNames As String = String.Empty
Dim parameterNames As String = String.Empty
For Each column As DataColumn In dtOriginal.Columns
If columnNames <> String.Empty Then
columnNames &= ", "
parameterNames &= ", "
End If
columnNames &= column.ColumnName
parameterNames &= "@" & column.ColumnName
Next
strSQL = $"INSERT INTO SE_SEH1TA ({columnNames}) VALUES ({parameterNames})"
Using insertCmd As New SqlCommand(strSQL, SeisanConnect, trSQL)
For Each column As DataColumn In dtOriginal.Columns
insertCmd.Parameters.AddWithValue("@" & column.ColumnName, newRow(column.ColumnName))
Next
insertCmd.ExecuteNonQuery()
End Using
trSQL.Commit()
SeisanConnect.Close()
End Using
End Function