In-depth analysis of dataset objects in ADO. net

ADO. NET is the generic name for class libraries used to operate databases in the. NET Framework SDK. The dataset class is one of the core members of ADO. NET and also a variety of development based on. NET platforms.ProgramThe class most commonly used by language development database applications. The dataset class is in ADO. net has a special position because dataset is in ADO. net plays a key role in extracting data from the database. After extracting data from the database, dataset is the place where data is stored, it is the cache mapped to data from various data sources in the computer memory, so sometimes dataset can be considered as a data container. At the same time, it plays an intermediate role in the process of reading and updating the database on the client (datareader can only retrieve data in the database ).

Various.. NET platform development language development database applications, generally not directly operate on the database (except for calling stored procedures directly in the Program), but first complete the data connection and fill the DataSet object through the data adapter, then, the client reads the dataset to obtain the required data. Similarly, it updates the data in the database, and then updates the data in the database through dataset. To understand and master ADO. net, you must first understand and master dataset. Dataset has three main features:

1. Independence. Dataset is independent of various data sources. Microsoft considers the diversity and complexity of various data sources when launching dataset. In. net, no matter what type of data source, it provides a consistent relational programming model, which is dataset.

2. Offline (disconnected) and connection. Dataset can operate data in the database in both offline and real-time connections. This is a bit like the recordset In ADO.

3. the DataSet object is a data view that can be expressed in XML format and is a data relationship view.

1. Structure Model of DataSet object and comparison with recordset

Although ADO. Net is a later version of ADO on the. NET platform, there is a big difference between the two. It highlights the recordset object in ADO and the DataSet object in ADO. net. Recordset is actually a very flexible object. It is also painstaking for Microsoft to launch it. For example, recordset can operate the database offline, and has excellent performance and high efficiency, which makes the programmers feel better at that time. Although recordset is already complex, dataset is much more complex than recordset. We know that each dataset is often a set of one or more able objects, these objects consist of data rows and columns, as well as primary keys, foreign keys, constraints, and information about the relationship between data in the datatable object. Recordset can only store a single data table, although this data table can be generated by joining several data tables. So sometimes, recordset is more similar to the able in dataset. The Structure Model 01 of the DataSet object is shown in:

Figure 01: Structure Model diagram of the DataSet object

Figure 01 shows that the structure of the DataSet object is still very complex. The next layer of the DataSet object is the datatablecollection object, datarelationcollection object, and extendedproperties object. As mentioned above, each DataSet object is composed of several able objects. Datatablecollection is used to manage all able objects in dataset. The parent/child relationship between two able objects in dataset is a datarelation object. It associates rows in one able with rows in another datatable. This association is similar to the association between primary key columns and foreign key columns in a relational database. The datarelationcollection object is used to manage the datarelation relationship between all data tables in the dataset. In dataset, dataset, able, and datacolumn all have the extendedproperties attribute. Extendedproperties is actually a propertycollection used to store various user-defined data, such as select statements used to generate a dataset.

Ii. Use Dataset:

Dataset is actually a dataset. As mentioned above, dataset is a data container that maps data in the database to the memory cache. It provides a consistent relational Programming Model for any data source. In dataset, the constraints and relationships between data tables are defined, and data in the data table can be sorted. Dataset is generally used in three ways:

1. Fill the data in the database with dataset through the dataadapter object.

2. update the database through dataset operations on the dataadapter object.

3. load XML data streams or text to dataset.

The following describes in detail the specific implementation of the preceding dataset usage method. The language is C #.

1. Fill the data in the database with dataset through the dataadapter object:

To learn how to use dataset, you must master another common member of ADO. Net-data provider ). The data provider (also known as the managed provider) is a collection of classes in. net Framework SDK 1.0 data providers are divided into two types: the SQL server. NET data provider and the ole db. NET data provider. When the. NET Framework SDK 1.1 was reached, the ODBC. NET data provider and the Oracle. NET data provider were added to ADO. net. The database objects of the SQL server. NET data provider are limited to SQL Server 7.0 and later versions, and the database objects of Oracle. NET data provider are limited to Oracle 8.1.7 and later versions. The ole db. NET data provider and the ODBC. NET data provider have many more operational database types, as long as they provide the ole db provider and ODBC provider locally.

There is a dataadapter class in these data providers, such as ole db. NET framework data provider is oledbdataadapter class, the SQL server. NET framework data provider is the sqldataadapter class, the ODBC. NET framework data provider is the odbcdataadapter class. With these dataadapter, you can retrieve data from the database and fill the tables in the dataset.

The dataadapter dataset filling process is divided into two steps: first, the required data is retrieved from the database through the selectcommand attribute of dataadapter. Selectcommand is actually a command object. Then, the retrieved data is filled with dataset through the fill method of dataadapter.CodeListing 01 takes the northwind database in Microsoft SQL Server as the object, and C # uses the sqldataadapter in the SQL server. NET data provider to fill the dataset implementation method:

Code List 01:

Sqlconnection sqlconnection1 = new sqlconnection ("Data Source = localhost; Integrated Security = sspi; initial catalog = northwind ");
// Create a data connection
Sqlcommand selectcmd = new sqlcommand ("select customerid, companyName from customers", sqlconnection1 );
// Create and initialize the sqlcommand object
Sqldataadapter sqldataadapter1 = new sqldataadapter ();
Custda. selectcommand = selectcmd;
Sqlconnection. open ();
// Create a sqldataadapter object and retrieve data based on the selectcommand attribute
Dataset dsdataset1 = new dataset ();
Sqldataadapter1.fill (dsdataset1, "MERs ");
// Fill dataset with the fill method of sqldataadapter
Sqlconnection. Close ();
// Close the data connection
For dataadapter of other data providers, it is similar to the preceding method to retrieve data in the database and populate the dataset.

2. update the database by operating dataset with the dataadapter object:

Dataadapter uses its update method to update the database with data in dataset. When the data contained in the dataset instance is changed, call the update method. dataadapter analyzes the changes and runs the corresponding commands (insert, update, or delete ), and use this command to update the data in the database. If the datatable in dataset is mapped to a single database table or generated from a single database table, you can use the commandbuilder object to automatically generate the deletecommand, insertcommand, and updatecommand of dataadapter. You can use the dataadapter object to operate dataset to update the database. You only need to add the following Code List 02 to code list 01 and then merge the two to delete the first row of data in the MERs data table:

Code List 02:

Sqlcommandbuilder sqlcommandbuilder1 = new sqlcommandbuilder (sqldataadapter1 );
// Initialize the sqlcommandbuilder instance with the sqldataadapter1 Parameter
Dsdataset1.tables ["MERs"]. Rows [0]. Delete ();
// Delete the first row of data in the customers table in Dataset
Sqldataadapter1.update (dsdataset1, "MERs ");
// Call the update method to update the data in dataset from the database
Dsdataset1.tables ["MERs"]. acceptchanges ();
Because I do not understand the dataset structure and its relationship with the database, many beginners often update the data in the dataset, and think that the data in the database is also updated, therefore, when you open the database and find that the data has not been updated, you will be confused. Through the above introduction, the doubts should be eliminated.

3. xml and Dataset:

Data in dataset can be created from XML data streams or documents. In addition, the. NET Framework can control the loading of XML data streams or documents and how to create the relational structure of dataset. Loading XML data streams and documents to dataset is the readxml method of the DataSet object. (Note: When readxml is used to load very large files, the performance will decrease ). The readxml method reads data from a file, stream, or xmlreader, and uses the XML source and optional xmlreadmode parameters as parameters. This readxml method reads the content of XML streams or documents and loads the data into dataset. Based on whether the specified xmlreadmode and relational architecture already exist, it also creates the relational architecture of the dataset.
3. dataset and Data Binding)

Data Binding is the most frequently used and important technology in data binding technology. It can also be said that it is one of the most basic knowledge required to develop database applications in various. NET development languages. Data Binding is important because. net Framework SDK does not provide database development components, such as dbtextbox, dblabel, and other common components used for database development in.. NET Framework SDK. The data binding technology can transform the textbox component into the dbtextbox component and the label component into the dblabel component. All of these are directly related to dataset.

Data Binding is classified into two types: simple data binding and complex data binding. Simple data binding components include lable and Textbox, and complex data binding components include DataGrid, ListBox, and ComboBox. In fact, simple data binding and complex data binding are not clearly differentiated, but when components are bound to data, some components with more complex structures perform similar operations during data binding, some components with a simpler structure are similar in data binding. Therefore, two categories are generated. The following describes the functions and implementation methods of dataset in simple data binding and complex data binding by combining the textbox component and the DataGrid component.

1. Simple data binding:

Simple data binding generally uses the Add method of the databindings attribute in these components to bind a row in a able in the dataset with a property of the component to display data. The data binding of the textbox component is implemented by adding the code in listing 03 after listing 01, in code list 03, the Code binds the "mermerid" data in the customers data table in dataset to the text attribute of Textbox, so that dbtextbox is generated. Other methods for data binding of simple data binding components are similar to this operation:

Code List 03:

Textbox1.databindings. Add ("text", dsdataset1, "MERs. customerid ");
2. complex data binding:

Complex data binding generally sets the datasource attribute and displaymember attribute of the component to complete data binding. The datasource attribute value is generally set to the dataset to be bound, and the displaymember attribute value is generally set to the data table or a column in the data table to be bound. The General Implementation Method of Data Binding of the DataGrid component is to add the code in code list 04 after code list 01. The function of code list 04 is to bind the MERs data table in dataset with the DataGrid. Other methods applicable to complex data binding for data binding are similar to this operation:

Code List 04:

Datagrid1.datasource = dsdataset1;
Datagrid1.datamember = "MERs ";
Iv. Summary

the dataset class is a very important core member of ADO. net. It is the cache mapped to data in the database on the local computer. Any operations on dataset are completed in the computer cache. Understanding this is the first step to grasp dataset. Dataset has complex structures. However, it is not too difficult to grasp the components in the DataSet object and their relationships. This article covers the characteristics, structure, and usage of dataset. It is helpful for you to master the core members of ADO. net.