Chapter 6

Working with Data in a

Connected Environment

VB.Net 2005 - Chapter 6 1

1
Chapter 6

„ Lesson 0: Connected & Disconnectd Environment

in ADO.NET
„ Lesson 1: Creating and Executing Command
Objects
„ Lesson 2: Working with Parameters in SQL
Commands

Note: Lesson 3: BLOBs and Lesson 4: Bulk Copy: For reading at

home
Lesson 5: Performing Transactions by Using the Transaction Object

VB.Net 2005 - Chapter 6 2

2
Lesson 0: Connected & Disconnectd
Environment in ADO.NET
„ 2 ways using with ADO.NET
Web
Form
Data
Connection Data Adapter DataSet
Source
Windows
Disconnectd Form

Connected
Web
Form
Data
Connection Command DataReader
Source
Windows
Form
VB.Net 2005 - Chapter 6 3

3
Disconnected Data Access
Advantages Disadvantages
„ Single database server „ Expensive when
can support many users open, close
‰ reduced server's connections
resources „ Retrieving large
„ Data using more flexible result sets can be
„ Data not 'tied' to a very slow
connection
„ Places demand on
‰ easy to pass client memory and
between tiers or CPU
persist to file
„ Highly suited to Web and
n-tier Apps VB.Net 2005 - Chapter 6 4

4
System.Data Architecture
Program

DataReader DataSet

DataProvider

DataAdapter

Database
VB.Net 2005 - Chapter 6 5

5
 Details: System.Data Architecture
Program

DataReader Maintain
Action SQL data
DataSet
Get data
Maintain
Command Object data
Get data

Connection Object
DataAdapter
Provider

Database
VB.Net 2005 - Chapter 6 6

ADOConnection
Similar to Connection object in ADO
ADOCommand
Similar to Command object in ADO
ADODataSetCommand
Somewhat like Recordsets for ADO.NET (DataSet)
Designed for stateless operations
ADODataReader
For streaming methods, similar to Stream
SQLConnection, SQLCommand, and SQLDataSetCommand, SQLDataReader

6
Main Difference:DataSet-DataReader
DataSet
‰ Data structure to store schema and data in a disconnected
fashion
‰ Useful for editing data offline and later update to data
source
„ DataReader
‰ Like Phone connection.
‰ Doesn’t need to store data in memory
‰ Object to access data in a connected, forward-only, read-
only fashion
‰ When performance is your chief concern, especially with
large amounts of data, use a DataReader class

VB.Net 2005 - Chapter 6 7

7
Differences DataSet and DataReader

VB.Net 2005 - Chapter 6 8

8
Lesson 1: Creating and Executing
Command Objects
„ 1.What Are Command Objects?
„ 2.Creating and Configuring Command
Objects
„ 3.Creating SQL Commands (SQL
Statements) with the Query Designer

VB.Net 2005 - Chapter 6 9

9
Lesson 1: Creating and Executing
Command Objects
„ 1.What Are Command Objects?

VB.Net 2005 - Chapter 6 10

10
Lesson 1: Creating and Executing
Command Objects
„ 1.What Are Command Objects?
‰ To execute SQL statements,stored procedures
‰ Contain the necessary information to execute
SQL statements

VB.Net 2005 - Chapter 6 11

11
Lesson 1: Creating and Executing
Command Objects
„ 1.What Are Command Objects?
‰ Depend on Data Providers

VB.Net 2005 - Chapter 6 12

12
Lesson 1: Creating and Executing
Command Objects
„ 1.What Are Command Objects?
‰ Common properties (p.254)
„ CommandText
„ CommandType
„ Connection
„ Parameters

VB.Net 2005 - Chapter 6 13

13
Lesson 1: Creating and Executing
Command Objects
„ 1.What Are Command Objects?
‰ Common Command Object Methods (p.255)
„ ExecuteNonQuery
„ ExecuteReader
„ ExecuteScalar

VB.Net 2005 - Chapter 6 14

14
Lesson 1: Creating and Executing
Command Objects
„ 2.Creating and Configuring Command
Objects
‰ Creating a Command Object That Executes a
SQL Statement
‰ Creating a Command Object That Executes a
Stored Procedure
‰ Creating a Command Object That Performs
Catalog Operations
‰ Creating a Command Object That Returns a
Single Value
VB.Net 2005 - Chapter 6 15

15
Lesson 1: Creating and Executing
Command Objects
„ 2.Creating and Configuring Command
Objects
‰ Creating a Command Object That Executes a
SQL Statement (p.256)

VB.Net 2005 - Chapter 6 16

16
Lesson 1: Creating and Executing
Command Objects
„ 2.Creating and Configuring Command
Objects
‰ Creating a Command Object That Executes a
Stored Procedure (p.257)

VB.Net 2005 - Chapter 6 17

17
Lesson 1: Creating and Executing
Command Objects
„ 2.Creating and Configuring Command
Objects
‰ Creating a Command Object That Performs
Catalog Operations (p.257)

VB.Net 2005 - Chapter 6 18

18
Lesson 1: Creating and Executing
Command Objects
„ 2.Creating and Configuring Command
Objects
‰ Creating a Command Object That Returns a
Single Value (p.258)

VB.Net 2005 - Chapter 6 19

19
Lesson 1: Creating and Executing
Command Objects
„ 3.Creating SQL Commands (SQL
Statements) with the Query Designer
‰ Creating SQL Commands (SQL Statements)
with the Query Designer
‰ Performing Database Operations Using
Command Objects

VB.Net 2005 - Chapter 6 20

20
Lesson 1: Creating and Executing
Command Objects
„ 3.Creating SQL Commands (SQL
Statements) with the Query Designer
‰ Creating SQL Commands (SQL Statements)
with the Query Designer
„ We can use the Query Designer to assist in creating
SQL for Command objects
„ Select database in Server Explorer-> select New
Query from the Data menu.

VB.Net 2005 - Chapter 6 21

21
Lesson 1: Creating and Executing
Command Objects
„ 3.Creating SQL Commands (SQL
Statements) with the Query Designer
‰ Performing Database Operations Using
Command Objects (p 260)

VB.Net 2005 - Chapter 6 22

22
How to receive DataReader
Connectionn Open

Commandn
Which data?

DataReadern
Result
VB.Net 2005 - Chapter 6 23

23
DataReader Class ™Datareader and MS Access

„ Store the information obtained by the command

„ In stateless stream type object
„ Very efficient
„ Forward-only cursor
„ Read-only cursor

Dim dr as OLEDBDataReader = cmd.ExecuteReader

Do while dr.Read()
msgbox dr("City").toString
Loop
dr.Close VB.Net 2005 - Chapter 6 24

24
DataReaders

„ You can’t access anything until you call

Read()the first time

VB.Net 2005 - Chapter 6 25

25
Core DataReader method/property
‰ Read: Reads, and set pointer to the next record.
‰ Close
‰ IsClosed
‰ HasRows:Returns true if DataReader contains rows
‰ FiledCount: Number of columns
‰ GetName(i): returns the label of the ith column in the
current row
‰ GetString(i) :returns the value of the ith column as the
specified type

VB.Net 2005 - Chapter 6 26

26
 DataReader method/property

conSinhVien.Open()
cmdSinhVien = New OleDbCommand("Select .. ", conSinhVien)
drSinhVien = cmdSinhVien.ExecuteReader
Do While drSinhVien.Read
MessageBox.Show(drSinhVien.GetString(0))
Loop
drSinhVien.Close()
conSinhVien.Close()

VB.Net 2005 - Chapter 6 27

27
 Note: DataReader

„ ADO.NET does not provide all the server-side cursor

‰ Don’t keep DataReaders open longer than necessary
„ For flexible updates & client-side manipulation…
‰ Use DataSets and DataAdapters

„ Only one DataReader use at a time

„ Tie to Connection=> cannot used other DataReader.
„ To reuse connection=>call DataReader.Close.
„ Don’t depend on the garbage collector-> explicitly
close.
„ Tie the connection ‘ life to DataReader
‰ CommandBehavior.CloseConnection in ExecuteReader.

VB.Net 2005 - Chapter 6 28

To tie the connection ‘ life to DataReader :

CommandBehavior.CloseConnection in ExecuteReader. =>close
connection automatically when DataReader.Close method is invoked,
especially in n-tier applications in which the calling tier doesn’t have
direct access to the underlying connection

28
Note about DataReader

„ The first row of data is not available until you

call the Read method.
„ Using with stored procedure uses a return or
output parameter, must close DataReader
before get parameter
„ DataReader cannot be used for data binding
„ System.DBNull.value

VB.Net 2005 - Chapter 6 29

29
Note about DataReader

„ Use Item collection, to get values:

‰ Using index instead of by key (column name).

‰ Using native type instead of using the Item collection followed by

an explicit cast.
„ sdr.GetDecimal(1) instead of CDbl(sdr.Item("UnitPrice")).
„ Concat field’ values: A large of operation
‰ Using StringBuilder instead string concatenation.
‰ While sdr.Read
strProductName = sdr.GetString(0)
sb.Append(strProductName)
sb.Append(sdr.GetDecimal(1).ToString())
sb.Append(vbTab)
sb.Append(sdr.GetInt16(2).ToString())
End While

VB.Net 2005 - Chapter 6 30

30
Lesson 1: Creating and Executing
Command Objects
„ 3.Creating SQL Commands (SQL
Statements) with the Query Designer
‰ Lab: P265

VB.Net 2005 - Chapter 6 31

31
Lesson 2: Working with Parameters in
SQL Commands
„ 1.What Is a Parameter and Why Should I
Use Them?
„ 2.Types of Parameters
„ 3.Creating Parameters
„ 4.Adding Parameters to Command
Objects

VB.Net 2005 - Chapter 6 32

32
Lesson 2: Working with Parameters in
SQL Commands
„ 1.What Is a Parameter and Why Should I Use
Them?
‰ parameter can be thought of as a type of variable
‰ use to pass and return values between your
application and a database.
‰ Parameter data types are assigned using the types
defined in the System.Data.SqlDbType enumeration.
‰ pass parameter values to SQL statements when we
want to change the criteria of your queries quickly.

VB.Net 2005 - Chapter 6 33

33
Lesson 2: Working with Parameters in
SQL Commands
„ 2.Types of Parameters (p 274)
‰ Input parameter. (default)
‰ Output parameter.
‰ InputOutput parameter.
„ InputOutput parameters are used to both send and
receive data when executing a command.
‰ The type of parameter is designated in the
Direction property of the parameter
‰ With a parameter, we ou can set its Direction
property to Input, Output, InputOutput, or
ReturnValue.

VB.Net 2005 - Chapter 6 34

34
Lesson 2: Working with Parameters in
SQL Commands
„ 3.Creating Parameters (p 274)
‰ Create parameters:instance of the Parameter
class
‰ setting its name and data type
‰ Choose ParameterDirection

VB.Net 2005 - Chapter 6 35

35
Lesson 2: Working with Parameters in
SQL Commands
„ 4.Adding Parameters to Command
Objects
‰ Command objects have a Parameters property
that represents a collection of parameters
‰ After you create a parameter, you must add it
to the Parameters collection of the Command
object

VB.Net 2005 - Chapter 6 36

36
Lesson 2: Working with Parameters in
SQL Commands
„ Lab: Lab: Working with Parameters page
275

VB.Net 2005 - Chapter 6 37

37
Lesson 2: Summary

„ Update Database with connect mode

‰ Type 1: Using dynamic Sql generation
‰ Type 2: Using dynamic SQL with parameters
‰ Type 3: Using Query Parameters
‰ Type 4: Using Stored Procedure

VB.Net 2005 - Chapter 6 38

38
Your Questions?

VB.Net 2005 - Chapter 6 39

39