C#datatable instance

One. Create a memory table, the code below will use it
DataTable auto = new DataTable ();

Auto. Columns.Add ("ID");

Auto. Columns.Add ("Name");

for (int i=1;i<=10;i++)

{

Auto. Rows.Add (New Object[]{i, "baibaoqing"});

}

Two. The following methods are commonly used for DataTable objects

1. Replication and cloning of DataTable

Create a complete copy of the DataTable (the structure and data of the full Copu copy table), implemented by the copy method of the DataTable

DataTable Copy_table=auto. Copy ()

for (int i=0;i<copy_table.rows.length;i++)

{

Response.Write ("<script>alert" ("+copy_table.rows[i][0]+");</script> ");

}

The output is a number from 0 to 10.

Sometimes you need to copy the table schema (table results) of the DataTable without copying the data, and you can use the Clone method of the DataTable

DataTable Clone_table=auto. Clone ();

After a DataTable is cloned, some DataRow objects in the DataTable object (row data) may be copied to the cloned Datatabel, and the ImportRow method of the DataTable can be used

Clone_table.importrow (Auto. Rows[0]);

2. Enumerating the DataTable

Traversing the rows and columns of a DataTable through a foreach Loop

System.Text.StringBuilder buffer=new System.Text.StringBuiler ();

foreach (DataColumn dc in Auto. Colmns)

{

Buffer. Append (String.Format ("{0,15}", DC.) ColumnName));

}

Buffer. Append ("/r/t");

foreach (DataRow dr in Auto. Rows)

{

foreach (DataColumn dc in Auto. Colmns)

{

Buffer. Append (String.Format ("{0,15}", DR[DC));
}

Buffer. Append ("/r/t");

}

TextBox1. Text=buffer. ToString ();

Three. Use of DataView

1. Sort order

DataView view=new DataView (auto);

View. sort= "Make Asc,year DESC";

But I rarely use the DataView sort feature, and my approach is to use the order by in SQL statements

2. Use RowFilter to find exact

DataView view=new DataView (auto);

View. Rowfilter= "Make like ' aa% ' and year>2001";

You can also set the query condition in the SQL statement to see the personal habits and practical needs.

3 Export DataView to a new table

DataTable New_table=view. ToTable ("MyTable", True, "id", "name");

MyTable the name of the new table, true to show different values, delete the same row (equivalent to SQL distinct),

ID and name is the column ID of the new table.

4. Enumerate DataView

And enumeration Datatabe are very similar

System.Text.StringBuilder buffer=new System.Text.StringBuiler ();

foreach (DataColumn dc in Auto. Colmns)

{

Buffer. Append (String.Format ("{0,15}", DC.) ColumnName));

}

Buffer. Append ("/r/t");

foreach (DataRowView dv in view)

{

foreach (DataColumn dc in Auto. Colmns)

{

Buffer. Append (String.Format ("{0,15}", DV[DC));
}

Buffer. Append ("/r/t");

}

TextBox1. Text=buffer. ToString ();

Also in. NET 2.0, a DataTable object can process an XML file, and like a DataSet, a DataTable object has ReadXml and WriteXml methods, not specifically used, and then summed up when the opportunity is used.

--------------------------------------------------------------------------------------------------------------- ---------------------------

To get the primary key of a table through a DataTable

In many cases we need to know what the primary key of the table is. A table in Ado.net that a DataTable can map a database is provided. You can then take advantage of the DataTable's property PrimaryKey, which is the datacolumn[] type is an array. We can use the following code

Datacolumn[] cols;
cols = Table.primarykey;
Note that not cols are DataColumn arrays, not DataColumn variables. This is done primarily to address the problem of federated primary keys.
for (int i = 0; i < cols. Length; i++)
{
MessageBox.Show (Cols[i]. ColumnName);
}

The question has been settled, but cols. Length is 0. There was no information from the primary key that was taken from the database when the DataTable was populated by default. How do I get the primary key? The study found that when populating a dataset, you can use the DataAdapter MissingSchemaAction attribute to help us solve the problem, so we have the following code:

Populating a DataTable with DataAdapter
DataAdapter. MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataAdapter. Fill (Table);

Datacolumn[] cols;
cols = Table.primarykey;
Note that not cols are DataColumn arrays, not DataColumn variables. This is done primarily to address the problem of federated primary keys.
for (int i = 0; i < cols. Length; i++)
{
MessageBox.Show (Cols[i]. ColumnName);
}

So we can get what we want. The MissingSchemaAction property is the action that needs to be performed to determine if an existing dataset (or DataTable) schema does not match the incoming data. MissingSchemaAction.AddWithKey is an enumeration value that is used to add the necessary column and primary key information to complete the schema, which allows the user to explicitly set the PRIMARY KEY constraint on each DataTable.

--------------------------------------------------------------------------------------------------------------- ---------------------------

Statistics of data records in a DataTable

When we use SQL Server these databases, we can easily count the results by sum, aver, count, and so on, in the dataset (DataTable) where the data has been retrieved. In particular, the dataset was obtained through Web service, but there was no way to change the SELECT statement to get the statistics. So is it possible to make statistics in the dataset/datatable?

In MSDN, there is a MS recommended statistical method, which is to sum the data line by row, which is actually equal to none (perhaps this method is only for the DataGrid to get subtotals), Because this method uses the DataGrid ItemDataBind event to accumulate the data, it is no different from our manual write code statistics.

This article introduces a simple method that can easily obtain the statistical results of records in a DataTable without the need for an itemized calculation. The simple approach is to invoke the function compute of a powerful DataTable.

The description of the call (in C # as an example, hereinafter):

public Object Compute (String strexpression,string strfilter)

Parameters:

Strexpression: The expression string to evaluate, essentially similar to the statistical expression in SQL Server

Strfilter: Statistic filter string, only records that satisfy this filter condition will be counted

Second, the invocation example:

The following example assumes a product sales table that describes the actual record of sales of each salesperson in a marketplace, including fields: Name, Gender (sex,0 is female, 1 male), birthday (birthday), code for selling products (proid), Number of sales (Quantity), Sales price (prices).

1. Count the number of salespeople who have sex for women:
Table.compute ("Count (*)", "sex=0");

2. Statistics of all salespeople older than 20 years of age
Table.compute ("Count (*)", "birthday< '" +today);//today for today's date string

3. Statistics the average price of a sales product
Table.compute ("aver (Price)", "true");

4. Number of products sold with product Code 1:
Table.compute ("Sum (Quantity)", "proid=1");

5. Statistics total sales amount for all products:
To count the total sales amount, we can get it by quantity*price because there is no amount data in the table for a sales promoter. Like what:
Table.compute ("Sum (Quantity*price)", "true");

One problem here is that the statistical function of a DataTable is not as strong as SQL Server, and this statistic is wrong because compute statistics do not have the function of data such as SUM (Quantity*price). How about that.

For the statistics of such complex data, we can create a new field in the DataTable to complete, such as amount, and set the field expression to quantity*price so that we can use the statistics function:
Table.compute ("Sum (Amount)", "true");

All of these are calculated totals for each column, and to add a row to the total you can use the following method:

System.Data.DataRow Datarow=dataset.tables[0]. NewRow ()
' Suppose your dataset is a dataset, the table is at index 0, and assume that all of your fields can be aggregated.

System.datarow DataRow = new System.datarow ();
Datarow=dt. NewRow ();

And then there's the statistic:
int i;
int fldcnt;

Fldcnt=dt. Cols.count;

For (i=0;i< fldcnt-1;i++)
DataRow (i) =dt.compute ("Sum" ("+i.tostring () +"), "true");

Dt. Rows.Add (DataRow);

All right, that's it. Hope to be useful to everyone.

-------------------------------------------------------------------------------------------

In the following example, 3 Join methods are implemented to connect two DataTable, equivalent to the SQL inner join method, and return all columns of the DataTable.
If the DataColumn in the two DataTable is duplicated, the second is set to Columnname+ "_second", and the following code is in the hope of helping.
Using System;
Using System.Data; Chinaz

Namespace WindowsApplication1
{
public class Sqlops
{
Public Sqlops ()
{
}
public static DataTable Join (DataTable A, DataTable Second, datacolumn[] FJC, datacolumn[] SJC)
{
Create a new DataTable
DataTable table = new DataTable ("Join");
Use a DataSet to leverage DataRelation
using (DataSet ds = new DataSet ()) Chinaz

Chinaz

{
Copy the DataTable into the dataset

Ds. Tables.addrange (New Datatable[]{first.copy (), Second.copy ()});

Chinaz

datacolumn[] Parentcolumns = new DATACOLUMN[FJC. Length];

Chinaz

for (int i = 0; i < Parentcolumns. Length; i++)
{
Parentcolumns[i] = ds. Tables[0]. Columns[fjc[i]. ColumnName];
}
datacolumn[] Childcolumns = new DATACOLUMN[SJC. Length];
for (int i = 0; i < Childcolumns. Length; i++)
{
Childcolumns[i] = ds. TABLES[1]. Columns[sjc[i]. ColumnName]; Chinaz

Chinaz

}

Create Association
DataRelation r = new DataRelation (string. Empty,parentcolumns,childcolumns,false);
Ds. Relations.Add (R); Chinaz

Create a column for the associated table
for (int i = 0; i < First.Columns.Count; i++)
{
Table. Columns.Add (First.columns[i]. ColumnName, First.columns[i]. DataType);
}
for (int i = 0; i < Second.Columns.Count; i++)
{
See if there are any duplicate columns, if there is a column in the second DataTable added _second

Chinaz

if (!table. Columns.contains (Second.columns[i]. ColumnName))
Table. Columns.Add (Second.columns[i]. ColumnName, Second.columns[i]. DataType);
Else
Table. Columns.Add (Second.columns[i]. ColumnName + "_second", Second.columns[i]. DataType);
}
Table. Beginloaddata ();
foreach (DataRow firstrow in DS. Tables[0]. Rows)