Data access with the ADO connection method

1. Introduction to the connection environment

1.1. Characteristics of the connected environment
? A connected environment is where the user always maintains a connection to the data source in this environment
? Advantages
– Easy to implement safety control in the environment
– Synchronization issues are easy to control
? Better data real-time than other environments
? Disadvantages
– must maintain a continuous network connection
– Poor scalability

1.2. Object model in connected environment
? Xxxconnection
– Establish a connection with a data source, such as SqlConnection, for establishing a Microsoft SQL Server? Connection, OleDbConnection is used to establish a connection to any OLE DB-enabled data source
? Xxxcommand
– a command that executes a data source, such as SqlCommand, can call a Microsoft SQL Server? Stored procedure, the ExecuteReader method of the Xxxcommand object can be
Returns a Xxxdatareader object
? Xxxdatareader
– Read data as a stream in a read-only, forward-only manner, such as SqlDataReader can read from Microsoft SQL Server? The data in
The –xxxdatareader is returned by the ExecuteReader method of the Xxxcommand (usually the Select command)
? Xxxxmlreader
– Provides a fast, non-cached, forward-fetching way to read XML data

1.3. Connect to a data source

Defining connections
? Xxxconnection (e.g. SqlConnection)
? The connection string is a string that contains the data connection parameter that defines the connection to the data source
? Parameters contained in the connection string
? Important parameters of the connection string:
–provider the type of data provider used for the connection
–data Source The name of the SQL Server to connect to
–initial Catalog The name of the database to connect to
–integrated Security uses Windows authentication to determine user rights
–user Id/password SQL Server login name and password
–persist Security Info After connection

1.4. Turn the connection on and off
? Open connection
–xxxconnection.open ()
? Close connection
–xxxconnection.close ()
? Exit Connection Pool
–xxxconnection.dispose ()

2. Create a command

2.1. Command Object
? The Command object is a reference to an SQL statement or stored procedure
? Command object can be executed directly
? Property
–name: Optional attribute that can be used to reference the object
–connection: Reference to the Connection object, interacting with the database
A kind of –commandtype:text, StoredProcedure, directtable
The name of the –COMMANDTEXT:SQL statement or stored procedure
–parameters: can have 0 or more parameters

Important methods for 2.2. Command objects
? ExecuteScalar
– Returns a unique value
? ExecuteNonQuery
– for updating the database or changing the database structure, returning the number of rows affected
? ExecuteReader
– Returns a collection of data rows
? ExecuteXmlReader (SqlCommand only)
– Returns a result set of XML

3. Return a single value

3.1. Return a single value
? Using the ExecuteScalar () method
? ADO is more efficient and returns the entire recordset
? Example
– Inventory quantity for a specific product
– Number of products
– Use COUNT, MAX, MIN, AVERAGE

3.2. DEMO
Executing command commands that return a single value

4. Return Data rows

? Using the ExecuteReader () method
– Returns a DataReader
– For example: SqlDataReader, OleDbDataReader
? DataReader
– Read-only, forward, stream of data rows

5. Get Data using DataReader
? Read method
– Get the next line
– returns True if there is a subsequent data row present;
? Item Property
–areader["Acolumnname"] or areader[columnposition]
? GetXxx methods, for example: GetString, GetInt32
–getstring[columnposition] (starting with base zero)
? GetValues method
– Returns all columns of the current row at once, efficiently
? IsDbNull method
– Used to test if NULL is returned
? Close method
When the –read method returns False, the Close method should be called to close DataReader, releasing the connection

6. Return multiple result sets
? A stored procedure may contain more than one SQL statement
– Group Related Tasks
– Encapsulating business Logic
? If a stored procedure returns multiple result sets
– Call NextResult to move to the next result set
? Determine how many rows of data are affected by a stored procedure
– Using the RecordsAffected property

7. No return value
? Using the ExecuteNonQuery () method
? Executing DDL and DCL statements
–create/alter/drop
–grant/deny/revoke
? Executing other DML statements
–insert/update/delete

8. Parameters for command commands
? Introduced
–sql statements and stored procedures can have input and output parameters and return values
The –command object parameter is used to set or return these parameters
–sqlparameter, OleDbParameter

? ParameterName

– The name of the command parameter, such as @catid
? Dbttype
– Type of connection to the database with SqlType and OleDbType
? Direction
–parameterdirection the value specified by the enumeration set, including:
? ParameterDirection.Input (Input)
? Parameterdirection.inputoutput (input/output)
? ParameterDirection.Output (Output)
? ParameterDirection.ReturnValue (return value)

9. Business
? A transaction is a series of interrelated tasks that are successfully committed or failed as a whole (commit or rollback)
? ACID
–atomicity (atomic): Transaction or all commit, or no commit at all
–consistency (Consistent): Transactions guarantee the integrity of data
–isolation (Detach): Transactions handle concurrency of data operations
–durability (persistent): Data operations involving transactions are saved normally even after a catastrophic situation such as a system crash after a transaction has ended

9.2. Implementing transactions using T-SQL
? SQL transaction Statements
–begin TRAN, COMMIT TRAN, ROLLBACK TRAN
? code example

BEGIN TRANDECLARE @orderDetailsError int,@productError intDELETE  from"OrderDetails "WHEREProductID= theSELECT @orderDetailsError = @ @ERRORDELETE  fromProductsWHEREProductID= theSELECT @productError = @ @ERRORIF @orderDetailsError = 0  and @productError = 0COMMIT TRANELSEROLLBACK TRAN

9.3. Managing transactions with ADO
? xxxconnection– such as SqlConnection
–begintransaction
? xxxtransaction– such as SqlTransaction
–commit
–rollback
? Isolation level

Data access with the ADO connection method