VB.Net/Database ADO.net/Database Reader

Execute Reader Example

  

Imports System
Imports System.Data
Imports System.Data.SqlClient
    Public Class MainClass
        Public Shared Sub ExecuteReaderExample(ByVal con As IDbConnection)
            Dim com As IDbCommand = con.CreateCommand
            com.rumandType = CommandType.Text
            com.rumandText = "SET ROWCOUNT 10;SELECT Production.Product.Name, Production.Product.ListPrice FROM Production.Product ORDER BY Production.Product.ListPrice DESC;SET ROWCOUNT 0;"
            Using reader As IDataReader = com.ExecuteReader
                While reader.Read
                    Console.WriteLine("  {0} = {1}", reader("Name"), reader("ListPrice"))
                End While
            End Using
        End Sub
        Public Shared Sub Main()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                con.Open()
                ExecuteReaderExample(con)
                con.Close()
            End Using
        End Sub
    End Class

Execute Scalar Example

  

Imports System
Imports System.Data
Imports System.Data.SqlClient
    Public Class MainClass
        Public Shared Sub ExecuteScalarExample(ByVal con As IDbConnection)
            Dim com As IDbCommand = con.CreateCommand
            com.rumandType = CommandType.Text
            com.rumandText = "SELECT COUNT(*) FROM HumanResources.Employee;"
            Dim result As Integer = CInt(com.ExecuteScalar)
            Console.WriteLine("Employee count = " & result)
        End Sub
        Public Shared Sub Main()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                con.Open()
                ExecuteScalarExample(con)
                con.Close()
            End Using
        End Sub
    End Class

Execute the command and retrieve and XmlReader to access the results

  
Imports System
Imports System.Xml
Imports System.Data
Imports System.Data.SqlClient
    Public Class MainmClass
        Public Shared Sub ConnectedExample()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                Using com As SqlCommand = con.CreateCommand
                    com.rumandType = CommandType.Text
                    com.rumandText = "SELECT DepartmentID, [Name], GroupName FROM HumanResources.Department FOR XML AUTO"
                    con.Open()
                    " .
                    Using reader As XmlReader = com.ExecuteXmlReader
                        While reader.Read
                            If reader.NodeType = XmlNodeType.Element Then
                                Dim currentEle As XElement = XElement.ReadFrom(reader)
                                Console.WriteLine("Element:  {0}", currentEle.Name)
                                If currentEle.HasAttributes Then
                                    For i As Integer = 0 To currentEle.Attributes.Count - 1
                                        Console.Write("  {0}: {1}", currentEle.Attributes()(i).Name, currentEle.Attributes()(i).Value)
                                    Next
                                End If
                            End If
                        End While
                    End Using
                    con.Close()
                End Using
            End Using
        End Sub
        Public Shared Sub Main()
            ConnectedExample()
        End Sub
    End Class

Select top 5 Records in data table

 
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
    Shared Dim WithEvents con As SqlConnection
    Shared Sub Main()
        con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") 
        Dim cmd As New SqlCommand()
        cmd.rumandText = "SELECT TOP 5 FirstName, LastName FROM Employee"
        cmd.Connection = con
        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Console.WriteLine( _
                       "{0} - {1}", reader.GetString(0), reader.GetString(1))
            End While
            reader.Close()
        Finally
            con.Close()
        End Try
    End Sub
    Shared Sub con_StateChange(ByVal Sender As Object, _
                 ByVal E As StateChangeEventArgs) Handles con.StateChange
        Console.WriteLine( _
                   "{0} - {1}", "ConnectionState", E.CurrentState.ToString())
    End Sub
End Class

Use SqlDataReader to read a single row of data

 
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
    Shared Dim WithEvents con As SqlConnection
    Shared Sub Main()
        con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") 
        Dim cmd As New SqlCommand()
        cmd.rumandText = "SELECT FirstName, LastName FROM Employee"
        cmd.Connection = con
        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
            While reader.Read()
                Console.WriteLine("{0} - {1}", _
                                reader.GetString(0), reader.GetString(1))
            End While
            reader.Close()
        Finally
            con.Close()
        End Try
    End Sub
End Class