C # and database Access Technology Summary (16) The DataSet object

DataSet object

A DataSet object can be used to store data that is queried from a database, because it disconnects from the database as soon as it gets or updates data, so programmers can use this to efficiently access and manipulate the database.

And because the DataSet object has the characteristics of offline access to the database, it can be used to receive a large amount of data information.

DataSet Object Overview

The dataset is the object that is used to access the database in ADO.

Since it does not know the structure of the tables in the database before accessing the database, the data is stored in a dynamic XML format within it. This design enables datasets to access data from different data sources .

The DataSet object itself is related to a different database, and instead it fetches the data from the database and updates the modified data to the database through the DataAdapter object.

As you can see in DataAdapter's narration, the programmer can populate (fill) or update the DataSet object with the Dataapater object after establishing a connection with the database.

. Net of this kind of design, well conforms to the object-oriented thinking in the low-coupling, object function of the only advantage.

If the DataSet object can be connected directly to the database, then the DataSet object must be designed only for a specific database, and the generality is very poor, which is very detrimental to the dynamic expansion of the dataset.

Because the dataset is independent of the data source, the dataset can contain data that is local to the application, or it can contain data from multiple data sources.

Interactions with existing data sources are controlled through DataAdapter.

The DataSet object is often used in conjunction with the DataAdapter object.

Through the DataAdapter object, the general process of populating data into a dataset is:

(1) Create DataAdapter and DataSet objects.

(2) Use the DataAdapter object to produce one or more DataTable objects for the dataset.

(3) The DataAdapter object fills the data fetched from the data source into the DataRow object in the DataTable and appends the DataRow object to the Rows collection of the DataTable object.

(4) Repeat step (2) until all data in the data source has been populated into the DataTable.

(5) Add the DataTable object generated by step (2) to the dataset.

Using a dataset, the process of updating the modified data in the program to the data source is:

(1) Create a copy of the DataSet object to be manipulated to avoid data corruption caused by misoperation.

(2) Inserting, deleting, or changing operations on data rows of a dataset, such as a DataRow object in a DataTable, at which time the operation cannot affect the database.

(3) Call the Update method of DataAdapter to update the modified data in the dataset to the data source.

DataSet object Model

As you can see from the previous narration, the DataSet object is primarily used to store data result sets from the database.

In order to better correspond to the data tables and tables in the database, the DataSet object contains the DataTable and DataRelation types of objects.

The DataTable is used to store the data in a table, where the DataRows object is used to represent the field structure of the tables and a piece of data in the list.

In addition, the DataView object in the DataTable is used to generate and correspond to the Data view.

Objects of the DataRelation type are used to store the constraint relationship between the DataTable.

Both DataTable and DataRelation objects can be managed with the object's collection (Collection) object class.

As you can see, the methods and objects in the dataset are consistent with the methods and objects in the relational database model, and the DataSet object can be regarded as the mapping of the database in the application code, and the operation of the actual database can be accomplished by accessing the DataSet object.

The object model of the dataset.

The key components in the DataSet object model are described below.

1.DataRelationCollection and the DataRelation

The DataRelation object is used to describe a relationship between tables in a dataset, such as primary and foreign keys, which associates a row in one DataTable with a row in another DataTable, or identifies a matching column of two tables in a dataset.

DataRelationCollection is a collection of DataRelation objects that describe the relationships between data tables in the entire DataSet object.

2.ExtendedProperties

Datasets, DataTable, and DataColumn all have extendedproperties properties. You can include custom information in it, such as the SQL statement used to build the result set or the time the data was generated.

3.DataTableCollection and the DataTable

In the DataSet, a DataTable object is used to map the tables in the database, and datatablecollection is used to manage all the databtable under the dataset.

A DataTable has the following common properties.

(1) TableName: Used to get or set the name of the DataTable.

(2) DataSet: Used to indicate which dataset the DataTable belongs to.

(3) Rows: A collection of DataRow objects used to represent the DataTable, that is, the records used in the corresponding data table.

This property allows the programmer to access each record in the DataTable in turn. This property has the following methods.

ADD: Appends the row created by the AddRow method of the DataTable to the end.

InsertAt: Appends the row created by the DataTable's AddRow method to the location specified by the index number.

Remove: Deletes the specified DataRow object and physically deletes the corresponding data from the data source.

RemoveAt: Deletes data directly according to the index number.

(4) Columns: A collection of DataColumn objects used to represent the DataTable, through which each field in the DataTable can be accessed sequentially.

A DataTable has the following common methods.

DataRow NewRow () Method: This method is used to add a new row for the current DataTable, returning a DataRow object that represents the row record, but the method does not append the created DataRow to the DataRows collection. Instead, you need to complete the add action by invoking the Add method of the DataTable object Rows property.

DataRow [] Select () method: After the method executes, an array of DataRow objects is returned.

void Merge (DataTable table) method: This method can combine the DataTable in the parameter with this DataTable.

void load (DataReader reader) Method: This method uses the IDataReader object in the parameter to load data from the corresponding data source into the DataTable for subsequent operation.

void Clear () method: This method is used to purge data in a DataTable and is usually called before data is fetched.

void Reset () Method: The method uses Song to set the Datatabl object.

DataColumn and DataRow objects

In a DataTable, a DataColumn object is used to describe the field of the corresponding data table , and a DataRow object is used to describe the record of the corresponding database .

It is important to note that the DataTable object generally does not modify the structure of the table, so it is generally read only through Column objects.

For example, by datatable.table["TableName"]. Column[columnname] to get the column name .

The common properties of the DataColumn object are as follows.

Caption property: Used to get and set the title of the column .

ColumnName attribute: Used to describe the name of the DataColumn in DataColumnCollection.

DataType property: Used to describe the type of data stored in the column.

In a DataTable, a DataRow object is used to describe the record of the corresponding database.

The DataRow object is similar to the Rows property in the DataTable and is used to describe the records in the DataTable.

Unlike the same objects in the ADO version, ADO. NET DataRow has "raw data" and "updated data", and the modified data in the DataRow is not immediately reflected in the database, only call the dataset's Update method to update the data.

The important properties of a DataRow object are the RowState property, which indicates whether the DataRow has been modified or modified . The RowState property can take values such as added, Deleted, modified, or unchanged.

The DataRow object has the following important methods.

void AcceptChanges () method: This method is used to submit to the database all modifications to the row after the last execution of the AcceptChanges method.

void Delete () method: This method is used to delete the current DataRow object.

Methods for setting the RowState property of the current DataRow object: This class of methods are:

void setadded ();

void SetModified ();

Used to set the DataRow object to added and modified respectively.

void AcceptChanges () method: This method is used to submit to the database all modifications to the row after the last execution of the AcceptChanges method.

void BeginEdit () method: This method is used to start an edit operation on a DataRow object.

void CancelEdit () method: This method is used to cancel an edit operation on the current DataRow object.

void EndEdit () method: This method is used to terminate an edit operation on the current DataRow object.

The following code describes how to use the DataTable, DataColumn, and DataRow objects in a comprehensive manner for database operations.

Private  voidDemonstraterowbeginedit () {//Create a DataTable objectDataTable table=NewDataTable ("table1"); //Create a DataColumn object and set its properties to Int32 typeDataColumn column=NewDataColumn ("col1", Type.GetType ("System.Int32" )); //Add column to DataTable 　　 table.　　Columns.Add (column); //Create 5 DataRow objects and add them to the DataTable using a for loop 　　 DataRow NewRow;  for(intI=0; i<5; i++) {//RowChanged event would occur for every additionnewrow=table.   　　 NewRow (); newrow[0]=i; Table.　　Rows.Add (NewRow); }//Commit changes to the database using the AcceptChanges method of the DataTable 　　 table.　　AcceptChanges (); //start manipulating each object in the DataRow 　　 foreach(DataRow rowinchtable. Rows) {//use the BeginEdit method to start the Operation 　　 row.   　　 BeginEdit (); row[0]=(int) row[0]+Ten; } table. rows[0].　　BeginEdit (); Table. rows[1].　　BeginEdit (); Table. rows[0][0]= -; Table. rows[1][0]= -; Try{//terminating operation on a DataRow objectTable. rows[0].　　　　EndEdit (); Table. rows[1].　　EndEdit (); }Catch(Exception e) {//Error HandlingConsole.WriteLine ("Exception of type {0} occurred.", E.gettype ()); }} 

The main business logic for the above code is as follows:

(1) Create objects of the DataTable and DataColumn types and set the data type of the DataColumn object to System.Int32.

That is, the DataColumn object can be used to receive field data of type int.

(2) Add the DataColumn object to the DataTable.

(3) Create 5 Daarow objects in turn and assign them to them through a for loop. After the assignment is complete, add the 5 DataRow objects to the DataTable.

(4) Use the AcceptChanges method to implement updates to the DataColumn and DataRow objects.

(5) using the BeginEdit method, start editing the DataRow object and use the EndEdit method to indicate the end of the edit.

There are several general ways to access data using DataTable, DataColumn, and Dmarow objects.

(1) Use the Table name and table index to access the DataTable. To improve the readability of the code, it is recommended to use the table name to access the table. The code is as follows:

DataSet ds=NewDataSet ();D atatable DT=NewDatatble ("Mytablename");//Add a DataTable to the table in the datasetds. Tables.add (DT);//Accessing a DataTable//1 access by table name, recommended useDs. tables["Mytablename"]. NewRow ();//2 indexed access, index value starting from 0, deprecatedDs. tables[0]. NewRow (); 

(2) Use the Rows property to access data records, for example:

   foreach (DataRow  row in    table.) Rows)   {       row[0]= (int) row[0]+;   

(3) Use the Rows property to access the specified field for the specified row, for example:

//first create a data column for the DataTable objectDataTable table=NewDataTable ("table1");D atacolumn column=NewDataColumn ("col1", Type.GetType ("System.Int32") ); table. Columns.Add (column);//Next, add row data to the DataTablenewrow=table. NewRow (); newrow[0]=Ten; table. Rows.Add (NewRow);//Set index row is 0, column name is col1 dataTable. rows[0]["col1"]= -;//It is not recommended to set the index row to be 0 and the index column to be 0 of the data//table. rows[0][0]=100; 

(4) Comprehensive use of DataRow and DataColumn objects to access data within a DataTable.

As you can see from the following code, the Rows property in the DataTable object corresponds to its DataRow object, and the Columns property corresponds to DataColumn.

foreach (DataRow  Dr in    dt.) Rows) {      foreach(DataColumn  dc in    dt. Columns)      {           // Access data in array           dr[dc]=;      }}

C # and database Access Technology Summary (16) The DataSet object