VB.Net Tutorial » Database ADO.net » DataTable

[VelMurugan]

Output data in DataTable to XML file

Code Select Expand

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Windows.Forms

public class DataTableToXML
   public Shared Sub Main
        Application.Run(New Form1)
   End Sub
End class



Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        Dim scores_dataset As New DataSet("Scores")

        Dim students_table As DataTable = scores_dataset.Tables.Add("Students")

        students_table.Columns.Add("FirstName", GetType(String))
        students_table.Columns.Add("LastName", GetType(String))
        students_table.Columns.Add("StudentId", GetType(Integer))

        students_table.Columns("StudentId").Unique = True

        Dim first_last_columns() As DataColumn = { _
            students_table.Columns("FirstName"), _
            students_table.Columns("LastName") _
        }
        students_table.Constraints.Add( _
            New UniqueConstraint(first_last_columns))

        ' Make the TestScores table.
        Dim test_scores_table As DataTable = _
            scores_dataset.Tables.Add("TestScores")

        ' Add columns to the TestScores table.
        test_scores_table.Columns.Add("StudentId", GetType(Integer))
        test_scores_table.Columns.Add("TestNumber", GetType(Integer))
        test_scores_table.Columns.Add("Score", GetType(Integer))

        ' Make the combined StudentId/TestNumber unique.
        Dim studentid_testnumber_score_columns() As DataColumn = { _
            test_scores_table.Columns("StudentId"), _
            test_scores_table.Columns("TestNumber") _
        }
        test_scores_table.Constraints.Add( _
            New UniqueConstraint(studentid_testnumber_score_columns))

        ' Make a relationship linking the
        ' two tables' StudentId fields.
        scores_dataset.Relations.Add( _
            "Student Test Scores", _
            students_table.Columns("StudentId"), _
            test_scores_table.Columns("StudentId"))

        ' Make some student data.
        students_table.Rows.Add(New Object() {"A", "A", 1})
        students_table.Rows.Add(New Object() {"B", "B", 2})
        students_table.Rows.Add(New Object() {"C", "C", 3})
        students_table.Rows.Add(New Object() {"D", "D", 4})

        ' Make some random test scores.
        Dim score As New Random
        For id As Integer = 1 To 4
            For test_num As Integer = 1 To 10
                test_scores_table.Rows.Add( _
                    New Object() {id, test_num, score.Next(65, 100)})
            Next test_num
        Next id

        students_table.Columns("FirstName").ColumnMapping = MappingType.Element
        students_table.Columns("LastName").ColumnMapping = MappingType.Element
        students_table.Columns("StudentId").ColumnMapping = MappingType.Attribute

        Dim string_writer As New StringWriter()
        students_table.WriteXml(string_writer)
        Console.WriteLine(string_writer)

        grdScores.DataSource = scores_dataset
    End Sub
End Class
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Public Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing AndAlso components IsNot Nothing Then
            components.Dispose()
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer. 
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.grdScores = New System.Windows.Forms.DataGrid
        CType(Me.grdScores, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'grdScores
        '
        Me.grdScores.DataMember = ""
        Me.grdScores.Dock = System.Windows.Forms.DockStyle.Fill
        Me.grdScores.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdScores.Location = New System.Drawing.Point(0, 0)
        Me.grdScores.Name = "grdScores"
        Me.grdScores.Size = New System.Drawing.Size(292, 273)
        Me.grdScores.TabIndex = 1
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.Add(Me.grdScores)
        Me.Name = "Form1"
        Me.Text = "MemoryDataSet"
        CType(Me.grdScores, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub
    Friend WithEvents grdScores As System.Windows.Forms.DataGrid

End Class

Source ; java2s

[VelMurugan]

Filter DataTable by a logic condition

Code Select Expand

Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb

public class CreateDataTableAndFilteredByLogicCondition
   public Shared Sub Main
        Application.Run(New Form1)
   End Sub
End class


Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        Dim contacts_table As New DataTable("Contacts")

        ' Add columns.
        contacts_table.Columns.Add("FirstName", GetType(String))
        contacts_table.Columns.Add("LastName", GetType(String))
        contacts_table.Columns.Add("Street", GetType(String))
        contacts_table.Columns.Add("City", GetType(String))
        contacts_table.Columns.Add("State", GetType(String))
        contacts_table.Columns.Add("Zip", GetType(String))

        contacts_table.Rows.Add(New Object() {"A", "A", _
            "1234", "B", "A", "11111"})
        contacts_table.Rows.Add(New Object() {"B", "B", _
            "22", "B", "C", "22222"})
        contacts_table.Rows.Add(New Object() {"C", "C", _
            "3", "P", "K", "33333"})
        contacts_table.Rows.Add(New Object() {"", "D", _
            "4", "P", "KS", "44444"})

        grdAll.DataSource = contacts_table
        grdAll.CaptionText = "All Records"
       
        Dim dv_name As New DataView(contacts_table)
        dv_name.RowFilter = "FirstName >= 'A'"
        grdName.DataSource = dv_name
        grdName.CaptionText = "LastName >= E"

    End Sub
End Class

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Public Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing AndAlso components IsNot Nothing Then
            components.Dispose()
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer. 
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.SplitContainer1 = New System.Windows.Forms.SplitContainer
        Me.SplitContainer2 = New System.Windows.Forms.SplitContainer
        Me.grdAll = New System.Windows.Forms.DataGrid
        Me.grdCO = New System.Windows.Forms.DataGrid
        Me.grdName = New System.Windows.Forms.DataGrid
        Me.SplitContainer1.Panel1.SuspendLayout()
        Me.SplitContainer1.Panel2.SuspendLayout()
        Me.SplitContainer1.SuspendLayout()
        Me.SplitContainer2.Panel1.SuspendLayout()
        Me.SplitContainer2.Panel2.SuspendLayout()
        Me.SplitContainer2.SuspendLayout()
        CType(Me.grdAll, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.grdCO, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.grdName, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'SplitContainer1
        '
        Me.SplitContainer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.SplitContainer1.Location = New System.Drawing.Point(0, 0)
        Me.SplitContainer1.Name = "SplitContainer1"
        Me.SplitContainer1.Orientation = System.Windows.Forms.Orientation.Horizontal
        '
        'SplitContainer1.Panel1
        '
        Me.SplitContainer1.Panel1.Controls.Add(Me.grdAll)
        '
        'SplitContainer1.Panel2
        '
        Me.SplitContainer1.Panel2.Controls.Add(Me.SplitContainer2)
        Me.SplitContainer1.Size = New System.Drawing.Size(519, 485)
        Me.SplitContainer1.SplitterDistance = 180
        Me.SplitContainer1.TabIndex = 2
        Me.SplitContainer1.Text = "SplitContainer1"
        '
        'SplitContainer2
        '
        Me.SplitContainer2.Dock = System.Windows.Forms.DockStyle.Fill
        Me.SplitContainer2.Location = New System.Drawing.Point(0, 0)
        Me.SplitContainer2.Name = "SplitContainer2"
        Me.SplitContainer2.Orientation = System.Windows.Forms.Orientation.Horizontal
        '
        'SplitContainer2.Panel1
        '
        Me.SplitContainer2.Panel1.Controls.Add(Me.grdCO)
        '
        'SplitContainer2.Panel2
        '
        Me.SplitContainer2.Panel2.Controls.Add(Me.grdName)
        Me.SplitContainer2.Size = New System.Drawing.Size(519, 301)
        Me.SplitContainer2.SplitterDistance = 173
        Me.SplitContainer2.TabIndex = 0
        Me.SplitContainer2.Text = "SplitContainer2"
        '
        'grdAll
        '
        Me.grdAll.DataMember = ""
        Me.grdAll.Dock = System.Windows.Forms.DockStyle.Fill
        Me.grdAll.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdAll.Location = New System.Drawing.Point(0, 0)
        Me.grdAll.Name = "grdAll"
        Me.grdAll.Size = New System.Drawing.Size(519, 180)
        Me.grdAll.TabIndex = 0
        '
        'grdCO
        '
        Me.grdCO.DataMember = ""
        Me.grdCO.Dock = System.Windows.Forms.DockStyle.Fill
        Me.grdCO.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdCO.Location = New System.Drawing.Point(0, 0)
        Me.grdCO.Name = "grdCO"
        Me.grdCO.Size = New System.Drawing.Size(519, 173)
        Me.grdCO.TabIndex = 1
        '
        'grdName
        '
        Me.grdName.DataMember = ""
        Me.grdName.Dock = System.Windows.Forms.DockStyle.Fill
        Me.grdName.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdName.Location = New System.Drawing.Point(0, 0)
        Me.grdName.Name = "grdName"
        Me.grdName.Size = New System.Drawing.Size(519, 124)
        Me.grdName.TabIndex = 1
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(519, 485)
        Me.Controls.Add(Me.SplitContainer1)
        Me.Name = "Form1"
        Me.Text = "Contacts"
        Me.SplitContainer1.Panel1.ResumeLayout(False)
        Me.SplitContainer1.Panel2.ResumeLayout(False)
        Me.SplitContainer1.ResumeLayout(False)
        Me.SplitContainer2.Panel1.ResumeLayout(False)
        Me.SplitContainer2.Panel2.ResumeLayout(False)
        Me.SplitContainer2.ResumeLayout(False)
        CType(Me.grdAll, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.grdCO, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.grdName, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub
    Friend WithEvents SplitContainer1 As System.Windows.Forms.SplitContainer
    Friend WithEvents grdAll As System.Windows.Forms.DataGrid
    Friend WithEvents SplitContainer2 As System.Windows.Forms.SplitContainer
    Friend WithEvents grdCO As System.Windows.Forms.DataGrid
    Friend WithEvents grdName As System.Windows.Forms.DataGrid

End Class