Command and Data Reader

1
Command and Data Reader

• ASP.NET
• http//sdetu/courses/sdetc101/
• Lecture 15
• Wednesday, 25th March 2008

2
Agenda

• Stored Procedures
• The command object
• Data Reader Object

3
Recall

• Understanding ADO.NET
• The Connection and command objects
• Inside Data Adapter
• ADO.NET Namespaces
• Steps to access a database through ADO.NET
• DEMO Step by Step Approach
• Object Model

4
Stored Procedure

• A program (or procedure) which is physically
  stored within a database
• Usually written in a proprietary database
  language like PL/SQL
• Advantage when it is run, in response to a user
  request, it is run directly by the database
  engine, which usually runs on a separate database
  server.
• As such, it has direct access to the data it
  needs to manipulate and only needs to send its
  results back to the user, doing away with the
  overhead of communicating large amounts of data
  b4ack and forth.

5
Connection Object

• Creating a Connection
• Dim oConn As New OleDbConnection
• Opening a Connection
• Parameters Used to Construct a Connection String
• Provider The OLEDB provider used to access the
  database.
• Data Source The IP address or name of the server
  on which the database resides.
• Database The name of the database to be used
  once the connection is open.
• User ID The user ID for the account used to
  access the database.
• Password The password for the account used to
  access the database.

6
Example

• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDBConnection
• Dim sConnString As String
• sConnString ProviderSQLOLEDBData
  Source(local)Initial_ catalogMusicUser
  IDmusicPasswordmusic
• oConn New OleDBConnection(sConnString)
• oConn.Open()
• oConn.Close()
• End Sub

7
The Command object

• Command
• an instructionin this case to create, retrieve,
  update, or delete data in your data store.
• Enables you to fill the Data Reader objects with
  data.
• Building a Command
• You can explicitly set the command objects
  properties,
• pass parameters into the command objects
  constructor, or a combination of the two.
• Example
• oCmd New OleDbCommand()
• oCmd New OledbCommand(sSQL, oConn)

8
Example

• //returns all of the rows in the t_bands table in
  the Music database
• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OledbConnection
• Dim oCmd As OledbCommand
• Dim sSQL As String
• sSQL SELECT FROM t_bands
• oConn New OledbConnection
• oConn.ConnectionString ProviderSQLOLEDBDa
  ta_ Source(local)Initial CatalogMusic
  UserIDmusic_ Passwordmusic
• oConn.Open()
• oCmd New OledbCommand(sSQL, oConn)
• oConn.Close
• End Sub

9
Properties

• Connection
• Used to set or get the connection against which
  to execute the command.
• CommandText
• Gives you a means of holding your command (as a
  string) for later execution.
• It can contain a SQL statement, a stored
  procedure name.
• Example
• oCmd.CommandText SELECT band_id, band_title,
  music_type_id, record_company_id FROM t_bands
• oCmd.CommandText prGetBands
• oCmd.CommandType CommandType.StoredProcedure

10
Properties

• CommandType
• Gets the CommandText or sets how it is
  interpreted.
• The possible values, or enumerations, of the
  CommandType property are
• StoredProcedure
• Text
• CommandTimeout
• Gets or sets the time, in seconds, to wait while
  executing the command before terminating the
  attempt and generating an error.
• oCmd.CommandTimeout 60 (Default 30 sec)
• The command objects commandTimeout property and
  the connection objects ConnectionTimeout
  property are completely disparate properties. ?

11
Appending parameters

• The OleDbCommand object supports a collection
  property named Parameters.
• The parameters property is actually a
  OleDbParameterCollection object that can contain
  more than one OleDbParameter object.
• The Parameters property enables you to append
  parameters to the command object
• How to do appending ?
• Create an OleDbParameter object
• ExampleConstructing the OleDbParameter object
  by setting its properties explicitly rather than
  passing them into the OleDbParameter object
  constructor.
• oParam New OleDbParameter()
• oParam.ParameterName _at_iID
• oParam.DBType OleDbType.Integer
• oParam.Value 1

12
Example

• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDbConnection
• Dim oCmd As OleDbCommand
• Dim oParam As OleDbParameter
• Dim sSQL As String
• oConn New OleDbConnection(ProviderSQLOLEDBD
  ata
• Source(local)Initial CatalogMusicUser
  IDmusic_ Passwordmusic)
• oConn.Open()
• oCmd New OleDbCommand(sSQL, oConn)
• oCmd.CommandType CommandType.StoredProcedure
• oCmd.CommandText prGetBandTitle
• oParam New OleDbParameter()
• oParam.ParameterName _at_iID
• oParam.DBType OleDbType.Integer
• oParam.Value 1
• oCmd.Parameters.Add(oParam)
• End Sub

13
Executing a Command

• Command object has many useful methods
• ExecuteReader(),
• ExecuteNonQuery(), and
• Prepare()
• ExecuteNonQuery method
• Does not return any rows from the datasource
• The ExecuteNonQuery() method does return the
  number of rows, as an integer, that were affected
  by the executed command.
• Shall be useful when executing INSERT,UPDATE or
  DELETE commands depending on your requirements

14
Example

• Dim iAffected As Integer
• oCmd New OleDbCommand()
• oCmd.Connection oConn
• oCmd.CommandType CommandType.Text
• oCmd.CommandText UPDATE t_bands SET band_title
  Hootie and TheBlowfish WHERE band_title
  Hootie The Blowfish
• iAffected oCmd.ExecuteNonQuery()

15
Executing a Command

• ExecuteReader method
• Executes the CommandText against the commands
  connection and builds an object (called Data
  Reader object ) capable of forward-only data
  reads.
• oDR oCmd.ExecuteReader()

16
Example

• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDbConnection
• Dim oCmd As OleDbCommand
• Dim oDR As OleDbDataReader
• oConn New OleDbConnection(ProviderSQLOLEDB
  Data
• Source(local)Initial CatalogMusicUser
  IDmusicPasswordmusic)
• oConn.Open()
• oCmd New OleDbCommand()
• With oCmd
• .Connection oConn
• .CommandType CommandType.StoredProcedure
• .CommandText prGetBands
• // to populate the DataReader object with the
  results of the query
• oDR oCmd.ExecuteReader()
• End With
• While oDR.Read()
• //iterate through the DataReader and add an Item
  to
• //the ListBox server control, lstBands, for each
  record
• lstBands.Items.Add(NewListItem(oDR.Item(band_t
  itle),oDR.Item(band_id)))

17
Using Data Readers

• A DataReader object is effectively a forward-only
  collection of records from your data source.
• Purpose To provide data for display
• Steps
• Declaring a variable
• Dim oDR As OleDbDataReader
• Construct your Connection and Command objects
• Initialize the DataReader object by calling the
  Command objects ExecuteReader method
• oDR oCmd.Executereader()

18
Example

• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDbConnection
• Dim oCmd As OleDbCommand
• Dim oDR As OleDbDataReader
• oConn New OleDbConnection(ProviderSQLOLEDB
  Data
• Source(local)Initial CatalogMusicUser
  IDmusic Passwordmusic)
• oConn.Open()
• oCmd New OleDbCommand()
• With oCmd
• .Connection oConn
• .CommandType CommandType.Text
• .CommandText SELECT FROM t_bands
• oDR .ExecuteReader()
• End With
• End Sub

19
Properties

• Item
• Returns the value for a given column in its
  native format
• In order to reference the value of a column, you
  need to pass a string representing the column
  name or an integer representing the columns
  index.
• Example
• You could reference the band_title field in
  either of the following ways
• oDR.Items(band_title)
• oDR.Items(1)

20
Properties

• FieldCount
• Read-only property
• Returns the number fields, as an integer, in the
  current record.
• E.g.
• Dim iFCount As Integer
• iFCount oDR.FieldCount
• Application of the FieldCount property is to
  iterate through the columns in a DataReader and
  write out the columns value
• IsClosed
• Returns a Boolean value indicating whether the
  DataReader is closed.
• A value of true means that the DataReader is
  closed.

21
Properties

• RecordsAffected
• Returns the number of rows that are changed,
  inserted,ordeleted by the Command object that
  opens the DataReader.
• 0 is returned from the RecordsAffected property
  if no records were affected by the command object
• 1 isreturned for SELECT commands.
• The RecordsAffected property is not set until the
  DataReader object is closed.

22
Example (1/3)

• lt_at_ Page LanguageVB gt
• lt_at_ Import NamespaceSystem.Data gt
• lt_at_ Import NamespaceSystem.Data.OleDb gt
• ltSCRIPT LANGUAGEVB RUNATservergt
• Sub Page_Load(Sender As Object, E As EventArgs)
• Dim oConn As OleDbConnection
• Dim oCmd As OleDbCommand
• Dim oDR As OleDbDataReader
• Dim oParam As OleDbParameter
• Dim iBandID As Integer 0
• If Page.IsPostBack Then iBandID
  lstBands.SelectedItem.Value
• oConn New OleDbConnection(ProviderSQLOLE
  DBDataSource(local)Initial
  CatalogMusicUser IDmusicPasswordmusic)
• oConn.Open()
• oCmd New OleDbCommand()

23
Example (2/3)

• With oCmd
• .Connection oConn
• .CommandType CommandType.StoredProcedure
• .CommandText prBandDelete
• oParam New OleDbParameter
• With oParam
• .ParameterName BandID
• .OleDbType OleDbType.Integer
• .Value iBandID
• End With
• .Parameters.Add(oParam)
• oDR .ExecuteReader()
• lstBands.Items.Clear
• lstBands.Items.Add(New ListItem(,0))
• While oDR.Read()
• lstBands.Items.Add(New
• ListItem(oDR.Item(band_title))
• End While
• End With

24
Example (3/3)

• oDR.Close
• oConn.Close
• If oDR.isClosed Then
• If oDR.RecordsAffected gt 0 Then
  lblDeleted.Text You deleted
• oDR.RecordsAffected bands from the
  database.
• End If
• End Sub
• lt/SCRIPTgt
• ltHTMLgt
• ltBODYgt
• ltFORM IDfrmBandDelete Runatservergt
• ltaspListBox IDlstBands Size1
  AutoPostBacktrue Runatserver/gt
• ltBR/gtltBR/gt
• ltaspLabel IDlblDeleted Text
  ForeColorRed Runatserver/gt
• lt/FORMgt
• lt/BODYgt
• lt/HTMLgt

25
Stored Procedure Example

• CREATE PROCEDURE prBandDelete
• _at_BandID INT 0
• AS
• IF _at_iBandID gt 0
• BEGIN
• DELETE FROM t_songs WHERE album_id IN (SELECT
  album_id FROM t_albums WHERE band_id _at_iBandID)
• DELETE FROM t_albums WHERE band_id _at_iBandID
• DELETE FROM t_band_members WHERE band_id
  _at_iBandID
• DELETE FROM t_bands WHERE band_id _at_iBandID
• END
• SELECT band_id, band_title FROM t_bands ORDER
  BY band_title

26
Methods

• Read
• Advances the DataReader object to the next record
  each time it is called.
• GetValue
• Returns the value of a specified field in its
  native format
• E.g. iID oDR.GetValue(0) Or iID
  oDR.GetValue(band_id)
• GetData Type
• Return the data in a column as the specified data
  type
• Only accept a column index,also called an ordinal
  reference, as a parameter.
• No data type conversion is performed, so the data
  type of the column must be of the data type
  specified
• Example
• Dim sBandName As String
• sBandName oDR.GetString(0)

27
Methods

• GetOrdinal
• Returns a columns ordinal reference value, or
  index, as an integer when passed a column name.
• Example Returns 0 as band_id is the first
  column
• Dim iOrdinal As Integer
• iOrdinal oDR.GetOrdinal(band_id)
• GetName
• It returns a columns name as a string when
  passed its index
• Exact opposite of the GetOrdinal method
• Example returns band_id
• Dim sName As String
• sName oDR.GetName(0)

28
Methods

• Close
• Closes a DataReader object
• Closing the DataReader object is mandatory when
  youre done using it
• You will get an error if you dont close your
  DataReader and then attempt to alter your
  Connection object
• Example
• oDR.Close()

29
Questions ?
30
(No Transcript)