SQL Commands and SQL DataReader

1
SQL Commands and SQL DataReader

• Mauricio Featherman, Ph.DWashington St.
  University

2
What is an ADO.NET SQLCommand?

• The command class (SQL or OleDB) is used to
  execute SQL code (called a commands commandtext)
  during a data session
• The mechanism to exchange data between the
  presentation tier and the datasource
• They hold a definition of what to read or write
  to the data store

3
How do I Create and Use One?

• First you declare the variable
• Then you construct it using 2 arguments a
  SQLstring that is the commandtext and the
  connection to useDim CmdFoo as New
  SQLCommand(Select From Heaven,conABC)or
  Dim CmdFoo as New OleDbCommand(Select From
  Heaven,conABC)
• Finally you execute it using one of its 3 methods

4
Alternate Constructor

• You can also Dim cmdGo as New
  SQLCommand()cmdGo.commandtext Select from
  HeavencmdGo.connection conFPcmdGo.commandType
  CommandType.text

5
Methods of the SQLCommand

• ExecuteReader (used to run commandtext that uses
  the select keyword)
• ExecuteScalar (used to run commandtext that uses
  the select keyword)
• ExecuteNonQuery (used to run a commandtext that
  uses INSERT, UPDATE, or DELETE keywords)

6
Methods of the SQLCommand

• Those that use a SQL Select statement to create
  and fill a datareader (in-memory array)
  (.ExecuteReader)
• Those that just run whatever SQL code DDL/DML you
  give it but dont return any data to you (UPDATE,
  INSERT, DELETE, CREATE TABLE, DROP TABLE, etc.)
  (.ExecuteNonQuery)
• Those that just run an aggregate query and give
  you a numeric answer such as Invoices 57
  (.ExecuteScalar)

7
DataReaders

• If you execute a reader (.ExecuteReader) then you
  pull rows and columns of info (aka resultset or
  recordset) into a datareader an in-memory array
• The datareader is a forward-only, read-only
  stream of data
• There are OleDbDataReaders and SqlDataReaders
  both ar optimized to quickly work with data from
  different types of databases.
• Can also ExecuteReader(SchemaOnly) to see the
  datatypes of the datasource table

8
DataReader Limitations

• While they are lighting fast, they can only have
  1 row of their data accessible at a time
• This means you have to loop them to retrieve
  their data using the .Read method
• When inside each row you use one of the GetValue
  methods (GetString, GetDecimal, etc.) to retrieve
  the contents of the column (the cell) or more
  simly .item(0)
• This is fine for any of the list-based controls,
  however too cumbersome for stuffing data into a
  table (we will use datsets and tables for that)

9
Typical code to push data from a database column
to a control on form (more on this later)

• Dim connection, command, datareaderConstruct
  connection(specify connectionstring)Construct
  command(specify SQL text and Connection)Try we
  use a try catch block to elegantly catch any
  errors that will blow up the program
  Connection.Open While DataReader.Read Put
  data into a control End WhileCatch any
  errors Messagebox to show caught
  errorsFinally Connection.CloseEnd Try