Thursday, May 1, 2008

DataTable as a Database

Continuing the previous article on offline scallable computing, the we can use datatable as our database.

There are a many methods and classes that provide support this. First we have the DataSet which can be thought as a database containing many DataTables. You can save the dataset directly into an XML file or create a DataSet directly from the XML file.
  • Writing can be done using either DataSet.WriteXml(filePath, XmlWriteMode.WriteSchema).
  • Retrieving the data is back to the DataSet is done using DataSet.ReadXML(filePath).
You can then use DataView to facilitate the selection of data. For example if you have an employee table and you want to select an employee record using the dataview, you could do the following:

DIM dv as DataView

dv = New DataView(ds.Tables("EmpMaster"), "empno='111000'")

You can update the record retrieved in the DataView by performing a BeginEdit and EndEdit.

For example you can update the persons address as follows:

dv.AllowEdit = True 'This is because by default the view is read-only
dv(0)("Address") = "111 AprilFool Lane"

This all happens in memory so after updating, you need to write the dataset back to the file. This is the only weakness of this approach since we will need to write back the whole dataset even though we just updated one column in one row.

No comments: