ADO.NET designed for ADO programmers
Summary: This article discusses how to implement basic database operations in ADO.NET, and when using ADO.NET instead of ADO. Data Access Data in Directory .NET Data Data, DataTable and RecordSet Convert Existing Code Update Data XML Extension Support Summary Since Several An Open Database Connection (ODBC) Application Programming Interface (API), there have been various Various database access technology, and ADO.NET is one of the latest. In this process, many interesting things have occurred. For example, COM breaks into the database field and starting to cultivate the colonial process of OLE DB. Then, the ActiveX® Data Objects (ADO) of the OLE DB Automation Version is then selected to rule the Visual Basic® and ASP communities of the Windows® database developer. Through the .NET, Microsoft is providing a general-purpose frame (ie, Framework Class Library), which will include all existing Windows APIs or even more. It is particularly worth mentioning that it includes a large number of commonly used libraries, and these libraries need to be obtained separately by each COM object. In these libraries, you will find XML and ADO object models, which are integrated into classes called ADO.NET. ADO.NET is in fact becoming the basis for building data perception .NET applications. Unlike ADO, ADO.NET follows the more general principles, not so special for the database. ADO.NET gathers all classes that allow data processing. These classes represent data container objects with typical database functions such as index, sorting, and views. Although ADO.NET is an authoritative solution for .Net database application, it is not like the ADO model as a database center, which is a major feature of ADO.NET. ADO.NET has great differences from ADO. ADO.NET is a new data access programming model that requires a comprehensive understanding, investment and new thinking of developers. However, once you start to master ADO.NET, you will realize that the original ADO skills are very helpful to create effective applications and resolve all kinds of old problems in different, more clever and reliable ways. In the rest of this article, I will focus on how to implement basic database operations in ADO.NET. I want to explain that when ADO.NET is better than ADO, and when you are best to give up ADO. ADO.NET is not improving ADO to match the .NET infrastructure. As long as you look at ADO.NET's grammar, code design and transplant, you will understand this. Data Access in .NET Access the data source in ADO.NET is determined by the managed provider. Functionally, the hosted provider is very similar to the OLE DB provider, but there are two important differences. First, the management provider works in the .NET environment, retrieves and discloses DataReader and DataTable et al. Net class. Second, because their architecture is optimized for .NET, it is relatively simple. At present, ADO.NET provides two hosting providers: a use of SQL ServerTM 7.0 or higher, another for all OLE DB providers you might have installed. In both cases you use different classes, but follow similar naming rules. In addition to the prefix, the name is the same. The preceding 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, skip the intermediate layer represented by the OLE DB provider.
The ADO class is the .NET interface on the OLE DB provider, which works using the COM InteroP bridge. ADO.NET object's beginners can refer to OMRI Gazitt's article introduction ADO : Data Access Service (English) for Microsoft .NET Framework (English) and my ADO evolution (English). The former is technically strong, providing a high level of commentary overview for the ADO.NET program model. The latter mainly introduces the goals of ADO.NET and its links between XML, scripts, and other technologies. Reading data The ADO.NET application that needs to be read from the data source first to create a connection object. The connection object can be SQLConnection or Adoconnection depending on the target provider. Keep in mind that you can use the ADO.NET class to connect to the SQL Server database, but we do not recommend this. The only disadvantage is that your code is to pass unnecessary additional code layers. It first tested the ADO's hosted provider, and then host the provider to call the SQL Server OLE DB provider. The SQL Server managed provider and the OLE DB provider directly operate data. The significant difference between the ADO and ADO.NET connection objects is that the ADO.NET connection does not support the CursorLocation property. Please note that this is not a document error, but a controversial design problem. In order to highlight data-centric principles, ADO.NET has no explicit implementation of the cursor. In ADO, you are accustomed to extracting records from databases or any other OLE DB compatible data sources. You can choose a client or server cursor, each of which has several pre-set cursor types. ADO.NET is designed to extract data from the data source and provide new programming interfaces to read and analyze data. In ADO, you create a Recordset object by specifying connection and command text. Recordset has a certain strategy for the position and type of the cursor. You can read the data as described in the following way:
Create a static copy of the selected record in memory, and then processes these records as needed when disconnecting from the data source. ADO is called a static cursor. By fast, forward only a read-only cursor, this cursor works in a recording static snapshot. ADO called read-only cursor. Access data through both cursors of the server, these cursors need to be well connected, but you can detect changes to other connected users at all times at each different level. ADO calls them for key sets and dynamic cursors. The first two ways work work within the disconnected record set and read information from the client, which is the similarities. In addition, in a Web-oriented environment and for new N-layer systems, these two methods have proven to be the highest frequency. In ADO, all of these methods are corresponding to different types of cursors. You will find later in this article, although ADO.NET is very different, it enables any feature that you can implement with ADO. Only, your code will extract data from the actual data source and its physical storage medium and format. ADO.NET provides two objects to process data extracted from the data source. They are DataSet and DataReader objects. The former is a buffer recorded in memory, and you can access and modify from any direction. The latter is a highly optimized object, which is designed to scroll only the read-only record only forward. Note that DataSet looks like a static cursor, but in fact, in .NET, the DataReader object corresponds to the ADO read-only cursor. In ADO.NET, server-side cursors are not supported. However, this doesn't mean you can't use a cursor. What you need to do is imported the ADO type library in .NET. Click Right button on the References node of the project window. Once imported, you can start using local ADO objects in your application. Although I admit that it is a very difficult thing to turn. Net is a very difficult thing, but I personally suggest that you consider rewritten existing applications with .NET. It can be fully imported as the first step towards .NET, this is not necessary to invest too much time and resources. However, remember this is just the first step on the long road. This is not the only step you move to .NET. The real reason for .NET has value-for-money, in the unified and consistent programming interfaces, and is widely used in local categories. You can import COM type libraries, but import COM type libraries can only be used as a temporary solution or intermediate step, we do not encourage this. When using ADO.NET, it should be fully taken into account in the fact that it uniforms the data container programming interface. Whether you plan to write what application, Windows Form, Web Forms, and Web services, you can process data by the same group. Regardless of the SQL Server database, OLE DB, XML files are still an array, and you can scroll and process them through the same methods and properties. Figure 1: Solution Explorer menu If you insist on using ADO in .NET, please prepare to face some side effects. For example, you need additional code to use a recordset from the data binding control. DataSet, DataTable and Recordset In ADO.NET, there is no object directly with the Recordset object. The closest is a DataTable object. Although the functions of these two objects are almost the same, they do different roles in their respective frameworks. Recordset is a large object, with many ADO features, but still lack. Recordset is excellent in many aspects, such as creative, and can still work, rich in functionality, and so on. However, in some ways still needs to be improved. For example, because of the intrinsic COM characteristics inherent, the work sequences through the network will be very heavy. Another example is a binary object, so the module running on a different platform is difficult to share it, and it cannot pass through the firewall.
In addition, the Recordset represents a single table of multiple records. If the table is generated by one or more Join, the update of the original data source may be difficult. If you want to make the disconnected record set and the original data source, the data source must be able to identify SQL. However, your recordset is likely to be created by a non-SQL provider. In ADO.NET, all features of Ado Recordset are split into several simpler objects, and DataReader is one of them. DataReader simulates the operation of a fast and forward read-only cursor. DataTable is a simple object representing the data source. You can manually construct a DataTable or you can automatically populate it through the DataSet command. DataTable does not distinguish the source of the data it contains. This object allows you to process data in memory, as well as performing views, sort, editing, application filters, create views, etc. There is no object corresponding to the DataSet in the ADO. The DataSet object is a container class that is a key object that implements ADO.NET data extraction. DataSet groups one or more DataTable objects. DataTable discloses its content through a general collection such as rows and columns. When you try to read data from a data table, you may pass through two different object layers: DataTableMapping and DataView. The DataTableMapping object describes the mapping relationship between the data column in the data source and the map relationship between the DataTable object. When populating DataSet, the DataSetCommand object uses this class. It maintains links between the abstract columns in the data set and the physical columns in the data source. The view of the table is implemented by a DataView object. It represents a custom view of a DataTable that can be bound to a specific control (such as a Windows Form and a data grid in a web form). This object is equivalent to the implementation of the SQL CREATE VIEW statement in memory. All tables in the DataSet can be placed in a relationship through a public domain. This relationship is managed by DataRelation object. This looks like ADO's data formation, but there is a certain difference. You don't need to use data formation language, you will eventually have a very flexible structure system. The ADO.NET navigation model allows you to easily remove all of its sub-lines from the primary bank in a table. The DataRelelation object is equivalent to the implementation of the join statement in memory, which can be used to establish the parent / sub-relationship of the column of the same data type. Once a relationship is established, no changes to this relationship will occur if there is a change, and if it occurs, it will cause the runtime exception. Views and relationships are two ways to achieve a primary table / tomultian table architecture. To remember, the view is just a mask on the record, and the relationship is a dynamic link between one or more columns set in two tables. If you use a relationship, you cannot change the order or set conditions. If your code requires a pair of foreign key relationships, and does not change the data, you'd better not use a formatted JOIN command. If you need additional filtering, you should use the ADO.NET custom view. Converting existing code There are many ASP pages to use ADO objects to extract data. Let us discuss several typical situations, you may encounter these situations in the near future and adaptation code. If you have an ASP page generated from a single recordset, the DataReader object will be your best partner. When you browse the DataReader object, it outputs the result 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 quickly check if DataReader is empty with the Hasmorerows property. If you only need to quickly browse a series of records, there is no more faster than DataReader. It also applies to query a single record. You cannot edit the DataReader content, but you can move its content into objects that are easier to manage, such as DataTable or one or more DATAROW objects. DataReader is no longer a suitable tool when you need complex relationships between tables and records. In ADO, you will eventually process records. The more your data model links, the more complex the SQL command. The navigation model remains sequential, and finally putting into the cache is often more than what you need. DataSet and DataLation objects are the foundation of this form of relationship. In order to manage the parent / sub-relation, ADO also encapsulates the data formation engine. From functional, data formation and ADO.NET relationship are the same. However, from the design perspective, they have little common in common. Forming a recordset embeds all information into a single list object. ADO.NET relationship is a dynamic link you can establish between two data tables at any time. In order to create a hierarchical recordset during the execution of a single ADO command, ADO relies on the SHAPING OLE DB service provider and uses a specific class SQL language. In ADO.NET, each object involved in the relationship is always seen as a separate individual. The relationship itself is disclosed as an object and has a certain behavioral rule. For example, the DataRelelation object can be changed from the father to the subline layer. You can do this by adding the ForeignKeyConstraint object to the CONSTRAINTS collection of the DataTable. The ForeignKeyConstraint object indicates a set of columns associated with the foreign key relationship when deleting or updating the values and rows. As mentioned earlier, once the relationship is set, you cannot perform changes that may undermine the relationship before it is terminated by the program preset. In addition, the relationship is unpaid. You can build two different relationships, such as relationships between customers and orders, orders, and products. However, when navigating in the order is looking for a certain customer, you cannot jump from one order to the product line related to it. You must open an order / product relationship, locate the order you need, and then get the relevant row. That is why don't you do not implement a pair of relationships through the original unformed SQL JOIN statement. Need to store records in the ASP Session object? With the ADO.NET and DataSet objects, you can have a fairly safe operation without causing the ADO record set in Git that may lead to the problems discussed in the Access Conflict (English), and there will be no hassle of thread similarity. When updating data update data, web applications typically use unformatted SQL statements, or use a better parameterized stored procedure. However, when unconnected data is required, you may want to use a built-in service to update all records that need to be revised. ADO provides a batch update mechanism to implement this feature. The UpdateBatch method is used to send the RECORDSET change stored in the copy buffer to the server to update the data source. It uses an open lock that allows all pending local changes. It also transmits all changes to the data source in a single operation. Open lock occurs only when the data source locks to change after the submission. Open lock allows two users to access the same record at the same time, but the changes entered by a user will soon be overwritten by another user. Of course, this approach requires data sources to detect and prevent data conflicts. The entire data source is also required to be more stable and there is no frequent changes. Otherwise, it is not difficult to imagine that coordination cost will soon exceed the savings caused by the replacement strict lock.
In fact, use the UpdateBatch method to return an error when any changes fails. You can then access the error through the ErrorS collection and an Error object. It is important to understand why the ADO.NET model is a more powerful tool for updating data, understanding the working principle of open-locked in Ado is critical. In the ADO code, you can't control everything that happened after calling UpdateBatch. That is, the update is performed on the server by scrolling the changed row, and then compares the current value in the corresponding record in the original value, and the data source. When all values are consistent, the appropriate SQL statement (INSERT, UPDATE or DELETE) is performed on the table. The problem is that you cannot control the SQL statement that is actually applied to changes. The server-side update code is not the code you have written, if you use a non-SQL provider, it can't run. At the beginning of this section, I have said that the web application usually updates the data by parameterization stored procedures. However, if you use a batch update, it is different. In ADO.NET, this model has expanded. Now it uses a more common architecture, allowing you to specify basic operational commands, such as insert, delete, update, and select. It is very obvious: no matter what data source can be extracted from it and the same support is provided. Batch updates in ADO.NET, you need to create a DataSetCommand object, is SQLDataSetCommand or AdodataSetCommand. Note: In Beta 2, the DataSetCommand object will be called a DataAdapter object. After you have a DataSetCommand object, you can call its Update method. DataSetCommand provides properties such as INSERTCOMMAND, DELETECMMAND, UPDATECMMAND, and SELECTCOMMAND. They are all Command objects. However, you don't have to set them unless the default behavior cannot meet the needs. This is the same as in ADO. In the UPDATE process, if any xxxCommand properties are set, there is a primary key information, and a Command object will be automatically generated. Note that the above process must be made correctly, and the primary key must be set for the data table involved. The following code shows how to set the primary key for DataSet's 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 basically an array of Datacolumn objects. If you want to use the stored procedure to update the table, or use a dedicated non-SQL data provider, you often use these command properties. XML Extension Support In ADO, XML is just an input and output format. However, in ADO.NET, XML is a data format that provides a means of operation, organization, sharing, and delivery of data. Any data brought to DataSet, regardless of its source, can be processed through a double-sided programming model. You can alternately access information, or by row, you can also access in non-order, hierarchical paths driven by XML document object model. DataSet reads and writes data and architecture as an XML document. Data and architectures can be transmitted via HTTP and can be used on all platforms that support XML. The same data can be rendered by different architectures at different times, which is achieved by XSLT. You can write architectures using the READXMLSCHEMA method. The XML architecture includes a description of the table in the data set, and the relationship and constraints of the table. This step should be completed before calling the ReadXmldata method to populate DataSet. The following code example is the simplest ASP.NET page that displays the update data table. <% @ Import namespace = "system.data"%> <% @ import namespace = "system.io"%>
