Ado. NET programming Beauty----data access methods (connection oriented and no connection oriented)

　　Recently, when learning ADO, the data access method was mentioned: connection oriented and non-connection oriented. So, Baidu a bit, found that there is no good information, however, in the school library found a good book ("ASP. NET MVC5 Web site development of the United States", of course, I am not advertising, only because it is really OK), the difference between the two and the principle of speaking very clearly, the following we will enter the theme. I'm in a good mood today ....

We all know that ADO provides a data access interface to a database or an external data source, which itself implements a connection-oriented and non-connected approach to data access. Connection-oriented is based on the database connection, after opening the database connection, after the data access instruction into the database execution, the use of cursors to access the result set, the advantage is fast access, the disadvantage is the need to establish a connection, and there will be a locking problem; The data is stored in memory, and then through a series of objects, properties and methods such as data access, and these access work is in memory, and the database does not maintain a connection, the advantage is to save the database connection resources and no access to the locking problem, but the disadvantage is to consume memory, and updates to the data are not reflected in the database.

Let's take a look at the specific principles below.

　　Connection-oriented operations

　　　　　The connection-oriented operation starts with the IDbConnection interface, opens the database connection through open (), closes the connection with close () and frees the resource after the operational database completes. If a database transaction is required, start the transaction with the BeginTransaction method after the connection is opened and get the object for the IDbTransaction interface. The action of a transaction is generally not complete or all fails, so if any error occurs during execution or if a transaction needs to be undone, rollback () can be used to undo it, and a commit () is used to prompt the execution to complete. Ado. NET is also a layer of isolation that allows transaction processing to be set up when transactions are started.

ExecuteReader () returns the IDataReader interface object, the IDataReader interface object represents a one-way forward cursor returned by the database, and a one-way forward cursor is read-only, so the IDataReader interface object cannot be manipulated directly to modify the data. The only way to modify the data is to execute the three SQL instructions for INSERT, update, and delete, which allows the program to access the database much faster. And ADO is no longer required to maintain the data set cursor relative saving resources ExecuteReader () is the only way to get the Idatereader interface object, and IDbCommand other methods, such as Execuenonquery () and ExecuteScalar () call ExecuteReader () to execute the instruction, but the last value returned is different.

The use of the Idatereader interface object is fairly straightforward, and each time the Read method is called, the cursor moves forward one row (each line is a Idatereader interface object), and after the cursor is moved, the value of the field can be obtained by GetValue (). Or you can use GetOrdinal () to get the index number of a field, or use IsDBNull () to determine whether a field is a null value, and so on.

　　For non-connected operations

　　　　To support ADO for non-connected objects. NET provides the Idateadapter interface and the IDbDataAdapter interface object to support the padding and updating of offline data. The IDataAdapter interface contains fill () and update () to control the filling and updating of the data respectively, while the IDbDataAdapter interface object contains SelectCommand, InsertCommand, UpdateCommand and DeleteCommand etc 4 instructions to support 4 tasks such as CRUD for offline data.

Offline functionality is one of the features of ADO, which is provided by a dataset and a DataTable, which can be treated as a database in memory, a DataTable in memory, a dataset that can hold multiple DataTable, Just as you can hold multiple data tables within a database, and you can also have conditions and associated settings between the DataTable.

The data for the dataset is obtained for the fill () of the IDbDataAdapter interface object that is not connected, and IDbDataAdapter generates the corresponding DataTable using the results of the select instruction that is contained in the SelectCommand property. , add to the dataset, or generate a DataTable directly with fill (), and then manually add it to the dataset. The dataset itself also supports input and output to the XML data, as long as the ReadXml () is called to generate a dataset from the XML data, or call WriteXml () to generate XML data, and the DataTable has the same method. Datasets and DataTable also support merging functions, and the merge () dataset and DataTable allow the program to merge two datasets or rows of data. At the same time, depending on the method defined by MissingSchemaAction, how to deal with missing or unable corresponding structure fields.

A DataTable is an object that actually stores data, which is itself a container containing a large collection of objects, containing the DataColumn object that defines the field and the DataRow object that loads the data, each of which represents a data row , so the judgment and operation of the data rows can be found in the members of the DataRow, such as IsNull (), which determines whether the field is a null value, returns the ItemArray property of all data, and determines whether there is a change in the RowState property. The DataColumn object is the data type and format definition for the Division field, and the fields within the DataRow are essentially the same as the DataColumn objects of the DataTable's Columns property content.

In addition to the basic data management, the DataTable also supports the search and summarization of data, Select () allows the program to retrieve rows of data using search instructions, and returns a conforming set of data, and Compute () allows the program to summarize data rows using summary directives. such as Sum or AVG, and other instructions. The DataTable can also be associated with other DataTable, the association object is provided by the DataRelation object, however, the DataRelation object only records the association between the DataTable, and is not actually responsible for the control of the data. Similar to the Datarelaion object is the constraint object, which allows the program to restrict data within the data column, such as foreign key restrictions or uniqueness restrictions, which are automatically checked by the DataTable when the data row is operational. Offline data can also support a specific range of processing capabilities, which is provided by DataView, and the RowFilter property within the DataView object can be set to DataView how to filter rows of data within a DataTable. Or use the RowStateFilter property for RowState filtering. DataView also provides the ability to create and modify, or to assemble filtered data columns into a new DataTable.

Ado. NET programming Beauty----data access methods (connection oriented and no connection oriented)