How to access SQL Server database using DataReader in a visual basic.Net application

This simple program shows how to retrieve data from the FinAccounting database using DataReader and Data Commands. When the code in the program is executed, the data in the FinAccounting database is displayed in two textbox controls.




This book Database programming using visual basic, c# (c sharp) sql server gives you all the steps you should know to use the DataReader object in a database application.

Preparation:

Design a form using .NET environment and place two TextBox controls on a form.

In this article we will use the FinAccounting database.

Tasks:

-Establish the connection with the Database using Connection object.
-Execute the command.
-The contents of the first record are displayed in the textboxes.
Program Code :

We use the SqlDataReader class to read data. The SqlDataReader class is defined in the System.Data.SqlClient namespace. So at the beginning of the program we need to use this class and we do so by adding the namespace. The Imports statement does this task.

Imports System.Data.SqlClient 
 
Public Class Form1 Inherits System.Windows.Forms.Form 
 
´Declare and setup the Connection string 
 
Dim str_connection As String = "Data Source=SYS1;Integrated Security=SSPI; 
Initial Catalog=FinAccounting" 
 
´Declare and build a query string  
Dim str_sql_user_select As String = "SELECT * FROM Accounts" 
 
Dim mycon As SqlConnection 
Dim comUserSelect As SqlCommand 
 
´Declaring the DataReader object 
 
Dim myreader As SqlDataReader 
 
Private Sub Form1_Load(ByVal sender As Object,ByVal e As System.EventArgs)  
Handles MyBase.Load 
 
´Instantiate the connection object   
mycon = New SqlConnection(str_connection) 
 
´Instantiate the command object 
comUserSelect = New SqlCommand(str_sql_user_select, mycon) 
 
TextBox1.Text = "" 
TextBox2.Text = "" 
 
´Opening a Connection with the Open() method 
 
mycon.Open() 
 
´Creating a DataReader object by using  
´the ExecuteReader() method of the Command object.  
 
myreader = comUserSelect.ExecuteReader 
 
If (myreader.Read = True) Then	 
	TextBox1.Text = myreader(0)	 
	TextBox2.Text = myreader(1) 
Else	 
	MsgBox ("You have reached eof") 
End If 
End Sub 
End Class 

Steps in the above program:

• Declare and setup the Connection string
• Declare and build a query string
• Declare the DataReader object
• Instantiate the Connection object
• Instantiate the Command object
• Open a Connection with the Open() method
• Create a DataReader object by using the ExecuteReader() method of the Command object

ExecuteReader method of the Command Object

ExecuteReader is used in the above program to execute a row-returning command, such as a SELECT statement. The rows are returned in a SqlDataReader. The DataReader class is a read-only, forward-only data class, which means that it should only be used for retrieving data to display it. We cannot update data in a DataReader.