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

 

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

 

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

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

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

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

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

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

 

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

 

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

 

  AccessNwind.mdbデータベースに得意先テーブル(tblCustomersUpdate)作成

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

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

  DataTableのレコード編集(レコードの追加、編集、削除を含む)

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

 

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

 

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

 

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

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

  DataTableを生成する方法

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

  OleDbDataAdapterFill()メソッドで使用する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()では、SQLCreate TableステートメントでAccessNwind.mdbデータベースに得意先テーブルを作成します。行34-39では、SQLDrop TableステートメントでAccessNwind.mdbデータベースから得意先テーブルを削除しています。

 

Drop Table tblCustomersUpdate

41-49では、SQLCreate Tableステートメントを実行してAccessNwind.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)

このサンプルでは、OleDbDataDataAdapterUpdate()メソッドで使用する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は、CustomerIDCompanyNameContactNamePhoneのカラムから構成されています。

 

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()では、AccessNwind.mdbデータベースの得意先テーブルに新規レコードを2件追加します。行70-74では、SQLInsertステートメントを実行して1件目のレコードを追加しています。

 

Insert Into tblCustomersUpdate
 (CustomerID, CompanyName, ContactName, Phone)
 Values(1,'
フレンドリーソフト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,'
フレンドリーソフト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,'
フレンドリーソフト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では、DataTableRowsコレクションから1番目のレコードを選択してDelete()メソッドで削除しています。行98では、DataTableRowsコレクションから2番目のレコードを選択して電話番号を変更しています。行99-104では、DataTableNewRow()/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") = "
フレンドリーソフト3"
102:   dr("ContactName") = "
葛西 千春"
103:   dr("Phone") = "0480-33-3333"
104:   mdt.Rows.Add(dr)
105: End Sub

 

Sub UpdateData()の処理

 

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

 

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

 

アプリケーションで用意した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 = ?)) )

 

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

 

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

 

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

 

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

 

 

Note

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

 

このサンプルでは、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 = ?

 

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