Quickly Retrieve Data Using the DataReader

March 20, 2007

Related Content
• StringBuilder - Optimum method for Concatenation
• CommandBehavior - A Better Way to Close a Connection

The DataReader provides the quickest way to access data. It does however lack sorting and relational abilities.

In this example, I will use the DataReader with the ExecuteReader() method. For simplicity the code is executed when the page is loaded. I am retrieving data from the course table's course_name field. The data is displayed on the aspx page in a label. For the concatenation we use the Concatenation.
'import namespaces
'without these some of the commands would not be recognized
Imports System.Data.SqlClient
Imports System.Data
Imports System.Text
Imports Microsoft.ApplicationBlocks.Data
Imports System.Configuration.ConfigurationManager

Partial Public Class DataReaderPage
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

'connectionString is defined in the web.config file - see below
Dim ConnectionString As String = ConnectionStrings("myConnectionString").ConnectionString
Dim con As New SqlConnection(ConnectionString)
Dim sql As String = "select * from course"
 Dim cmd As New SqlCommand(sql, con)
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
 
Dim htmlStr As New StringBuilder("")

Do While reader.Read()
htmlStr.Append("<strong>")
htmlStr.Append(reader("course_title"))
htmlStr.Append("</strong><br />")
Loop

 reader.Close()
con.Close()

'this is a label defined on the aspx page
 htmlContent.Text = htmlStr.ToString

End Sub

End Class
This is the connection string in the web.config file. This is a SQL server connection. Replace SERVERNAME with your server's name. If the database is running on the same machine, you can use localhost. Replace DBNAME with your database's name. Replace LOGIN and PASSWORD with your database's login information.
<connectionStrings>
<add name="PRIMarketingConnectionString" connectionString="Data Source=SERVERNAME;Initial Catalog=DBNAME;Persist Security Info=True;User ID=LOGIN;Password=PASSWORD" providerName="System.Data.SqlClient" />
</connectionStrings>