VB.Net/Database ADO.net/Database Reader

Execute Reader Example

<source lang="vbnet">

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
  
   
 </source>

Execute Scalar Example

<source lang="vbnet">

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
  
   
 </source>

Execute the command and retrieve and XmlReader to access the results

<source lang="vbnet"> 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
  
   
 </source>

Select top 5 Records in data table

<source lang="vbnet"> 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

 </source>

Use SqlDataReader to read a single row of data

<source lang="vbnet"> 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

 </source>