DataView - Find a value in DataView

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

public class FindAValueInDataView
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"

dv_name.Sort = "FirstName, LastName"
MessageBox.Show(dv_name.Find(New String() {"A", "A"}).ToString)

End Sub
End Class

_
Partial Public Class Form1
Inherits System.Windows.Forms.Form

'Form overrides dispose to clean up the component list.
_
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.
_
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

DataSet - Build up a DataSet

Imports System.Data.SqlClient
Imports System.Data

Module Module1

Sub Main()
Try
Dim DS As New DataSet()

DS.ReadXml("Authors.XML")

Dim XMLContent As String = DS.GetXml()

Dim I As Integer

For I = 0 To DS.Tables(0).Rows.Count - 1
DS.Tables(0).Rows(I).Item("Author") = UCase(DS.Tables(0).Rows(I).Item("Author"))
Next

For I = 0 To DS.Tables(0).Rows.Count - 1
Console.WriteLine(DS.Tables(0).Rows(I).Item("Title"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Author"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Publisher"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Price"))
Next

DS.WriteXml("NewAuthors.xml")

Catch Ex As Exception
Console.WriteLine("Exception: " & Ex.Message)
Console.WriteLine(Ex.ToString)
Console.ReadLine()
End Try

End Sub

End Module

DataSet XML - Save data in DataSet to xml file

Imports System.Data.SqlClient
Imports System.Data

Module Module1

Sub Main()
Try
Dim DS As New DataSet()

DS.ReadXml("Authors.XML")

Dim XMLContent As String = DS.GetXml()

Dim I As Integer

For I = 0 To DS.Tables(0).Rows.Count - 1
DS.Tables(0).Rows(I).Item("Author") = UCase(DS.Tables(0).Rows(I).Item("Author"))
Next

For I = 0 To DS.Tables(0).Rows.Count - 1
Console.WriteLine(DS.Tables(0).Rows(I).Item("Title"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Author"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Publisher"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Price"))
Next

DS.WriteXml("NewAuthors.xml")

Console.ReadLine()

Catch Ex As Exception
Console.WriteLine("Exception: " & Ex.Message)
Console.WriteLine(Ex.ToString)
End Try

End Sub

End Module

DataSet XML - Load XML data to Data

Imports System.Data.SqlClient
Imports System.Data

Module Module1

Sub Main()
Try
Dim DS As New DataSet()

DS.ReadXml("Authors.XML")

Dim XMLContent As String = DS.GetXml()

Dim I As Integer

For I = 0 To DS.Tables(0).Rows.Count - 1
Console.WriteLine(DS.Tables(0).Rows(I).Item("Title"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Author"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Publisher"))
Console.WriteLine(DS.Tables(0).Rows(I).Item("Price"))
Next

Catch Ex As Exception
Console.WriteLine("Exception: " & Ex.Message)
Console.WriteLine(Ex.ToString)
End Try

End Sub

End Module

DATA GRID VIEW - Read data in SqlServer to DataGridView

Imports System.Data.SqlClient
Imports System.Data

Imports System.Windows.Forms

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

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
''Dim sConnString As String = "server=vs-lv;uid=sa;pwd=;database=Northwind"
Dim ConnString As String = "Data Source=vs-lv;Initial Catalog=Northwind;Integrated Security=True"
Dim SQLString As String = "SELECT * FROM Customers"
Dim SqlDataAdapter1 As New SqlDataAdapter(SQLString, ConnString)
Dim DataSet1 As New DataSet()
SqlDataAdapter1.Fill(DataSet1, "Customers")
DataGridView1.DataSource = DataSet1.Tables("Customers")
End Sub
End Class
_
Partial Class Form1
Inherits System.Windows.Forms.Form

'Form overrides dispose to clean up the component list.
_
Protected 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.
_
Private Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button
Me.DataGridView1 = New System.Windows.Forms.DataGridView
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(35, 289)
Me.Button1.Margin = New System.Windows.Forms.Padding(4, 4, 4, 4)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(229, 29)
Me.Button1.TabIndex = 0
Me.Button1.Text = "Read SQL Server 2000"
Me.Button1.UseVisualStyleBackColor = True
'
'DataGridView1
'
Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
Me.DataGridView1.Location = New System.Drawing.Point(16, 15)
Me.DataGridView1.Margin = New System.Windows.Forms.Padding(4, 4, 4, 4)
Me.DataGridView1.Name = "DataGridView1"
Me.DataGridView1.RowTemplate.Height = 23
Me.DataGridView1.Size = New System.Drawing.Size(503, 266)
Me.DataGridView1.TabIndex = 1
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 15.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(535, 332)
Me.Controls.Add(Me.DataGridView1)
Me.Controls.Add(Me.Button1)
Me.Margin = New System.Windows.Forms.Padding(4, 4, 4, 4)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView

End Class

DATA GRID VIEW - Load data in Access to DataGridView

Imports System.Data.OleDb
Imports System.Data

Imports System.Windows.Forms

public class LoadAccessDataToDataView
public Shared Sub Main
Application.Run(New Form1)
End Sub
End class
Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyMDB.mdb"
Dim SQLString As String = "SELECT * FROM TestDB"
Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
Dim DataSet1 As New DataSet()
Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)
OleDBConn1.Open()
OleDbDataAdapter1.Fill(DataSet1, "TestDB")
DataGridView1.DataSource = DataSet1.Tables("TestDB")
End Sub
End Class


_
Partial Class Form1
Inherits System.Windows.Forms.Form

'Form overrides dispose to clean up the component list.
_
Protected 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.
_
Private Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button
Me.DataGridView1 = New System.Windows.Forms.DataGridView
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(35, 289)
Me.Button1.Margin = New System.Windows.Forms.Padding(4, 4, 4, 4)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(171, 29)
Me.Button1.TabIndex = 0
Me.Button1.Text = "Read Access"
Me.Button1.UseVisualStyleBackColor = True
'
'DataGridView1
'
Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
Me.DataGridView1.Location = New System.Drawing.Point(16, 15)
Me.DataGridView1.Margin = New System.Windows.Forms.Padding(4, 4, 4, 4)
Me.DataGridView1.Name = "DataGridView1"
Me.DataGridView1.RowTemplate.Height = 23
Me.DataGridView1.Size = New System.Drawing.Size(503, 266)
Me.DataGridView1.TabIndex = 1
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 15.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(535, 332)
Me.Controls.Add(Me.DataGridView1)
Me.Controls.Add(Me.Button1)
Me.Margin = New System.Windows.Forms.Padding(4, 4, 4, 4)
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView

End Class

DATA GRID - DataSet Relation

Imports System.Data
Imports System.Data.OleDb

Imports System.Windows.Forms

public class DataSetWithRelation
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))

Dim test_scores_table As DataTable = scores_dataset.Tables.Add("TestScores")

test_scores_table.Columns.Add("StudentId", GetType(Integer))
test_scores_table.Columns.Add("TestNumber", GetType(Integer))
test_scores_table.Columns.Add("Score", GetType(Integer))

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))

scores_dataset.Relations.Add("Student Test Scores",students_table.Columns("StudentId"),test_scores_table.Columns("StudentId"))

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})

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.Rows(1).SetColumnError(2, "Bad name format")
students_table.Rows(2).RowError = "Missing registration"

grdScores.DataSource = scores_dataset
End Sub
End Class


_
Partial Public Class Form1
Inherits System.Windows.Forms.Form

'Form overrides dispose to clean up the component list.
_
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.
_
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 = "MemoryDataSetWithError"
CType(Me.grdScores, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

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

End Class