How to use datasets and datatables in vb.net

Source:

http://msdn.microsoft.com/en-us/library/system.data.datatable(VS.71).aspx

' Put the next line into the Declarations section.

Private myDataSet As DataSet

Private Sub MakeDataTables()

' Run all of the functions.

MakeParentTable()

MakeChildTable()

MakeDataRelation()

BindToDataGrid()

End Sub

Private Sub MakeParentTable()

' Create a new DataTable.

Dim myDataTable As DataTable = New DataTable("ParentTable")

' Declare variables for DataColumn and DataRow objects.

Dim myDataColumn As DataColumn

Dim myDataRow As DataRow

' Create new DataColumn, set DataType, ColumnName and add to DataTable.

myDataColumn = New DataColumn()

myDataColumn.DataType = System.Type.GetType("System.Int32")

myDataColumn.ColumnName = "id"

myDataColumn.ReadOnly = True

myDataColumn.Unique = True

' Add the Column to the DataColumnCollection.

myDataTable.Columns.Add(myDataColumn)

' Create second column.

myDataColumn = New DataColumn()

myDataColumn.DataType = System.Type.GetType("System.String")

myDataColumn.ColumnName = "ParentItem"

myDataColumn.AutoIncrement = False

myDataColumn.Caption = "ParentItem"

myDataColumn.ReadOnly = False

myDataColumn.Unique = False

' Add the column to the table.

myDataTable.Columns.Add(myDataColumn)

' Make the ID column the primary key column.

Dim PrimaryKeyColumns(0) As DataColumn

PrimaryKeyColumns(0) = myDataTable.Columns("id")

myDataTable.PrimaryKey = PrimaryKeyColumns

' Instantiate the DataSet variable.

myDataSet = New DataSet()

' Add the new DataTable to the DataSet.

myDataSet.Tables.Add(myDataTable)

' Create three new DataRow objects and add them to the DataTable

Dim i As Integer

For i = 0 To 2

myDataRow = myDataTable.NewRow()

myDataRow("id") = i

myDataRow("ParentItem") = "ParentItem " + i.ToString()

myDataTable.Rows.Add(myDataRow)

Next i

End Sub

Private Sub MakeChildTable()

' Create a new DataTable.

Dim myDataTable As DataTable = New DataTable("childTable")

Dim myDataColumn As DataColumn

Dim myDataRow As DataRow

' Create first column and add to the DataTable.

myDataColumn = New DataColumn()

myDataColumn.DataType = System.Type.GetType("System.Int32")

myDataColumn.ColumnName = "ChildID"

myDataColumn.AutoIncrement = True

myDataColumn.Caption = "ID"

myDataColumn.ReadOnly = True

myDataColumn.Unique = True

' Add the column to the DataColumnCollection.

myDataTable.Columns.Add(myDataColumn)

' Create second column.

myDataColumn = New DataColumn()

myDataColumn.DataType = System.Type.GetType("System.String")

myDataColumn.ColumnName = "ChildItem"

myDataColumn.AutoIncrement = False

myDataColumn.Caption = "ChildItem"

myDataColumn.ReadOnly = False

myDataColumn.Unique = False

myDataTable.Columns.Add(myDataColumn)

' Create third column.

myDataColumn = New DataColumn()

myDataColumn.DataType = System.Type.GetType("System.Int32")

myDataColumn.ColumnName = "ParentID"

myDataColumn.AutoIncrement = False

myDataColumn.Caption = "ParentID"

myDataColumn.ReadOnly = False

myDataColumn.Unique = False

myDataTable.Columns.Add(myDataColumn)

myDataSet.Tables.Add(myDataTable)

' Create three sets of DataRow objects, five rows each, and add to DataTable.

Dim i As Integer

For i = 0 To 4

myDataRow = myDataTable.NewRow()

myDataRow("childID") = i

myDataRow("ChildItem") = "Item " + i.ToString()

myDataRow("ParentID") = 0

myDataTable.Rows.Add(myDataRow)

Next i

For i = 0 To 4

myDataRow = myDataTable.NewRow()

myDataRow("childID") = i + 5

myDataRow("ChildItem") = "Item " + i.ToString()

myDataRow("ParentID") = 1

myDataTable.Rows.Add(myDataRow)

Next i

For i = 0 To 4

myDataRow = myDataTable.NewRow()

myDataRow("childID") = i + 10

myDataRow("ChildItem") = "Item " + i.ToString()

myDataRow("ParentID") = 2

myDataTable.Rows.Add(myDataRow)

Next i

End Sub

Private Sub MakeDataRelation()

' DataRelation requires two DataColumn (parent and child) and a name.

Dim myDataRelation As DataRelation

Dim parentColumn As DataColumn

Dim childColumn As DataColumn

parentColumn = myDataSet.Tables("ParentTable").Columns("id")

childColumn = myDataSet.Tables("ChildTable").Columns("ParentID")

myDataRelation = New DataRelation("parent2Child", parentColumn, childColumn)

myDataSet.Tables("ChildTable").ParentRelations.Add(myDataRelation)

End Sub

Private Sub BindToDataGrid()

' Instruct the DataGrid to bind to the DataSet, with the

' ParentTable as the topmost DataTable.

DataGrid1.SetDataBinding(myDataSet, "ParentTable")

End Sub

For more info check out :

Dataset class

http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx

General searching and sorting of a dataset

http://www.eggheadcafe.com/community/aspnet/10/74275/dataset-search-and-sortin.aspx

How to access the DAL provided by VB2008 / VWD2008

http://weblogs.asp.net/scottgu/archive/2006/01/15/435498.aspx