Introduction to ADO. NET

ADO. NET will be used to build data awareness.. NET application. unlike ADO, ADO. NET is more universal, not so specific to the database design. ADO. NET aggregates all classes that can process data. these classes present data container objects with typical database functions, such as indexing, sorting, and browsing. despite ADO. NET is important.. NET database application solutions, which show more comprehensive design, not only as database-centric as the ADO model ..

ADO. NET is very different from ADO. it is a brand new access programming model. when you start to use ADO. NET, you will find that any ADO skills you have mastered will be of great help in building effective programs and solving problems, it can help you develop more flexibly and securely in solving problems.

ADO. NET is not an improved version of ADO to adapt to. NET infrastructure. After learning about the syntax, code design, and porting of ADO. NET, you will understand it.

1. NET data access

Access ADO. the data source in. NET is controlled by the hosting provider. although there are two major differences between managed providers and ole db, they are extremely similar. first, host the provider in.. NET environment, through DataReader and DataTable.. NET class to retrieve and display data. second, their architecture is relatively simple because they are used to adapt. NET.

In this case, ADO. NET is divided into two different types of hosting providers: one for SQL Server? Version 7.0 or later. It is also applicable to all ole db providers that you may have installed. although the classes used in the two managed providers are different, they all follow a similar naming method. in addition to the prefix, other names are the same. in the previous case, the prefix is SQL, and the latter is ADO.

You need to use the SQL class to access the SQL Server table, because the SQL class skips the intermediate layer presented by the OLE DB provider and directly enters the internal API of the database Server. the ADO class is located at the top of the ole db Provider. NET interface, using the COM Interop bridge for work.

For more information about ADO. NET, read Omri Gazitt's article, which mainly introduces ADO +:

About Microsoft .. the database Access Services in the. NET Framework, and the discussion about ADO + in my article mainly points out the evolution of Data types. the former is more purely technical and provides a higher level of information about ADO. NET Programming Model overview. The latter mainly explains the goals of ADO. NETR and its connection with XML, scripts, and other technologies.

2. Read data

ADO. to read data from the data source, you must first create a connection object. the connection object can be SQLConnection or ADOConnection, depending on the target provider. it should be noted that, although not recommended here, you can also use ADO. to connect to the SQL Server database. the only disadvantage of this method is that the code needs to pass through an unnecessary additional code layer. first, it will call the hosting provider of ADO, and then the hosting provider of ADO will call the ole db provider of SQL Server. as the ole db provider does, the SQL Server hosting provider operates data directly,

About ADO and ADO. the biggest difference between. NET and the connection object is ADO. NET connection does not support the CursorLocation attribute. this is not so much a document BUG as a controversial design issue. to enforce its data-centric benchmark, ADO. NET.

In ADO, you are used to extracting records from databases or other ole db-compatible data sources by using cursors. You can select a client or server-side cursor, each cursor has several preset cursor types. in ADO. NET extracts data from data sources and provides new programming interfaces for reading and analyzing data.

In ADO, you can create a Recordset object by specifying the connection and command text. There are some rules on the cursor location and type Recordset. You can read data by following the method below.

Create a static copy of the selected record in the memory, and then process the copy when it is disconnected from the data source. ADO is called a static cursor.

You can use a fast, forward-only, read-only cursor to scroll data. ADO is called a forward-only cursor.

Accessing data through a server-side cursor requires a good connection, but you can detect information input by other connected users at different levels. ADO is called dynamic cursor.

The first two operations are performed on the disconnected recordsets and the information is read from the client cache. In addition, the first two methods are often used in WEB-oriented environments and new n-tier systems.

In ADO, all these methods correspond to different types of cursors. in this article, you will find that, ADO. NET is different from ADO, but it has all the functions of ADO. in contrast, your code will extract data from the actual data source and its physical storage media and format.

ADO. NET allows DataSet and DataReader to extract data from the data source. the former is the cache recorded in the memory. You can access and modify it in any direction. the latter is a highly optimized object and moves forward only in the read-only record set. Note: DataSet looks like a static cursor, but in fact, in. NET, it corresponds to the ADO read-only cursor as a DataReader object.

In ADO. NET, although it does not support server-side cursors, it does not mean that you cannot use cursors. in fact, the steps you need to do are. NET input ADO library. you only need to right-click the references node to run the local ADO object in your own program.

But I personally think that when you want to turn. NET, please consider carefully. first, you must enter ADO completely, which does not take much time and effort. NET ,. however, this is only the first step of the long journey and the path to it. NET step .. NET is based on a uniform, continuous and stable interface and a wide range of local classes. COM libraries can be supported, reasonable, but not encouraged, because it is only a short-term solution or a transitional step.

When you want to start using ADO. NET, consider the fact that ADO.. NET unifies the programming interfaces of Data containers ,. therefore, no matter what type of program: Windows Form, Web Form, or Web Service, You have to process related data in the same group of classes. whether the backend data source is SQL Server database, or OLE DB provider, XML file, or array, you can use the same method and attribute for processing.

Figure 1. Solution Explorer menu

If you insist on using ADO in the. NET world, be prepared to face some other influences. For example, you need additional code to use recordset from the data binding control.

3. DataSet, DataTable, and Recordset

Recordset object ., ADO. NET does not directly correspond to it. the closest is the DataTable object. although they have almost the same functions, they play different roles in their respective frameworks.

Recordset is a very large object with most ADO functions, but it is still lacking in some aspects. recordset has excellent performance in some aspects, such as: It is creative, it can be operated offline, and has many features, but still needs to be improved in some aspects, such as: Based on its inherent COM features, recordset is difficult to attach on the network; Recordset is a binary object, so modules on different platforms are difficult to share it; And snakes cannot pass through the firewall. in addition, it represents a single record table. if the table is used as a JOIN result, it is difficult to update the original code source. when you try to unify the offline recordset with the original code source, the data source must be able to identify the SQL. in any case, your recordset can be created by a non-SQL provider.

In ADO. NET, all functions of ADO Recordset are split into several simpler objects: one of them is DataReader. DataReader simulates fast, read-only, and only forward read-only cursor operations.

DataTable represents the data source and is a simple object. you can manually construct a able or use the DataSet command to automatically generate a DataTable. dataSet does not know much about the data it contains. it allows you to process data in the memory, or other tasks such as sorting, editing, filtering, and creating browsing.

A DataSet object is a data container class that implements ADO.. NET key object for data extraction. dataSet aggregates one or more able objects. dataTable exposes its own content through a common set such as a row or column. when you try to read data from a data table, you may be passing through two different layers: ableablemapping and DataView.

The DataTableMapping object contains the data columns in the data source and datating relationships between DataTable objects.

This class is used by the DataSetCommand object when DataSet is filled. It maintains links between Abstract columns in the dataset and physical columns in the data source.

The table view is implemented through the DataView object. It indicates the custom view of the DataTable and can be bound to a specific control (such as a data grid in a Windows form or a Web form. This object is equivalent to the implementation of the SQL CREATE VIEW statement in memory.

All tables in DataSet can be associated with each other through a public domain. the relationships between them are managed by the DataRelation object. this is like the formation of ADO data, but there is still the biggest difference.

In DataRelation, you do not need to use data to form a language, but also have flexible organizational structures. through ADO. NET navigation model, you can easily move from the primary row in a table to all its child rows.

DataRelation object is about the implementation of the JOIN statement in the memory. It can be used to establish a parent/child relationship with the same data type ,. once a link is established, any modification that damages the link is forbidden. views and relations are two methods to complete the master/detail architecture. remember that the view is only a mask loaded on the record, but the relation

It is a dynamic link between several columns. In relation, you cannot change the order or set conditions.

If your code requires a one-to-one foreign key relationship and changes data, it is best not to use the JOIN command. if you need additional filtering functions, You can seek ADO.. NET custom views.

4. Convert existing code

A large number of ASP pages use ADO objects to extract data. Let's review several typical cases, which may be helpful for processing porting or rewriting code in the future.

If you have an ASP page for generating reports from a single recordset, DataReader will be your helper.

{
String strConn, strCmd;
StrConn = "DATABASE = MyAgenda; SERVER = localhost; UID = sa; PWD = ;";
StrCmd = "Select * From Names where ID =" + contactID. Text;
SQLConnection oCN = new SQLConnection (strConn );
SQLCommand oCMD = new SQLCommand (strCmd, oCN );
OCN. Open ();
SQLDataReader dr;
OCMD. Execute (out dr );
While (dr. Read ()){
// Use dr. GetString (index) or
// Dr ["field name"] to Response. Write data
}

You can use the HasMoreRows attribute to quickly check whether the DataReader is empty. if you only process a series of records, nothing is more than DataReader. faster, better object. It is also suitable for querying a single record.

The content of DataReader. cannot be edited, but you can move the content to objects with more management functions, such:

DataTable or one or more DataRow objects.

When you need to process the complex relationship between tables and records, DataReader is not a proper tool. The more data model links, the more complex SQL commands are. The navigation module maintains continuity, and the data stored in the cache is often more than what you need. DataSet and DataRelation objects are the basis of this table relationship model.

To manage the parent/child relationship, ADO also encapsulates the data-shaping engine. in general, data shaping and ADO. NET relationship is one thing. in terms of design, the two have almost nothing in common. the Shaped recordsetsct embedded list object contains information about all data tables. ADO. NET is a dynamic link. You can establish a link between two data tables at any time. ADO relies on the Shaping ole db service provider and uses specialized SQL language features to generate a hierarchical recordset when executing a single ADO command.

In ADO. NET, every object involved in a link is always considered as a separate individual. The link itself is exposed as an object and has certain behavior rules. For example, a DataRelation object can be changed layer by layer from the parent row to the Child row. You can add the ForeignKeyConstraint object to the Constraints set of the DataTable. The ForeignKeyConstraint object indicates the constraint on a group of columns associated with foreign key relationships when values and rows are deleted or updated. As mentioned above, once a link is set up, you cannot make changes that may damage the link until it is terminated by program preset.

As mentioned earlier, once a relationship is set, unless it is a procedural termination and you cannot modify it, it will suddenly interrupt.

In addition, relations are not deferred. you can set two different relationships between MERs and Orders, and between Orders and Products. however, when you navigate to orders for a customer, you cannot jump from an order to the related products line. the solution is to open the Orders/Products relationship, lock the order you need, and then obtain the relevant rows.

Do programmers need to store records in ASP Session pairs? With ADO. NET and DataSet objects, you can work securely without any trouble in "Storing an ADO Recordset in GIT Might Cause An Access Violation.

5. Update Data

Web programs usually update data using program-free statements or parameter-based stored procedures. however, when you encounter offline data, you may want to use the built-in service to update all records that need to be modified. to complete this task. ADO provides a batch update mechanism.

The UpdateBatch method is used to send the Recordset changes saved in the copy buffer to the server to update the data source. It uses open locks to allow all pending local changes. It also sends all changes to the data source in a single operation. Open locks occur only when the data source is locked for the record to be changed after the change is submitted. Open locks allow two users to access the same record at the same time, but changes entered by one user will soon be overwritten by another user. Of course, this method requires the data source to detect and prevent data conflicts. The entire data source is also required to be stable without frequent changes. Otherwise, it is hard to imagine that the cost of coordination will soon exceed the savings brought about by the replacement of strict locking. In fact, using the UpdateBatch method will return an error when any change fails. Then, you can access the Error through the Errors collection and Error object.

To understand why the ADO. NET model is a more powerful tool for updating data, it is critical to understand the working principle of open locking in ADO. In ADO code, you cannot control what happens after UpdateBatch is called. That is to say, the update is performed on the server by rolling the modified rows and then comparing the original value with the current value in the corresponding record in the data source. When all values are consistent, an appropriate SQL statement (INSERT, UPDATE, or DELETE) is executed on the table ).

The preceding statement shows that you cannot control SQL statements. The update code on the server side is neither better than what you write, nor works when you use a non-SQL provider. At the beginning of this chapter, I have already mentioned that Web applications are a typical process of updating data through parameterized storage processes. However, if you use batch update, the situation will be different.

In ADO.. NET, the model has been extended. now, it adopts a more general architecture, through which you can define your own command statements about basic operations, such as insert, delete, update, and select. obviously, you can observe the attempt to extract data from the data source and provide the same support regardless of the nature of the data source. ADO.. NET. You must create a DataSetCommand object: SQLDataSetCommand or ADODataSetCommand.

Note: In Beta 2, The DataSetCommand object is called a DataAdapter object.

Once you use the DataSetCommand object, you can use its Update method. dataSetCommand provides a series of attributes, such as InsertCommand, DeleteCommand, UpdateCommand, and SelectCommand. they are all Command objects, but you cannot set them unless the default settings are not completed as required. this is the same as that in ADO. during the Update process, if the xxxCommand attribute is not set but the primary keyword already exists, the Command object is automatically generated.

The following code sets the primary keyword for the EmployeesList table,

DataColumn [] keys = new DataColumn [1];
Keys [0] = m_oDS.Tables ["EmployeesList"]. Columns ["EmployeeID"];
M_oDS.Tables ["EmployeesList"]. PrimaryKey = keys;

The primary keyword is basically an array of DataColumn objects.

If you want to use stored procedures to update forms, or you want to use a dedicated non-SQL data provider to perform operations, you will often use these command attributes.

6. XML extension support

In ADO, XML is only used as the input and output formats. however, in ADO.. NET, XML provides you with a series of methods, such as manipulating, reorganizing, sharing, and transferring. any data you input into DataSet, whether original or not, can be processed through a dual-sided programming model.

Like XML documents, DataSet reads/writes data and modes. Data and modes can be transferred in HTTP or run on any platform that supports XML. The same data can be executed in different modes in different time periods. You use ReadXmlSchema to write the schema. The XML format contains the name of tables in the data set, just like relations and constraints in the data set. You should complete this step before calling ReadXmlData.

The following code example shows the simplest ASP. NET page for displaying updatable data tables.

<% @ Import Namespace = "System. Data" %>
<% @ Import Namespace = "System. IO" %>

<Script runat = "server" language = "C #">
Void Page_Load (Object source, EventArgs e)
{
DataSet data = new DataSet ();

// Loads XML data and schema
StreamReader sr;
Sr = new StreamReader (Server. MapPath ("data. xml "));
Data. ReadXml (sr );
Sr. Close ();

// Add a new record passed through the URL
If (Request. QueryString. Count> 0)
{
DataTable dt = data. Tables [0];
DataRow dr = dt. NewRow ();
Dr ["FirstName"] = Request. QueryString ["First"];
Dr ["LastName"] = Request. QueryString ["Last"];
Dt. Rows. Add (dr );
Dt. AcceptChanges ();

StreamWriter sw;
Sw = new StreamWriter (Server. MapPath ("data. xml "));
Data. WriteXml (sw );
Sw. Close ();
}

// Refreshes the UI (made of a grid)
Grid. DataSource = data. Tables [0]. DefaultView;
Grid. DataBind ();
}
</Script>

2. You can add new rows to the table. However, it does not involve SQL Server or Access tables. It is just an XML file. In the code that processes it, the XML node or XMLDOM method is not used. You can use the same intuitive data table interface to read and update XML records. You work in the same way as you do in ADO, but the model here is more in-depth, larger, and has more potential for you to explore.

Figure 2. Example of an updateable table

7. Conclusion

The success of Web applications has changed the face of a typical distributed system. Currently, most distributed systems are n-layer systems, which require higher scalability and interoperability. Therefore, non-join Data Processing and XML have become the best practices and are widely accepted in the industry.

ADO. NET tries. the best of NET is integrated. all programming modes for data access are comprehensive and very powerful. this mode may not meet your requirements one by one, but it is a huge step towards the design of the model. Remember ADO anyway. NET is only a test version and only supports limited documentation.

ADO programmers will benefit from this beta version because they are already familiar with ADO.. NET, including the inspiring model, which is the highest level of consumer action. ADO.. NET code is not compatible with the existing ADO code, but its functions are similar. to make full use of ADO. NET, rather than simply calculating the fastest way to place code, it is better to figure out ADO. NET. no matter what ,. the NET programming mode you selected-Windows Forms, Web Forms, or Web Services, ADO. NET will help you with data access.