Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use DataSet,DataTable,DataView in C#". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use DataSet,DataTable,DataView in C#.

Create Dataset and DataTableDataSet ds = new DataSet (); / / DataSetName defaults to "NewDataSet" DataTable table = ds.Tables.Add ("Customers"); / / or / DataTable table1 = new DataTable ("Customers"); / / ds.Tables.Add (table1); / / add columns DataColumn col=table.Columns.Add ("OrderID", typeof (int)); / / Type.GetType ("System.Int32") col.AllowDBNull=false; col.MaxLength=5; col.Unique = true / / automatically add a UniqueConstraint constraint table.PrimaryKey = new System.Data.DataColumn [] {table.Columns ["CustomerID"]} for this column; / / set the primary key, AllowDBNull is automatically set to false, and automatically apply the UniqueConstraint constraint / / auto-grow column DataColumn col1=table.Columns.Add ("OrderID", typeof (int)); col.AutoIncrement=true; col.AutoIncrementSeed=1; col.AutoIncrementStep=1; col.ReadOnly=true;// computed column table.Columns.Add ("ItemTotal", typeof (decimal), "Quantity*Unique") Add rows / / use the Add () method of the DataRowCollection object DataRow row = ds.Tables [0] .NewRow (); row ["CustomerID"] = "ALFKI"; row ["CustomerName"] = "XX"; ds.Tables [0] .Rows.Add (row); / / use the LoadDataRow method of the Table object object [] rowData = {"ALFKI", "XX", "x", ""}; ds.Tables [0] .LoadDataRow (rowData, false) Modify the line / / use the BeginEdit and EndEdit methods of the DataRow object. DataRow row = ds.Tables [0] .Rows.Find ("ANTON"); / / look up in the primary key column, you can use the index to access the row ds.Tables [0] .Rows [3] if (row! = null) {row.BeginEdit (); row ["CompanyName"] = "New Company"; row ["ContactName"] = "New Contact"; row.EndEdit ();} / assign using the DataRow object ItemArray attribute. Objec [] rowData = {null, "New Company", "New Contact", null}; / / null means that the column data DataRow row = ds.Tables [0] .Rows.Find ("ALFKI") is not modified; row.ItemArray = rowData;// single field modifies if (row.IsNull ("phone")) {row ["phone"] = DBNull.Value;} / / to determine whether the field value is empty or not. 4. Delete a row / / Delete method is not actually to delete a line from DataTable, but it is best to use the Delete method when it is marked as delete. / / it is best to use the Delete method to first mark the deletion and then submit it uniformly with the table.AcceptChanges () method, or the RejectChanges () method to roll back the line state DataRow [] rows = ds.Tables [0] .Select ("Compane like'a%'"); foreach (DataRow row in rows) {row.Delete () } ds.Tables [0] .AcceptChanges (); / / if the RowState of the row is Added, RowState will become Detached after calling Delete. After calling AcceptChanges, the row is removed from the table. / / if the RowState of the line is Unchanged, RowState will become Deleted after calling Delete. After calling AcceptChanges, the row is removed from the table. The / / Remove and RemoveAt methods actually delete a row from the DataRow, which is equivalent to calling the DataRow.Delete () method first and then the AcceptChanges () method. The state of DataRowState has changed twice. DataRow row = ds.Tables [0] .Rows.Find ("ALFKI"); ds.Tables [0] .Rows.Remove (row); / / ds.Tables [0] .RemoveAt (index) / / Clear method ds.Tables [0] .clear (); ds.Clear (); data line status (RowState)

UnChanged status: refers to the state that the row in the table has not changed since it was created, or that the row has not changed since the last time it was modified.

Added state: this state means that a row has been added to the table, but the AcceptChanged method of the table object has not been called. When the AcceptChanged method is called, all rows in the Added state become Unchanged.

Modified status: this state indicates that the row has been modified. When the AcceptChanged method is called, all rows in the Modified state become Unchanged

Deleted status: this state indicates that the row has been deleted from the table, but the AcceptChanged method of the table object has not been called

Detached status: this state indicates that the row does not belong to any table, or has been detached from the table and no longer belongs to the DataRow object of any table.

The newly created row (DataRow object) is in the Detached state, and after it is added to the DataTable object, the state of the DataRow object becomes Added. If the DataRow is modified, the line is in the Modified state. If you remove the DataRow object from the table using the Remove method, or if you remove the row using the Delete method and the AcceptChanged method, the row is in the Detached state.

Merging two DataSet// merges the specified dataSet, datatable, or datarow [] with the current ds, in the process, changes in the current ds are retained or discarded based on the given parameters and incompatible schemas are processed. Ds.Merge (DataSet otherDataSet, bool preserveChanges, System.Data.MissingSchemaAction missingSchemaAction); preserveChanges: whether to retain changes to the current ds when two datasets contain records with the same primary key value. Default is false.

False: updates the current ds record with the otherDataSet record. The result of the primary key line where ds exists but otherDataSet does not exist will be deleted.

True: keep the record of ds and add a new record.

MissingSchemaAction: how to handle two data sets that contain different columns (two DataSet with different schemas).

Add: add a new column of otherDataSet to ds.

AddWithKey: add the new column and primary key information of otherDataSet to ds.

Error: if the specified column mapping is missing, an InvalidOperationException is generated.

Ignore: ignore any new columns.

Ds.Tables [0] .merge (dataSet.Tables [0], false, MissingSchemaAction.Add); 6. Add relationship ds.Relations.Add ("CustomerToOrders", / / relationship name: the default relationship name is DataRelation ds.Tables ["Customer"] .Columns ["CustomerID"], / / parent column, can be array ds.Tables ["Orders"] .Columns ["CustomerID"] / / child column, can be array)

Note: UniqueConstraint is created by default to the parent table and ForeignKeyConstraint is constrained to the child table.

You can use two arguments to one of the constructors of DataRelation to avoid automatically creating constraints.

/ / get the child line from the parent line: GetChildRows DataRow [] rows = ds.Tables ["Customer"] .Rows [0] .GetChildRows ("CustomerToOrders"); / / CustomerToOrders is the relationship name. / / get lines from child lines: GetChildRows or GetParentRow DataRow [] rows = ds.Tables ["Orders"] .Rows [0] .GetParentRows ("CustomerToOrders"); / get different line versions: if (rows [0] .HasVersion (DataRowVersion.Proposed) {rows = rows [0] .GetChildRows ("CustomerToOrders", DataRowVersion.Proposed) } data row version (DataRowVersion)

Current: represents the current value of the row. No version of the row exists for the row in the deleted state.

Original: represents the original value of the row. No version of the row exists for the row in Added state

Proposed: represents the suggested value of the row. Rows that are not part of the table, that is, rows in the Detached state are stored in the row version; there is also a row version for the row being edited.

Default: represents the default version of the row. The default row version for rows in the Added,Modified or Unchanged state is Original for rows in the deleted state for Current;. The default version of a row in the Detached state is proposed.

When the AcceptChanged method is called, all rows in the deleted state become Detached, which is removed. The rest of the rows become Unchanged, and the values in the Original version are overwritten to the values in the Current row version.

7. Data constraint. 1. Primary key constraint, using UniqueConstraintUniqueConstraint unique = new UniqueConstraint (ds.Tables [0] .Columns ["phone"]); ds.Tables [0] .Constraints.Add (unique); 2. Foreign key constraint: ForeignKeyConstraintForeignKeyConstraint fk = new ForeignKeyConstraint ("CustomerOrder", ds.Tables ["Customer"]. Columns ["CustomerID"], / / parent column, can be array ds.Tables ["Orders"]. Columns ["CustomerID"] / subcolumn, can be an array); fk.UpdateRule = Rule.Cascade Fk.DeleteRule = Rule.SetNull;fk.AcceptRejectRule = Rule.None;ds.Tables ["Orders"] .Constraints.add (fk); / / add a constraint to the word table, and the target automatically adds a Unique constraint.

When performing an operation on a column or row of a schedule, use these rules to determine the operation rules that should be performed on the rows of the child table.

Rule.Cascade: cascading (default)

Rule.None (No Action)

Rule.SetDefault

Four kinds of Rule.SetNull.

Note: the prerequisite for enabling constraints is:

Ds.EnforceConstraints = true;//true is the default

AccpetRejectRule is executed only when the AcceptChanges or RejectChanges method is executed on DataSet. Executes later than UpdateRule and DeleteRule.

VIII. DataTable event

Dt.ColumnChanging

Dt.ColumnChanged

Dt.RowChanging

Dt.RowChanged

Dt.RowDeleting

Dt.RowDeleted

Dt.TableClearing

Dt.TableCleared

Dt.TableNewRow

CustTable.ColumnChanged + = new DataColumnChangeEventHandler (Column_Changed); private static void Column_Changed (object sender, DataColumnChangeEventArgs e) {Console.WriteLine ("Column_Changed Event: name= {0}; Column= {1}; original name= {2}", e.Row ["name"], e.Column.ColumnName, e.Row ["name", DataRowVersion.Original]);} IX, DataSet other methods ds.Clone (); / / only clone a Datasetds.Copy () with the same structure / / copy a Datasetds.ReadXml with the same structure and content ("D:\ 1.xml"); ds.ReadXmlSchema ("D:\ 1.xml"); ds.WriteXml ("D:\ 1.xml", XmlWriteMode.WriteSchema); ds.WriteXmlSchema ("D:\ 1.xml"); ds.GetXml (): / / return mode and content XMLds.GetXmlSchema:// only return pattern information DataTable dt;dt.Compute (expression,filter) / / calculate dt.Select (filter,sort,DataViewRowState) 10. Select () method of DataTable

Get an array of DATAROW objects

Select ()

Select (string filterExpression)

Select (string filterExpression, string sort)

Select (string filterExpression, string sort, DataViewRowState recordStates)

Note: the above Select operation is case-insensitive (the recorded fields are not sensitive). If you need to be case-sensitive, you need to set the caseSensitive property of DataTable to true.

DataRow [] arrRows = table.Select ("pubdate > ='# 1ca','tn','wa''"); / / the following statement selects DataRow DataRow [] arrRows = table.Select ("state in ('ca','tn','wa')") where the date of the Pubdate field is on or after January 1, 2000; / / supports in and returns all lines where "state" equals CA, TN or WA DataRow [] arrRows = table.Select ("state like' ca*'") / support like DataRow [] arrRows = table.Select ("isnull (state,0) = 0"); / / you can also use some Sql functions to select records in DataTable where the State field is empty DataRow [] arrRows = table.Select ("state='tn' and zip like '37records'"); / / you can also use And, Or and NotDataRow [] arrRows = table.Select ("id > 5", "id desc") / complete method with filtering, sorting, and row status: DataRow [] arrRows = table.Select ("id > 5", "id desc", DataViewRowState.Added); 11. DataView data View / / create data View DataView DataView dv = ds.Tables ["Author"] .DefaultView; / / or DataView dv = new DataView (ds.Tables ["Author"]); / / row filtering dv.RowFilter = "state='CA' and City=aa"; / / row filtering, the date needs to be surrounded by a # number. Or dv.RowFilter = "Sum" / / the available function Convert,Len,IsNull,IIF,SubStringdv.RowStateFilter = DataViewRowState.Added | DataViewRowState.OriginalRows;// primary key lookup int rowIndex = dv.Find ("wison"); / / pay attention to the lookup to get the row index. DataRowView [] rowviews = dv.FindRows ("Raing"); / / Field sort dv.Sort = "state desc,firstName asc"; / / add line dv.AllowNew = true;DataRowView newRow = dv.AddNew (); newRow ["customername"] = "aa"; / / modify line dv.AllowEdit = true;dv [0] ["customername"] = "aa"; / / delete line dv.AllowDelete = true;dv.Delete (1); / / traverse foreach (DataRowView rowview in dv) {rowview ["customername"] = "aa" } Line status of DataView (DataViewRowState):

Added: new line.

CurrentRows: the current row includes unchanged rows, new rows, and modified rows. (by default, DataViewRowState is set to CurrentRows.)

Deleted: deleted rows.

ModifiedCurrent: current version of the original data that has been modified

ModifiedOriginal: the original version of the modified data.

None: none.

OriginalRows: the original row consists of unchanged rows and deleted rows.

Unchanged: the row that has not changed.

12. DataViewManagerDataViewManager manager=ds.DefaultViewManager;// or new DataViewManager (ds) DataView dv=manager.CreateDataView (ds.Tables [0]); / / create DataView// or manager.DataViewSettings ["Order"] .Sort = "orderDate desc" so far, I believe you have a deeper understanding of "how to use DataSet,DataTable,DataView in C#". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.