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

zhaozj2021-02-08  217

Guide: .NET has made great adjustments in data access. Under the .NET framework, data access is done by ADO.NET, which is an improvement and perfect version of ADO. Its most significant change is its complete XML based entirely. For those who engage in ADO development, the disappearance of the Recordset object has also surprised them.

Translation: .NET Technology Network (www.51dot.com) Slash Original Source: http://www.dnjonline.com/articles/essentials/iss22_essentials.html

Table 4 shows existing data in the DataSet. It can be found that there are now two data types, while Schema also has the corresponding adjustment to describe these two data types.

Please note that we have not created an internal connection or an external connection, but similar to the two separate tables with two separate tables with the Shape language. Of course, you can also create a connection, which behaves in a DataSet as a table.

When the ADO developer is operating the Recordset object, you need to know that they need a client or the cursor of the server. MoveFirst or AbsolutePosition, which will consume very large server resources in the case of the server-side cursor, but in the case of the client cursor, it is indeed efficient and powerful tools. There is a huge difference in the two categories. A client cursor's Recordset object is actually more similar to a high-performance array, rather than a serialized access structure.

On the other hand, DataSet is always the 'client', and can play the highest efficiency of the 'high performance array' access model. There is a field collection in the Recordset, but there is no corresponding collection for a RECORDSET object. All tables in DataSet have a column and a collection of rows, you can use simple random access technology to operate them, Table 5 shows the object model of DataSet in ADO.NET.

By cycling, you can operate every line in DataTable, however I use another method in the next example: DataTable SELECT method. This is an overloaded method, from essentially, it is equivalent to combining the refordset's Filter and sort properties. SELECT method Returns an array of DATAROW objects - can be processed using standard arrays. It should be noted that all these processes are made in your program's cache, and DataSet has been completely disconnected.

The following sample code fills two data tables in the DataSet (the second table does not use the WHERE clause) and then returns an array from 'Authors' with the SELECT method and uses the result to create a dynamic drop-down list.

DIM DC AS New AdodatasetCommand (_ "SELECT AU_ID, AU_FNAME," & _ "AU_LNAME FROM AUTHORS", STRCONNECT) DIM DS AS NEW DATASET ()

'Declare An Array of DataRowsdim Dr () AS DataRowdim i as in

Dc.FillDataSet (DS, "Authors") DC = New AdodatasetCommand (_ "Select * from title", strconnect) DC.FILDATASET (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 Last Name of the head letter is before 'r', and the line is classified, the 'Titles' table is ignored here.

Contact between tables

If you don't use Shape Language to work, you are likely to just create a Dataset with a data table and operate it, just like a Recordset object. When you add multiple tables to the DataSet, you will want to establish associations between them for operation. In the following code, a DataSet object named DS is created in the same way as the above example:

DR () AS DATARODIM DRCHILDREN () AS DataRowdim DL AS DataReLATIONDIM i, J AS Integer

DL = New DataRelation ("Authortitles", _ds.tables ("authors"). Column ("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 ("AU_FNAME")) & "" _ & CSTR (I) ("au_lname")) DRCHILDREN = DR (i) .Getchildrows (DL) for j = 0 to ubound (drchildren) listbox1.items.add ("" & _cstr (drchildren (j) ("title_id")) NextNext

DataSetCommands

Table 6: A multi-relationship between two tables using List Box

This code has established a parent-child relationship between the 'Authors' table and the 'Titles' table, which is achieved by creating a DataRelelation object (named DL) and adds it to DataSet. Association Specifies AU_ID to the key field, and get the sub-row of each row in the parent table by matching the AU_ID in the sub-table ('titles'). This is implemented by the ReLate statement in the Shape language filtered in the ADO data.

When you specify the row in the parent table, you can use this association. You can get all the relevant lines in all subtles through the getChildrows method, of course, the association relationship here is determined by you. DataReLations makes it very simple to create an Master-Detail program. The display result of the above code is shown in Table 6.

Let's take a look at the AdodatasetCommand object and a SQLDataSetCommand object similar to it. We have already understood one of the three main functions, which is to add data to the DataSet object by using a command string and a connection number. The other two main functions are discussed below, first is updating. In traditional ADO, a client's RecordSet object is updated via a SQL statement. Here SQL simulates open lock, thus updated to be returned to the database. This is a flexible mechanism, but there are two shortcomings: 1. Automatically generated SQL statement is not easy to change, so if you write some high-efficiency stored procedures, it will be more frequent than directly using SQL statements. Second, this is the continuation of the first problem. When you need to update the data source that you can't understand, you can't use the client's Recordset. These are not compatible with ANSI standards like Active Directory, Exchange 2000, and INDEXING SERVICES. So if you want to update them through ADO, you can only use the server side cursor.

These issues are solved in ADO.NET. The first method, DataSet is completely disconnected from the data source, and AdodatasetCommand interacts as a separate entity with the data source. The update is completely made by AdodataSetCommand, and DataSet is completely isolated.

The second method, AdodatasetCommand will update the SQL statement as a public property so you can easily replace SQL statements, or store procedures. More excellent is if you want to use the stored procedure, Visual Studio.net will generate it for you. In the next section, we can see specific applications.

Finally, the data table mapping is complete. Users of data do not need to get such a data table: column named 'au_fname' and 'au_lname'. Not only is an unoperative problem, but more importantly, this will expose the structure of the database. Data mapping allows you to replace the name of the column in DataSet, if needed, you can provide different data table mappings for different users, below I How to use Visual Studio.net, create data update code in the graphical interface.

Simple visual design

Visual Studio.net provides graphical design tools for Windows Forms, Web Forms, Web Services, and Components and XML Schemas. The designer only needs to drag the control from the toolbar to the work area. Here, the work area will have a great difference in the interface that the end user can see.

When you drag a non-visual object such as AdodatasetCommand, it will be displayed in the design view, but the user will not see this object. This is also true of other data controls.

Table 7 shows a VB.NET project, this project has a form, there is a DataGrid control, a CommandButton, and AdodatasetCommand control, where you can handle AdodatasetCommand as in Visual Studio 6: In the visual interface You can use the wizard to create an AdodatasetCommand's connection string, command string; on the interface with the database, you can choose to automatically generate SQL statements, select existing stored procedures, or create a new stored procedure.

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

If you don't want to use the stored procedure, you can also use the SQL statement directly, you can also modify these statements on the properties panel. The work you can also do is included to simplify the column so that you can easily use it in the future. Specific operations can be found in the dialog box of Table 9. After completing the above work, the application's written changes are relatively easy, below is the code of DataGrid's bound code:

Me.adodatasetcommand1.filldataset (dsauthors) me.dataGrid1.datasource = DSAUTHORS

AdodatasetCommand1 has become a property of forms ME, which loads the DSAUTHORS table into a DataSet object, then set the DataGrid's DataSource property to DSAUTHORS. Finally, write the CLICK event of CommandButton:

Me.adodatasetcommand1.update (dsauthors)

The UPDATE event will update the data source according to the modification of DSAuth (the specific UPDATE method has been set during the stored process). This is similar to the batch update of the RecordSets object that is disconnected by the ADO, but is higher. The final completed application can be seen in Table 10, which has been replaced with a new column name.

Table 10: Operation results

Typened DataSet

For many developers, they have become accustomed to using ADO's Recordset object, like using Field (Fields) instead of using properties, which has its advantage, but there are also many defects. First, different from the properties, the field is not strong, it is not supported for IntelliSense technology (automatic reminder grammar, parameters, and object properties). Also, because it is not a strong type, you can't add a custom method or attribute for it, which means that you will encounter a lot of restrictions when you need to completely encapsulate a Recordset object.

In contrast, because the .NET platform supports inheritance, you can create a subclass of a DataSet object and add new features to it. This is a type of DataSet, which basically does not use the scope limit, all DataSet builds the features built in .NET will be supported, including binding, internal operations of XML DOM. In order to create such a type of DataSet object, you need is just an XML Schema in an XSD format.

When you create a type of Dataset based on a DataSetCommand, you can even let Visual Studio.net create such an XML Schema, all the job is just a little bit on the 'Generate Dataset' menu (see Table 7) . The XML Schema used here is the same as in Table 2 (unless you have changed the structure of the data table)

In the following example I will use the type of AuthorsDataSet object I have created to replace the DataSet object. All tables and columns in AuthorsDataSet will be its properties, so the code written will be easier to view, and the strong type will be more difficult to erroneously, while also using IntelliSense technology. Table 11 shows the type of DataSet programming interface, and note that the IntelliSense menu is also displayed.

In Table 11, we can see that after AuthorsDataSet is created, also created authorsselecttable, authorsselectable, authorsselectrow, and all columns (inherited in datacolumn), so we can see Inheritance of the important significance of .NET. The FindByauthor_id method is automatically added to the AuthorsselectTable class. The column properties are automatically added to the Authorsselectrow class, and all classes of the class are non-hidden and easy to expand. If you are accustomed to using disconnected or virtual (fabricated) Recordset objects, the stego use of ADO.NET is a good choice for use with type DataSet objects. ADO to ADO.NET is a revolutionary development, in all .NET Framework fields, many basic components can be rewritten because there is no need to be compatible with binary compatibility, the interface of all types of components can be performed. Rewind and improve, and ADO.NET is just one of them.

After a few weeks of use, I think ADO.NET is a more complete model compared to ADO, I am very satisfied with the improvement of ADO. Although some extent, for developers, you need to learn a new object model, but I still recommend the developer to transfer to .net. ADO.NET inherits the excellent characteristics of ADO and is easier to use.

转载请注明原文地址:https://www.9cbs.com/read-3246.html

New Post(0)