OleDbConnection - Using sql statement

Option Strict On

Imports System.Data
Imports System.Data.OleDb

Public Module UsingStatement
Public Sub Main()
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NorthWind.mdb")

Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader

Using conn
conn.Open
cmd.CommandText = "Select * From Customers"
cmd.CommandType = CommandType.Text
cmd.Connection = conn
dr = cmd.ExecuteReader()
If dr.HasRows Then
Do While dr.Read()
' Get first and last name of customer
Console.WriteLine(CStr(dr.Item(1)) & " " & CStr(dr.Item(2)))
Loop
Else
Console.WriteLine("The table has no rows.")
End If
End Using
End Sub
End Module

Create OleDbConnection to Access database

Imports System.Data

Module Test

Sub Main()
Dim sConnectionString, sSQL As String

'SQL Connection String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\yourdatabase.mdb"

sSQL = "SELECT Title FROM yourTable"

Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
Dim dr As System.Data.OleDb.OleDbDataReader
conn.Open()

dr = cmd.ExecuteReader()

Do While dr.Read()
System.Console.WriteLine(dr.Item("Title"))
Loop

dr.Close()
conn.Close()
End Sub
End Module

Create OleDbConnection to Sql server

Imports System.Data

Module Test

Sub Main()
Dim sConnectionString, sSQL As String

'SQL Connection String
sConnectionString = "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=YourDataBase;Data Source=(local)"

sSQL = "SELECT Title FROM yourTable"

Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
Dim dr As System.Data.OleDb.OleDbDataReader
conn.Open()

dr = cmd.ExecuteReader()

Do While dr.Read()
System.Console.WriteLine(dr.Item("Title"))
Loop

dr.Close()
conn.Close()
End Sub
End Module

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