ADO.NET Accessのホームへ戻る

OleDbDataAdapterのUpdate()メソッドでDataTableをデータベースに反映するサンプル（SQLコマンド自動生成）

図 Update()メソッドでデータベースに反映するサンプル(SQLコマンド自動生成)

DataTable上で変更されたレコードをデータベースに反映するには、次の３種類の方法があります。

・ OleDbDataAdapterのUpdate()メソッドでDataTableをデータベースに反映する方法

(Insert/Update/Deleteコマンドを自動作成)

・ OleDbDataAdapterのUpdate()メソッドでDataTableをデータベースに反映する方法

(Insert/Update/Deleteコマンドを手動作成)

・ DataTableを手動でデータベースに反映する方法

このサンプルは、DataTable上で変更されたレコードをOleDbDataAdapterのUpdate()メソッドを使用してデータベースに反映します。Update()メソッドが使用するSQLのInsert, Update, Deleteステートメントは、OleDbCommandBuilderで自動生成しています。Insert、Update、Deleteステートメントを手動で作成する手法については、後述するサンプルで解説しています。

このサンプルでは、次のような処理を行っています。

・ AccessのNwind.mdbデータベースに得意先テーブル(tblCustomersUpdate)作成

・ 得意先テーブルにテストデータ作成

・ 得意先テーブルをDataTableに取り込む

・ DataTableのレコード編集（レコードの追加、編集、削除を含む）

・ Update()メソッドでDataTableを得意先テーブルに反映

OleDbDataAdapterのUpdate()メソッドでDataTableを得意先テーブルに反映するときに使用するSQL(Insert/Update/Delte)は、OleDbCommandBuilderで自動生成したものを使用します。

このサンプルでは、以下のノウハウを習得することができます。

▲ Accessのデータベースからテーブルを削除する方法 (Drop Table)

▲ Accessのデータベースにテーブルを作成する方法 (Create Table)

▲ DataTableを生成する方法

▲ DataTableのレコードを処理（追加、編集、削除、抽出）する方法

▲ OleDbDataAdapterのFill()メソッドで使用するSQLを自動生成する方法

サンプルのPage_Load()イベントでは、CreateTable()、CreateDataTable()、LoadData()、DisplayData()、ModifyData()、UpdateData()などのSub/Functionを呼び出して得意先テーブルのレコードをDataTable上で編集して元の得意先テーブルに反映しています。

11: Sub Page_Load()
12: Dim strSQL As String = "Select * From tblCustomersUpdate"
13: mcon = New OleDbConnection( _
14: ConfigurationSettings.AppSettings("conStringAccNw"))
15: mda = New OleDbDataAdapter(strSQL, mcon)
16: mcon.Open()
17: CreateTable()
18: mdt = CreateDataTable()
19: LoadData()
20: mda.Fill(mdt)
21: DisplayData("得意先テーブルの内容(変更前)")
22: ModifyData()
23: DisplayData("得意先テーブルの内容(変更後)")
24: UpdateData()
25: mdt.Clear()
26: mda.Fill(mdt)
27: DisplayData("得意先テーブルの内容（再ロード後)")
28: DisplayCommand()
29: mcon.Close()
30: End Sub

Sub CreateTable()の処理

CreateTable()では、SQLのCreate TableステートメントでAccessのNwind.mdbデータベースに得意先テーブルを作成します。行34-39では、SQLのDrop TableステートメントでAccessのNwind.mdbデータベースから得意先テーブルを削除しています。

Drop Table tblCustomersUpdate

行41-49では、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)

このサンプルでは、OleDbDataDataAdapterのUpdate()メソッドで使用するSQL(Insert, Update, Delete)をOleDbCommandBuilderで自動生成するため、得意先テーブルにConcurrencyIDフィールドを追加しません。ConcurrencyIDの使い方については、後述するNoteで説明しています。OleDbCommandBuilderで生成されたSQLは、Where句にすべてのフィールドを指定してレコードの競合を回避しています。OleDbCommandBuilderが自動生成したSQLについては、後述するSub UpdateData()で詳しく解説します。

32: Sub CreateTable()
33: Dim cmd As OleDbCommand = mcon.CreateCommand
34: cmd.CommandText = "Drop Table tblCustomersUpdate"
35: Try
36: cmd.ExecuteNonQuery()
37: Catch
39: End Try
40: Dim sbSQL As New StringBuilder()
41: With sbSQL
42: .Append("Create Table tblCustomersUpdate " & vbCrLf)
43: .Append(" (CustomerID int Not Null Primary Key, " & vbCrLf)
44: .Append(" CompanyName nvarchar(40) Not Null, " & vbCrLf)
45: .Append(" ContactName nvarchar(30) Not Null, " & vbCrLf)
46: .Append(" Phone nvarchar(24) Not Null) ")
47: End With
48: cmd.CommandText = sbSQL.ToString
49: cmd.ExecuteNonQuery()
52: End Sub

Function CreateDataTable()の処理

CreateDataTable()では、得意先テーブルのDataTableを生成して戻り値として返します。DataTableは、CustomerID、CompanyName、ContactName、Phoneのカラムから構成されています。

54: Function CreateDataTable() As DataTable
55: Dim dt As New DataTable("Customers")
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: End With
62: dt.PrimaryKey = New DataColumn() {dt.Columns("CustomerID")}
63: Return dt
64: End Function

Sub LoadData()の処理

LoadData()では、AccessのNwind.mdbデータベースの得意先テーブルに新規レコードを2件追加します。行70-74では、SQLのInsertステートメントを実行して１件目のレコードを追加しています。

Insert Into tblCustomersUpdate
(CustomerID, CompanyName, ContactName, Phone)
Values(1,'フレンドリーソフト１', '葛西　秋雄', '0480-11-1111')

行76-80では、2件目のレコードを追加しています。

66: Sub LoadData()
67: Dim strSQL As String
68: Dim cmd As OleDbCommand = mcon.CreateCommand()
69:
70: strSQL = "Insert Into tblCustomersUpdate " & _
71: " (CustomerID, CompanyName, ContactName, Phone) " & _
72: " Values(1,'フレンドリーソフト１', '葛西　秋雄', '0480-11-1111') "
73: cmd.CommandText = strSQL
74: cmd.ExecuteNonQuery()
75:
76: strSQL = "Insert Into tblCustomersUpdate " & _
77: " (CustomerID, CompanyName, ContactName, Phone) " & _
78: " Values(2,'フレンドリーソフト２', '葛西　千夏', '0480-22-2222') "
79: cmd.CommandText = strSQL
80: cmd.ExecuteNonQuery()
81: End Sub

Sub DisplayData()の処理

DisplayData()では、DataTableに格納されている得意先テーブルを得意先IDの昇順に並べ替えて表示します。

83: Sub DisplayData(strTitle As String)
84: Dim dr As DataRow
85: Dim dc As DataColumn
86: Response.Write("<h4>" & strTitle & "</h4>")
87: For Each dr In mdt.Select("", "CustomerID")
88: For Each dc In mdt.Columns
89: Response.Write(String.Format("<b>{0}</b>: {1}<br>", _
90: dc.ColumnName, dr(dc)))
91: Next
92: Response.Write("<hr>")
93: Next
94: End Sub

Sub ModifyData()の処理

ModifyData()では、DataTableに登録されているレコードを削除、編集したり、新規のレコードを追加します。行97では、DataTableのRowsコレクションから1番目のレコードを選択してDelete()メソッドで削除しています。行98では、DataTableのRowsコレクションから2番目のレコードを選択して電話番号を変更しています。行99-104では、DataTableのNewRow()/Rows.Add()メソッドで新規レコードを追加しています。

96: Sub ModifyData()
97: mdt.Rows(0).Delete()
98: mdt.Rows(1)("Phone") = "0480-99-9999"
99: Dim dr As DataRow = mdt.NewRow
100: dr("CustomerID") = 3
101: dr("CompanyName") = "フレンドリーソフト３"
102: dr("ContactName") = "葛西　千春"
103: dr("Phone") = "0480-33-3333"
104: mdt.Rows.Add(dr)
105: End Sub

Sub UpdateData()の処理

UpdateData()では、DataTableの変更されたレコードをNwind.mdbデータベースの得意先テーブルに反映しています。このサンプルでは、DataTableをデータベースに反映させるときOleDbDataAdapterのUpdate()メソッドを使用しています。Update()メソッドの引数には、DataTableを指定します。

行108では、OleDbCommandBuilderを使用してUpdate()メソッドで実行するSQL(Insert, Update, Delete)を生成しています。OleDbCommandBuilderの引数には、OleDbDataAdapterを指定します。OleDbCommandBuilderは、OleDbDataAdapterのSelectCommandに格納されているSelectステートメントをベースにInsert、Update、Deleteステートメントを生成します。

アプリケーションで用意したSelectCommand:

SELECT * RROM tblCustomersUpdate

OleDbCommandBuilderが生成したInsertCommnad:

INSERT INTO tblCustomersUpdate

(CustomerID , CompanyName , ContactName , Phone)

VALUES ( ? , ? , ? , ? )

OleDbCommandBuilderが生成したUpdateCommand:

UPDATE tblCustomersUpdate

SET CustomerID = ? , CompanyName = ? , ContactName = ? , Phone = ?

WHERE ( (CustomerID = ?) AND

((? IS NULL AND CompanyName IS NULL) OR (CompanyName = ?)) AND

((? IS NULL AND ContactName IS NULL) OR (ContactName = ?)) AND

((? IS NULL AND Phone IS NULL) OR (Phone = ?)) )

OleDbCommandBuilderが生成したDeleteCommand:

DELETE FROM tblCustomersUpdate

WHERE ( (CustomerID = ?) AND

((? IS NULL AND CompanyName IS NULL) OR (CompanyName = ?)) AND

((? IS NULL AND ContactName IS NULL) OR (ContactName = ?)) AND

((? IS NULL AND Phone IS NULL) OR (Phone = ?)) )

これらのSQLのWhere句には、得意先テーブルのすべてのフィールドを指定してレコードの競合を回避しています。OleDbCommandBuilderが生成したInsert、Update、Deleteステートメントを表示するには、次のように記述します。

Response.Write(mcb.GetInsertCommand.CommandText & "<hr>")
Response.Write(mcb.GetUpdateCommand.CommandText & "<hr>")
Response.Write(mcb.GetDeleteCommand.CommandText & "<hr>")

行109では、OleDbDataAdapterのUpdate()メソッドを使用してDataTable上で変更（追加、編集、削除）されたレコードを得意先テーブルに反映しています。得意先テーブルのレコードが他のクライアントによって変更されているときは、反映されません。

107: Sub UpdateData()
108: mcb = New OleDbCommandBuilder(mda)
109: mda.Update(mdt)
110: End Sub

Note

DataTableをデータベースに反映するとき２重更新を回避するスマートな方法

このサンプルでは、DataTableで変更されたレコードをデータベースに反映するとき、次のようなSQLを使用しています。

UPDATE tblCustomersUpdate

SET CustomerID = ? , CompanyName = ? , ContactName = ? , Phone = ?

WHERE ( (CustomerID = ?) AND

((? IS NULL AND CompanyName IS NULL) OR (CompanyName = ?)) AND

((? IS NULL AND ContactName IS NULL) OR (ContactName = ?)) AND

((? IS NULL AND Phone IS NULL) OR (Phone = ?)) )

UpdateステートメントのWhere句には、得意先テーブルのすべてのフィールドを指定しています。この場合、得意先テーブルのフィールド数が多いときWhere句が複雑になりあまり効率よくありません。この不都合を回避するには、得意先テーブルにConcurrencyIDのフィールドを追加してこのフィールドをWhere句に追加します。

Update tblCustomersUpdate
Set CompanyName = ?,
ContactName = ?,
Phone = ?
ConcurrencyID = ConcurrencyID + 1

Where CustomerID = ? And
ConcurrencyID = ?

UpdateのWhere句には、テーブルの主キー(CustomerID)とConcurrencyIDを指定するだけで２重更新が回避できます。

ADO.NET Accessのホームへ戻る