Datatable operation guide

After defining the schema for the datatable, that is, after setting the required column name, you can add data rows to the table by adding the datarow object to the table's rows set. Similar to adding datacolumn, you can also create a datarow object in a table by using the datarow constructor or by calling the add method of the table's rows attribute.

The datacolumn object set represents the schema of the table ). Datatable saves all columns in the table through the internal datacolumncollection type. On the contrary, a set of datarow types indicates the actual data in the table. In this way, if the MERs table has 10 records, you can use 10 datarow types to list them. Members of the datarow class can insert, delete, evaluate, and operate the values in the table.

Create a datarow data row object. The implementation code is as follows:

// Create a mers MERs data table

Datatable customerstable = new datatable ("customers ");

// Create a new data row

Datarow Arow = customerstable. newrow ();

// Set the row Value

Arow [columnname] = data;

// Add data rows to the created MERs data table

Customerstable. Rows. Add (Arow );

Function Description:

Create an Arow row, pay data for a column name in this row, and add the row to the MERs table. Using datarow is somewhat different from using datacolumn, because you cannot directly create an instance of this type, but get a reference from a given datatable. For example, if you want to add a new row to the MERs table, the datatable. newrow () method can obtain the next space and then fill in the data in each column.

The implementation code is as follows:

// Create a data table

Datatable customerstable = new datatable ("customers ");

// Declare the row and column variables of the data table

Datacolumn column;

Datarow row;

// Create a new column, set the data column and column name of the column, and add the new column to the MERs table

Column = new datacolumn ();

Column. datatype = system. type. GetType ("system. int32 ");

Column. columnname = "custid ";

Customerstable. Columns. Add (column );

// Create another column

Column = new datacolumn ();

Column. datatype = type. GetType ("system. String ");

Column. columnname = "custlname ";

Customerstable. Columns. Add (column );

// Create a new row and add the row to the MERs table

For (INT I = 0; I <10; I ++)

{

Row = customerstable. newrow ();

Row ["custid"] = I;

Row ["custlname"] = "item" + I. tostring ();

Customerstable. Rows. Add (ROW );

}

Function Description:

Create two columns custid and custlname for the customerstable table, generate 10 rows in a loop, and add them to the customerstable table.

8.4.7 datatable: Update row

As described in the previous section, how to add a datarow row is required. Next, we need to know how to update an existing datarow row with a new value. The following describes how to update rows in datatable.

The most commonly used method is to first use the select () method to obtain rows that meet the given filtering conditions. Once the datarow is obtained, modify it accordingly. For example, if a new button is clicked, search for all rows with the value of 5 in the employeeid column of the able. Once these items are identified, you can change the value 5 of the employeeid column to 6. The instance code is as follows:

// Database connection string

String connectionstring =

"Data Source = Local; initial catalog = northwind; Integrated Security = true; userid = sa; Password = ";

// Obtain the data information in the orders table using the SQL statement.

String commandstring = "select * from orders ";

Sqldataadapter dataadapter = new sqldataadapter (commandstring, connectionstring );

Dataset dataset = new dataset ();

// Fill the dataset

Dataadapter. Fill (dataset, "orders ");

// Fill the data table

Datatable = dataset. Tables ("orders ");

// Create a variable used to filter the rows whose value is 5 in the employeeid Column

String filterstr = "employeeid = 5 ";

String stremployeeid = NULL;

// Query all rows with the value of 5 in the employeeid column.

Datarow [] employeeid = datatable. Select (filterstr );

// Change the value 5 of the employeeid column to 6.

For (INT I = 0; I <employeeid. length; I ++)

{

Datarow temp = employeeid [I];

Stremployeeid + = temp ["employeeid"] = 6;

Employeeid [I] = temp;

}

8.4.8 delete a able row programmatically

There are two ways to delete a datarow object from a able object: The Remove Method of the datarowcollection object and the delete method of the datarow object.

Both the remove and delete methods can delete the datarow row of the able, but the former is to delete the datarow from the datarowcollection, while the latter only marks the row as deleted. The actual deletion occurs only when the application calls the acceptchanges method. By using Delete, You can programmatically check which rows are marked as deleted before deletion. If a row is marked as deleted, its rowstate attribute is set to deleted.

When using dataset or datatable with dataadapter and relational data sources, use the delete method of datarow to remove rows. The delete method only marks the row as deleted in dataset or able without removing it. When the dataadapter encounters a row marked as deleted, it executes its deletecommand method to delete the row from the data source. Then, you can use the acceptchanges method to permanently remove the row. If you use remove to delete a row, the row is completely removed from the table, but the dataadapter does not delete the row from the data source.

The Remove Method of datarowcollection uses datarow as the parameter and removes it from the collection. The implementation code is as follows:

Datatable customerstable = new datatable ("customers ");

Datarow workrow = emptable. newrow ();

Customerstable. Rows. Remove (workrow );

The following example shows how to call the delete Method on datarow to change its rowstate to deleted. The implementation code is as follows:

Datatable customerstable = new datatable ("customers ");

Datarow workrow = emptable. newrow ();

Workrow. Delete ();

If you mark a row as deleted and call the acceptchanges method of the datatable object, the row will be removed from the datatable. In contrast, if rejectchanges is called, The rowstate of the row will be restored to the State before it is marked as deleted.

8.4.9 fill data with datatable

Dataset in ADO. NET is the memory resident representation of data. It provides a consistent relational programming model independent of the data source. Dataset indicates the entire dataset, which contains the relationships between tables, constraints, and tables. Because dataset is independent of the data source, dataset can contain local data of the application or data from multiple data sources. The interaction with existing data sources is controlled by dataadapter.

The selectcommand attribute of dataadapter is a command object used to retrieve data from the data source. The insertcommand, updatecommand, and deletecommand attributes of dataadapter are also command objects, which are used to manage data updates in the data source according to the modification of data in dataset.

The Fill Method of dataadapter is used to fill the dataset with the result of selectcommand of dataadapter. The dataset and able object to be filled in fill (or the name of the datatable to be filled using the row returned from selectcommand) as its parameter.

The fill method uses the datareader object to implicitly return the column names and types used to create a table in dataset and the data used to fill the table rows in dataset. Tables and columns are created only when they do not exist. Otherwise, fill uses the existing dataset architecture.

The following code uses the sqldataadapter object to retrieve data from the Microsoft SQL Server northwind database and fill it with the MERs table in the MERs dataset.

The implementation code is as follows:

// Use the fill method of dataadapter to fill data in the MERs table

String connectionstring =

"Data Source = Local; initial catalog = northwind; Integrated Security = true; userid = sa; Password = ";

String commandstring = "select * from MERs ";

Sqldataadapter dataadapter = new sqldataadapter (commandstring, connectionstring );

Dataset MERs = new dataset ();

Dataadapter. Fill (customers, "customers ");

8.4.10 implement SQL statement-like query in datatable

Perform a query similar to an SQL statement on the dataset that is detached from the database.

That is, (Select... from tablename where.

Save the data queried from dataset as a row to Arow.

The implementation code is as follows:

Dataset DS = new dataset ();

Datarow [] Arow = Ds. Tables [tablename]. Select ("" + columnsname + "= '" + datavalue + "'");

Code Description:

Qtablename: name of the table in the dataset Ds.

Qcolumnsname: name of a column in the tablename table.

Qdata: the value of the parameter corresponding to the column name.

Function Description:

The preceding statement function is equivalent to the query statement in the SQL statement.

Select * From tablename where columnsname = Data

8.4.11 data record statistics in datatable

When using databases such as SQL-server and Oracle, you can easily calculate or calculate relevant results through statistics and calculation functions such as sum, aver, Count, etc, can I use the same data set in the data table that has been retrieved? The datatable dynamically created in the program cannot use SQL statements for query statistics. For example, the SELECT statement cannot be used to obtain query statistics. How can we make statistics in datatable?

This section describes a simple method to easily obtain the record statistics in the datatable. This simple method is to call the powerful datatable function compute.

The function is as follows:

Public object compute (string expression, string filter)

Parameter description:

The qexpression parameter must be an aggregate function. The expression string to be calculated is basically similar to the statistical expression in SQL Server. For example, the following is a legal expression:Count (ID).

Qfilter: A Statistical filter string. Only records that meet this filter condition are counted, that is, the rows used in the expression.

The following example describes the order information in the orders data table of the northwind database in the SQL-Server database, including the following fields: orderid, customerid, and employee ID), order time (orderdate), ship number (shipvia), freight (freight ).

1. count the number of employees whose employee ID is 5:

Table. Compute ("count (*)", "employeeid = 5 ");

2. count the number of freight above 100 in all freight

Table. Compute ("count (*)", "Freight> 100 '");

3. Calculate the average value of the freight

Table. Compute ("aver (freight)", "true ");

4. Collect the total freight for employee ID:

Table. Compute ("sum (freight)", "employeeid = 5 ");

The preceding statements calculate the total of each column. You can use the following method to add a row to calculate the total:

// Create a dataset

Dataset MERs = new dataset ();

// Add a data table named mermerstable to the dataset.

Datatable customerstable = customers. Tables. Add ("customerstable ");

// Create a new row and add it to the customerstable data table

Datarow = new datarow ();

Datarow = customerstable. newrow ();

// Then the statistics are displayed. The Int variable I: cyclic variable is declared. The number of columns in the colcnt: mermerstable table is

Int I;

Int colcnt;

Colcnt = customerstable. cols. count;

For (I = 0; I <colcnt-1; I ++)

// Calculate the sum of column I and assign the result to the new datarow data row

Datarow (I) = customerstable. Compute ("sum (" + I. tostring () + ")", "true ");

// Add data rows to the customerstable data table

Customerstable. Rows. Add (datarow );

8.4.12 application Summary of datatable and XML

In ASP. in net2.0, ADO. net and XML are very closely integrated. In chapter 7th, We have detailed the introduction in ADO. NET data can be easily accessed in XML through dataset. Can the datatable operate XML data files as in dataset? The answer is yes. The following is an operation to write data in the able to an XML file. The instance code is as follows:

// Create a mers MERs data table

Datatable dt = new datatable ("MERs ");

// Add two columns of custid. The data types of custlname are int32 and string, respectively, and add them to the data table.

Datacolumn DC1 = new datacolumn ("custid", type. GetType ("system. int32 "));

Datacolumn DC2 = new datacolumn ("custlname", type. GetType ("system. String "));

DT. Columns. Add (DC1 );

DT. Columns. Add (DC2 );

// Create and assign values to data rows in a loop and add them to the data table

For (INT I = 0; I <Al. Count; I ++)

{

Datarow DR = DT. newrow ();

Ds_option DSO = (ds_option) al [I];

Dr ["custid"] = DSO. ID;

Dr ["custlname"] = DSO. Name;

DT. Rows. Add (DR );

}

// Write data in the data table to the XML file

String xmlstr;

System. Io. stringwriter writer = new system. Io. stringwriter ();

DT. writexml (writer );

Xmlstr = writer. tostring ();

Return xmlstr;

With the operation of writing data to an XML file, you can also read data information from the XML file to the datatable data table. The method is similar, but you must first create a datatable structure, otherwise, an error occurs.

The instance code is as follows:

String tbxml = xmlinfo;

Datatable dt = new datatable ("MERs ");

Datacolumn DC1 = new datacolumn ("custid", type. GetType ("system. int32 "));

Datacolumn DC2 = new datacolumn ("custlname", type. GetType ("system. String "));

DT. Columns. Add (DC1 );

DT. Columns. Add (DC2 );

System. Io. stringreader reader = new system. Io. stringreader (tbxml );

DT. readxml (Reader );