(Wonderful record) ADO. NET (2)

Database in memory-DataSet class
DataSet class is the offline container of data. A dataset is composed of a group of data tables. Each table has some data columns and data rows.
DataSet objects include: data tables and data relations

Instantiate DataSet:
DataSet ds = new DataSet ();
// This only generates an empty dataset, which does not contain any tables.
DataSet attributes:
Tables:(DataTableCollection) table set, used to manage each sub-table.
Relations :(DataRelationCollection) is a collection of relationships used to manage the relationships between tables.
DataSet Method
WriteXml ():Write the content written to DataSet into the XML file.
ReadXml ():Write the content in the xml file to the file and write it to DataSet.

In-memory tables-Data Tables

A data table is very similar to a physical database table. It consists of columns and may contain zero or multiple rows of data. A data table can also define the primary key code (which can be one or more columns), and the column can also contain constraints.

Instantiate the DataTable:
DataTable dt = new DataTable ();
// Here, dt is just an empty object, with no columns or rows in it
Ds. Tables. Add (dt );
// Add a data table to the dataset
DataTable attributes:
Rows:( DataRowCollection) manage all rows in the table
Columns:( DataColumnCollection) manage all columns in the table
Constraints(ConstraintCollection) manage all constraints in the table
TableName:( String) Name of the data table in the dataset
PrimaryKey:( DataColumn []) array of primary key columns of the table
DefaultView:( DataView) default view of the table
DataTable method:
NewRow ():(DataRow) generates a new row object based on the table structure.

DataColumn data column:
Columns in the data table
Instantiation:
DataColumn dc = new DataColumn ();
Or
DataColumn dc = new DataColumn (string, Type );

Dt. Columns. Add (dc );// Add the column object to the column set of the table
DataColumn attributes:
AllowDBNull:( Bool) whether to allow null Columns
AutoIncrement:( Bool) whether the column is an auto-increment table
ColumnName:( String) Name of the current column
DataType:( Type) Data Type of the current column
MaxLength:( Int) the width of the current column
ReadOnly(Bool) whether the current column is a read-only column (only inserted and deleted, but not modified)
Unique:( Bool) whether the current column is a unique Column
DefaultValue:( Object) default value of the current column

DataRow data row:
Data rows in the table. When creating a table, you must first create a column before adding a row.
The DataRow object generally uses the NewRow method of the able object to generate a new row.
Method:
Delete ():Delete current row
GetChildRows ():Obtain the corresponding rows in the associated sub-table (the table association needs to be established in Dataset in advance)
GetParentRow ():Obtain the row corresponding to the associated parent table (the table association needs to be established in Dataset in advance)
The entire data row has a status flag.RowState,The RowState flag tracks all changes made to the DataTable,When the data is consistent with the database, the row status mark is used to determine what SQL operations should be performed.
Added-- Add new data Rows to the Rows set of the able. All rows created in the client are set to this value. When they are consistent with the database, the SQL INSERT statement is used.
Deleted-- Mark the data row in the able as deleted using the DataRow. Delete () method. This row still exists in the able, but cannot be seen on the screen (unless DataView is explicitly set ). DataView is discussed in the next chapter. The Rows marked as deleted in the DataTable will be deleted from the database when they are consistent with the database.
Detached-- This status is displayed immediately after the data row is created. You can also return this status by calling DataRow. Remove. Discrete rows are not part of any DataTable. Therefore, rows in this status cannot use any SQL statement.
Modified-- If the value in the column changes, a row of data will be modified.
Unchanged-- The data row has not changed since the last call of AcceptChanges

Example of manually creating DataSet:
DataTable dt = new DataTable ("login"); // generate a new data table
// Define a new column "ids", whose type is integer.
DataColumn dc0 = new DataColumn ("ids", Type. GetType ("System. Int32 "));
Dt. Columns. Add (dc0); // Add "ids" to the table
// Add the "username" column to the table. The type is string type.(

Dt. Columns. Add ("username", Type. GetType ("System. String "));
Dt. Columns. Add ("password"); // Add the "password" column to the table. The type is string type.
Dt. Columns ["password"]. DataType = Type. GetType ("System. String ");
Dt. Columns [0]. AutoIncrement = true; // set it to auto-increment Column
Dt. Columns [1]. Unique = true; // set as a Unique Column
Dt. Columns [1]. ReadOnly = true; // set as a read-only Column
Dt. Columns [2]. DefaultValue = "666666"; // set the default value for this column
Dt. Columns [2]. MaxLength = 8; // specify the maximum length of the column

DataRow dr1 = DataRow dr1 = dt. NewRow (); // generate a new row
Dr1 [1] = "aaa"; // Add two rows of data to the table. ids is auto-incrementing, and password is the default column.
Dt. Rows. Add (dr1 );
DataRow dr2 = dt. NewRow ();
Dr2 [1] = "bbb ";
Dt. Rows. Add (dr2 );
// Display the table content
For (int I = 0; I <dt. Rows. Count; I ++)
{
For (int j = 0; j <dt. Columns. Count; j ++)
{
Console. Write (dt. Rows [I] [j]. ToString () + "\ t ");
}
Console. Write ("\ n"); Console. Write ("\ n ");
}
DataSet ds = new DataSet (); // defines an empty DataSet.
Ds. Tables. Add (dt); // Add the dt table to the DataSet.

DataView Data View
A custom view of the data that can be bound to a able for sorting, filtering, searching, editing, and navigation. In addition, a custom DataView can be used to represent a subset of data in the DataTable.
You can also directly add, modify, delete, and query the data view. However, it is not recommended that you operate the table directly.
Instantiate a DataView object
DataView dv = new DataView ();
Dv. Table = ds. Tables [0];
Or
DataView dv = ds. Tables [0]. DefaultView;

Common attributes:
Count: Number of records in DataView obtained after RowFilter and RowStateFilter are set
RowFilter: Gets or sets the expressions used to filter which rows are viewed in DataView. Similar to the where clause in SQL
Sort: Gets or sets one or more sort columns and the sort order of DataView. Similar to the order by clause in SQL
Table: Get or set the source data table

Constraints on a Constraint data table
Constraint is used to enforce constraints on one or more DataColumn objects in the DataTable, and is used to maintain the data integrity rules in the DataTable.
It mainly includes:
UniqueConstraint: Create a primary key constraint or a unique key constraint
ForeignKeyConstraint: Create a foreign key constraint
Other integrity constraints can be set through the attribute values of the DataColumn object.
UniqueConstraint class:
Common constructors:
(Whether the constraint name, column to which it belongs, is set as a primary key constraint)
Main attributes:
Columns: Columns affected by the Constraint
ConstraintName: Name of the constraint
IsPrimaryKey: Whether the constraint is a primary key
You can create a primary key in either of the following ways:
1. Use the PrimaryKey attribute of DataTable to set the primary key column
DataColumn [] pk = new DataColumn [1];
Pk [0] = dt. Columns ["ProductID"];
Dt. PrimaryKey = pk;
2. Use the UniqueConstraint class to Set primary key columns
DataColumn [] pk = new DataColumn [1];
Pk [0] = dt. Columns ["ProductID"];
Dt. Constraints. Add (new UniqueConstraint ("PK_Products", pk [0]), true );

ForeignKeyConstraint class:
Common constructors:
(Constraint name, primary key column of the parent table, foreign key column of the child table)
Main attributes:
Columns: Columns affected by the Constraint
RelatedColumns: Primary key columns in the relevant primary table
Table: Table
RelatedTable: Related primary tables
ConstraintName: Name of the constraint
DeleteRule: Constraint operation corresponding to deletion
UpdateRule: The corresponding constraint operation when updating
Update and delete Constraints
Cascade-- If the parent key is updated, the new key value should be copied to all child records. If the parent record is deleted, the Child record is also deleted. This is the default option.
None-- If you do not perform any operation, this option will leave an isolated row in the child data table.
SetDefault-- If a subrecord is defined, each affected subrecord sets the foreign key column as its default value.
SetNull-- All child rows set the primary column to DBNull. (According to the naming convention selected by Microsoft, the main column should be SetDBNull .)
For example:
DataTable categories = new DataTable ("Categories"); // primary table
Categories. Columns. Add (new DataColumn ("CategoryID", typeof (int )));
Categories. Columns. Add (new DataColumn ("CategoryName", typeof (string )));
Categories. Columns. Add (new DataColumn ("Description", typeof (string )));
Categories. Constraints. Add (new UniqueConstraint ("PK_Categories", categories. Columns ["CategoryID"], true ));

DataColumn parent = ds. Tables ["Categories"]. Columns ["CategoryID"];
DataColumn child = ds. Tables ["Products"]. Columns ["CategoryID"]; // Columns in the table
ForeignKeyConstraint fk = new ForeignKeyConstraint ("FK_Product_CategoryID", parent, child );
Fk. UpdateRule = Rule. Cascade;
Fk. DeleteRule = Rule. SetNull;
Ds. Tables ["Products"]. Constraints. Add (fk );

Data Relationship DataRelation
We can set the relationship between the table and the table in the dataset. With the relationship between the table and the table, we can retrieve the data in the corresponding primary table from the subtable, or retrieve data from the corresponding sub-table from the master table.
The DataRelation object belongs to DataSet rather than DataTable.In addition, the foreign key constraints here are also different from the data relationship. The foreign key constraints are only used to ensure the reference integrity of the table in the dataset, the data relationship is used to achieve mutual access between tables.
Create a Data Relationship:
DataSet ds = new DataSet ("Relationships ");
Ds. Tables. Add (CreateBuildingTable ());(
Ds. Tables. Add (CreateRoomTable ());
Ds. relations. add ("Rooms", ds. tables ["Building"]. columns ["BuildingID"], ds. tables ["Room"]. columns ["BuildingID"]);

And traverse the Data Relationship to list all the child rows in the Rooms table.
Foreach (DataRow theBuilding in ds. Tables ["Building"]. Rows)
{
DataRow [] children = theBuilding. GetChildRows ("Rooms ");
Foreach (DataRow theRoom in children)
Console. WriteLine ("Room: {0}", theRoom ["Name"]);
}

Data adapter DataAdapter:
The data adapter serves as a bridge between a dataset in memory and a hard disk database.The data adapter can load the data in the database to the data set in the memory, or update the modified data in the data set in the memory to the database.
As long as we set our DataAdapter, we can perform offline operations on the dataset. We can first load the relevant data in the database to the data set in the memory, and then we can disconnect from the database and operate on the data in the data set in the memory, instead of directly submitting data to the database. After the operations on the dataset in the memory are completed, you can open the link to the database and update the modification results of the dataset in the memory to the database. This idea is applicable to mobile office and SmartClient technologies.
Instantiate the SqlDataAdapter object:
SqlDataAdapter SqlAdapter = new SqlDataAdapter ();
After the SqlDataAdapter object is instantiated, this SqlDataAdapter is still a data adapter that does not actually work, because its operations on databases and datasets actually use its four SqlCommand objects (SelectCommand, InsertCommand, updateCommand, DeleteCommand. Therefore, after instantiating the SqlDataAdapter object, we need to instantiate its related SqlCommand object.
SqlAdapter. SelectCommand = new SqlCommand ();
SqlAdapter. InsertCommand = new SqlCommand ();
SqlAdapter. UpdateCommand = new SqlCommand ();
SqlAdapter. DeleteCommand = new SqlCommand ();
The reference of these four sqlcommands actually points to the instance of a SqlCommand object., We also need to set the relevant attributes (Connection, CommandType, CommandText, Parameters) of each SqlCommand object. Here, refer to the related knowledge of SqlCommand.
Two Methods of the SqlDataAdapter object:
Fill (DataSet, string );// SqlDataAdapter automatically calls its SelectCommand object to query the hard disk database, and adds the query result to the memory data set.
Update (DataSet, string );// SqlDataAdapter automatically calls the corresponding InsertCommand, DeleteCommand, and UpdateCommand to update the corresponding data to the hard disk database based on the data changes in the memory data set.

Notes for using the SqlDataAdapter object:
A. The SqlDataAdapter object contains four SqlCommand objects. These four SqlCommand objects have the same attributes and methods as common SqlCommand objects. (
B. the new keyword must be used to instantiate the SqlDataAdapter object. After the SqlDataAdapter object is instantiated, reference of four SqlCommand objects is automatically generated, but the four SqlCommand objects are not instantiated and initialized, it needs to be instantiated and initialized one by one.
C. The Fill () method of the SqlDataAdapter object automatically calls its SelectCommand object. The Update () method automatically calls its InsertCommand, UpdateCommand, and DeleteCommand.
D. When using the SqlDataAdapter object to operate the database, you do not need to explicitly open or close the connection. When Fill () and Update () are executed, the link is automatically opened and closed.
E. The essence of using the SqlDataAdapter object to operate a database is that the SqlDataAdapter object calls its four SqlCommand objects.
F. Operations on datasets in the memory can only be performed using the properties and methods of DataSet and its sub-objects, while operations on databases must be performed using SQL statements.
For example:
Private static SqlCommand GenerateSelectCommand (SqlConnection conn)
{
SqlCommand aCommand = new SqlCommand ("RegionSelect", conn );
ACommand. CommandType = CommandType. StoredProcedure;
ACommand. UpdatedRowSource = UpdateRowSource. None;
Return aCommand;
}
DataSet ds = new DataSet ();
SqlDataAdapter da = new SqlDataAdapter ();
Da. SelectCommand = GenerateSelectCommand (conn );
Da. Fill (ds, "Region ");

Weak DataSet and strong DataSet (
When we use the Fill () method of the SqlDataAdapter object to query data, the table is automatically created in the DataSet in the memory and corresponding columns and rows are created, the DataSet created in this way is of a weak type. Each data in its data table is of the object type. Although the object type data can accept any data, this is also a weakness of the weak type. Because of its non-strict restrictions on the data type, it can store the wrong data type, so that an error occurs when updating the data. In addition, it is of the object type. During data operations, unpacking and packing are often required. The Unpacking and packing process consumes a lot of system resources, will reduce the program performance from a program.
Because weak DataSet has these defects, we recommend that you use strong DataSet. A strongly typed DataSet is to create a custom DataSet derived from DataSet in advance, add the specified table and column to it, and specify the Data Types of its tables and columns. Then, use table ing and column ing to map it to the tables and columns in the database. In this way, the problem of weak DataSet will not occur.

DataSet Problems
Although DataSet and SqlDataAdapter can be called "best practices" for database operations, they are newly added data access methods in ADO. NET. However, for most developers, this data access is not very optimistic. One of the major reasons is that this kind of DataSet is to load the "tables" table in the database to the "tables" table in the memory. In this memory, the orders table is not always suitable for the program code of "Object World". It is often clumsy and complicated to process complicated business logic. Therefore, we tend to use entity classes and generic sets to achieve data access, or use DataSet and entity classes for data operations, the design and use of entity classes and generic sets are described in the next article.