Ado. Net designed for ADO programmers)

Abstract:This article discusses how to use ADO. Net to perform basic database operations and when to use ADO. net instead of ADO.

Directory

. NET data access
Read data
Dataset, datatable, and recordset
Convert existing code
Update Data
XML extension support
Summary

Open Database Connection (ODBC) Application launched several years agoProgramSince APIs, various database access technologies have emerged, and ADO. NET is the latest one. Many interesting things have taken place in this process. For example, com broke into the database field and began to cultivate the colonial process of ole db. Then, ActiveX Data Objects (ADO), which is roughly equivalent to ole db automation, is elected to govern Visual Basic and ASP of Windows database developers.Community.

Through. net, Microsoft is providing a general framework (framework class library), which will include all existing windows APIs and more. It is particularly worth mentioning that it includes a large number of frequently-used libraries, which need to be obtained separately through each COM object. In these libraries, you will find that the XML and ADO object models are integrated into the class subtree called ADO. net.

Ado. Net has actually become the basis for building data-aware. NET applications. Unlike ado, ADO. Net follows a more general principle and is not so dedicated to databases. Ado. Net is a collection of all classes that allow data processing. These classes represent data container objects with typical database functions (such as indexing, sorting, and views. Although ADO. NET is an authoritative solution for. NET database applications, it is not as database-centric as the ADO model in general design, which is a major feature of ADO. net.

Ado. Net differs greatly from ADO. Ado. Net is a new data access programming model that requires developers to fully understand, invest, and think about. However, once you start to master ADO. net, you will realize that the original ADO skills help you create effective applications and solve various Old Problems in a different, but more clever and reliable way.

In this articleArticleI will focus on how to implement basic database operations in ADO. Net mode. I would like to explain when ADO. Net is a better choice than ado, and when should you give up ado. Ado. Net does not improve ADO to conform to the. NET infrastructure. Just take a look at the ADO. Net syntax,CodeDesign and transplantation will understand this point.

. NET data access

The way to access the data source in ADO. NET is determined by the hosting provider. In terms of functionality, hosting providers are very similar to ole db providers, but there are two important differences. First, manage the provider to work in the. NET environment, and retrieve and publish data through the. NET class such as datareader and datatable. Second, because their architecture is optimized for. net, it is relatively simple.

Currently, ADO. NET provides two types of hosting providers: one for SQL Server 7.0 or later, and the other for all other OLE DB providers that you may have installed. In both cases, you use different classes, but follow similar naming rules. Except for the prefix, the names are the same. The prefix of the previous case is SQL, and the latter case is ADO.

You should use the SQL class to access the SQL Server table because they directly enter the internal API of the database server and skip the intermediate layer represented by the OLE DB Provider. The ADO class is the. net interface on the ole db provider, which uses the com InterOP bridge for work.

ADO. net object beginners can refer to the Omri gazitt Article introduction ADO +: for Microsoft. net Framework Data Access Service (English) and my ADO + promote the evolution of data types (English. The former is highly technical and provides a high-level commentary overview for the ADO. Net program model. The latter mainly introduces the goal of ADO. NET and its connection with XML, scripts, and other technologies.

Read data

The ADO. NET application that needs to read data from the data source must first create a connection object. The connection object can beSqlconnectionOrAdoconnection. Remember, you can use the ADO. Net class to connect to the SQL Server database, but we do not recommend this. The only drawback is that your code passes through unnecessary additional code layers. It first calls the hosting provider of ADO, then the hosting provider calls the SQL Server OLE DB Provider. The SQL server hosting provider and the OLE DB Provider operate data directly.

The significant difference between ADO. NET and ADO. net connection objects is that ADO. net connections are not supported.CursorlocationAttribute. Note that this is not a document error, but a controversial design issue. To highlight the data-centric principle, ADO. NET has no explicit implementation of cursors.

In ADO, you are used to using cursors to extract records from databases or any other ole db-compatible data sources. You can select a client or server cursor. Each cursor has several preset cursor types. ADO. NET is designed to extract data from data sources and provide new programming interfaces to read and analyze data.

In ADO, you specify the connection and command text to createRecordsetObject. Recordset has a certain policy on the cursor position and type. You can read data in one of the following ways:

• Create a static copy of the selected records in the memory, and then process the records as needed when they are disconnected from the data source. ADO is called a static cursor.

• You can use a fast and forward-only read-only cursor to scroll data. This cursor works in the static snapshot of the record. ADO is called a read-only cursor.
• Access data through two cursors on the server. These cursors need to be well connected, but you can detect changes of other connected users at any time at different levels. Ado calls them key sets and dynamic cursors.

The first two methods work in the disconnected record set and read information from the client cache, which is similar to each other. In addition, in the Web-oriented environment and for newNLayer system, the two methods prove to be the most frequently used.

In ADO, all these methods correspond to different types of cursors. You will find later in this article that, although ADO. NET is quite different, it can implement any function that you can implement with ADO. However, your code extracts data from the actual data source and its physical storage media and format.

ADO. NET provides two objects to process data extracted from the data source. They areDatasetAndDatareaderObject. The former is the cache recorded in the memory, and you can freely access and modify it in any direction. The latter is a highly optimized object, designed for Rolling read-only records in only forward mode. Note:DatasetIt looks like a static cursor, but in fact, what corresponds to the ADO read-only cursor in. NET isDatareaderObject.

Server-side cursors are not supported in ADO. net. However, this does not mean that you cannot use a cursor. You need to import the ADO Type Library in. net. In the project windowReferencesRight-click the node. After the import, you can start to use the local ADO object in the application.

Although I admit that it is difficult to turn to. net, I personally recommend that you consider using. Net to rewrite existing applications. Full import of ADO can be taken as the first step towards. net, without the need to invest too much time and resources. However, remember that this is only the first step on a long journey. This is by no means the only step towards. net .. The real reason for net's value is that it provides unified and consistent programming interfaces and is widely used for local classes. You can import com-type libraries, but importing com-type libraries can only be used as temporary solutions or intermediate steps. We do not encourage this.

When using ADO. net, we should take into account the fact that it unifies the programming interfaces of Data containers. No matter what applications you plan to write, Windows Forms, web forms, or web services, you can use the same group of classes to process data. Whether the backend data source is an SQL Server database, OLE DB, XML file, or an array, you can use the same method and attributes to scroll and process their content.

Figure 1: Solution Explorer menu

If you insist on using ADO in. net, be prepared to face some side effects. For example, you need additional code to use the record set from the data binding control.

Dataset, datatable, and recordset

In ADO. netRecordsetObjects. The closest isDatatableObject. Although the functions of these two objects are almost the same, they play different roles in their respective frameworks.

RecordsetIt is a large object with many ADO functions, but it is still lacking.RecordsetExcellent performance in many aspects, such as creation, working During disconnection, and rich functions. However, improvements are still needed in some aspects. For exampleRecordsetThe inherent com feature makes serialization over the network very heavy. Another example is that it is a binary object, so it is difficult for modules running on different platforms to share it, and it cannot pass through the firewall. In addition,RecordsetIndicates a single table with multiple records. If the table is generated by one or more joins, it may be difficult to update the original data source. If you want to coordinate the disconnected record set with the original data source, the data source must be able to recognize the SQL. However, your record set may be created through a non-SQL provider.

In ADO. net, all functions of ADO recordset are split into several simple objects,DatareaderIs one of them.DatareaderSimulate a fast and forward-only read-only cursor operation.

DatatableIs a simple object that represents the data source. You can manually constructDatatable, You can also useDatasetCommand to automatically fill it.DatatableIt does not distinguish the sources of the data it contains. This object allows you to process data in the memory and perform operations such as browsing, sorting, editing, applying filters, and creating views.

Ado does notDatasetCorresponding object.DatasetAn object is a container class and a key object for extracting ADO. NET data.DatasetSet one or moreDatatableObject group.DatatableExpose its content through a common set such as rows and columns. When you try to read data from a data table, you may go through two different object layers:DatatablemappingAndDataview.

DatatablemappingObject describes the data columns andDatatableMappings between objects. When fillingDataset,DatasetcommandThe object must use this class. It maintains links between Abstract columns in the dataset and physical columns in the data source.

View of the tableDataviewObject implementation. It indicatesDatatableYou can bind a custom view 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.

DatasetAll tables in can be put into the link through a public domain. This relationship consistsDatarelationObject Management. This seems like the formation of ADO data, but there is an important difference. You do not need to use data to form a language. In the end, you will have a flexible structure. The ADO. Net navigation model allows you to easily move data from the primary row in a table to all its child rows.

DatarelationThe object is equivalent to the implementation of the join statement in the memory and can be used to establish the parent/child relationship of Columns with the same data type. Once a link is established, no changes that may damage the link are allowed. Views and relationships are two ways to implement the architecture of the master table/detail table. Remember, a view is only a mask on the record, and a link is a dynamic link between one or more columns in two tables. If you use a link, you cannot change the order or set conditions.

If your code requires a one-to-one foreign key relationship without changing data, you 'd better not use a join command without a format. If you need additional filtering functions, you should use the ADO. Net custom view.

Convert existing code

Many ASP pages use ADO objects to extract data. Let's discuss several typical situations that you may encounter when porting and adapting code in the near future.

If you have an ASP page for generating reports from a single record set,DatareaderObjects will be your best partner.
You browseDatareaderObject, it will output the results to the page.

 
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"] method response. write to output data}
 

You can also useHasmorerowsQuick attribute checkDatareaderWhether it is null. If you only need to quickly browse a series of recordsDatareaderBetter and faster objects. It can also be used to query a single record. You cannot editDatareaderBut you can move the content into objects that are easier to manage, suchDatatableOr one or moreDatarowObject.

Datareader is no longer an appropriate tool when you need to process the complex relationship between tables and records. In ADO, you need to process the record set. The more data model links you have, the more complex SQL commands you have. The navigation model is still sequential, and the data that is finally cached is usually more than what you need.DatasetAndDatarelationObjects are the basis of this table relationship model.

To manage parent/child relationships, ADO also encapsulates data formation engines. In terms of functions, data formation has the same relationship with ADO. net. However, from the design perspective, they have almost nothing in common. Form a record set to embed all information into a single list object. Ado. Net is a dynamic link that you can establish between two data tables at any time. To create a hierarchical record set during the execution of a single ADO command, ADO relies on the shaping ole db service provider and uses a specific SQL-like language.

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,DatarelationObjects can be changed layer by layer from parent row to child row. You canForeignkeyconstraintAdd objectDatatableOfConstraintsSet to perform this operation.ForeignkeyconstraintObjects indicate the constraints 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.

In addition, links cannot be transmitted. You can establish two groups of different relationships, such as relationships between customers and orders, orders, and products. However, when you navigate to an order to find a customer, you cannot jump from an order to a related product line. You must also open the order/product relationship, locate the order you need, and then get the relevant line. This is why sometimes it is better not to use the original non-format SQL join statement to implement a one-to-one relationship.

Do I need to store records in ASP session objects? Use ADO. NET andDatasetObject, you can perform safe operations without having to store the ADO record set in git, which may lead to access conflicts (in English) and thread similarity issues.

Update Data

When updating data, web applications usually use non-formatted SQL statements, or use better parameterized stored procedures. However, when you need to use unconnected data, you may want to use the built-in service to update all records that need to be modified. ADO provides a batch update mechanism to implement this function.

UpdatebatchThe method is used to save the data in the copy buffer.RecordsetChange 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 factUpdatebatchMethod. An error is returned when any change fails. Then, you canErrorsSet andErrorObject.

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 the callUpdatebatchEverything that happens afterwards. 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 problem is that you cannot control the SQL statements actually used for changes. The server-side update code is not better than the code you write. If you use a non-SQL provider, it cannot even run. At the beginning of this section, I have mentioned that Web applications usually update data through parameterized stored procedures. However, if you use batch update, it is different.

In ADO. net, this model has been extended. Now it adopts a more general architecture, allowing you to specify basic operation commands, such as insert, delete, update, and select. The intention is obvious: No matter what data source, you can extract data from it and provide the same support. Batch update in ADO. net, you need to createDatasetcommandObject isSqldatasetcommandOrAdodatasetcommand.

Note:In beta 2,DatasetcommandObject will be calledDataadapterObject.

YesDatasetcommandObject, you can call itsUpdateMethod.DatasetcommandProvideInsertcommand,Deletecommand,UpdatecommandAndSelectcommand. They are bothCommandObject. However, you do not need to set them unless the default behavior does not meet your needs. This is the same as in ADO. InUpdateIf no xxxcommand attribute is set but the primary key information existsCommandObject. Note that to make the above process correctly, you must set a primary key for the involved data table.

The following code sets a primary key for the dataset employeeslist table:

Datacolumn [] keys = new datacolumn [1]; keys [0] = m_ods.tables ["employeeslist"]. columns ["employeeid"]; m_ods.tables ["employeeslist"]. primarykey = keys;
 

The primary key is basicallyDatacolumnAn array of objects.

If you want to use stored procedures to update Tables or use specialized non-SQL data providers, you will often use these command attributes.

XML extension support

In ADO, XML is only in the input and output formats. However, in ADO. net, XML is a data format that provides a means to operate, organize, share, and transmit data. Any importDatasetRegardless of the source, the data can be processed through the dual-side programming model. You can access the information sequentially or by row, or by using the non-sequential and hierarchical paths driven by the XML Document Object Model.

DatasetRead and Write Data and architecture as XML documents. Both data and architecture can be transmitted over HTTP and used on all platforms that support xml. The same data can be presented in different architectures at different times, which is implemented through XSLT. You can useReadxmlschemaMethod compiling architecture. The XML architecture includes the description of the tables in the dataset and the relationship and constraints of the tables. Before callingReadxmldataMethod FillingDatasetYou should complete this step before.

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 (); // load XML data and architecture streamreader SR; Sr = new streamreader (server. mappath ("data. XML "); data. readxml (SR); Sr. close (); // Add the new record 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 ();} // refresh the UI (composed of grids) 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, no XML node orXmldomMethod. 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: updatable table example

Summary

The success of Web applications has changed the face of a typical distributed system. Currently, most distributed systems useNLayer 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 to unify some of today's best practices under. net. This programming model for data access is comprehensive and powerful. However, this model may not be able to meet everyone's requirements. In the future model design, we still need to take a big step. However, remember that currently ADO. NET is only a beta version with limited documentation support.

ADO programmers benefit the most from the beta version because they are familiar with many aspects of ADO. net, including the highest level of abstraction, which is an enlightening model. The ADO. Net code is not compatible with the existing ADO code, but has similar functions. To make full use of ADO. net, you should spend some time understanding the concept itself, not just finding the fastest way to transplant code. No matter which. NET programming model you choose, Windows Forms, web forms, or Web Services, ADO. NET will help you deal with data access problems.