Using Data Reader ado.net tutorials

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

This lesson describes when to use Data Reader

When you have connected to a database and queried it, you need to access the result set and for that purpose you use data reader. Data readers are fast, unbuffered, forward-only, read-only connected stream that retrieve data on a per-row basis and implement the System.Data.IDataReader interface.

Using the DataReader object, you are getting as close to the raw data as possible in ADO.NET, because you do not have to populate a DataSet object, which sometimes may be expensive if the DataSet contains a lot of data.

The following code snippet uses the SqlCommand object to execute the query via the ExecuteReader method which returns a populated SqlDataReader object, which you loop through and then print out the User names. In addition you use the Read method to obtain a row from the results of the query accessing the columns of the returned row by passing the name or the ordinal reference of the column to the DataReader.

    Dim ConnectionString As String = _
    "Data Source=SqlServerNameOrIP;Initial Catalog=DBName;Integrated Security=True"
    Dim connection As New SqlConnection(ConnectionString)
    Try
      connection.Open()

      ' declare and create a sql command object
      Dim command As SqlCommand = connection.CreateCommand

      ' set the sql statement to execute at the data source
      command.CommandText = "SELECT Username FROM aspnet_Users"

      ' declare and build the data reader object
      Dim reader As SqlDataReader = command.ExecuteReader

      ' check if reader contains one or more rows
      If reader.HasRows Then
      While reader.Read
      ' Way #1
      Console.WriteLine(reader.GetString(0))
      ' Way #2
      Console.WriteLine(reader(0).ToString)
      ' Way #3
      Console.WriteLine(reader("Username").ToString)
      End While
      End If
      ' release the resources
      reader.Close()
      command.Dispose()
    Catch ex As Exception
      Console.Write(ex.Message)
    Finally
      ' close the connection
      connection.Close()
    End Try
  

NOTE: If you want high performance and you only need to access the data you are retrieving once, then the DataReader is the way to go.


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.