Skip Navigation LinksASP.Net 2.0 Training : ASP.Net:dataTables
Banner for ASP.Net 2.0 training classes in Visual Basic 2005 

Skip Navigation Links
Visit my other site:

certification training

Free Flash tutorials and training

Free Dreamweaver Video Tutorials

Free PHP Training

Visual Basic Windows Training




If you like this site please link to it


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

  1. Original
  2. Current
  3. 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%'"


      ASP.Net 2.0 tutorials & training