Using Data Reader
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.