ADO.Net 2.0 DataTable Object
The dataTable consists of tabular rows and columns of data and constraints
It holds the data in memory
The dataTable must contain dataColumn objects
Example of dataTable and dataColumns
You must add
Imports System.Data
Add this code:
Dim Contacts As New dataTable("People")
'create a dataColumn
Dim ContactID As New DataColumn("ContactID")
ContactID.DataType = GetType(Integer)
ContactID.Unique = True
ContactID.AllowDBNull = False
ContactID.Caption = "ContactID"
Contacts.Columns.Add(dc)
'create a dataColumn
Dim username As New DataColumn("Username")
username.MaxLength = 35
username.AllowDBNull = True
Contacts.Columns.Add(username)
'create a dataColumn
Dim age As New DataColumn("age", GetType(Integer))
age.DefaultValue = 0
Contacts.Columns.Add(age)
dataColumn properties and their default values
allowDBNull |
True – does not need to have a value |
Caption |
columnName |
dataType |
String |
MaxLength |
-1 no max length check |
Unique |
False – allows duplicates |
Primary Key
The primarykey is made up of one or more columns. The data in the primary key is unique to each row of data.
To set a datacolumn as the primary key create it like this:
Contacts.PrimaryKey = New DataColumn() {ContactID}
Must be added AFTER the datacolumn has been added to the dataTable
Adding Data
Data is added to a dataTable by adding dataRow objects.
DataRow objects are contained in a rows collection of the dataTable object
Example:
Adding data to dataTable
Continue with our last example
Add the following code:
'populate data method 1
Dim dr As DataRow = Contacts.NewRow()
dr("ContactID") = 1234567
dr("Username") = "Steve"
dr("age") = 33
Contacts.Rows.Add(dr)
Create a dataRow object
Insert data with the Add method
'populate data method 2
Contacts.Rows.Add(1234568, "Joe", 44)
Add the data all at one time without creating a separate dataRow object
'populate data method 3
Contacts.LoadDataRow(New Object() {12345679, "Jerry", 25}, _
LoadOption.OverwriteChanges)
The loadDataRow requires a primary key
LoadOption has 3 possible values:
overWriteRow |
Overwrites the current dataRowVersion and the original dataRowVersion and then changes rowState to unchanged |
preserveCurrentValues |
Default- overwrited the original dataRowVersion but not the current dataRowVersion |
updateCurrentValues |
Overwrites the current dataRowVersion but not the original dataRowVersion |
At this point nothing has been written to the actual database, only the in-memory dataTable
Bind the dataTable to a web control
- Add a gridView to your webform
- Add the following code to bind the dataTable to your new gridView
'Bind dataTable to gridView
GridView1.DataSource = Contacts
GridView1.DataBind()
Simply set the dataSource to your dataTable and execute the dataBind method
Here is the complete code at this point:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim Contacts As New dataTable("People")
'create a dataColumn
Dim ContactID As New DataColumn("ContactID")
ContactID.DataType = GetType(Integer)
ContactID.Unique = True
ContactID.AllowDBNull = False
ContactID.Caption = "ContactID"
Contacts.Columns.Add(ContactID)
Contacts.PrimaryKey = New DataColumn() {ContactID}
'create a dataColumn
Dim username As New DataColumn("Username")
username.MaxLength = 35
username.AllowDBNull = True
Contacts.Columns.Add(username)
'create a dataColumn
Dim age As New DataColumn("age", GetType(Integer))
age.DefaultValue = 0
Contacts.Columns.Add(age)
'populate data method 1
Dim dr As DataRow = Contacts.NewRow()
dr("ContactID") = 1234567
dr("Username") = "Steve"
dr("age") = 33
Contacts.Rows.Add(dr)
'populate data method 2
Contacts.Rows.Add(1234568, "Joe", 44)
'populate data method 3
Contacts.LoadDataRow(New Object() {12345679, "Jerry", 25}, _
LoadOption.OverwriteChanges)
'Bind dataTable to gridView
GridView1.DataSource = Contacts
GridView1.DataBind()
End Sub
RowState property
Added |
dataRow added to dataTable |
Deleted |
Datarow deleted from the dataRow |
Detached |
dataRow created but not added to dataTable |
Modified |
dataRow has been modified since last call to acceptChanges |
Unchanged |
dataRow not changed since last call to AcceptChanges |
rowState is changed to unchanged when acceptChanges method is called updating the data.
DataRowVersion
A dataRow can hold up to 3 versions of the data
- Original
- Current
- Proposed
- When dataRow is created the datarowversion is current
- When the dataRow is being edited it created another version called proposed
- When the dataRow is edited the current version becomes the original and the proposed version becomes the current and the proposed version is gone.
AcceptChanges
AcceptChanges resets the dataRow’s rowState back to unchanged
After loading the dataTable the rowState changes to Added
AcceptChanged changes it back to unchanged
DataRow, DataTable and dataset objects all have the acceptChanges method
Calling the acceptChanges from a dataRow affects that dataRow only
Calling acceptChanges from a dataTable resets all the dataRow’s rowState
Keeping track of changes are important
You can use the getChanges method to create a dataTable of dataRow objects containing ONLY the changes so you can send only the changes back to the server.
After you make your update call acceptChanges so you can keep track of future changes.
dr.AcceptChanges()
rejectChanges
RejectChanges rools back the dataRow to the last time acceptChanges was called and erases all the changes since then
Undeletes any deletions pending
Copies the original datarowversion to the current dataRowversion
Basically changes the datarow back to the way it was right after the last acceptchanges was called
hasVersion
Use hasVersion to check for the existence of a dataRowVersion
If(dr.hasVersion(version)) then
Copy dataTable
Copying a dataTable copies the data and schema
To copy a dataTable use the following code
Dim dtCopy As DataTable = Contacts.Copy()
Here is some more cody to add a dataRow to the copy and bind it to your dataGrid
dtCopy.Rows.Add(1234569, "JoeCopy", 33)
GridView1.DataSource = dtCopy
GridView1.DataBind()
Clone dataTable
Cloning a dataTable copies only the schema, not the data
Dim dtClone As DataTable = Contacts.Clone()
Add some more code to show how the original data is gone. Only the newly added dataRow exists
dtClone.Rows.Add(1234570, "JoeClone", 33)
GridView1.DataSource = dtClone
GridView1.DataBind()
XML in dataTable
The dataTable has a writeXML method which writes the data in a dataTable to an XML file or stream
Add the following code to your example:
Contacts.WriteXml(Server.MapPath("Contacts.xml"))
Response.Redirect("contacts.xml")
It produces an XML file and redirects the browser to it
Notice the additions to the copied and cloned dataTable are not represented
You can add the schema information as well if you like:
Contacts.WriteXml(Server.MapPath("Contacts2.xml"),XmlWriteMode.WriteSchema)
Response.Redirect("contacts2.xml")
You can read an XML file with schema saved back into a dataTable
Remove the redirect line:
Response.Redirect("contacts2.xml")
And add the following:
Dim Newdt As New DataTable()
Newdt.ReadXml(Server.MapPath("Contacts2.xml"))
GridView1.DataSource = Newdt
GridView1.DataBind()
Create a new dataTable
Use the readXML method to read the XMl file into the dataTable
Bind it to the gridView
dataView
A dataView is a sorted or filtered view of a dataTable
You can have many dataViews for each dataTable
Add the following code to
create a new dataview
Sort the view by username in ddescending order
Bind the dataView to your gridView
Dim myView As New DataView(Contacts)
myView.Sort = "Username desc"
GridView1.DataSource = myView
GridView1.DataBind()
dataView’s RowFilterThe rowFilter is set to a Where clause out of a SQL statement without the WHERE
Add this line after the sort
myView.RowFilter = "username like 'J%'"
