calculation between two rows of DataTable in C#

Question

Hi. I have Datatable which has tow tables together appended. I have many rows in that two of which are Total and Rate.( Note this are the Rows in my Datatable). I want to calulalte the total amount by multiplying the two rows. How shall I do that. The datatables are coming from sql Compact and I cannot do their the calculation as it's not always needed depending on some settings it has to be done. It can be even percentage calculation or any thing. Is there any way to perform calculation between 2 rows in datatable???? I am using C#. I tried compute function but cant make it. The table might look like this :

Title Prod1 Prod2 Prod3 10 20 30 0 20 10 5 6 0 50 0 0 Total 65 46 40 Rate 10 20 10 Amount 650 920 400

i.e Total=sum of Prodi , i=1,2,3
and Amount=Total*Rate. There should be some way to achieve this. How shall I do????

Thanks in advance.

Answer 1

What you could do is:

1. Get data from database into data table
2. Pick the two lines for Rate and Total from the result
3. (If you don't need to display them, remove these lines from the table)
4. Do your calculation based on the values in the two rows
5. Add a new row to the table manually for the results

EDIT
Maybe I didn't fully understand your data structure. So let's look at some scenarios. If yours isn't in there, please provide more detail on how one row in your data table actually looks like:

Case a: Data is stored within columns, you want the Totals and Amount columns within that row, too

Value1 Value2 Value3 Rate Total Amount
1      5      3      10     ?     ?

You could adapt your SQL statement as follows:

SELECT Value1, Value2, Value3, Rate, 
       (Value1+Value2+Value3) AS Total, 
       ((Value1+Value2+Value3) * Rate) AS Amount 
FROM <table>

Alternatively, if you're using a typed dataset in C#, you could use a normal SELECT * ... statement and add columns with respective expressions (Expression property - the equivalent to computed columns) manually to the table in your typed dataset.

Case b: Data is stored within rows, you want Total and Amount to be rows, too

RowTitle Value
Value1   1
Value2   5
Value3   2
Rate     10

Now you need to use a for-loop in C# to iterate over all the rows, sum up the values and then create two new rows with RowTitle "Total" and "Amount" and add them to your table.

EDIT 2
You, so I understand from your second comment that you actually have two tables in your database. One that contains the actual data, and one that contains the Rates. You now select from these tables according to user defined settings and you actually get two tables in your data set in C# - one for that data, one for the Rate.

AFAIK the SQL Server Compact Edition does not allow you to use JOIN, but that would have been the easiest: select the data from your data table and join it to the "Rates" table, selecting the respective rate according to the user settings. Then you could do the calculation I noted in "Case a" above.

However, I think you can not use JOIN, so I'd suggest that you get the data into your application (resulting in two separate tables, one for the data, one for the Rate). You modify your data SELECT statement so that you get an additional column Amount with default value 0.

SELECT *, 0 AS Amount FROM <data table>

Then, in C# you iterate over all the records in your data table and calculate the value of Amount for the record.

private void GetData(...)
{
    // Call SELECT statements to fill a dataset with two tables
    DataSet set = new DataSet();
    ....

    // set now contains tables[0] (for the data) and tables[1] (for the Rate).
    // Get the Rate
    int rate = (int)set.Tables[1].Rows[0]["Rate"]; // Assuming that the column is named "Rate"

    // Now iterate over all data rows and calculate amount
    foreach (DataRow row in set.Tables[0])
    {
        row["Amount"] = ((int)row["Total"] * rate);
    }

    // Mark the data set as unmodified
    set.AcceptChanges();
}

Note that this code may need some tuning - I've not actually tested it. If the "Rates"-Table contains more than one row and the correct rate needs to be selected depending on other settings, you might consider creating a function that returns the correct row depending on these settings:

private DataRow FindRateRow(DataSet set, <parameters for selecting the correct row>)
{
    DataRow[] row = set.Tables[1].Select(<Filter string according to parameters>);
    if (row != null && row.Length > 0)
        return row[0];
    return null;
}

Use int rate = (int)FindRateRow(set, ...)["Rate"]; above.