Introduction to ADO. NET data access

When using ADO. NET to develop applications, various requirements for data operations are required. In some cases, you may just want to display data on the form. In other cases, you may need to design a way to share information with another company.

Regardless of the operations on the data, you should understand some basic concepts about the data methods in ADO. NET. You may never have to know the details of data processing (for example, you may never have to directly edit an XML file containing data), but understand ADO. NET.

This section provides a high-level overview of these most important concepts. This topic intentionally skips many details (for example, there are much more content about the dataset than mentioned here), so that you can only introduce the concept of data integration in ADO. NET.

Note:When deploying an application that contains the Visual Studio data access component, make sure that the user who installs the application has version 2.7 or higher of the Microsoft Data Access Component (MDAC. For more information, see Add the startup conditions for the Microsoft Data Access component.

ADO. NET does not rely on continuous active connections

In traditional client/server applications, the component establishes a connection with the database and keeps the connection open when the application is running. For various reasons, this method is not practical in many applications:

• The opened database connection occupies valuable system resources. In most cases, the database can maintain only a small number of concurrent connections. Maintaining the system overhead of these connections will reduce the overall performance of the application.
• Similarly, applications that need to open database connections are extremely difficult to scale proportionally. For applications that do not have a well-scaled scale, it may be acceptable for four users to use it, however, if hundreds of users use it, the effect may be unacceptable. ASP. NET Web applications must be easily expanded, because the Web site traffic may suddenly increase by several orders of magnitude in a very short period of time.
• In ASP. NET Web applications, components are not connected to each other. The browser requests a page from the server. After the server completes processing and sends the page, the server no longer connects to the browser until the next request. In these cases, it is not feasible to maintain the opened database connection, because there is no way to know whether the data user (client) needs further data access.
• A Model Based on always-connected data may make it difficult and impractical to exchange data between applications and organizational boundaries using a connection structure. If the two components need to share the same data, they must be connected, or they must be designed with each other to transmit data.

For all these reasons, the use of ADO. NET for data access is designed with a controlled connection structure centered. Only enough time for the application to connect to the database to obtain or update data. Because the database is not occupied by idle connections for most of the time, it can provide services for more users.

Execute database Interaction Using Data commands

To perform operations in the database, you should execute SQL statements or stored procedures (including SQL statements ). You use SQL statements or stored procedures to read and write rows and execute Aggregate functions, such as adding or averaging. You also use SQL statements or stored procedures to create or modify tables or columns and execute transactions.

In ADO. NET, you can use data commands to Package SQL statements or stored procedures. For example, if you want to read a group of rows from the database, create a data command and configure it with the text of the SQL Select statement or the name of the stored procedure for obtaining records.

To obtain these rows, perform the following operations:

1. Open a connection.
2. Call the command execution method in the following order:
   1. The SQL statement or stored procedure referenced by the command.
   2. Close the connection.

      The connection is kept open for only enough time to execute statements or stored procedures.

After calling the command execution method, it returns a value. The command for updating the database returns the affected number of rows; the command for other types returns an error code. If this command uses the SELECT statement to query the database, it returns a group of rows. You can use a data reader (as a very fast read-only, forward-only cursor) to obtain these rows. For more information, see Use datareader to retrieve data.

Security descriptionWhen using Set the commandtype attributeTextWhen running the Data command, check the information sent from the client and then pass it to the database. Malicious users may attempt to send (insert) modified or other SQL statements to gain unauthorized access or damage the database. Before transmitting user input to the database, always ensure that the information is valid; if possible, always use parameterized queries or stored procedures, which is the best measure. For more information, see script exploitation.

If you need to execute multiple operations (for example, read some rows and update them), you can use multiple data commands, one for each operation. Each operation is executed separately. For example, to read multiple rows, you should open the connection, read these rows, and then close the connection. If you need to update the data, open the connection again, execute the update, and then close the connection again.

A Data command can contain multiple parameters (specifically, a set of parameter objects). You can use these parameters to create a parameterized query similar to the following:

Select * From customers Where (customer_id = @customerid)

Then, you can set these parameters and run the command to return or update the required data.

Data can be cached in a dataset.

The most common data task is to retrieve data from the database and perform some operations on the data: display data, process data, or send data to another component. Often, applications need to process more than one record, but a set of records, such as the customer list or today's order. This group of records required by applications usually comes from multiple tables: my customers and all their orders; all the authors named "Smith" and their books; and other similar record groups.

Once these records are obtained, applications usually use them in groups. For example, an application allows users to browse all authors named "Smith", check a Smith book, and then check the next Smith book.

In many cases, it is impractical for each application to return to the database when processing the next record. (This may lose many of the benefits of minimizing the need to open connections .) Therefore, the solution is to temporarily store records retrieved from the database and then use this temporary set.

This is the concept of a dataset. A dataset is the cache of records retrieved from the data source. It works like a virtual data storage zone: a dataset contains one or more tables (these tables are based on tables in the actual database ), in addition, it can contain information about the relationship between these tables and the constraints that the table can contain data.

Data in a dataset is usually a very simplified version of the database content. However, you can operate a dataset in a similar way to the actual data operation. In this way, you are not connected to the database, so that the database can freely execute other tasks.

Of course, you often need to update the data in the database (although not as frequently as retrieving data from the database ). Update operations can be performed on the dataset, and these updates can be directly written to the basic database.

Datasets are the passive containers of data. To actually retrieve data from the database and (optional) write data back to the database, use the data adapter. The data adapter contains one or more data commands that are used to fill a single table in the dataset and update the corresponding table in the database. (The data adapter usually contains four commands for selecting, inserting, updating, and deleting rows in the database .) ThereforeFillMethod, it may executeSELECT au_id, au_lname, au_fname FROM authors And other SQL statements.

Because a dataset is actually a private copy of database data, it does not necessarily reflect the current status of the database. If you want to view the latest changes made by other users, you can call the appropriateFillMethod to refresh the dataset.

One convenient way to use datasets is that components can exchange datasets as needed. For example, a business object in the middle layer can create and populate a dataset, and then send it to another component at another location in the application for processing. This function means that the component does not have to query the database separately.

Datasets are independent of data sources.

Although a dataset is a cache of data obtained from a database, there is no actual relationship between the dataset and the database. A dataset is a container that is filled with SQL commands or stored procedures executed from the data adapter.

Because a dataset is not directly bound to a data source, it is a good integration point for data from multiple sources. For example, some data in a dataset may come from a database, while others may come from non-database sources such as another database or workbook. Some data in the dataset may come from the stream sent by another component. Once the data is in the dataset, you can use a consistent object model to operate it no matter what its original source is.

Keep data as XML

Data needs to be moved from the data storage area to the dataset and from the dataset to various components. In ADO. NET, the format of transmitted data is XML. Similarly, if you want to keep data (such as in a file), store it as XML. If an XML file exists, you can use it like any data source and create a dataset from it.

In fact, in ADO. NET, XML is the basic data format. The ADO. NET data API automatically creates XML files or streams with information in the dataset and sends them to another component. The second component can call a similar API to read XML back to the dataset. (Data is not stored in the dataset in XML format (for example, data in the dataset cannot be analyzed using an XML analyzer), but is stored in a more effective format .)

XML-based data protocols provide many conveniences:

• XML is a standard industrial format. This means that your application data component can exchange data with any other component in any other application as long as the component understands XML. Many applications are written as understandable XML, providing an unprecedented level of exchange between heterogeneous applications.
• XML is text-based. The XML Representation of the data does not use any binary information, so that it can be sent through any protocol (such as HTTP. Most firewalls block binary information. However, by formatting the information into XML, components can easily exchange information.

For most solutions, you can use data in ADO. NET without knowing XML. ADO. NET automatically converts data to XML or from XML as needed. You use common programming methods to interact with data.

Architecture Definition Data Structure

Although you do not have to know anything about XML, you can read and write databases and use datasets. However, in some cases, using XML is your goal. If you do not want to access the data but want to design the data. In other words, XML is directly used when you use metadata in ADO. NET.

The dataset is represented in XML. The structure of a dataset (which tables, columns, data types, and constraints are defined in the XML Schema Definition Language (XSD. Just as the data contained in a dataset can be loaded and serialized from XML to XML, the structure of the dataset can also be loaded and serialized from the XML schema to the XML schema.

You do not have to delve into the architecture for most of the operations you perform on data in ADO. NET. Generally, Visual Studio. NET generates and updates the architecture as needed based on the operations you perform in the visualization designer. For example, when you use these tools to create a dataset that represents a table in the database, Visual Studio. NET generates an XML schema that describes the dataset structure. Then, the XML schema is used to generate a Typed Dataset. The data elements (tables, columns, and so on) in the dataset can be used as first-class members. For more information about typed datasets, see dataset introduction.

However, you may want to create or edit the architecture on your own. A typical example is to develop the architecture with a partner or customer. In this case, the architecture will serve as a contract between you and your partners regarding the XML-based data shape to be exchanged. In this case, you must map schema elements to your dataset structure frequently. For more information about the design architecture, see XML Architecture and Data.

Components of ADO. NET

The following illustration shows the main components of the ADO. NET application.

ADO. NET data component

The following table summarizes the preceding ADO. NET data components and provides links to more information.