Ado. NET in-depth study (2) [Special recommendation]

ADO guide:. NET has made great adjustments in data access. Under the. NET Framework, data access is done by ado.net, an improved and perfected version of ADO. Its most significant change is that it is completely xml-based. As for the people engaged in ADO development, they are amazed by the disappearance of the Recordset objects.
--------------------------------------------------------------------------------

Translation finishing:. NET technology Network (www.51dotnet.com) slash
Original source: http://www.dnjonline.com/articles/essentials/iss22_essentials.html

Table 4 shows the existing data in the dataset. You can see that there are now two types of data, and schema also adjusts to describe the two types of data.

Note that we do not now create an inner or outer join, but rather similar to creating a hierarchical Recordset object with two separate tables in the shape language. Of course you can also create a connection, which is represented as a table in the dataset.

ADO developers need to know whether they need a client or a server-side cursor when manipulating a Recordset object. Operations such as MoveFirst or absoluteposition will consume a lot of server resources in the case of server-side cursors, but they are efficient and powerful tools in the case of client cursors. There is a huge difference between the two types of cursors. A client cursor's Recordset object is actually more like a high-performance array than a serialized access structure.

In contrast, the DataSet is always ' client ' and can play the highest efficiency of the ' high-performance array ' access model. There is a collection of fields in the recordset, but there is no corresponding collection for a Recordset object. While all tables in the dataset have a column and a set of rows, you can use simple random access techniques to manipulate them, and table 5 shows the object model for manipulating the dataset in Ado.net.

You can work with each row in the DataTable by looping, but I use another method in the next example: a DataTable Select method. This is an overloaded method, which, in essence, is equivalent to combining the filter and SORT attributes of the recordset. The SELECT method returns an array of elements of the DataRow object-it can be handled using a standard array of methods. Note that all of these processes are in the cache of your program, and the dataset is completely disconnected from the data source.

The following example code populates the dataset with two data tables (the second table does not use a WHERE clause) and then returns an array from ' Authors ' using the Select method and creates a dynamic Drop-down list using the result.

Dim DC as New Adodatasetcommand (_
"SELECT au_id, au_fname," & _
"au_lname from Authors", strconnect)
Dim DS as New DataSet ()

' Declare an array of DataRows
Dim Dr () as DataRow
Dim I as Integer

dc. FillDataSet (ds, "Authors")
DC = New Adodatasetcommand (_
"SELECT * from titleauthor", strconnect)
dc. FillDataSet (ds, "Titles")

Dr = ds. Tables ("Authors"). Select (_
"Au_lname >= ' R", "au_lname ASC")

For i = 0 to UBound (DR)
ListBox1. Items.Add (_
CSTR (DR (i) ("au_fname") & "" _
& CStr (DR (i) ("au_lname"))
Next

Here, the SELECT statement returns all rows, where the beginning of the last name is preceded by ' R ' and the rows are categorized, and the ' Titles ' table is ignored here

The relationship between tables

If you haven't worked with shape LANGUAGE, you're probably just creating a dataset with a datasheet and manipulating it like a Recordset object. Once you have added more than one table to the dataset, you will want to establish an association between them for ease of operation. In the following code, assume that a DataSet object named DS is created in exactly the same way as the previous example:

Dim Dr () as DataRow
Dim Drchildren () as DataRow
Dim DL as DataRelation
Dim I, J as Integer

DL = New DataRelation ("Authortitles", _
Ds. Tables ("Authors"). Columns ("au_id"), _
Ds. Tables ("Titles"). Columns ("au_id"))
Ds. Relations.Add (DL)

Dr = ds. Tables ("Authors"). Select (_
"Au_lname >= ' R", "au_lname ASC")

For i = 0 to UBound (DR)
ListBox1. Items.Add (_
CSTR (DR (i) ("au_fname") & "" _
& CStr (DR (i) ("au_lname"))
Drchildren = Dr (i). GetChildRows (DL)
For j = 0 to UBound (drchildren)
ListBox1. Items.Add ("" & _
CStr (Drchildren (j) ("title_id"))
Next
Next

Datasetcommands


Table 6:
Use list box to display a many-to-many relationship for two tables

This code establishes a parent-child relationship between the ' Authors ' table and the ' Titles ' table, which is implemented by creating a DataRelation object (named DL) and adding it to the dataset. Association specifies that au_id is a key field, and that the child rows of each row of the parent table are obtained by matching the au_id in the subdatasheet (' Titles '). In the shape language of ADO data filtering, this is accomplished by RELATE statements.

When you specify a row in the parent table, you can take advantage of this association. You can use the GetChildRows method to get all the related rows in all the child tables, and of course the correlation is up to you. DataRelations makes it very easy to create a master-detail program. The above code shows the results shown in table 6.

Let's take a look at the Adodatasetcommand object and the Sqldatasetcommand object that is similar to its function. One of the three main features that we've learned is to add data to the DataSet object by using a command string and a connection number. The next two main features are discussed, first in the update (updating).

In traditional ADO, a client's Recordset object is updated through an SQL statement. Here SQL simulates optimistic locking, so updates are returned to the database. This is a flexible mechanism, but has two disadvantages: one, automatically generated SQL statements are not easy to change, so if you write some efficient stored procedures, it will be faster than using SQL statements directly. Second, this is the continuation of the first question. When a data source that needs to be updated doesn't understand ansi-sql, you can't use the client's recordset. Like Active Directory, Exchange 2000, and indexing Services, which are compatible with ADO data sources, are incompatible with ANSI standards. So if you want to update them with ADO, you can only use the server-side cursor.

These problems have been solved in the ado.net. In the first method, the dataset is completely disconnected from the data source, and Adodatasetcommand interacts with the data source as a separate entity. Updates are performed entirely by Adodatasetcommand, and the dataset is completely isolated.

The second method, Adodatasetcommand, takes the updated SQL statement as a public property so that you can easily replace the SQL statement or the stored procedure. Even better, if you want to use stored procedures, Visual Studio.NET will be generated for you, in the next section we can see the specific application.

Finally, on the data table mapping function. Users of the data do not need to have such a data table: The column is named ' au_fname ' and ' au_lname '. Not only is it a problem, but more importantly, it exposes the structure of the database, which allows you to replace the name of the column in the dataset and, if necessary, provide different data table mappings for different users, I'll show you how to use Visual Studio.NET, Create the code for the data update under the graphical interface.

Simple visual design

Visual Studio.NET provides graphical design tools for the design of Windows forms, Web Forms, Web Services, components, and XML schemas. The designer only needs to drag from the control on the toolbar to the work area. Here, the work area will be very different from the interface that the end user sees.

When you drag a Non-visual object, such as Adodatasetcommand, it will be displayed in Design view, but the user will not be able to see the object. The same is true for other data controls.

Table 7 shows a vb.net project that has a form with a DataGrid control, a CommandButton, and a Adodatasetcommand control, where you can like the visual Studio 6 in the same way to deal with Adodatasetcommand: In the visual interface you can use the wizard to create a Adodatasetcommand connection string, command string, on the interface with the database, you can choose to automatically generate SQL statements, select the existing stored procedures, or create a new stored procedure.

Table 8 shows the final results of the wizard, you can name the stored procedure you created, or just preview it and save it as a file for later modification.

If you do not want to use stored procedures, you can also use the SQL statements directly, you can also modify these statements in the property panel. Some of the work you can do involves giving a simplistic name to the column so that you can use it easily in the future. You can refer to the dialog box in table 9 for specific actions.

After the work has been done, the application has become relatively easy to write, and the following is the binding code for the DataGrid:

Me.ADODataSetCommand1.FillDataSet (dsauthors)
Me.DataGrid1.DataSource = dsauthors

ADODataSetCommand1 becomes a property of the form me that loads the Dsauthors table into a DataSet object, and then sets the DataSource property of the DataGrid to Dsauthors. Finally, write the CommandButton click event:

Me.ADODataSetCommand1.Update (dsauthors)

The update event will update the data source based on modifications to the dsauthors (the specific Update method has been set in the stored procedure). This is similar to the bulk update of disconnected Recordsets objects in ADO, but is more efficient. You can see the final completed application in table 10, whose column name has been replaced with the new column name.


Table 10: Run Results

Typed dataset

For many developers, they are accustomed to using ADO's Recordset objects, like using fields (fields) instead of using attributes (properties), which has its advantages, but there are many drawbacks. First, unlike attributes, fields are not strongly typed and are not supported by IntelliSense technology (automatic reminder syntax, parameters, and object properties). In addition, because it is not a strong type, you cannot add custom methods or attributes to it, which means that when you need to fully encapsulate the functionality of a Recordset object, you will encounter many limitations.

In contrast, because the. NET platform supports inheritance, you can create subclasses of the DataSet object and add new features to it. This is the dataset of the type, which has essentially no scope limitations, and all the features built into. NET will be supported, including bindings, and internal operations of the XML DOM. In order to create such a DataSet object with a type, all you need is an XSD-formatted XML SCHEMA.

When you create a DataSet of the type based on a datasetcommand, you can even let Visual Studio.NET create such an XML SCHEMA, all work is just the mouse in ' Generate The DataSet ' menu is slightly more (see table 7). The XML schema used here is the same as in table 2 (unless you have made changes to the structure of the datasheet)

In the following example I will use the Authorsdataset object of the type I created to replace the DataSet object. All of the tables and columns in the Authorsdataset will be its properties, so the code written out will be easier to see, and the strong type will be more error-prone and can take advantage of IntelliSense technology. Table 11 shows the programmatic interface of typed datasets, noting that IntelliSense menus are also displayed.



In table 11 we can see that after Authorsdataset was created, we also created Authorsselecttable (inherited from the DataTable), Authorsselectrow (inherited from DataRow), And all the column classes (inherited from DataColumn), we can see the importance of inheritance to. NET.

The findbyauthor_id method is automatically added to the Authorsselecttable class, and the column properties are automatically added to the Authorsselectrow class, and the code for all classes is hidden and easily extensible. If you are accustomed to using a disconnected (disconnected) or virtual (fabricated) Recordset object, it is a good choice to turn to Ado.net to use a DataSet object of the type.

ADO to Ado.net is a revolutionary development, in all the. NET framework domain, many basic components can be overridden, because the interface of all types of components can be rewritten and improved without being subject to the mandatory constraints of binary compatibility, and ado.net is just one example.

After a few weeks of use, I think ado.net is a better model than ADO, and I'm very satisfied with the improvements it has made to ADO. Although to some extent, for developers, need to learn a new object model, but I still recommend developers to. NET transfer. Ado.net inherits the fine features of ADO and is easier to use.