1. Dataset
ObjectL
And sqldatareader
Object comparisonSqldatareader is a fast and efficient structure for obtaining query results. It only supports Reading query results one way forward and the data is read-only. Dataset allows you to view query results cyclically and cyclically, and supports sorting, searching, and caching and modifying data. L DataSet object member dataset contains the datatable object and datarelation object. Datatable objects include datarow, datacolumn, and Constraint Objects.
2. datatable
ObjectThe datatable object includes the columns attribute and the rows attribute, which are respectively the datacolumn set and the datarow set. They represent the collection of all columns and rows in the result set respectively. L
Datatable
Reading dataThe Rows attribute of the datatable is used to read data in the datatable. The parameter specifies an integer to represent the data row to be accessed.
Datarow ROW = Ds. Tables [0]. Rows [0]; // Indicates the first row of the current behavior result set
Row
[0
]Or
Row
["Orderid"
];// Indicates the first column of the current row to be accessed or the column named orderid in the current row. The index-based Data Reading method is faster than the string-based method. The following describes how to traverse query result sets cyclically:
Foreach (datarow row in DS. Table [0]. Rows)
Foreach (datacolumn Col in DS. Table [0]. columns)
Consol. writeline (row [col]);
3.
Validate datatable
Data inThe database provides various constraints to ensure data validity. When the database query results are stored in the offline object dataset, after adding, deleting, and modifying data in dataset, sqldataadapter submits the data to the database. To ensure that the processed data is valid for the database, datatable needs to provide a Data Validity constraint mechanism similar to that of the database to ensure that the data in the dataset is also subject to the validity constraint of the database after offline processing. L
Datacolumn
Attribute
Readonly
Allowdbnull
Maxlength
Unique (
Uniqueness)L
Datatable
Constraints
Set
Uniqueconstraint
ClassSetting the unique attribute of datacolumn to true is also the uniqueness constraint that defines the column in The datatable. The display definition uniqueness constraint is to add the newly created uniqueconstraint object to the constraints set.
Primarykey
AttributeThe datatable class defines the primary key of the table through the primarykey attribute. The primarykey attribute contains the datacolumn object array.
Foreignkeyconstraint
ClassGenerally, you can create a datarealation between two able objects in the dataset to implicitly create a foreign key constraint.
Create a able in code
Object
Create a able
Object
Datatable dt = new datatable ("tablename ");// Create a able object and name it at the same time
To Dataset
Tables
Set to add able
Datatable dt = Ds. Tables. Add ("orders ");// Create a able and add it to Dataset
Datatablemapping DTM = new datatablemapping ();
DTM = da. tablemappings. Add ("", "orders ");// The first parameter is null, and the second parameter is consistent with the definition.
Datacolumnmapping [] columnmaps;
Columnmaps = new datacolumnmapping []
{
New datacolumnmapping ("orderid ","
Order No "),
......
}
DTM. columnmappings. addrange (columnmaps );
Datacolum Col = DT. Columns. Add ("
Order Number ", typeof (INT ))// Add a column to the datatable. The column name is consistent with that defined in columnmaps.
......// Set Col attributes
Da. Fill (DT );The dataset attribute of the datatable determines whether the datatable belongs to a dataset. If the datatable belongs to a tables set of a dataset, the dataset is returned. Otherwise, null is returned. A ableablek can only exist in one dataset. To add a datatable to multiple dataset, you must use the copy or clone method.
Extract Architecture InformationBy default, dataset and able returned by the fill method of the dataadapter class do not contain schema information. If a datatable does not contain a column, dataadapter automatically adds these columns to the datatable. The dataadapter class can return the result set with schema information through the missingschemaaction attribute or the fillschema method. The returned result assembly with schema information results in a large amount of system consumption. Generally, you can use the code to provide the datatable and dataset architectures, instead of using the above two methods. L
Set datatable
Constraints
Set primary key primarykey
AttributeThe primarykey attribute of dtatatable primary key is an array of datacolumn objects.
Datatable TBL
= Ds. Tables. Add ("MERs ");
TBL. Columns. Add ("orderid", typeof (INT ));
TBL. Columns. Add ("productid", typeof (INT ));
TBL. primarykey = new datacolumn [] {
TBL. Columns ["orderid"],
TBL. Columns ["productid"]};The constraints set that adds other constraints to the able class has an overloaded add method, which can be used to add new primary key values, unique keys, and foreign key constraints.
The method for explicitly creating uniqueness and foreign key constraints is as follows::
Datatable. Constraints. Add (New uniqueconstraint (...));
Datatable. Constraints. Add (New foreignkeyconstraint (...));
The code for implicit primary key creation and uniqueness constraints is as follows:
Datatable. Constraints. Add ("pk_orderid", orders. Columns ["orderid"], true );
Datatable. Constraints. Add ("uk_customerid", orders. Columns ["customerid"], false );// The first parameter indicates the name of the created constraint, the second parameter indicates the column to which the constraint is added, and the third parameter true indicates the primary key constraint and false indicates the uniqueness constraint.
Implicitly create a foreign key constraint
Datatable. Constraints. Add ("fk_customerid", tblcustomers. Columns ["mermerid"]
, Tblorders. Columns ["mermerid"]);// The first parameter represents the constraint name, the second parameter represents the datacolumn of the parent table, and the third array represents the datacolunm in the child table. L
Process automatic incremental ColumnsIf auto increment is used in the database, the auto increment feature of ADO. Net helps to make the pending insertion consistent before these new rows are committed to the database.
Datacolumn Col = TBL. Columns. Add ("orderid", typeof (INT ));
Col. autoincrement = true;
Col. autoincrementseed =-1;
Col. autoincrementstep =-1;Ado. Net only generates a new auto-increment value based on the data in the datatable. It does not represent the next auto-increment value to be generated in the database. Therefore, compared with the new row in the database, the auto-increment value in the datatable is only a placeholder and cannot be directly submitted to the database. L adding expression-based column databases to datatable will avoid storing data that can be exported from existing data in the database. SQL supports that the query results contain computed columns.
Select unitprice, quantity, unitprice * quantity as itemtotal from [Order Details]After the query results of the preceding query statement are stored in the datatable, if unitprice or quantity is changed, the value of itemtotal in the datatable will not change. ADO. net supports creating expression-based datacolumn objects. You can set the expression attribute of a datacolumn to an expression instead of the computed expression in the query, in this way, the value of the expression changes with the value of the calculated item.
Datatable TBL = Ds. Tables. Add ("orders ");
TBL. Columns. Add ("quantity", typeof (INT ));
TBL. Columns. Add ("unitprice", typeof (decimal ));
TBL. Columns. Add ("itemtotal", typeof (decimal), "quantity * unitprice ");
4.
Modify datatable
Data inL
Add a new datarow
Datarowcollection
Class add
MethodThe newrow method of the datatable class returns a new datarow object, sets the item attribute to fill in columns, and then calls the add method of datarowcollection to add a new row to the datatable.
Datatable TBL = new datatable ("MERs ");
TBL. Columns. Add ("mermerid", typeof (string ));
TBL. Columns. Add ("companyName", typeof (string ));
Datarow ROW = TBL. newrow ();
Row ["mermerid"] = "NewCo ";
Row ["companyName"] = "new customer"
TBL. Rows. Add (ROW );Overload add method of datarowcollection
TBL. Rows. Add ("NewCo", "New Customer ");// The parameter corresponds to the values of the datatable columns. The rowstate of the new row is added as added by the add method.
Datatable
Class loaddatarow
MethodTBL. loaddatarow (new object () {"NewCo", "New Customer"}, false); the first parameter is the value list of each column of datarow, when the second parameter is false, the rowstate of datarow is set to added. If the parameter is true, the rowstate of datarow is unchanged. L
Modify existing row
Use datarow
Item
AttributeWhen modifying a datarow object, use the find method of the rows set to find the row to be modified, and then use its item attribute to reset the values of each column.
Datarow ROW = TBL. Rows. Find ("NewCo ");
If (ROW = NULL)
//
No related lines found
Else
Row ["companyName"] = "new value"
End if
Use datarow
Class beginedit
Method and endedit
MethodYou can call the beginedit and endedit methods to buffer the changes to the data rows and call the endedit method to save the changes to the rows. If you are sure to save the changes, you can call canceledit to cancel the changes, this row is returned to the beginedit status.
Datarow ROW = TBL. Rows. Find ("NewCo ");
If (ROW = NULL)
//
No related lines found
Else {
Row. beginedit ();
Row ["companyName"] = "new value"
Row. endedit ();
End if
Use datarow
Itemarray
AttributeYou can use the itemarray attribute to obtain and modify data in the entire datarow object. Each item corresponds to a column in datarow and can update multiple column values in a row at a time.
Datarow row
= TBL. Rows. Find ("NewCo ");
Row. itemarray = new object [] {null, "New Value "};// Null indicates that the corresponding column does not need to be changed.
Process datarow
NULL in
ValueThe isnull method of the datarow class can be used to check whether a column is null; the isnull method accepts a column name, an integer of the column index, or a datacolumn object as a parameter. If the value of a column needs to be set to null, the Value Attribute of the dbnull class should be used.
Row ["companyName"] = dbnull. value;L
Delete datarow
Delete
MethodTo delete a data row, you only need to call the delete method of datarow. However, this method does not delete the data row from the datatable, but by ADO. net mark the row as pending deletion, so that the pending deletion can be submitted to the database using the dataadapter class. If the row is completely deleted from the datatable, the row will not be deleted from the database when you submit the pending changes to the dataset. Remove the remove or removeat method of the datarowdatarowcollection class to completely remove a data row from the datatable, instead of marking the data row as a pending change
Datarow row
= TBL. Rows. Find ("NewCo ");
TBL. Rows. Remove (ROW );
// Or
TBL. Rows. removeat (TBL. Rows. indexof (ROW ));In addition, the dataset and able classes each have a clear method, which can clear all their data objects, but retain the internal structure. L
Datarow
Rowstate
AttributeDataset can perform data cache changes. Before submitting these changes to the database, the cache changes do not affect the data of the database. Database changes include insertion, update, and deletion. The rowstate attribute of datarow records the data changes of dataset. The rowstate attribute value is the value in the datarowstate enumeration. The list is as follows:
| Constant |
Value |
Description |
| Unchanged |
2 |
This row does not contain any changes |
| Detached |
1 |
This row is not a member of datatabler |
| Added |
4 |
This row has been added to the datatable but does not exist in the database. |
| Modified |
16 |
This row contains pending changes |
| Deleted |
8 |
This row contains pending Deletion |
After the sqldataadapter class successfully submits the pending changes stored in dataset, it calls the datarow acceptchanges method to clear the pending changes stored in datarow. The rejectchanges method of the datarow class, which is used to Cancel Changes stored in datarow.
