OleDbDataAdapterUpdate()メソッドを使用してDataTableをデータベースに反映するサンプル(SQLコマンド手動作成)

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

 

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

 

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

 

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

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

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

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

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

 

OleDbDataAdapterUpdate()メソッドでDataTableを得意先テーブルに反映するときに使用するSQL(Insert/Update/Delte)は、プログラムで用意したものを使用します。

 

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

 

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

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

  テーブルのフィールド(カラム)にデフォルト値を設定する方法 (Default 0)

  DataTableを生成する方法

  DataTableDataColumnにデフォルト値を設定する方法 (DefaultValue = 0)

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

  OleDbParametersコレクションのAdd()メソッドの使い方

  OleDbParameterSourceVersionプロパティの使い方

  DataRowVersionCurrentOriginalの使い方

 

サンプルの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()では、SQLCreate TableステートメントでAccessNwind.mdbデータベースに得意先テーブルを作成します。行32-37では、SQLDrop TableステートメントでAccessNwind.mdbデータベースから得意先テーブルを削除しています。

 

Drop Table tblCustomersUpdate

38-48では、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,
  ConcurrencyID int Default 0)

得意先テーブルのConcurrencyIDフィールドは、レコードの競合(2重更新)を回避するために使用します。このフィールドには、レコードの更新回数が格納されます。SQLInsertステートメントで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を生成して戻り値として返します。ConcurrencyIDDataColumnDefaultValueプロパティには0を設定しています。DataTableNewRow()/Rows.Add()メソッドでDataRowを追加するとき、ConcurrencyIDを省略するとデフォルト値の0が格納されます。

 

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

 

Insert Into tblCustomersUpdate
 (CustomerID, CompanyName, ContactName, Phone)
 Values(1,'
フレンドリーソフト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,'
フレンドリーソフト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,'
フレンドリーソフト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に登録されているレコードを削除、編集したり、新規のレコードを追加します。DataTableNewRow()/Rows.Add()メソッドで新規レコード追加するとき、DataColumnConcurrencyIDに値を設定していませんがデフォルトの0が格納されます。

 

98: Sub ModifyData()
 99:   Dim dr As DataRow
100:
102:   dr = mdt.Rows(0)
103:   dr.Delete()
104:
106:   dr = mdt.Rows(1)
107:   dr("Phone") = "0480-99-9999"
108:
109:   dr = mdt.NewRow
110:   dr("CustomerID") = 3
111:   dr("CompanyName") = "
フレンドリーソフト3"
112:   dr("ContactName") = "
葛西 千春"
113:   dr("Phone") = "0480-33-3333"114:
115:   mdt.Rows.Add(dr)
116: End Sub

 

Sub UpdateData()の処理

 

UpdateData()では、DataTable上の変更されたレコードをNwind.mdbデータベースの得意先テーブルに反映しています。このサンプルでは、DataTableをデータベースに反映させるときOleDbDataAdapterUpdate()メソッドを使用しています。Update()メソッドの引数には、DataTableを指定します。行119では、CreateInsertCommand()関数を呼び出してOleDbDataAdapterInsertCommandプロパティに戻り値を設定しています。CreateInsertCommand()は、InsertOleDbCommandを生成して返します。行120では、CreateUpdateCommand()関数を呼び出してOleDbDataAdapterUpdateCommandプロパティに戻り値を設定しています。CreateUpdateCommand()は、UpdateOleDbCommandを生成して返します。行121では、CreateDeleteCommand()関数を呼び出して、OleDbDataAdapterDeleteCommandプロパティに戻り値を設定しています。CreateDeleteCommand()は、DeleteOleDbCommandを生成して返します。

 

122では、OleDbDataAdapterUpdate()メソッドでDataTable上の変更されたレコードを得意先テーブルに反映します。DataTableに新規レコードが追加されたときは、OleDbDataAdapterInsertCommnadプロパティに格納されている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を生成します。この関数からは、戻り値としてInsertOleDbCommandを返します。行127-131With…End Withでは、StringBuilderAppend()メソッドでSQLInsertステートメントを生成しています。

 

Insert Into tblCustomersUpdate
 (CustomerID, CompanyName, ContactName, Phone)
 Values(?, ?, ?, ?)

Insertステートメントでは、ConcurrencyIDを省略していますがデフォルト値の0が格納されます。行136-141With…End Withでは、OleDbParameterCollectionAdd()メソッドでパラメータを追加しています。Add()メソッドの引数には、parameterNameOleDbTypesizesourceColumnを指定しています。sourceColumnには、パラメータ値として使用するDataColumnを指定します。たとえば、sourceColumnCustomerIDを指定したときは、パラメータ変数@CustomerIDDataTableのカレントDataRowCustomerIDが設定されます。

 

OleDbParameterCollectionAdd()メソッドにsourceColumnを指定することにより、OleDbDataAdapterUpdate()メソッドで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を生成します。この関数からは、戻り値としてUpdateOleDbCommandを返します。行147-155With…End Withでは、StringBuilderAppend()メソッドでSQLUpdateステートメントを生成しています。

 

Update tblCustomersUpdate
 Set CompanyName = ?,

  ContactName = ?,
  Phone = ?,
  ConcurrencyID = ConcurrencyID + 1
 Where CustomerID = ? And
  ConcurrencyID = ?

Updateステートメントでは、レコードの2重更新を回避するためにConcurrencyIDを使用しています。ConcurrencyIDは、レコードを更新する度に+1加算します。Where句には、得意先テーブルの主キー(CustomerID)ConcurrencyIDを指定します。ConcurrencyIDを使用しないときは、得意先テーブルのすべてのフィールドを指定する必要があります。

 

161-170With…End Withでは、OleDbParameterCollectionAdd()メソッドでUpdateステートメントのパラメータを追加しています。Add()メソッドの引数には、parameterNameOleDbTypesizesourceColumnを指定しています。Set句のパラメータ@NewCompanyName@NewContactName@NewPhoneには、SourceVersionプロパティを設定していませんが、省略するとDataRowVersion.Currentがデフォルトとして採用されます。つまり、これらのパラメータには、DataTableのカレントDataRowの更新後の値が設定されます。

 

Where句のパラメータ@OrgCustomerID@OrgConcurrencyIDには、SourceVersionプロパティにDataRowVersion.Originalを設定していますので、DataTableのカレントDataRowの更新前の値が設定されます。

 

OleDbDataAdapterUpdate()メソッドは、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を生成します。この関数からは、戻り値としてDeleteOleDbCommandを返します。行176-180With…End Withでは、StringBuilderAppend()メソッドでSQLDeleteステートメントを生成しています。

 

Delete * From tblCustomersUpdate
 Where CustomerID = ? And
  ConcurrencyID = ?

Deleteステートメントでは、レコードの競合を回避するためにConcurrencyIDを使用しています。ConcurrencyIDは、レコードを更新する度に+1加算します。Where句には、得意先テーブルの主キー(CustomerID)ConcurrencyIDを指定します。ConcurrencyIDを使用しないときは、得意先テーブルのすべてのフィールドを指定する必要があります。

 

186-191With…End Withでは、OleDbParameterCollectionAdd()メソッドでDeleteステートメントのパラメータを追加しています。Add()メソッドの引数には、parameterNameOleDbTypesizesourceColumnを指定しています。Where句のパラメータ@CustomerID@ConcurrencyIDには、SourceVersionプロパティにDataRowVersion.Originalを設定していますので、DataTableのカレントDataRowの更新前の値が設定されます。

 

OleDbDataAdapterUpdate()メソッドは、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