Dataset in C #

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 is also the most commonly used class for developing database applications based on. NET platform programming languages. 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 makes a datatable
The row in is associated with the row in another able. 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 is reached, the ODBC. NET is added to ADO. net.
Data provider and the Oracle. NET data provider. 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. However, the ole db. NET data provider and the ODBC. NET data provider have many more operational database types, as long as they provide Ole
DB provider and ODBC provider.
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. Code List 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 MERs ", sqlconnection1); // create and initialize the sqlcommand object sqldataadapter sqldataadapter1 = new sqldataadapter (); custda. selectcommand = selectcmd; sqlconnection. open (); // create a sqldataadapter object and retrieve the data dataset dsdataset1 = new dataset (); sqldataadapter1.fill (dsdataset1, "customers") based on the selectcommand attribute "); // use the fill method of sqldataadapter to fill in datasetsqlconnection. 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 of dataadapter
And updatecommand. 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 dsdataset1.tables ["MERs"] With sqldataadapter1 as the parameter. rows [0]. delete (); // Delete the first row of data in the customers table in Dataset: sqldataadapter1.update (dsdataset1, "customers"); // call the update method, use Data in dataset to update data 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 an XML Stream or document and loads the data
Dataset. Based on whether the specified xmlreadmode and relational architecture already exist, it also creates the relational architecture of the dataset.

Example:

We recommend that you create an XML file named xmlfile. xml,

<? XML version = "1.0" encoding = "UTF-8"?> <Userinfo> <user> <Name> JOHN </Name> <age> 18 </age> <city> Shanghai </city> </user> <Name> liu Li </Name> <age> 18 </age> <city> Beijing </city> </user> </userinfo>

In the page_load event of the page, write the code that fills the gridview:

Protected void page_load (Object sender, eventargs e) {If (! Ispostback) // determine whether the page is loaded for the first time {dataset myds = new dataset (); // initialize the dataset myds. readxml (server. mappath ("~ /Xmlfile. xml "); // read data from the XML file gridview1.datasource = myds; // set the grid data source gridview1.databind (); // bind data }}