Using DataReader And SQLServer

Using DataReaders, SQL Server

In this section we will work with databases in code. We will work with ADO .NET objects in code to create connections and read data using the data reader. We will see how to connect using our own connection objects, how to use the command object and so on. The namespace that needs to be imported when working with SQL Connections is System.Data.SqlClient. This section works with common database operations like insert, select, update and delete commands.

Working with SQL Server

When working with SQL Server the classes with which we work are described below.

The SqlConnection Class
The SqlConnection class represents a connection to SQL Server data source. We use OleDB connection object when working with databases other than SQL Server. Performance is the major difference when working with SqlConnections and OleDbConnections. Sql connections are said to be 70% faster than OleDb connections.

The SqlCommand Class
The SqlCommand class represents a SQL statement or stored procedure for use in a database with SQL Server.

The SqlDataAdapter Class
The SqlDataAdapter class represents a bridge between the dataset and the SQL Server database. It includes the Select, Insert, Delete and Update commands for loading and updating the data.

The SqlDataReader Class
The SqlDataReader class creates a data reader to be used with SQL Server.

DataReaders

A DataReader is a lightweight object that provides read-only, forward-only data in a very fast and efficient way. Using a DataReader is efficient than using a DataAdapter but it is limited. Data access with DataReader is
read-only, meaning, we cannot make any changes (update) to data and forward-only, which means we cannot go back to the previous record which was accessed. A DataReader requires the exclusive use of an active connection for the entire time it is in existence. We instantiate a DataReader by making a call to a Command object's ExecuteReader command. When the DataReader is first returned it is positioned before the first record of the result set. To make the first record available we need to call the Read method. If a record is available, the Read method moves the DataReader to next record and returns True. If a record is not available the Read method returns False. We use a While Loop to iterate through the records with the Read method.

Sample Code

Code to Retrieve Data using Select Command

The following code displays data from Discounts table in Pubs sample database.

Imports System.Data.SqlClient
Public Class Form1 Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim dr As New SqlDataReader()
'declaring the objects

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'establishing connection. you need to provide password for sql server
Try
myConnection.Open()
'opening the connection
myCommand = New SqlCommand("Select * from discounts", myConnection)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()
While dr.Read()
'reading from the datareader
MessageBox.Show("discounttype" & dr(0).ToString())
MessageBox.Show("stor_id" & dr(1).ToString())
MessageBox.Show("lowqty" & dr(2).ToString())
MessageBox.Show("highqty" & dr(3).ToString())
MessageBox.Show("discount" & dr(4).ToString())
'displaying the data from the table
End While
dr.Close()
myConnection.Close()
Catch e As Exception
End Try
End Sub

End Class

The above code displays records from discounts table in MessageBoxes.

Retrieving records with a Console Application

Imports System.Data.SqlClient
Imports System.Console
Module Module1

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim dr As SqlDataReader

Sub Main()
Try
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Select * from discounts", myConnection)
dr = myCommand.ExecuteReader
Do
While dr.Read()
WriteLine(dr(0))
WriteLine(dr(1))
WriteLine(dr(2))
WriteLine(dr(3))
WriteLine(dr(4))
' writing to console
End While
Loop While dr.NextResult()
Catch
End Try
dr.Close()
myConnection.Close()
End Sub

End Module

NEXT>>inserting,updating and deleting