1 2 3 4 5 Previous Next 

DataTable: Compute


Compute how many rows with dates before today are in a datatable

This sample needs only a datagrid and a label on a form Private Sub Form1_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As New DataTable
        dt.Columns.Add("Count", GetType(System.Int32))
        dt.Columns.Add("Date", GetType(System.DateTime))
        For i As Integer = 0 To 24
            dt.LoadDataRow(New Object() _
            {1, New Date(2005,1,1).AddMonths(i)}, True)
        Next
        DataGrid1.DataSource = dt
        ' This is the sample
        Dim ThisDate As String = Now.ToString("#MM/dd/yy#")
        ThisDate = ThisDate.Replace("-", "/")
        Label1.Text = dt.Compute("Sum(Count)", "Date < " _
               & ThisDate).ToString
End Sub

DataTable: Change order of columns (Net 1.x)


Changing the order of columns in a DataTable is in Net1.1 almost impossible.

Therefore it is better not to do it, but by instance use in Datagrid Styles to set the order.

However, if you use a XML DataSet as file, you often want to change that order, this sample
makes that possible.

To run this sample you need only a form with on that a DataGrid 

Private Sub Form1_Load(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As DataTable = CreateTables()
        Dim dtAr(dt.Columns.Count - 1) As DataColumn
        Dim dtFake As DataTable = dt.Clone
        For i As Integer = dtFake.Columns.Count - 1 To 0 Step -1
            dtAr(i) = dtFake.Columns(i)
            dtFake.Columns.RemoveAt(i)
        Next
        Dim dtNew As New DataTable
        dtNew.Columns.Add(dtAr(1))
        dtNew.Columns.Add(dtAr(0))
        For i As Integer = 0 To dt.Rows.Count - 1
            dtNew.Rows.Add(dtNew.NewRow)
            For y As Integer = 0 To dtNew.Columns.Count - 1
                dtNew.Rows(i)(dtNew.Columns(y)) = _
                dt.Rows(i)(dtNew.Columns(y).ColumnName)
            Next
        Next
        DataGrid1.DataSource = dtNew.DefaultView
    End Sub
'sample datatable
    Private Function CreateTables() As DataTable
        Dim dt As New DataTable("Persons")
        dt.Columns.Add("Name")
        dt.Columns.Add("USA", GetType(System.Boolean))
        dt.LoadDataRow(New Object() {"Ken Tucker", True}, True)
        dt.LoadDataRow(New Object() {"Cor Ligthert", False}, True)
        Return dt
    End Function

DataTable: Change order of columns (Net 2.0)


This sample shows beside the changes of the order of columns the use of the method DataView.ToTable.

With this new feature from Net 2.0 is an often asked question become very easy to do.

To test this sample is a windowsform project with one DataGridView on the form enough

 

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt1 As New DataTable("myTable")
        dt1.Columns.Add("Count", GetType(System.Int32))
        dt1.Columns.Add("Date", GetType(System.DateTime))
        For i As Integer = 0 To 24
            dt1.LoadDataRow(New Object() _
            {i, New Date(2005, 1, 1).AddMonths(i)}, True)
        Next
        Dim dt2 As New DataTable("myTable")
        dt2.Columns.Add("Date", GetType(System.DateTime))
        dt2.Columns.Add("Count", GetType(System.Int32))
        dt2.Merge(dt1)
        DataGridView1.DataSource = dt2
    End Sub
End Class

DataTable: Rotate the X and the Y in a datatable (reflection)


Sometimes is asked how to show a datatable rotated in a datagrid. Here a sample how that is easy to do. It needs a datagrid on a form.
Private Sub Form1_Load(ByVal sender As Object, _
            ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds As New DataSet
        Dim dt1 As DataTable = CreateTables()
        ds.Tables.Add(dt1)
        Dim dt2 As New DataTable("Reflection")
        ds.Tables.Add(dt2)
        For i As Integer = 0 To ds.Tables("Original").Rows.Count - 1
            dt2.Columns.Add(i.ToString)
        Next
        For i As Integer = 0 To ds.Tables("Original").Rows.Count - 1
            Dim dr As DataRow = ds.Tables("Reflection").NewRow
            For y As Integer = 0 To ds.Tables("Original").Columns.Count - 1
                dr(y) = ds.Tables("Original").Rows(y).Item(i)
            Next
            ds.Tables("Reflection").Rows.Add(dr)
        Next
        DataGrid1.DataSource = ds.Tables("Reflection")
End Sub

  'To have a table to use is one created below
Private Function CreateTables() As DataTable
        Dim dt As New DataTable("Original")
        dt.Columns.Add("Name")
        dt.Columns.Add("State")
        dt.Columns.Add("Country")
        dt.LoadDataRow(New Object() {"Cor", "Holland", "EU"}, True)
        dt.LoadDataRow(New Object() {"Ken", "Florida", "US"}, True)
        dt.LoadDataRow(New Object() {"Michael", "New Jersey", "US"}, True)
        Return dt
End Function


DataTable: Sort/Filter


Sometimes is asked how to sort/filter a datatable for external use in a dataset. Here a sample how that is easy to do. (In version 2005 there is a standard method for this DataView.ToTable(4 times overloaded)
Dim dv As New DataView(dt)
dv.Sort = "bla"
dv.RowFilter = "bla = 1"
Dim dtnew As DataTable = dt.Clone
For Each dvr As DataRowView In dv
dtnew.ImportRow(dvr.Row)
Next
dt.Clear()
dt = dtnew.Copy


1 2 3 4 5 Previous Next