DataTable. Compute

 

DataTable. Compute

1: Aggregate Function "Sum ()"

2: free computing expression "20*30 + 1"

3: bool expression "1 = 2"

4: IFF logical expression "IIF (20> 1000, 0, 1)" // It also supports IsNull, Trim, SubString, etc.

A small application: Let the DataTable simulate the Formula function of Excel.

The Formula function in Excel is powerful. It would be nice if DataTable has similar functions. Most event DataTable is only used as a data carrier and is rarely used for computing. The Compute function provided by DataTable provides powerful computing functions.

The Compute function has two parameters: Expression and Filter.

Expresstion is a calculated Expression. For details about Expression, see "http://msdn2.microsoft.com/zh-cn/library/system.data.datacolumn.expression (VS.80). aspx" here ". Filter is a condition Filter, similar to the Where condition of SQL.

1: Expresstion.

First look at the simplest usage, Using Aggregate functions. This aggregate function is a commonly used function in reports or Excel, such as Sum and Avg. For data columns, Datatable can be easily calculated, such as DataTable. cumpute ("Sum (column 1)", "column 1> 0"); for simple statistical functions, these functions are enough. (Other functions provided include min, max, count, variance, standard deviation, and so on ). This function is common and not surprising.

Let's look at the "free expression" calculation. Write the mathematical expression as a string and then directly input the Compute function to obtain the calculation result. In this way, the dynamically generated computing expression can be computed. For example, you need to calculate "column 1 × 30 + 20 + 2" or purely mathematical calculations.

The Compute function can also perform logical computing. For example, this expression can return false: Compute ("1 = 2", "true"); this usage may be similar to the above situation, results can be obtained when some conditions are freely assembled.

LogicTest is also frequently used in Excel. You can specify some logical expressions, such as "IF (20> 1000, 0, 1 )". How can this function be implemented if it is placed in the DataTable? Compute is also used to write the statement: "IIF (20> 1000, 0, 1 )".

In the DataTable, if"IF (C102 = 0, 0, C105/C102 * 30)You can use the above technical points.

2: AsSecond ParameterFilterIs a simple query condition. For example, "true", "Id> 4", "Name like '% nd' and sex = 'male '".

The conditions cannot be complex. These conditions are sufficient.

The previously used Marge works well and can also be used for internal connections. These functions are useful when the data volume is small and requires simple computation.

Of course, if the logic is extremely complex, this function of DT is not enough. You have to write the logic yourself (even if you want to implement"IF (C102 = 0, 0, C105/C102 * 30)", It is also necessary to write a bit of processing logic by yourself, but it is more common, just write it once ).

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

DataTable. Compute method  

Calculates the given expression on the current row used to pass filtering conditions.

public Object Compute (     string expression,     string filter )

Instance:
       String sConnectionString; // declare a string
       // Connect the database string to the XBMIS database. The user name is sa.
       SConnectionString = "Data Source =.; Initial Catalog = XBMIS; User ID = sa ;";
       // Create a SqlConnection database connection object
       SqlConnection Conn = new SqlConnection (sConnectionString );
       // Enable Conn
       Conn. Open ();
       // The SQL statement Retrieves all the data in the T_YongH table
       String commandString = "Select BianH AS number, ZhuCYHM AS registration name, XingM AS name, XingB AS gender From T_YongH ";
       SqlDataAdapter dataAdapter = new SqlDataAdapter (commandString, Conn );
       Ds_datatable ds = new ds_datatable ();
       // Fill the dataset
       DataAdapter. Fill (ds, "YongH ");
       // Fill the data table
       DataTable dataTable = ds. Tables ["YongH"];
       // Create a new row and add it to the able data table
       DataRow dataRow;
       DataRow = dataTable. NewRow ();
       // Use Sum to calculate the total number, and use the Count function to calculate the number
       DataRow [0] = dataTable. Compute ("Sum (number)", "true ");
       DataRow [1] = dataTable. Compute ("Count (No )","");
       // Add data rows to the YongHTable data table
       DataTable. Rows. Add (dataRow );
       GridView1.DataSource = able;
       GridView1.DataBind ();

Parameter description:

The expression 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 ).

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