Liverpool John Moores University CMPCP0030 WebeCommerce Development in ASP

1
Liverpool John Moores UniversityCMPCP0030Web/e
Commerce Development in ASP ADO .NET
2.0Lecture 07Connections, Commands Data
Readers
2
ADO.NET Object Model

• Diagram showing the principal classes in the
  ADO.NET object model
• The true reality of the object model is
  considerably more complicated
• The Data Source controls can encapsulate all the
  ADO.NET classes

3
Connections

• Connections are responsible for handling the
  physical communication between a database and an
  ASP.NET web application or web service
• The connection object is part of a Data Provider
  and each Data Provider implements
  its own version
• The OleDb Data Provider implements the
  OleDbConnection class in the
  System.Data.OleDb name space
• The SQL Server Data Provider implements the
  SqlConnection class in the
  System.Data.SqlClient name space
• Connections are constructed in Data Source
  controls automatically
• However, we frequently construct our own
  connection objects in code

4
cont

• Some notable properties of the (base)
  DbConnection class are below
• The various versions of the connection object
  OleDbConnection, SqlConnection etc expose a
  slightly different set of properties
• The ConnectionString is the most significant
  property
• They consist of a set of name/value pairs
  separated by semi colons
• ProviderMicrosoft.Jet.OLEDB.4.0Data
  Source\aspado.mdb

5
cont

• Some notable methods of the (base) DbConnection
  class are below
• The Open Close methods are used most frequently
• When you call the Open method on a connection
  that is open, the
  connection is closed and then re-opened
• When another ADO.NET object uses a connection,
  the object will leave the
  connection in the same state that it was found
• When you explicitly call the Open method, the
  connection will remain open
  awaiting you to call the Close method

6
Data Commands

• Essentially, data commands are simply SQL
  statements or references to Stored Procedures
  that are executed against a connection object
• Data commands can execute DML SQL statements such
  as SELECT
• You can use data commands to call Stored
  Procedures in the database
• You can execute SQL statements that return no
  rows, such as statements that
  form part of the databases DDL SQL
• The data command object is part of a Data
  Provider and each Data Provider implements its
  own version in the same way as a connection
• Data commands that execute DML SQL statements
  (those that retrieve and update
  rows of data) are either used
  inside a Data Adapter or beside a Data Reader

7
cont

• Some notable properties of the (base) DbCommand
  class are below
• The various versions of the command object
  OleDbCommand, SqlCommand etc expose a slightly
  different set of properties

8
cont

• Some notable methods of the (base) DbCommand
  class are below

9
Data Readers

• Data readers are used to read values from a
  result set sequentially
• A data reader object returns a read only, forward
  only stream of data from the data command
  that constructed the data reader
• A data reader is substantially faster and more
  efficient than a data set
• Like connections and data commands, data readers
  are part of a Data Provider and are
  thus specific to a data source
• OleDbDataReader, SqlDataReader etc
• Data readers are always used in conjunction with
  data commands
• A data reader is appropriate when you do not need
  cached data access

10
cont

• Some notable methods of the (base) DbDataReader
  class are below