Chapter 10 ADO What is ADO ADO is

Chapter 10 ADO

What is ADO? ADO is a Microsoft technology ADO stands for Active. X Data Objects ADO is a programming interface to access data in a database

What is ADO? • ADO. NET is an object-oriented set of libraries that allows you to interact with data sources. • Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file.

Data Providers • ADO. NET allows us to interact with different types of data sources and different types of databases. • There isn't a single set of classes that allow you to accomplish this universally. • Different data sources expose different protocols. • Some older data sources use the ODBC protocol, many newer data sources use the Ole. Db protocol 4

Data Providers Provider Name API prefix Data Source Description ODBC Data Provider Odbc Data Sources with an ODBC interface. Normally older data bases. Ole. Db Data Provider Ole. Db Data Sources that expose an Ole. Db interface, i. e. Access or Excel. Oracle Data Provider Oracle For Oracle Databases. SQL Data Provider Sql For interacting with Microsoft SQL Server. 5

ADO. NET Objects Connection Object Command Object Data. Reader Data. Set Data. Adapter

Connection Object To interact with a database, you must have a connection to it. The connection helps identify the database server, the database name, user name, password, and other parameters that are required for connecting to the data base. A connection object is used by command objects so they will know which database to execute the command on. 7

Command Object You use a command object to send SQL statements to the database. A command object uses a connection object to figure out which database to communicate with. You can use a command object alone, to execute a command directly, or assign a reference to a command object to a Data. Adapter, which holds a set of commands that work on a group of data as described below.

Data. Reader Many data operations require that you only get a stream of data for reading. The data reader object allows you to obtain the results of a SELECT statement from a command object. For performance reasons, the data returned from a data reader is a fast forward-only stream of data. This means that you can only pull the data from the stream in a sequential manner This is good for speed, but if you need to manipulate data, then a Data. Set is a better object to work with.

Data. Sets Data. Set objects are in-memory representations of data. They contain multiple Datatable objects, which contain columns and rows. You can even define relations between tables to create parent-child relationships.

Data. Adapter The data adapter helps to manage data in a disconnected mode. The data adapter fills a Data. Set object when reading the data and writes in a single batch when persisting changes back to the database. A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database. Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data.

Using a Connection The purpose of creating a Connection object is so you can enable other ADO. NET code to work with a database. Other ADO. NET objects, such as a Command a Data. Adapter take a connection object as a parameter. ◦ ◦ ◦ Instantiate the Connection. Open the connection. Pass the connection to other ADO. NET objects. Perform database operations with the other ADO. NET objects. Close the connection.

Using Command Objects A Sql. Command object allows you to query and send commands to a database. It has methods that are specialized for different commands. The Execute. Reader method returns a Data. Reader object for viewing the results of a select query. For insert, update, and delete SQL commands, you use the Execute. Non. Query method. If you only need a single aggregate value from a query, the Execute. Scalar is the best choice

Data. Reader Objects A Data. Reader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. You can read from Data. Reader objects in a forward-only sequential manner. Once you've read some data, you must save it because you will not be able to go back and read it again.

Data. Sets and Data. Adapters A Data. Set is an in-memory data store that can hold numerous tables. Data. Sets only hold data and do not interact with a data source. It is the Data. Adapter that manages connections with the data source and gives us disconnected behavior. The Data. Adapter opens a connection only when required and closes it as soon as it has performed its task.

Data. Adapters A Data. Adapter performs the following tasks when filling a Data. Set with data: ◦ Open connection ◦ Retrieve data into Data. Set ◦ Close connection 16