As reviewed in the previous lesson, a data adapter allows
you to retrieve records from a database and make them available to your Windows
Forms Application. To make reading data of a database a little faster, the .NET
Framework provides a class used to read data from a database. For s SQL Server
database, this class is called SqlDataReader

Practical
Learning: Introducing the Data Reader

Start Microsoft Visual Studio .NET or Visual C#

Display the Server Explorer. Expand the Servers node, followed by the name
of the computer, followed by SQL Servers, followed by the name of the server

Right-click the server and click New Database

Set the New Data Name to CPAS and accept to use Windows NT
Integrated Security

Click OK

Under the name of the server in Server Explorer, expand the GCS node

Right-click the Tables node and click New Table

Create the table with the following columns (change only the indicated
information; anything that is not mentioned should be ignored and use the
default):

Column Name

Data Type

Length

Allow Nulls

Other Properties

RepairOrderID

int

Primary Key
Identity: Yes

OrderDate

datetime

Unchecked

OrderTime

datetime

20

Unchecked

CustomerName

varchar

Unchecked

Address

varchar

City

varchar

State

char

2

ZIPCode

varchar

10

Make

varchar

20

Unchecked

Model

varchar

32

Unchecked

CarYear

smallint

5

Unchecked

ProblemDescription

text

Unchecked

PartName1

varchar

UnitPrice1

decimal

10

Default Value: 0.00
Scale: 2

Quantity1

tinyint

3

Default Value: 0

SubTotal1

decimal

10

Default Value: 0.00
Scale: 2

PartName2

varchar

UnitPrice2

decimal

10

Default Value: 0.00
Scale: 2

Quantity2

tinyint

3

Default Value: 0

SubTotal2

decimal

Default Value: 0.00
Scale: 2

PartName3

varchar

UnitPrice3

decimal

Scale: 2

Quantity3

tinyint

Default Value: 0

SubTotal3

decimal

Default Value: 0.00
Scale: 2

PartName4

varchar

UnitPrice4

decimal

Default Value: 0.00
Scale: 2

Quantity4

tinyint

Default Value: 0

SubTotal4

decimal

Default Value: 0.00
Scale: 2

PartName5

varchar

UnitPrice5

decimal

Default Value: 0.00
Scale: 2

Quantity5

tinyint

Default Value: 0

SubTotal5

decimal

Default Value: 0.00
Scale: 2

JobPerformed1

varchar

80

JobPrice1

decimal

Default Value: 0.00
Scale: 2

JobPerformed2

varchar

80

JobPrice2

decimal

Default Value: 0.00
Scale: 2

JobPerformed3

varchar

80

JobPrice3

decimal

Default Value: 0.00
Scale: 2

JobPerformed4

varchar

80

JobPrice4

decimal

Default Value: 0.00
Scale: 2

JobPerformed5

varchar

80

JobPrice5

decimal

Default Value: 0.00
Scale: 2

TotalParts

decimal

Unchecked

Default Value: 0.00
Scale: 2

TotalLabor

decimal

Unchecked

Default Value: 0.00
Scale: 2

TaxRate

decimal

Unchecked

Default Value: 7.75
Scale: 2

TaxAmount

decimal

Unchecked

Default Value: 0.00
Scale: 2

OrderTotal

decimal

Unchecked

Default Value: 0.00
Scale: 2

Recommendations

text

Save the table as RepairOrders and close it

Create a new Windows Forms Application named CPAS2

Design the form as follows:

Control

Name

Text

Other Properties

GroupBox

Customer and Car Information

Label

First Name:

TextBox

txtFirstName

Label

Last Name:

TextBox

txtLastName

Label

Address

TextBox

txtAddress

Label

City:

TextBox

txtCity

Label

State:

TextBox

txtState

Label

ZIP Code:

TextBox

txtZIPCode

TextAlign: Right

Label

Make / Model:

TextBox

txtMake

TextBox

txtModel

Label

Year:

TextBox

txtCarYear

TextAlign: Right

Label

Problem Description:

TextBox

txtProblem

GroupBox

Parts Used

Label

Part Name

Label

Unit Price

Label

Qty

Label

Sub Total

TextBox

txtPartName1

TextBox

txtPartUnitPrice1

0.00

TextAlign: Right

TextBox

txtPartQuantity1

0

TextAlign: Right

TextBox

txtPartSubTotal1

0.00

TextAlign: Right

TextBox

txtPartName2

TextBox

txtPartUnitPrice2

0.00

TextAlign: Right

TextBox

txtPartQuantity2

0

TextAlign: Right

TextBox

txtPartSubTotal2

0.00

TextAlign: Right

TextBox

txtPartName3

TextBox

txtPartUnitPrice3

0.00

TextAlign: Right

TextBox

txtPartQuantity3

0

TextAlign: Right

TextBox

txtPartSubTotal3

0.00

TextAlign: Right

TextBox

txtPartName4

TextBox

txtPartUnitPrice4

0.00

TextAlign: Right

TextBox

txtPartQuantity4

0

TextAlign: Right

TextBox

txtPartSubTotal4

0.00

TextAlign: Right

TextBox

txtPartName5

TextBox

txtPartUnitPrice5

0.00

TextAlign: Right

TextBox

txtPartQuantity5

0

TextAlign: Right

TextBox

txtPartSubTotal5

0.00

TextAlign: Right

GroupBox

Jobs Performed

Label

Job Description

Label

Price

TextBox

txtJobDescription1

TextBox

txtJobPrice1

0.00

TextAlign: Right

TextBox

txtJobDescription2

TextBox

txtJobPrice2

0.00

TextAlign: Right

TextBox

txtJobDescription3

TextBox

txtJobPrice3

0.00

TextAlign: Right

TextBox

txtJobDescription4

TextBox

txtJobPrice4

0.00

TextAlign: Right

TextBox

txtJobDescription5

TextBox

txtJobPrice5

0.00

TextAlign: Right

GroupBox

Order Summary

Button

btnResetOrder

Reset Order

Button

btnCalculateOrder

Calculate Order

Label

Total Parts:

TextBox

txtTotalParts

0.00

TextAlign: Right

Label

Total Labor:

Text

txtTotalLabor

0.00

TextAlign: Right

Label

Tax Rate:

TextBox

txtTaxRate

7.75

TextAlign: Right

Label

%

Label

Tax Amount:

TextBox

txtTaxAmount

0.00

TextAlign: Right

Label

Total Order:

TextBox

txtTotalOrder

0.00

TextAlign: Right

Button

btnSaveOrder

Save this Order and Start New Order

Button

btnOpenOrder

Open an Existing Order

Label

Recommendations

TextBox

txtRecommendations

Multiline: True
ScrollBars: Vertical

Double-click the New Order/Reset button and implement its Click event as
follows:

Execute the application and create a repair order. Here is an example:

Save the order

Close the form and return to your programming environment

The SQL Data Reader

The .NET
Framework provided XML-supported classes used to read from, and write to, an XML
file going forth but without back. To support a unidirectional approach to
reading data from a SQL Server database, the .NET Framework provides the SqlDataReader.
This class reads data in a top-down direction without referring back to a record
it passed already:

In other words, the SqlDataReader reads the first
record, moves down, reads the second record, moves down, and so on, until it
gets to the last record. Once it has passed a record, it doesn't and cannot
refer back to it.

To create a data reader, you can declare a pointer to SqlDataReader.
This class doesn't have a constructor. This means that, to use it, you must
(directly) specify where it would read its data. To provide data to the reader,
the SqlCommand class is equipped with the ExecuteReader() method
that is overloaded with two versions. The simplest version of this method uses
the following syntax:

public SqlDataReader ExecuteReader();

Based on this, before using a data reader, you should first
create a command that would specify how data would be acquired. Once the data is
reader, you can pass it to the data reader by assigning the result of a call to
a SqlCommand.ExecuteReader() method to a SqlDataReader object.

Using a SQL Data Reader

Once data is supplied to the reader, you can access it, one
record at a time, from top to bottom. To access data that the reader acquired,
you can call its Read() method whose syntax is:

public virtual bool Read();

As you can see, the Read() method simply reads a
record and moves on. When reading the records of a table, as mentioned already
many times, the data reader reads one record at a time and moves to the next.
Before moving to the next record, you can access the values stored in the
current record. To help with this, the columns of the table being read are
stored in a collection and each column can be referred to with a numeric index.
The first column has an index of 1. The second column has an index of 2, and so
on. To retrieve the actual data stored in a column, you may need to know the
type of information that column is holding so you can read it accurately.

Depending on the data type that a column was created with,
you can access it as follows:

If the column holds the following data type

Use the following method

System.Data.SqlTypes Equivalent

bit

GetBoolean()

GetSqlBoolean()

char, nchar

GetChar()

GetSqlChar()

varchar, nvarchar

GetString()

GetSqlString()

text, ntext

GetString()

GetSqlString()

binary, varbinary

GetBinary()

GetSqlBinary()

decimal

GetDecimal()

GetDouble()

GetSqlDecimal()

float

GetFloat()

GetSqlSingle()

int

GetInt32()

GetSqlInt32()

money, smallmoney

GetDecimal()

GetSqlDecimal()

bigint

GetInt64()

GetSqlInt64()

datetime, smalldatetime

GetDateTime()

GetSqlDateTime()

smallint, tinyint

GetInt16()

GetByte()

GetSqlInt16()

When using one of the Get... or GetSql... methods, the
compiler doesn't perform any conversion. This means that, before sending the
data, you have two responsibilities. First you must convert the value read to
the appropriate (and probably exact) format. For example, if you read a natural
number from a column created with the tinyint data type, even though C#
allows a short to be implicitly converted to an int, the compiler
you use for your application would not perform or assume the conversion: the
value of a column created with tinyint must be read with GetByte()
or GetSqlByte() and trying to use GetInt32() or GetSqlInt32()
would throw an error.

Practical
Learning: Reading Data

Display the form. On the form, double-click the Open button and implement
its Click event as follows: