C # DataSet

First, the basic concept

The dataset is the central concept of ADO. Datasets can be treated as an in-memory database, which is a separate collection of data that is not dependent on the database. The so-called independence, that is, even if the data link is disconnected, or shut down the database, the dataset is still available, the dataset is internally XML to describe the data, because XML is a platform-independent, language-independent data Description language, and can describe the complex relationship of data, For example, the data for a parent-child relationship, so the dataset can actually accommodate data with complex relationships and is no longer dependent on the database link.

In a typical multi-tier implementation, the steps for creating and refreshing a DataSet and updating the original data in turn include: Generate and populate each DataTable in the dataset with data from the data source by using DataAdapter.　　Change the data in a single DataTable object by adding, updating, or deleting a DataRow object.　　Call the GetChanges method to create a second dataset that reflects only the changes made to the data.　　Call the Update method of DataAdapter and pass the second DataSet as a parameter.　　Call the Merge method to merge the changes from the second DataSet into the first one. Call AcceptChanges for the DataSet. Alternatively, call RejectChanges to cancel the change. It is important to note that all data in the dataset is loaded in memory and can improve data access speed and drive data security. Greatly improves the speed and stability of the program operation. Data model because a dataset can be considered an in-memory database, it can also be said that a dataset is a collection of data tables that can contain any number of data tables (DataTable), and the Data Table (DataTable) in each dataset Corresponds to a data table (table) in a data source or a Data View (view). The data table is essentially a collection of rows (DataRow) and columns (DataColumn) in order to protect the correctness of data records in memory, avoid read and write conflicts at concurrent accesses, and the DataTable in the DataSet object is responsible for maintaining each record. Save the initial state and the current state of the record, respectively. It can be seen from this that a dataset is a distinct concept from a recordset that can only hold a single sheet of data. Iii. Use introduction 1, create DataSet ds = new DataSet ("DatasetName"); 2, view call

Da. Fill (ds, "Orders");

DataTable tbl = ds. TABLE[0];

foreach (DataColumn col in tbl. Columns)

Console.WriteLine (Col. ColumnName);

3. View the data returned by SqlDataAdapter

DataRow object

DataTable tbl = ds. TABLE[0];

DataRow row = tbl. ROW[0];

Console.WriteLine (row["OrderID"]);

Check the data stored in the DataRow

DataTable tbl = row. Table;

foreach (DataColumn col in tbl. Columns)

Console.WriteLine (Row[col]);

Check for DataRow objects in dattable

foreach (DataRow row in tbl. Rows)

DisplayRow (row);

4. Verifying data in a dataset

Verify the properties of the DataColumn: Readonly,allowdbnull,maxlength,unique

Constrains collection of DataTable objects: Uiqueconstraints,primarykey,foreignkeyconstraints

There is usually no need to deliberately create a foreignkeyconstraints, because one is created when a relationship is created between the two DataTable objects in the dataset.

Retrieving schema information using Sqldataadapter.fill mode

5. Write code to create a DataTable object

To create a DataTable object:

DataTable tbl = new DataTable ("TableName");

Add a DataTable to the table collection of a DataSet object

DataSet ds = new DataSet ();

DataTable tbl = new DataTable ("Customers");

Ds. Tables.add (TBL);

DataSet ds = new DataSet ();

DataTable tbl = ds. Tables.add ("Customers");

A DataTable object can exist only in at most one DataSet object. If you want to add a DataTable to more than one dataset, you must use the Copy method or the Clone method. The Copy method creates a new Datatable;clone method that is the same as the original DataTable structure and contains the same counterpart, creating a new DataTable with the same structure as the original DataTable, but without any rows.

Adding columns to a DataTable

DataTable tbl = ds. Tables.add ("Orders");

DataColumn Col =tbl. Columns.Add ("OrderID", typeof (int));

Col. Allowdbnull= false;

Col. Maxlength= 5;

Col. Unique= true;

Tbl. primarykey= new DATACOLUMN[]{TBL. columns["Customersid"]};

When the primary key is set, the AllowDBNull is automatically set to false;

Working with AutoIncrement columns

DataSet ds = new DataSet ();

DataTable tbl = ds. Tables.add ("Orders");

DataColumn col = tbl. Columns.Add ("OrderID", typeof (int));

Col. autoincrement= true;

Col. autoincrementseed=-1;

Col. autoincrementstep=-1;

Col. readonly= true;

To add an expression-based column

Tbl. Columns.Add ("Itemtotal", typeof (Decimal), "Quantity*unitprice");

6. Modify the DataTable content

Add a new DataRow

DataRow row = ds. tables["Customers"]. NewRow ();

row["CustomerID"]= "ALFKI";

Ds. tables["Customers"]. Rows.Add (row);

Object[] avalues={"ALFKI", "Alfreds", "Anders", "030-22222"};

Da. tables["Customers"]. Loaddatarow (Avalues,false);

Modify when moving forward

Modify the contents of a row the corresponding content in the database is not automatically modified, and modifications to the row are considered to be subsequent use of the SqlDataAdapter object to submit pending changes to the database.

DataRow Rowcustomer;

rowcustomer= ds. tables["Custoemrs"]. Rows.find ("ANTON");

if (rowcustomer== null)

No customer found

Else

{

rowcustomer["CompanyName"]= "Newcompanyname";

rowcustomer["ContactName"]= "Newcontactname";

}

It is recommended to use this method

DataRow Rowcustomer;

rowcustomer= ds. tables["Custoemrs"]. Rows.find ("ANTON");

if (rowcustomer== null)

No customer found

Else

{

Rowcustomer.beginedit ();

rowcustomer["CompanyName"]= "Newcompanyname";

rowcustomer["ContactName"]= "Newcontactname";

Rowcustomer.endedit ();

}

Null indicates that the column's data is not modified

Obejct[] Acustomer ={null, "Newcompanyname", "Newcontactname", null}

DataRow Rowcustomer;

rowcustomer= ds. tables["Customers"]. Rows.find ("ALFKI");

Rowcustomer.itemarray= Acustomer;

Handling Null values for DataRow

To see if it is empty

DataRow Rowcustomer;

rowcustomer= ds. tables["Customers"]. Rows.find ("ALFKI");

if (Rowcustomer.isnull ("Phone"))

Console.WriteLine ("It" "snull");

Else

Console.WriteLine ("It" ' Snot Null ");

Give null value

rowcustomer["Phone"]= DBNull.Value;

Delete DataRow

DataRow Rowcustomer;

rowcustomer= ds. tables["Customers"]. Rows.find ("ALFKI");

Rowcustomer.delete ();

Clear DataRow

DataRow Rowcustomer = ds. tables["Customers"]. Rows.find ("ALFKI");

Rowcustomer.itemarray= Acustomer;

Da. tables["Customers"]. Remove (Rowcustomer);

Or

Ds. tables["Customers"]. RemoveAt (Intindex);

Using the Datarow.rowstate property: unchanged,detached,added,modified,deleted

Privatevoid Demonstraterowstate ()

{

Run a function to create a datatablewith one column.

DataTable myTable = maketable ();

DataRow Myrow;

Create a new DataRow.

Myrow = Mytable.newrow ();

Detached row.

Console.WriteLine ("New Row" +myrow.rowstate);

MYTABLE.ROWS.ADD (Myrow);

New row.

Console.WriteLine ("AddRow" +myrow.rowstate);

Mytable.acceptchanges ();

Unchanged row.

Console.WriteLine ("AcceptChanges" + myrow.rowstate);

myrow["FirstName"] = "Scott";

Modified row.

Console.WriteLine ("Modified" +myrow.rowstate);

Myrow.delete ();

Deleted row.

Console.WriteLine ("Deleted" +myrow.rowstate);

}

Check for pending changes in the DataRow

DataRow Rowcustomer;

rowcustomer= ds. tables["Customers"]. Rows.find ("ALFKI");

rowcustomer["CompanyName"]= "Newcompanyname";

String Strnewcompanyname,stroldcompanyname;

Console.WriteLine (rowcustomer["CompanyName", DataRowVersion.Current]);

Console.WriteLine (rowcustomer["CompanyName", DataRowVersion.Original]);

Traversing a DataSet

foreach (DataTable dt in dataset.tables)

foreach (DataRow dr in Dt. Rows)

foreach (DataColumn dc in Dr. Table.columns)

Console.WriteLine (DR[DC]);

Iv. attribute Methods

1. DataSet

Property

CaseSensitive: Used to control whether string comparisons in a DataTable are case-sensitive.

DatasetName: The name of the current dataset. If not specified, the property value is set to "NewDataSet". If the dataset content is written to an XML file, DatasetName is the root node name of the XML file.

DesignMode: Returns False if Dataset,designmode in the component is used at design time to return true.

HasErrors: Indicates whether the DataRow object in the dataset contains an error. If you commit a batch of changes to the database and set the ContinueUpdateOnError property of the DataAdapter object to True, you must check the dataset's HasErrors property to determine if there is an update failure after committing the change.

namespace and prefix: Specifying XML namespaces and prefixes

Relations: Returns a DataRelationCollection object.

Tables: Checks the existing DataTable object. Accessing the DataTable by index has better performance.

Method

AcceptChanges and RejectChanges: accepts or discards all pending changes in the dataset. When you call AcceptChanges, the RowState property of all rows with the RowState property value of added or modified is set to unchanged. Any DataRow object marked as deleted will be removed from the dataset. When RejectChanges is called, any DataRow object marked as added will be removed from the dataset, and the other modified Datrow objects will return to the previous state.

Clear: Clears all DataRow objects in the dataset. This method is faster than releasing a dataset and then creating a new dataset of the same structure.

Clone and copy: Using the Copy method creates a new dataset with the same structure and the same row as the original dataset. Using the Clone method creates a new dataset with the same structure, but does not contain any rows.

GetChanges: Returns a new dataset with the same structure as the original DataSet object, and also contains all pending changes in the original dataset.

Getxml and GetXmlSchema: Use the Getxml method to get the string from the contents of the dataset and her schema information into XML format. If you only want to return schema information, you can use GetXmlSchema.

Haschange: Indicates whether the dataset contains a DataRow object that has pending changes.

Merge: Loads data from another dataset, DataTable, or a set of DataRow objects in an existing dataset.

ReadXml and WriteXml: Use the ReadXml method to load XML data into a dataset from a file, TextReader, data flow, or XmlReader.

Reset: Returns the DataSet to an uninitialized state. If you want to discard an existing dataset and start working on a new dataset, it is better to use the Reset method than to create a new instance of the DataSet.

Event

MergeFailed: Triggered when an exception occurs in the merge method of the DataSet.

2. DataTable

Property

Method

Event

ColumnChanged: Triggered after the contents of the column have been changed

Columnchangding: Triggers before the contents of a column are changed

Rowchanged,rowchanging,rowdeleted,rowdeleting.

3, DataColumn

Property

4. DataRow

Property

Haserror: Determines whether the row contains an error.

Item: Accesses the contents of a column by specifying the number of columns in the row, the name of the column, or the DataColumn object itself.

ItemArray: Gets or sets the value of all columns in the row.

RowError: Returns a string containing the row error information.

RowState: Returns the value in the DataRowState enumeration to represent the current state of the row.

Table: Returns the DataTable where the DataRow object resides.

Method

AcceptChanges and RejectChanges: Commit and discard pending changes.

BeginEdit, CancelEdit, EndEdit

Clearerrors: Clears all errors in the DataRow.

The Delete:delete method does not actually remove the DataRow from the row collection of the DataRow table. When you call the Delete method of a DataRow object, ADO. NET marks the row for deletion, and then calls the Update method of the SqlDataAdapter object to delete its corresponding row in the database.

If you want to completely delete a DataRow, you can call the Delete method, then call its Acceptechanges method, and you can use the Remove method of the DataRowCollection object to accomplish the same task.

DataSet ds =new Datast ();

Datatabletel =new DataTable ();

Ds.. Tables.add (tel);

stringcode=ds.tables["Tel"].rows[0][0].tostring ();

V. Examples of usage

Operation of the dataset:

DataSet ds=new DataSet ();

DataTable dt=new DataTable ("NewTable");

Ds. Tables.add (DT);

DataSet ds=new DataSet ();

DataTable Dt=ds. Tables.add ("newtable");

Both of these methods can add a DataTable to the dataset to choose from. After you add a DataTable, you add rows and columns to it.

DataSet ds=new DataSet ();

Datatabledt=ds. Tables.add ("Newtables");

DataColumn Col=dt. Columns.Add ("NewColumn", typeof (int));

Col. Allowdbnull=false;

Col. maxlength=4;

Col. Unique=true;

The preceding code adds a column named "NewColumn" to the DataTable in the dataset with the type int and NOT NULL, with a maximum length of 4 and a true uniqueness.

Dt. Primarykey=newdatacolumn[]{dt. columns["ID"}

This code continues with the above code, adding a primary key column to a DataTable, which is a data group, and if there are multiple primary keys, simply add a column to the array. As follows:

Dt. Primarykey=newdatacolumns[]{dt. columns["OrderID"],dt. columns["ProductID"]}

To add a foreign key:

ForeignKeyConstraint FK;

Fk=newforeignkeyconstraint (ds. tables["Customers"]. columns["CustomerID"],ds. tables["Orders"]. columns["CustomerID"]);

Ds. tables["Orders"]. Constraints.add (FK);

The above code would add a FOREIGN key constraint if the primary key has been created for the Cusomers table and orders.

The above is to create a constraint based on the CustomerID of the Customers table and the Orders table.

The following describes modifying the contents of a DataRow:

DataRow Dr=ds. tables["Customer"]. Rows.find ("ANTON");

if (dr==null)

Else

{

Dr. BeginEdit ();

dr["CompanyName"]= "newvalue";

dr["ContactName"]= "newValue2";

Dr. EndEdit ();

}

The above code locates the rows in the DataTable through the Find method of the row collection, finds the "ANTON" row, and modifies the values of the CompanyName column and ContactName column in the ANTON row. You can also call CancelEdit to cancel the modification by BeginEdit and EndEdit to cache the modification of the row.

Determine if a column is a null value:

DataRow Dr=ds. tables["Customers"]. Rows.find ("AAA");

if (Dr. IsNull ("ContactName");

..

Else

dr["ContactName"]=dbnull.value

This is to determine if the ContactName column is empty, and if it is not the null value, ah, it is very unreasonable, here only to demonstrate the practice of assigning null values to the column.

Delete DataRow:

There are two ways to remove the Datarow,delete method and the Remove method and the RemoveAt method. The difference is that the Delete method does not actually delete a row from the DataTable, but instead flags it as a delete, just a token, and the Remove method is a real delete from the DataRow, and the RemoveAt method is the index of the underlying row to delete. Column:

DataRow Dr=ds. tables["Table"]. Rows.find ("a");

Ds. tables["Table"]. Remove (DR);

Or

Ds. tables["Table"]. Remove (index);

The DR is the line where "a" is located, and it is deleted after being detected, and index is the number of "a". For its usage in the dataset, refer to the MSDN

DataRow Dr=ds. tables["Customers"]. Rows.find ("AAA");

if (Dr. IsNull ("ContactName");

..

Else

dr["ContactName"]=dbnull.value

This is to determine if the ContactName column is empty, and if it is not, assign null values here only to demonstrate the practice of assigning null values to columns.

Usingsystem.data;

Usingsystem;

usingSystem.Windows.Forms;

Classdatat

{

static DataTable dt;//= new DataTable ();

Static DataSet ds;

static void Method1 ()

{

DT = new DataTable ("Name");

ds = new DataSet ();

Dt. Columns.Add (Newdatacolumn ("ID", typeof (Int32)));

Dt. Columns.Add (Newdatacolumn ("Name", typeof (String)));

Dt. Columns.Add (Newdatacolumn ("Sex", typeof (String)));

Dt. Columns.Add (Newdatacolumn ("Addr", typeof (String)));

}

static void Add (int id,string name,stringsex,string addr)

{

DataRow dr = dt. NewRow ();

dr["id"] = ID;

dr["name"] = name;

dr["sex"] = sex;

dr["Addr"] = Addr;

Dt. Rows.Add (DR);

}

static void Main ()

{

Datat DT = new Datat ();

Method1 ();

Add (+, "Join", "Male", "Beijing");

Add (101, "Lily", "FeMale", "Beijing");

Add (102, "JIM", "Male", "Beijing");

Ds. Tables.add (DT);

foreach (DataRow dr in Dt. Rows)

{

MessageBox.Show (dr["ID"). ToString () + "" + dr["Name"]. ToString () + "" +dr["Sex". ToString () + "" +dr["Addr"]. ToString (), "Message");

Console.WriteLine (dr["ID"). ToString () + "" + dr["Name"]. ToString () + "" +dr["Sex". ToString () + "" +dr["Addr"]. ToString ());

}

Try

{

foreach (DataTable dt2 in DS. Tables)

foreach (DataRow Dr in DT2. Rows)

Console.WriteLine (dr["ID"). ToString () + "" + dr["Name"]. ToString () + "" + dr["Sex". ToString () + "" + dr["Addr"]. ToString ());

}

catch (Exception ex)

{

Console.WriteLine ("DKFJKSDJFK");

}

}

}

C # DataSet