Ado.net Designed for ADO programmers (RPM)

ado| Program | programmer | Design SUMMARY: This article discusses how to implement basic database operations in a ado.net manner and when to use ado.net instead of ADO.

Directory
Data access in. NET
Reading data
Datasets, DataTable, and Recordset
Convert existing code
Update data
XML Extension Support
Summarize

Since the introduction of the Open Database Connectivity (ODBC) application Programming Interface (API) Several years ago, a variety of database access technologies have emerged, and Ado.net is the newest of them. In the process, a lot of interesting things happened. For example, COM intruded into the database domain and began to cultivate the colonization process of OLE DB. Then, Activex®data Objects (ADO), roughly equivalent to the OLE DB Automation version, was chosen to govern the Visual basic® and ASP communities of Windows® database developers.

Through. Net,microsoft is providing a common framework (the framework Class Library), which will include all existing Windows APIs and even more content. It is particularly noteworthy that it includes a large number of commonly used libraries, which now need to be obtained separately from each COM object. In these libraries, you'll find XML and ADO object models that are integrated into a class subtree called ado.net.

Ado.net is actually the basis for building data-aware. NET applications. Unlike ADO, Ado.net follows more general principles and is less specifically oriented to the database. Ado.net sets up all classes that allow data processing. These classes represent data container objects that have typical database functions, such as indexes, sorting, and views. Although Ado.net is the authoritative solution for. NET database applications, it is not as central to the database as the ADO model in the overall design, which is a major feature of the ado.net.

Ado.net differs greatly from ADO. Ado.net is a new data access programming model, which needs the comprehensive understanding, input and new thinking of the developer. However, once you start mastering ado.net, you will realize that the original ADO techniques are very helpful in creating effective applications and resolving old problems in different, yet more ingenious, and reliable ways.

In the remainder of this article, I will focus on how to implement basic database operations in a ado.net manner. I want to explain when Ado.net is a better choice than ADO, and when you should give up ADO. Ado.net is not an improvement in ADO to conform to the. NET infrastructure. As long as you look at Ado.net's syntax, code design, and porting, you'll understand this.

Data access in. NET
The way in which data sources are accessed in ado.net is determined by the hosting provider. Functionally, managed providers are very similar to OLE DB providers, but there are two important differences. First, the management provider works in the. NET environment, retrieving and exposing data through. NET classes such as DataReader and DataTable. Second, because their architectures are optimized for. NET, they are relatively simple.

Currently Ado.net provides two types of managed providers: one for SQL server™7.0 or later, and one for all other OLE DB providers that you might have installed. In both cases you use separate classes, but follow similar naming conventions. Names are the same except for prefixes. The previous case is prefixed with SQL, and the latter is ADO.

You should use SQL classes to access SQL Server tables because they go directly to the internal API of the database server and skip the middle tier represented by the OLE DB provider. The ADO class is a. NET interface on an OLE DB provider that works using COM Interop bridges.

Beginners of Ado.net objects can refer to Omri Gazitt's article ado+: Data access Services for the Microsoft. NET Framework (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 focuses on the goals of Ado.net and its links to XML, scripting, and other technologies.

Reading data
The ado.net application that needs to read data from the data source first creates a connection object. Depending on the destination provider, the connection object can be either SqlConnection or ADOConnection. Keep in mind that you can use the Ado.net class to connect to the SQL Server database, but we do not recommend doing so. The only drawback is that your code is going through unnecessary extra layers of code. It first calls the managed provider for ADO, and then the managed provider invokes the SQL Server OLE DB provider. SQL Server managed providers, like OLE DB providers, manipulate data directly.

The significant difference between ADO and Ado.net connection objects is that the Ado.net connection does not support CursorLocation properties. Note that this is not a document error, but a controversial design issue. To highlight the data-centric principle, ado.net does not have an explicit implementation of the cursor.

In ADO, you are accustomed to using cursors to extract records from a database or any other OLE DB-compliant data source. You can select client or server cursors, each of which has several preset cursor types. Ado.net is designed to extract data from a data source and to provide a new programming interface for reading and analyzing data.

In ADO, you create a Recordset object by specifying the connection and command text. The Recordset has a strategy for the location and type of the cursor. You can read data in one of the following ways:

Creates a static copy of the selected records in memory, and then processes the records as necessary when disconnected from the data source. ADO is called a static cursor.


Scrolls data through a fast, forward-only, read-only cursor that works in a static snapshot of a record. ADO is called a read-only cursor.


Data is accessed through two server-side Grand, which require a good connection, but you can detect changes to other connected users at all levels at any time. ADO calls them keyset and dynamic cursors.
The first two methods work in disconnected recordsets and read information from the client cache, which is a similarity. In addition, in a Web-oriented environment and for new N-tier systems, these two approaches have proved to be the most frequently used.

In ADO, all of these methods correspond to different types of cursors. You will find later in this article that although ado.net is very different, it can implement any functionality that you can implement with ADO. But your code extracts data from the actual data source and its physical storage medium and format.

Ado.net provides two objects to handle data extracted from the data source. They are DataSet and DataReader objects. The former is a memory-logged cache that you can access and modify in any direction. The latter is a highly optimized object designed to scroll only read-only records in forward mode. Note that the DataSet looks like a static cursor, but in fact, the DataReader object corresponds to the ADO read-only cursor in. NET.

Server-side cursors are not supported in Ado.net. However, this does not mean that you cannot use cursors. All you need to do is import the ADO type library in. NET. Right-click on the References node of the Project window. After you import, you can begin to use the local ADO object in your application.

Although I admit that it is difficult to make a decision to turn to. NET, I personally recommend that you consider overriding an existing application with. Net. You can take the full import of ADO as the first step toward. NET without spending too much time and resources. However, keep in mind that this is only the first step on a long road. This is by no means the only step toward. Net. The real reason for the value of. NET is a unified and consistent programming interface and extensive use of local classes. You can import a COM type library, but importing a COM type library can only be a temporary solution or intermediate step, and we do not encourage it.

When using ado.net, you should fully consider the fact that it unifies the data container class programming interface. Whether you're writing an application, a Windows form, a Web form, or a Web service, you can work with the data through the same set of classes. Regardless of whether the data source at the back end is a SQL Server database, OLE DB, XML file, or an array, you can scroll through the same methods and properties and work with their contents.



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 be able to use a recordset from a data-bound control.

Datasets, DataTable, and Recordset
In Ado.net, there are no objects directly corresponding to the Recordset object. The closest is the DataTable object. Although the functions of these two objects are almost the same, they play a different role in their respective frameworks.

The Recordset is a large object with many ADO functions, but still lacks. The Recordset has good performance in many ways, such as being able to create, work when disconnected, feature-rich, and so on. However, in some respects it still needs to be improved. For example, because of the inherent COM nature of the Recordset, serialization over the network will be onerous. And as a binary object, it's hard to share a module running on a different platform, and it can't go through a firewall. In addition, the recordset represents a single table for multiple records. If the table is generated by one or more JOIN, it can be difficult to update the original data source. If you want to keep the disconnected recordset and the original data source in harmony, the data source must be able to recognize SQL. However, your recordset is most likely created by a non-SQL provider.

In Ado.net, all the features of the ADO Recordset are split into simpler objects, and DataReader is one of them. DataReader simulates the operation of fast, forward-only read-only cursors.

A DataTable is a simple object that represents a data source. You can manually construct a DataTable, or you can populate it automatically with the DataSet command. A DataTable does not distinguish the source of the data it contains. This object allows you to work with data in memory and to perform actions such as browsing, sorting, editing, applying filters, creating views, and so on.

There are no objects corresponding to the DataSet in ADO. DataSet object is a container class, and it is the key object to implement Ado.net data extraction. The DataSet groups one or more DataTable objects. A DataTable exposes its contents through a universal set like rows and columns. When you try to read data from a datasheet, you may pass through two different object layers: DataTableMapping and DataView.

The DataTableMapping object describes the mapping relationship between a data column and a DataTable object in the data source. The DataSetCommand object uses this class when the DataSet is populated. It maintains links between the abstract columns in the dataset and the physical columns in the data source.

The view of the table is implemented through the DataView object. It represents a custom view of a DataTable that can be bound to a specific control, such as a data grid in Windows forms and Web forms. This object corresponds to the implementation of the SQL CREATE VIEW statement in memory.

All tables in the DataSet can be placed in a relationship through a common domain. This relationship is managed by the DataRelation object. This looks like ADO data formation, but there is one important difference. You don't need to use data to form a language, and you end up with a very flexible architecture. The Ado.net navigation model makes it easy for you to move from the main row in a table to all of its child rows.

The DataRelation object is equivalent to an in-memory implementation of a JOIN statement, which can be used to establish a parent/child relationship for a column of the same data type. Once a relationship is established, any changes that can break the relationship are not allowed, and if they occur, they can result in a Run-time exception. Views and relationships are two ways to implement a master table/BOM schema. Remember that a view is just a mask placed on a record, and a relationship is a dynamic link that is set between one or more columns of two tables. If you use relationships, you cannot change the order or set conditions.

If your code requires a one-to-one foreign key relationship and does not change the data, then it is best not to use the unformatted JOIN command. If you need additional filtering capabilities, you should use the Ado.net custom view.

Convert existing code
There are many ASP pages that use ADO objects to extract data. Let's discuss several typical scenarios that you might encounter when porting and adapting code in the near future.

If you have an ASP page that generates reports from a single Recordset, the DataReader object will be your best partner.
When you browse the DataReader object, it prints 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[the method Response.Write of "field name" to output data
}

You can also use the Hasmorerows property to quickly check whether the DataReader is empty. If you only need to browse a series of records quickly, there is no better and faster object than DataReader. It also applies to querying a single record. You cannot edit the contents of DataReader, but you can move its contents into more manageable objects, such as a DataTable or one or more DataRow objects.

When you need to work with complex relationships between tables and records, DataReader is no longer the right tool. In ADO, you end up having to work with recordsets. The more data model links you have, the more complex the SQL commands are. The navigation model is still in order, and the data that is put into the cache is often more than you need. The DataSet and DataRelation objects are the basis of this table relational model.

To manage parent/child relationships, ADO also encapsulates the data-generation engine. Functionally, data formation and ado.net relationships are the same. In terms of design, however, they have little in common. Form a recordset to embed all the information in a single List object. Ado.net relationships are dynamic links that you can establish between two of datasheets 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 a relationship is always considered a separate individual. The relationship itself is exposed as an object and has certain behavioral rules. For example, a DataRelation object can be changed from the parent row to the child row layer. You can do this by adding the ForeignKeyConstraint object to the Constraints collection of the DataTable. The ForeignKeyConstraint object represents a constraint on a set of columns associated with a foreign key relationship when values and rows are deleted or updated. As mentioned earlier, once you have set up a relationship, you cannot make changes that might break the relationship until it is terminated by a program preset.

In addition, relationships are not transitive. You can create two different sets of relationships, such as the relationship between a customer and an order, an order, and a product. However, when navigating in an order to find a customer, you cannot jump from one order to the product line associated with it. You must open the order/product relationship separately and locate the order you want before you can get the related line. This is why it is sometimes best not to implement a one-to-one relationship through the original unformatted SQL JOIN statement.

Do you need to store records in the ASP session object? With Ado.net and DataSet objects, you can operate fairly safely without causing trouble in the way that the ADO recordset stored in GIT might cause an access violation (in English) and not thread similarity.

Update data
When updating data, WEB applications typically use unformatted SQL statements, or use better parameterized stored procedures. However, when you need to use disconnected data, you may want to use the built-in services to update all records that need to be tracked. ADO provides a batch update mechanism to implement this functionality.

The UpdateBatch method is used to send the Recordset changes saved in the replica buffer to the server to update the data source. It takes an open lock, allowing all pending local changes. It also transfers all changes to the data source in a single operation. Open locking occurs only if the data source locks the record to be changed after the commit is changed. Open locking allows two users to access the same record at the same time, but changes to one user's input are quickly overwritten by another user. This approach, of course, requires that the data source be able to detect and prevent data collisions. It also requires that the entire data source is more stable and that no frequent changes occur. Otherwise, it is not difficult to imagine that coordination costs will soon outweigh the savings resulting from the replacement of strict locking. In fact, by using the UpdateBatch method, an error is returned when any change fails. You can then access this error by Errors the collection and the Error object.

To understand why the Ado.net model is a more powerful tool for updating data, it is critical to understand the workings of open locking in ADO. In the ADO code, you can't control what happens after you invoke UpdateBatch. That is, updates are made on the server by scrolling through the changed rows, and then comparing the original values with the current values in the corresponding records in the data source. Executes the appropriate SQL statement (INSERT, UPDATE, or DELETE) on the table when all values are consistent.

The problem is that you can't control the SQL statements that are actually applied to the changes. The server-side update code is no better than the code you write, and it cannot even run if you take a non-SQL provider. At the beginning of this section, I have said that WEB applications typically update data through parameterized stored procedures. However, it is different if you are using batch updates.

In Ado.net, this model has been expanded. It now employs a more general architecture that allows you to specify basic operational commands, such as inserts, deletes, updates, and selections. The intention is clear: no matter what data source, can be extracted from the data and provide the same support. With batch updates in ado.net, you need to create DataSetCommand objects that are Sqldatasetcommand or Adodatasetcommand.

Note: In Beta 2, the DataSetCommand object will be called the DataAdapter object.

Once you have the DataSetCommand object, you can call its Update method. DataSetCommand provides properties such as InsertCommand, DeleteCommand, UpdateCommand, and SelectCommand. All of them are Command objects. However, you do not have to set them unless the default behavior does not meet your needs. This is the same as in ADO. In the Update process, if no Xxxcommand property is set, but there is primary key information, the Command object is automatically generated. Note that to make the above procedure correct, you must set the primary key for the data table involved.

The following code shows how to set a primary key for the Employeeslist table of a DataSet:

Datacolumn[] keys = new datacolumn[1];
Keys[0] = m_ods.tables["Employeeslist"]. columns["EmployeeID"];
m_ods.tables["Employeeslist"]. PrimaryKey = keys;

A primary key is basically an array of DataColumn objects.

If you are using stored procedures to update tables, or if you are using a private non-SQL data provider, you often use these command properties.

XML Extension Support
In ADO, XML is nothing more than an input and output format. In Ado.net, however, XML is a data format that provides the means to manipulate, organize, share, and deliver data. Any data that is brought into the dataset, regardless of its origin, can be processed through a double-sided programming model. You can access information sequentially, either sequentially or by line, or in a non sequential, hierarchical path driven by the XML Document Object model.

The dataset reads and writes data and schemas as XML documents. Data and schemas can be transmitted over HTTP and can be used on all XML-enabled platforms. The same data can be rendered at different times through different architectures, which are implemented through XSLT. You can write schemas using the ReadXmlSchema method. XML schemas include descriptions of the tables in the dataset, as well as table relationships and constraints. You should complete this step before calling the Readxmldata method to populate the DataSet.

The following code example is the simplest asp.net page to display an updatable datasheet.

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

<script runat= "Server" language= "C #" >
void Page_Load (Object source, EventArgs E)
{
DataSet data = new DataSet ();

Loading XML data and schemas
StreamReader SR;
sr = new StreamReader (Server.MapPath ("Data.xml"));
Data. READXML (SR);
Sr. Close ();

Add a new record to pass through a URL
if (Request.QueryString.Count >0)
{
DataTable dt = data. Tables[0];
DataRow dr = dt. NewRow ();
dr["FirstName"] = request.querystring["a";
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 UI (made up of grids)
Grid. DataSource = data. Tables[0]. DefaultView;
Grid. DataBind ();
}
</script>

As shown in Figure 2, you can add new rows to the table. However, it does not involve SQL Server or Access tables. It's just an XML file, and in the code that handles it, no XML nodes or XMLDOM methods are used. You can read and update XML records with the same Visual data table interface. You work in much the same way as you do in ADO, but the model here is deeper and larger and has more potential for you to explore.



Figure 2: An example of an updatable table

Summarize
The success of WEB applications has changed the face of a typical distributed system. Now most distributed systems are N-tier systems, and such systems are increasingly demanding scalability and interoperability. Therefore, connectionless data processing and XML are best practices and are widely accepted by 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. But this model may not yet meet everyone's requirements and will take a big step in future model design. However, keep in mind that ado.net is still only beta, with limited documentation support.

ADO programmers benefit the most from beta releases because they are familiar with many aspects of ado.net, including the highest level of abstraction-heuristic models. The Ado.net code is incompatible with the existing ADO code, but has similar functionality. To make the most of ado.net, you should take some effort to understand the concept itself, not just the quickest way to find the porting code. Regardless of the. NET programming model you choose, Windows forms, Web Forms, or Web services, Ado.net helps you handle data access problems.