Post
Ask Question

Efficient Data Manipulation: Using Square Brackets in DataTable Compute

Use of square brackets [] with a Field Name in a Datatable "Compute" method expression in .Net.

Sum the values of a specific field of a data table.

Datatable.Compute("Compute Function", "") .

Example. Datatable l_dtEmploye contains "Age" Field,

Now if we want to sum the ages of the employees:

dtEmploye.Compute("Sum(Age)", "");

Sum the value of a specific field of a data table on the basis of a specific condition or filter.

Datatable.Compute("Compute Function", "particular condition").

Example. Suppose a data table contains "Age" and "Sex".

Now if we want to sum the age of male employees.

dtEmploye.Compute("Sum(Age)", "Sex = 'M'");

Until now, we didn't use square brackets with Field names and don't have any issues with the examples above. But if we change the field name "Emp Salary" instead of "Age", now it will give an error because the field name "Emp Salary" contains the special symbol "space".

So to avoid this error we use [] square brackets with the field name.

dtEmploye.Compute("Sum(Emp Salary)", ""); // Error
dtEmploye.Compute("Sum([Emp Salary])", ""); // Correct
dtEmploye.Compute("Sum([Emp Salary])", "Sex = 'M'"); // Correct

Suppose we have the following data table.

/// <summary>
/// This function is used to get data into p_dtData.
/// </summary>
/// <returns></returns>
public System.Data.DataTable GetData()
{
    System.Data.DataTable l_dtEmployee = new System.Data.DataTable("Employee");

    // Create columns for p_dtData .
    l_dtEmployee.Columns.Add("EmpId", typeof(int));
    l_dtEmployee.Columns.Add("Name", typeof(string));
    l_dtEmployee.Columns.Add("Sex", typeof(string));
    l_dtEmployee.Columns.Add("DateOfReport", typeof(DateTime));
    l_dtEmployee.Columns.Add("City", typeof(string));
    l_dtEmployee.Columns.Add("Emp Salary", typeof(Decimal));
    l_dtEmployee.Columns.Add("Leaves", typeof(float));

    // Create rows for p_dtData .
    l_dtEmployee.Rows.Add(10, "Abhishek", "M", DateTime.Now, "Nainital", 5500, .34);
    l_dtEmployee.Rows.Add(20, "Digvijay", "M", DateTime.Now, "Shimla", 4800, .98);
    l_dtEmployee.Rows.Add(30, "Shrish", "M", DateTime.Now, "Dehradun", 6700, .31);
    l_dtEmployee.Rows.Add(40, "Shaifali", "F", DateTime.Now, "Dehradun", 7000, .10);
    l_dtEmployee.Rows.Add(50, "Sonam", "F", DateTime.Now, "Delhi", 6500, .43);

    l_dtEmployee.Rows.Add(60, "Ankur", "M", DateTime.Now, "Delhi", 4500, .33);
    l_dtEmployee.Rows.Add(70, "Vipin", "M", DateTime.Now, "Dehradun", 8000, .44);
    l_dtEmployee.Rows.Add(80, "Jasmeen", "F", DateTime.Now, "Delhi", 6000, .65);
    l_dtEmployee.Rows.Add(90, "Rakesh", "M", DateTime.Now, "Jaisalmer", 2000, .32);
    l_dtEmployee.Rows.Add(100, "Annirud", "M", DateTime.Now, "Rohtak", 3900, .22);
    return l_dtEmployee;
}

Now, If we want to compute a sum or count on the basis of specific conditions then "Compute" is a function inside the data table in .Net.

Datatable.Compute("Compute Function", " On the bases of condition").

System.Data.DataTable l_dtEmp = GetData();
object l_value = l_dtEmp.Compute("Count([Emp Salary])", "Sex = 'M'");

We should use a field name with square brackets, it will resolve the conflicts if the field name contains any special character like space "First Name" or "Emp Id".


Similar Articles