ADO.NetVB.NetData Types

ADO.Net - DataTable

A datatable is a collection of datarow objects. Could be a database table or a view.

Samples

Creating a table Dynamically

1
2
3
4
5
' create a table structure
Dim Table As New DataTable
Table.Columns.Add("SellPrice", GetType(Double))
Table.Columns.Add("Total", GetType(Double))
table.Columns.Add("FullTableName", GetType(String))

Deleting Rows

1
2
3
4
5
6
Dim Keys(1) as datacolumn
Keys(0)=ds.tables(0).column(0)
ds.tables(0).primarykey=keys
dim aRow as datarow _
  = ds.tables(0).rows.find("I4")
aRow.Delete()

Adding Rows

1
2
3
dim aRow as datarow = ds.tables(0).newrow()
aRow("akey")="12"
ds.tables(0).Rows.add(aRow)

InMemory Add Rows

This adds the row to the dataset, but a mechanism is still needed to move the data from the recordset to the database.

1
2
3
4
5
6
7
8
For x = 0 To 200
    Dim aRow As DataRow = Table.NewRow
    aRow("SellPrice") = Sell
    aRow("Total") = Sell * CDbl(txtQuant.Text) - CDbl(txtTransactionCost.Text)
    Table.Rows.Add(aRow)

    Sell = Sell + 0.25
Next

Complete Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Public Shared Function cboYN() As DataTable
    Dim Table As New DataTable
    Table.Columns.Add("YNCode", GetType(String))
    Table.Columns.Add("YNText", GetType(String))

    Dim aRow As DataRow = Table.NewRow()
    aRow("YNText") = "Yes"
    aRow("YNCode") = "Y"
    Table.Rows.Add(aRow)

    aRow = Table.NewRow()
    aRow("YNText") = "No"
    aRow("YNCode") = "N"
    Table.Rows.Add(aRow)

    aRow = Table.NewRow()
    aRow("YNText") = ""
    aRow("YNCode") = ""
    Table.Rows.Add(aRow)

    Return Table
End Function

Create an inmemory table, load it with some data (Data is datarows)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
For x = 0 To 200

    Dim aRow As DataRow = Table.NewRow

    aRow("SellPrice") = Sell
    aRow("Total") = Sell * CDbl(txtQuant.Text) - CDbl(txtTransactionCost.Text)

    If IsDBNull(arow("executiontype")) Then
         ' they did not provide an execution type
         aRow("Total") += 100
    End If

    Table.Rows.Add(aRow)

    Sell = Sell + 0.25
Next