C# - How To Mathematical Operations In DataTable

Question

I have a DataTable and I want to do some mathematical operations before adding them to DataGridView. First one, I want to find maximum number of third column of my DataTable, second one I want to divide all values in third column with this maximum number. After all I want to replace my new values instead old values. My DataTable looks like that;

        Column 1     Column 2     Column 3
     ---------------------------------------
          a             b          2000000
          q             r          250000
          s             t          185000
          m             w          400000
          o             p          750000

After the operations, my DataTable should look like that;

        Column 1     Column 2     Column 3
     ---------------------------------------
          a             b          1
          q             r          0.125
          s             t          0.0925
          m             w          0.0002
          o             p          0.375

It's my code;

      connection.Open();
      //Some insignificant operations here.
      for (int q = 0; q < w.Length; q++)
      {
          for (int a = q + 1; a < w.Length; a++)
          {
              string query = ".....";
              SqlDataAdapter myAdapter = new SqlDataAdapter(query, connection);
              DataTable myTable = new DataTable();
              myAdapter.Fill(myTable);
              //I started here for finding maximum number in DataTable.
              int[] myColumn = dt.AsEnumerable().Select(x => x.Field<int>("Column3")).ToArray();
              int myMaximum = myColumn.Max();
              //I don't know what should I do after that.
              foreach (DataRow myRows in myTable.Rows)
              {
                  //Some significant operations again...
                  dgv1.Rows.Add(...);
              }
          }
      }
      connection.Close();

Answer 1

Have you considered adding a 4th column to the existing table? Make the column an “Expression” column to do the math as you describe, then hide the third column?

An example of this is below…

DataTable GridTable;

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  GridTable = GetDataTable();
  FillTable(GridTable);
  AddExpressionColumn(GridTable);
  dataGridView1.DataSource = GridTable;
  dataGridView1.Columns["Column3"].Visible = false;
}

private DataTable GetDataTable() {
  DataTable dt = new DataTable();
  dt.Columns.Add("Column1", typeof(string));
  dt.Columns.Add("Column2", typeof(string));
  dt.Columns.Add("Column3", typeof(int));
  return dt;
}

private void FillTable(DataTable dt) {
  dt.Rows.Add("a", "b", 2000000);
  dt.Rows.Add("q", "r", 250000);
  dt.Rows.Add("s", "t", 185000);
  dt.Rows.Add("m", "w", 400000);
  dt.Rows.Add("o", "p", 750000);
}

private void AddExpressionColumn(DataTable dt) {
  DataColumn expCol = new DataColumn("Result", typeof(decimal));
  dt.Columns.Add(expCol);
  string expressionString = "Column3 / " + GetMaxValue(dt).ToString();
  expCol.Expression = expressionString;
}

private int GetMaxValue(DataTable dt) {
  int max = (int)dt.Compute("Max(Column3)", "");
  if (max == 0)
    return 1;
  return max;
}

Answer 2

I would suggest get rid of DataTable and work with a plain c# classes.
DataTable is "heavy" structure which do much more than you need and in most of the cases you don't need it.

Create class to represent your data

public class Item
{
    public string Column1 { get; set; }
    public string Column2 { get; set; }
    public int Column3 { get; set; }
}

Load data

public List<Item> LoadData()
{
    var query = "SELECT Column1, Column2, Column3 FROM Table";
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        command.CommandType = CommandType.Text;
        command.CommandText = query;

        using (var reader = command.ExecuteReader())
        {
            var data = new List<Item>();
            while(reader.Read())
            {
                var item = new Item
                {
                    Column1 = reader.GetString(0),
                    Column1 = reader.GetString(1),
                    Column1 = reader.GetInt32(2)
                };
                data.Add(item);
            }

            return data;
        }
    }
}

Create class which will represent calculated data

public class CalculatedItem
{
    public string Column1 { get; }
    public string Column2 { get; }
    public decimal Calculated { get; }

    public CalculatedItem(Item item, decimal maxValue)
    {
        Column1 = item.Column1;
        Column2 = item.Column2;
        Calculated = (decimal)item.Column3 / maxValue
    }
} 

Usage

var data = LoadData();
var maxValue = data.Select(item => item.Column3).Max();
var calculatedData = data.Select(item => new CalculatedItem(item, maxValue)).ToList();

// Bind data to the DataGridView
DataGridView1.DataSource = calculatedDate;

Answer 3

Give this a go:

var max = myTable.Rows.Cast<DataRow>().Max(r => r.Field<double>("Column 3"));

foreach (var row in myTable.Rows.Cast<DataRow>())
{
    row["Column 3"] = row.Field<double>("Column 3") / max;
}

---

Runable test code:

var myTable = new DataTable();
myTable.Columns.Add("Column 3", typeof(double));
myTable.Rows.Add(20_00_000);
myTable.Rows.Add(250_000);
myTable.Rows.Add(185_000);
myTable.Rows.Add(400_000);
myTable.Rows.Add(750_000);

Console.WriteLine(String.Join(", ", myTable.Rows.Cast<DataRow>().Select(r => r.Field<double>("Column 3"))));    

var max = myTable.Rows.Cast<DataRow>().Max(r => r.Field<double>("Column 3"));

foreach (var row in myTable.Rows.Cast<DataRow>())
{
    row["Column 3"] = row.Field<double>("Column 3") / max;
}

Console.WriteLine(String.Join(", ", myTable.Rows.Cast<DataRow>().Select(r => r.Field<double>("Column 3"))));

This outputs:

2000000, 250000, 185000, 400000, 750000
1, 0.125, 0.0925, 0.2, 0.375

---

Update based on "Column 3" being an int.

Console.WriteLine(String.Join(", ", myTable.Rows.Cast<DataRow>().Select(r => r.Field<int>("Column 3"))));

int max = myTable.Rows.Cast<DataRow>().Max(r => r.Field<int>("Column 3"));
double[] results = myTable.Rows.Cast<DataRow>().Select(r => (double)r.Field<int>("Column 3") / max).ToArray();

Console.WriteLine(String.Join(", ", results));

Now just index in to results when you build your DataGridView.