前提条件
・DBは"MicroSoftSManagimentStudio"を使用
・BookListというテーブルから値を取得する
・データアダプタを使用
(identity) | (nvarchar(50)) | (nvarchar(50)) | (varchar(19)) |
---|---|---|---|
BookID | BookName | Author | ReadingCompletionDate |
1 | プラネタリウムのふたご | いしいしんじ | 2024/02/01 |
2 | medium | 相沢渉呼 | 2024/02/10 |
3 | ハーモニー | 伊藤計劃 | 2024/03/10 |
SELECT
・データセットでDB接続されている(dsDataSet.xsd)
・データセット名:dsDataSet
・取得した値をaspxに表示する(BookIDをViewStateに保持する)
BookList.vb
Dim DBConStr As String = "接続文字列"
Using ta As New dsDataSetTableAdapters.BookListTableAdapter(DBConStr)
Dim dt As New dsDataSet.BookListDataTable
ta.Fill(dt)
If dt.Count <> 0 Then
With dt(0)
.BookID = ViewState(BOOK_ID)
.BookName = lblBookName.Text
.Author = lblAuthor.Text
End With
End If
End Using
INSERT
トランザクションにする
BookList.vb
Using tran As New Transactions.TransactionScope(Transactions.TransactionScopeOption.Required)
Using ta As New dsDataSetTableAdapters.BookListTableAdapter(DBConStr)
Using dt As dsDataSet.BookListDataTable
Dim dr = dt.NewBookListRow
With dr
.BookName = txtBookName.Text
.Author = txtAutor.Text
.ReadingCompletionDate = CStr(DateTime.Now)
End With
dt.AddBookListRow(dr)
If ta.Update(dt) = 1 Then
'登録成功時の処理
Else
'登録失敗時の処理
End If
END Using
END Using
tran.Complete()
END Using
UPDATE
BookList.vb
Using tran As New Transactions.TransactionScope(Transactions.TransactionScopeOption.Required)
Using ta As New dsDataSetTableAdapters.BookListTableAdapter(DBConStr)
Using dt As dsDataSet.BookListDataTable
End If
With dt(0)
.BookName = txtBookName.Text
.Author = txtAutor.Text
End With
If ta.Update(dt) = 1 Then
'更新成功時の処理
Else
'更新失敗時の処理
End If
END Using
END Using
tran.Complete()
END Using
既存の場合はUPDATE、新規の場合はINSERT
FillやGetDataで取得し、結果によって分岐させる
BookList.vb
Using tran As New Transactions.TransactionScope(Transactions.TransactionScopeOption.Required)
Using ta As New dsHR10_46TableAdapters.T_VirtualExtensionGroupTableAdapter(CStr(Session(CommonConst.SESSION_DB_CONSTR)))
Dim dt As New dsHR10_46.T_VirtualExtensionGroupDataTable
If PageDataSet.T_VirtualExtensionGroup.Rows.Count = 0 Then
'新規登録
Dim newRow As dsHR10_46.T_VirtualExtensionGroupRow = dt.NewT_VirtualExtensionGroupRow()
With newRow
.VirtualExtensionGroupName = txtVirtualExtensionGroupName.Text
.VirtualExtensionGroupNo = CInt(txtVirtualExtensionGroupNo.Text)
.ExtensionNumberGroupID = CInt(ddlExtensionNumberGroup.SelectedValue)
.PBXCd = ddlPBXCd.SelectedValue
.DelFlg = False
.EntryID = CInt(Session(CommonConst.SESSION_LOGIN_EMPLOYEE_ID))
.EntryDate = regist_date
.EditID = 0
.EditDate = ""
dt.AddT_VirtualExtensionGroupRow(newRow)
End With
AddMsg("I00001", "登録")
Else
'更新
With PageDataSet.T_VirtualExtensionGroup(0)
.VirtualExtensionGroupName = txtVirtualExtensionGroupName.Text
.VirtualExtensionGroupNo = CInt(txtVirtualExtensionGroupNo.Text)
.ExtensionNumberGroupID = CInt(ddlExtensionNumberGroup.SelectedValue)
.PBXCd = ddlPBXCd.SelectedValue
.EditID = CInt(Session(CommonConst.SESSION_LOGIN_EMPLOYEE_ID))
.EditDate = regist_date
End With
AddMsg("I00001", "更新")
End If
ta.Update(dt)
End Using
tran.Complete()
End Using
Dim dtExtentionSettingSchedule As New dsSE02_08.T_ExtentionSettingScheduleDataTable
Using ta As New dsSE02_08TableAdapters.T_ExtentionSettingScheduleTableAdapter(cn_str)
' データを取得
ta.Fill(dtExtentionSettingSchedule, number)
' データを操作(行の追加や更新)
' 例:既存の行を更新
If dtExtentionSettingSchedule.Rows.Count > 0 Then
Dim row As dsSE02_08.T_ExtentionSettingScheduleRow = dtExtentionSettingSchedule(0)
row.SomeColumn = "新しい値" ' 列の値を変更
Else
' 新しい行を追加する場合
Dim newRow As dsSE02_08.T_ExtentionSettingScheduleRow = dtExtentionSettingSchedule.NewT_ExtentionSettingScheduleRow()
newRow.SomeColumn = "新しい値"
' 他の列の値もセットする必要があります
dtExtentionSettingSchedule.AddT_ExtentionSettingScheduleRow(newRow)
End If
' 変更をデータベースに反映
ta.Update(dtExtentionSettingSchedule)
End Using