OleDbDataAdapterのUpdate()メソッドを使用してDataTableをデータベースに反映するサンプル（SQLコマンド手動作成）
図 Update()メソッドでデータベースに反映するサンプル（SQLコマンド手動作成）
このサンプルは、DataTable上で変更されたレコードをOleDbDataAdapterのUpdate()メソッドを使用してデータベースに反映します。Update()メソッドが使用するSQLのInsert, Update, Deleteステートメントは、手動で作成しています。
このサンプルでは、次のような処理を行っています。
・ AccessのNwind.mdbデータベースに得意先テーブル(tblCustomersUpdate)作成
・ 得意先テーブルにテストデータ作成
・ 得意先テーブルをDataTableに取り込む
・ DataTableのレコード編集（レコードの追加、編集、削除を含む）
・ Update()メソッドでDataTableを得意先テーブルに反映
OleDbDataAdapterのUpdate()メソッドでDataTableを得意先テーブルに反映するときに使用するSQL(Insert/Update/Delte)は、プログラムで用意したものを使用します。
このサンプルでは、以下のノウハウを習得することができます。
▲ Accessのデータベースからテーブルを削除する方法 (Drop Table)
▲ Accessのデータベースにテーブルを作成する方法 (Create Table)
▲ テーブルのフィールド（カラム）にデフォルト値を設定する方法 (Default 0)
▲ DataTableを生成する方法
▲ DataTableのDataColumnにデフォルト値を設定する方法 (DefaultValue = 0)
▲ DataTableのレコードを処理（追加、編集、削除、抽出）する方法
▲ OleDbParametersコレクションのAdd()メソッドの使い方
▲ OleDbParameterのSourceVersionプロパティの使い方
▲ DataRowVersionのCurrent、Originalの使い方
サンプルのPage_Load()イベントでは、CreateTable()、CreateDataTable()、LoadData()、DisplayData()、ModifyData()、UpdateData()などのSub/Functionを呼び出して得意先テーブルのレコードをDataTable上で編集して元の得意先テーブルに反映しています。
10: Sub Page_Load()
11: Dim strSQL As String = "Select * From
tblCustomersUpdate"
12: mcon = New OleDbConnection( _
13:
ConfigurationSettings.AppSettings("conStringAccNw"))
14: mda = New OleDbDataAdapter(strSQL,
mcon)
15: mcon.Open()
16: CreateTable()
17: mdt = CreateDataTable()
18: LoadData()
19: mda.Fill(mdt)
20: DisplayData("得意先テーブルの内容(変更前)")
21: ModifyData()
22: DisplayData("得意先テーブルの内容(変更後)")
23: UpdateData()
24: mdt.Clear()
25: mda.Fill(mdt)
26: DisplayData("得意先テーブルの内容（再ロード後)")
27: mcon.Close()
28: End Sub
Sub CreateTable()の処理
CreateTable()では、SQLのCreate TableステートメントでAccessのNwind.mdbデータベースに得意先テーブルを作成します。行32-37では、SQLのDrop TableステートメントでAccessのNwind.mdbデータベースから得意先テーブルを削除しています。
Drop Table
tblCustomersUpdate
行38-48では、SQLのCreate Tableステートメントを実行してAccessのNwind.mdbデータベースに得意先テーブルを作成しています。
Create Table
tblCustomersUpdate
(CustomerID int Not Null
Primary Key,
CompanyName
nvarchar(40) Not Null,
ContactName
nvarchar(30) Not Null,
Phone nvarchar(24) Not Null,
ConcurrencyID int Default 0)
得意先テーブルのConcurrencyIDフィールドは、レコードの競合（2重更新）を回避するために使用します。このフィールドには、レコードの更新回数が格納されます。SQLのInsertステートメントでConcurrencyIDを省略するとデフォルト値として0が格納されます。
30: Sub CreateTable()
31: Dim cmd As OleDbCommand =
mcon.CreateCommand
32: cmd.CommandText = "Drop Table
tblCustomersUpdate"
33: Try
34: cmd.ExecuteNonQuery()
35: Catch
37: End Try
38: Dim sbSQL As New StringBuilder()
39: With sbSQL
40: .Append("Create Table
tblCustomersUpdate " & vbCrLf)
41: .Append(" (CustomerID int
Not Null Primary Key, " & vbCrLf)
42: .Append(" CompanyName nvarchar(40) Not Null, " &
vbCrLf)
43: .Append(" ContactName nvarchar(30) Not Null, " &
vbCrLf)
44: .Append(" Phone nvarchar(24) Not Null, " & vbCrLf)
45: .Append(" ConcurrencyID int Default 0) ")
46: End With
47: cmd.CommandText = sbSQL.ToString
48: cmd.ExecuteNonQuery()
51: End Sub
Function CreateDataTable()の処理
CreateDataTable()では、得意先テーブルのDataTableを生成して戻り値として返します。ConcurrencyIDのDataColumnのDefaultValueプロパティには0を設定しています。DataTableのNewRow()/Ro
53: Function CreateDataTable() As DataTable
54: Dim dt As New DataTable("Customers")
55: Dim dc As DataColumn
56: With dt.Columns
57:
.Add("CustomerID", GetType(Integer))
58:
.Add("CompanyName", GetType(String))
59:
.Add("ContactName", GetType(String))
60:
.Add("Phone", GetType(String))
61: dc = .Add("ConcurrencyID", GetType(Integer))
62: dc.DefaultValue = 0
63: End With
64: dt.PrimaryKey = New DataColumn()
{dt.Columns("CustomerID")}
65: Return dt
66: End Function
Sub LoadData()の処理
LoadData()では、AccessのNwind.mdbデータベースの得意先テーブルに新規レコードを2件追加します。行72-76では、SQLのInsertステートメントを実行して１件目のレコードを追加しています。
Insert Into
tblCustomersUpdate
(CustomerID, CompanyName,
ContactName, Phone)
Values(1,'フレンドリーソフト１', '葛西 秋雄', '0480-11-1111')
Insertステートメントでは、ConcurrencyIDを省略していますがデフォルト値の0が格納されます。行78-82では、2件目のレコードを追加しています。
68: Sub LoadData()
69: Dim strSQL As String
70: Dim cmd As OleDbCommand =
mcon.CreateCommand()
71:
72: strSQL = "Insert Into
tblCustomersUpdate " & _
73: " (CustomerID,
CompanyName, ContactName, Phone) " & _
74: " Values(1,'フレンドリーソフト１', '葛西 秋雄', '0480-11-1111') "
75: cmd.CommandText = strSQL
76: cmd.ExecuteNonQuery()
77:
78: strSQL = "Insert Into
tblCustomersUpdate " & _
79: " (CustomerID,
CompanyName, ContactName, Phone) " & _
80: " Values(2,'フレンドリーソフト２', '葛西 千夏', '0480-22-2222') "
81: cmd.CommandText = strSQL
82: cmd.ExecuteNonQuery()
83: End Sub
Sub DisplayData()の処理
DisplayData()では、DataTableに格納されている得意先テーブルを得意先IDの昇順に並べ替えて表示します。
85: Sub DisplayData(strTitle As String)
86: Dim dr As DataRow
87: Dim dc As DataColumn
88:
Response.Write("<h4>" & strTitle & "</h4>")
89: For Each dr In mdt.Select("",
"CustomerID")
90: For Each dc In
mdt.Columns
91:
Response.Write(String.Format("<b>{0}</b>: {1}<br>", _
92:
dc.ColumnName, dr(dc)))
93: Next
94:
Response.Write("<hr>")
95: Next
96: End Sub
Sub ModifyData()の処理
ModifyData()では、DataTableに登録されているレコードを削除、編集したり、新規のレコードを追加します。DataTableのNewRow()/Ro
98: Sub ModifyData()
99: Dim dr As DataRow
100:
102: dr = mdt.Ro
103:
dr.Delete()
104:
106: dr = mdt.Ro
107:
dr("Phone") = "0480-99-9999"
108:
109: dr =
mdt.NewRow
110:
dr("CustomerID") = 3
111:
dr("CompanyName") = "フレンドリーソフト３"
112:
dr("ContactName") = "葛西 千春"
113:
dr("Phone") = "0480-33-3333"114:
115: mdt.Ro
116: End Sub
Sub UpdateData()の処理
UpdateData()では、DataTable上の変更されたレコードをNwind.mdbデータベースの得意先テーブルに反映しています。このサンプルでは、DataTableをデータベースに反映させるときOleDbDataAdapterのUpdate()メソッドを使用しています。Update()メソッドの引数には、DataTableを指定します。行119では、CreateInsertCommand()関数を呼び出してOleDbDataAdapterのInsertCommandプロパティに戻り値を設定しています。CreateInsertCommand()は、InsertのOleDbCommandを生成して返します。行120では、CreateUpdateCommand()関数を呼び出してOleDbDataAdapterのUpdateCommandプロパティに戻り値を設定しています。CreateUpdateCommand()は、UpdateのOleDbCommandを生成して返します。行121では、CreateDeleteCommand()関数を呼び出して、OleDbDataAdapterのDeleteCommandプロパティに戻り値を設定しています。CreateDeleteCommand()は、DeleteのOleDbCommandを生成して返します。
行122では、OleDbDataAdapterのUpdate()メソッドでDataTable上の変更されたレコードを得意先テーブルに反映します。DataTableに新規レコードが追加されたときは、OleDbDataAdapterのInsertCommnadプロパティに格納されているInsertステートメント使用して得意先テーブルにレコードを追加します。DataTableのレコードが変更されているときは、UpdateCommandプロパティに格納されているUpdateステートメントを使用して得意先テーブルのレコードを更新します。DataTableのレコードが削除されているときは、DeleteCommandプロパティに格納されているDeleteステートメントを使用して得意先テーブルからレコードを削除します。
118: Sub UpdateData()
119:
mda.InsertCommand = CreateInsertCommand()
120:
mda.UpdateCommand = CreateUpdateCommand()
121:
mda.DeleteCommand = CreateDeleteCommand()
122:
mda.Update(mdt)
123: End Sub
Function CreateInsertCommand()の処理
CreateInsertCommand()では、DataTableに追加されたレコードを得意先テーブルに追加するSQLを生成します。この関数からは、戻り値としてInsertのOleDbCommandを返します。行127-131のWith…End Withでは、StringBuilderのAppend()メソッドでSQLのInsertステートメントを生成しています。
Insert Into
tblCustomersUpdate
(CustomerID, CompanyName,
ContactName, Phone)
Values(?, ?, ?, ?)
Insertステートメントでは、ConcurrencyIDを省略していますがデフォルト値の0が格納されます。行136-141のWith…End Withでは、OleDbParameterCollectionのAdd()メソッドでパラメータを追加しています。Add()メソッドの引数には、parameterName、OleDbType、size、sourceColumnを指定しています。sourceColumnには、パラメータ値として使用するDataColumnを指定します。たとえば、sourceColumnにCustomerIDを指定したときは、パラメータ変数@CustomerIDにDataTableのカレントDataRowのCustomerIDが設定されます。
OleDbParameterCollectionのAdd()メソッドにsourceColumnを指定することにより、OleDbDataAdapterのUpdate()メソッドでInsertステートメントを実行するときパラメータ値を自動的に設定してくれます。
125: Function
CreateInsertCommand() As OleDbCommand
126: Dim sbSQL
As New StringBuilder()
127: With sbSQL
128:
.Append("Insert Into tblCustomersUpdate " & vbCrLf)
129:
.Append(" (CustomerID, CompanyName, ContactName, Phone) " & vbCrLf)
130:
.Append(" Values(?, ?, ?, ?)")
131: End With
134: Dim cmd As
New OleDbCommand(sbSQL.ToString, mcon)
135: Dim pc As
OleDbParameterCollection = cmd.Parameters
136: With pc
137:
.Add("@CustomerID", OleDbType.Integer, 0, "CustomerID")
138:
.Add("@CompanyName", OleDbType.VarWChar, 40, "CompanyName")
139:
.Add("@ContactName", OleDbType.VarWChar, 30, "ContactName")
140:
.Add("@Phone", OleDbType.VarWChar, 24, "Phone")
141: End With
142: Return cmd
143: End Function
Function CreateUpdateCommand()の処理
CreateUpdateCommand()では、DataTableの変更されたレコードを得意先テーブルに反映するSQLを生成します。この関数からは、戻り値としてUpdateのOleDbCommandを返します。行147-155のWith…End Withでは、StringBuilderのAppend()メソッドでSQLのUpdateステートメントを生成しています。
Update tblCustomersUpdate
Set CompanyName = ?,
ContactName = ?,
Phone = ?,
ConcurrencyID =
ConcurrencyID + 1
Where CustomerID = ? And
ConcurrencyID = ?
Updateステートメントでは、レコードの2重更新を回避するためにConcurrencyIDを使用しています。ConcurrencyIDは、レコードを更新する度に+1加算します。Where句には、得意先テーブルの主キー(CustomerID)とConcurrencyIDを指定します。ConcurrencyIDを使用しないときは、得意先テーブルのすべてのフィールドを指定する必要があります。
行161-170のWith…End Withでは、OleDbParameterCollectionのAdd()メソッドでUpdateステートメントのパラメータを追加しています。Add()メソッドの引数には、parameterName、OleDbType、size、sourceColumnを指定しています。Set句のパラメータ@NewCompanyName、@NewContactName、@NewPhoneには、SourceVersionプロパティを設定していませんが、省略するとDataRowVersion.Currentがデフォルトとして採用されます。つまり、これらのパラメータには、DataTableのカレントDataRowの更新後の値が設定されます。
Where句のパラメータ@OrgCustomerID、@OrgConcurrencyIDには、SourceVersionプロパティにDataRowVersion.Originalを設定していますので、DataTableのカレントDataRowの更新前の値が設定されます。
OleDbDataAdapterのUpdate()メソッドは、DataTable上の変更されたレコードをデータベースに反映するとき、Updateステートメントのパラメータにレコードの更新前後の値を設定して実行します。DataTable上で変更されたレコードが他のクライアントから変更（削除も含む）されているときは、更新されません。
145: Function
CreateUpdateCommand() As OleDbCommand
146: Dim sbSQL
As New StringBuilder()
147: With sbSQL
148:
.Append("Update tblCustomersUpdate " & vbCrLf)
149:
.Append(" Set CompanyName = ?, " & vbCrLf)
150:
.Append(" ContactName = ?, " &
vbCrLf)
151:
.Append(" Phone = ?, " &
vbCrLf)
152:
.Append(" ConcurrencyID =
ConcurrencyID + 1 " & vbCrLf)
153:
.Append(" Where CustomerID = ? And " & vbCrLf)
154:
.Append(" ConcurrencyID = ? ")
155: End With
158: Dim cmd As
New OleDbCommand(sbSQL.ToString, mcon)
159: Dim pc As
OleDbParameterCollection = cmd.Parameters
160: Dim param
As OleDbParameter
161: With pc
162: .Add("@NewCompanyName", OleDbType.VarWChar, 40,
"CompanyName")
163:
.Add("@NewContactName", OleDbType.VarWChar, 30, "ContactName")
164:
.Add("@NewPhone", OleDbType.VarWChar, 24, "Phone")
166:
param = .Add("@OrgCustomerID", OleDbType.Integer, 0, "CustomerID")
167:
param.SourceVersion = DataRowVersion.Original
168:
param = .Add("@OrgConcurrencyID", OleDbType.Integer, 0, "ConcurrencyID")
169:
param.SourceVersion = DataRowVersion.Original
170: End With
171: Return cmd
172: End Function
Function CreateDeleteCommand()の処理
CreateDeleteCommand()では、DataTableから削除されたレコードを得意先テーブルから削除するSQLを生成します。この関数からは、戻り値としてDeleteのOleDbCommandを返します。行176-180のWith…End Withでは、StringBuilderのAppend()メソッドでSQLのDeleteステートメントを生成しています。
Delete * From
tblCustomersUpdate
Where CustomerID = ? And
ConcurrencyID = ?
Deleteステートメントでは、レコードの競合を回避するためにConcurrencyIDを使用しています。ConcurrencyIDは、レコードを更新する度に+1加算します。Where句には、得意先テーブルの主キー(CustomerID)とConcurrencyIDを指定します。ConcurrencyIDを使用しないときは、得意先テーブルのすべてのフィールドを指定する必要があります。
行186-191のWith…End Withでは、OleDbParameterCollectionのAdd()メソッドでDeleteステートメントのパラメータを追加しています。Add()メソッドの引数には、parameterName、OleDbType、size、sourceColumnを指定しています。Where句のパラメータ@CustomerID、@ConcurrencyIDには、SourceVersionプロパティにDataRowVersion.Originalを設定していますので、DataTableのカレントDataRowの更新前の値が設定されます。
OleDbDataAdapterのUpdate()メソッドは、DataTableから削除されたレコードをデータベースから削除るとき、Deleteステートメントのパラメータにレコードの更新前の値を設定して実行します。DataTable上から削除されたレコードが他のクライアントから変更（削除も含む）されたときはデータベースから削除されません。
174: Function
CreateDeleteCommand() As OleDbCommand
175: Dim sbSQL
As New StringBuilder()
176: With sbSQL
177:
.Append("Delete * From tblCustomersUpdate " & vbCrLf)
178:
.Append(" Where CustomerID = ? And " & vbCrLf)
179:
.Append(" ConcurrencyID = ? ")
180: End With
183: Dim cmd As
New OleDbCommand(sbSQL.ToString, mcon)
184: Dim pc As
OleDbParameterCollection = cmd.Parameters
185: Dim param
As OleDbParameter
186: With pc
187:
param = .Add("@CustomerID", OleDbType.Integer, 0, "CustomerID")
188:
param.SourceVersion = DataRowVersion.Original
189:
param = .Add("@ConcurrencyID", OleDbType.Integer, 0, "ConcurrencyID")
190:
param.SourceVersion = DataRowVersion.Original
191: End With
192: Return cmd
193: End Function