I. Create a memory table and the following code 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 "}); } II. The following describes the common usage of datatable objects. 1. Copy and clone datatable Create a full copy of the datatable (full copu copies the structure and data of the table), through 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 result is a number ranging from 0 to 10. You can use the clone method of datatable to copy the table mode (Table result) of able without copying data. Datatable clone_table = auto. Clone (); After cloning a datatable, you may need to copy some datarow objects (row data) in the datatable object to the cloned Abel. You can use the importrow method of the datatable. Clone_table.importrow (Auto. Rows [0]); 2. enumerate datatable Traverse the rows and columns of a datatable in 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 (); 3. Use dataview 1. Sort sorting Dataview view = new dataview (auto ); View. Sort = "make ASC, year DESC "; However, I seldom use the sort function of dataview. My approach is to use order by in SQL statements. 2. Use rowfilter for exact search Dataview view = new dataview (auto ); View. rowfilter = "Make like 'aa % 'and year> 2001 "; You can also set query conditions in SQL statements to view your habits and actual needs. 3. Export dataview to a new table Datatable new_table = view. totable ("mytable", true, "ID", "name "); The name of the new mytable table. True indicates that different values are displayed and the same row is deleted (equivalent to the SQL distinct ), ID and name are the column IDs of the new table. 4. enumerate dataview Similar to Enumerative Abe 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 (); In addition, in. NET 2.0, the able object can process XML files. Like dataset, the datatable object also has the readxml and writexml methods, which are not used in specific cases and can be summarized in the future. Certificate ------------------------------------------------------------------------------------------------------------------------------------------ Use datatable to obtain the table's primary key In many cases, we need to know what the table's primary key is. In ADO. net, datatable can be used to map tables in the database. You can use the datatable attribute primarykey, which is an array of the datacolumn [] type. We can use the following code: Datacolumn [] Cols;
Cols = table. primarykey;
// Note that not Cols is a datacolumn array, not a datacolumn variable. In this way, it is mainly used to deal with the problem of joint primary keys.
For (INT I = 0; I <cols. length; I ++)
{
MessageBox. Show (Cols [I]. columnname );
} This problem has been solved, but cols. length is 0. When the datatable is filled by default, no primary key information is obtained from the database. How to obtain the primary key? After research, we found that when filling dataset, we can use the missingschemaaction attribute of dataadapter to solve this problem, so we have the following code: // Use dataadapter to fill the datatable
Dataadapter. missingschemaaction = missingschemaaction. addwithkey;
Dataadapter. Fill (table ); Datacolumn [] Cols;
Cols = table. primarykey;
// Note that not Cols is a datacolumn array, not a datacolumn variable. In this way, it is mainly used to deal with the problem of joint primary keys.
For (INT I = 0; I <cols. length; I ++)
{
MessageBox. Show (Cols [I]. columnname );
} In this way, we can get what we want. The missingschemaaction attribute is the operation to be performed when the existing dataset (or datatable) architecture does not match the imported data. Missingschemaaction. addwithkey is an enumeration value. It is used to add required columns and primary key information to complete the schema. You can explicitly set primary key constraints on each able. Certificate ------------------------------------------------------------------------------------------------------------------------------------------ Statistics of data records in datatable When using SQL Server databases, we can easily calculate relevant results through sum, aver, count, and so on. What about dataset (datatable) that has retrieved data? In particular, dataset is obtained through the web service. At this time, there is no way to modify the SELECT statement to obtain these statistics. Can statistics be performed in dataset/datatable? In msdn, there is a statistical method recommended by MS, which is to calculate the sum of Data row by row, in fact, there is equal to none (maybe this method is only used for obtaining the subtotal for the DataGrid), because this method uses the itemdatabind event of the DataGrid to accumulate data, there is no difference from manual code writing statistics. This article introduces a simple method. You can easily obtain the record statistics in the datatable without having to calculate records one by one. This simple method is to call the powerful datatable function compute. 1. Call description (C # is used as an example, the same below ): Public object compute (string strexpression, string strfilter) Parameters: Strexpression: expression string to be calculated. It is basically similar to the statistical expression in SQL Server. Strfilter: A Statistical filter string. Only records that meet this filter condition are counted. Ii. Call example: In the following example, assume a product sales table that describes the actual sales records of various promoters in a mall. The fields include name, sex, and 0, 1 is male, birthday, proid, quantity, and price ). 1. Count the number of salespersons whose gender is female:
Table. Compute ("count (*)", "Sex = 0 "); 2. Count all sales staff older than 20
Table. Compute ("count (*)", "birthday <'" + today); // today is the date string of today. 3. Measure the average prices of products sold.
Table. Compute ("aver (price)", "true "); 4. Count the number of products sold with the product code 1:
Table. Compute ("sum (Quantity)", "proid = 1 "); 5. Total sales of all products:
To calculate the total sales amount, because the table does not contain the sales amount of a certain promoter of a product, we can obtain it through quantity * price. For example:
Table. Compute ("sum (quantity * price)", "true "); Here is a problem: datatable does not have a strong statistical function in sqlserver, which is incorrect because compute does not have the data function such as sum (quantity * price. What should we do? For Statistics of such complex data, we can create a new field in the datatable, such as amount, and set the expression of this field to quantity * price, in this way, we can use the statistical function:
Table. Compute ("sum (amount)", "true "); The preceding statements calculate the total of each column. You can use the following method to add a row to calculate the total: System. Data. datarow = dataset. Tables [0]. newrow ()
'Assume that your dataset is dataset, the table is indexed at 0, and all your fields can be summed up. Transfer address: http://chinesewind.cnblogs.com/archive/2005/11/30/287957.html System. datarow = new system. datarow ();
Datarow = DT. newrow (); The statistics are as follows:
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, all done. I hope it will be useful to you. Bytes ------------------------------------------------------------------------------------------- In the following example, three join methods are implemented to connect two datatables, which is equivalent to the inner join method of SQL and returns all columns of the datatable.
If the datacolumn in the two datatables has repeated values, set the second value to columnname + "_ second". The following code is used to help you.
Using system;
Using system. Data; chinaz Namespace windowsapplication1
{
Public class sqlops
{
Public sqlops ()
{
}
Public static datatable join (datatable first, datatable second, datacolumn [] FJC, datacolumn [] SJC)
{
// Create a new able
Datatable table = new datatable ("join ");
// Use a dataset to leverage datarelation
Using (Dataset DS = new dataset () chinaz Chinaz {
// Copy the datatable to 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 an association
Datarelation r = new datarelation (string. Empty, parentcolumns, childcolumns, false );
DS. relations. Add (r); chinaz // Create a column for The joined 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 ++)
{
// Check whether there are repeated columns. If there is a column in the second able, add _ 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) Chinaz {
// Obtain the row data
Datarow [] childrows = firstrow. getchildrows (R );
If (childrows! = NULL & childrows. length> 0)
{
Object [] parentarray = firstrow. itemarray;
Foreach (datarow secondrow in childrows) Chinaz {
Object [] secondarray = secondrow. itemarray;
Object [] joinarray = new object [parentarray. Length + secondarray. Length];
Array. Copy (parentarray, 0, joinarray, 0, parentarray. Length );
Array. Copy (secondarray, 0, joinarray, parentarray. length, secondarray. Length); chinaz
Table. loaddatarow (joinarray, true );
}
}
}
Table. endloaddata ();
}
Return table;
}
Public static datatable join (datatable first, datatable second, datacolumn FJC, datacolumn SJC) Chinaz {
Return join (first, second, new datacolumn [] {FJC}, new datacolumn [] {SJC });
}
Public static datatable join (datatable first, datatable second, string FJC, string SJC)
{
Return join (first, second, new datacolumn [] {first. Columns [FJC]}, new datacolumn [] {first. Columns [SJC]});
}
}
} |
