I have a DataTable (i.e. DT) that has a few columns of Type of String (i.e. colStr1, colStr2), and a few of the Type of Int32 (i.e. colInt1, colInt2). This is set in the DB as well as in the DataTable created in the code.
For one of my functions, I need to get a Sum of the Int column. But for specific data, the Sum will exceed the Int32.MaxValue.
What I'm looking for are two options:
(not sure this one is possible) Add a row, that will contain the SUM of the column while changing the Type for the SUM cell/field from Int32 to Int64.
Add a copy of those Int32 columns (with all the values) to the same DT, but a new column is defined as Type of Int64 (and data converted accordingly). Then I can do a SUM for those. I have following code for this, but not sure if it is most elegant and best way to do it:
DataColumn colBal64 = new DataColumn();
colBal64.ColumnName = "BalanceInt64";
colBal64.DataType = typeof(Int64);
dt.Columns.Add(colBal64);
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
DataRow dr = dt.Rows[i];
dr["BalanceInt64"] = dr["BalanceInt32"];
}
Any suggestion on how to better approach this? And the best way to do it? So far I found only copying the whole DT into a new DT, and not Column within the same DT, while converting into another Type.
Your first approach is, indeed, not possible. Given you have two Int32 columns, you could have an Int64, then split that bit-wise across the two Int32s, but that seems like a lot of hassle if you don't need to store the sum. (If you do need to store the sum, a better approach would be to use another table, or something else depending on how exactly you need to store that summation.)
Depending on how exactly you're writing this summation function, you should be able to just sum your values into an Int64, either by casting (as below) if you're using a .Sum() call or by just having your aggregation variable being an Int64 and adding the Int32s to it. As an example, the following works just fine in C#:
using System;
using System.Data;
var bigNumTable = new DataTable();
var column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "the_number";
bigNumTable.Columns.Add(column);
for (int i=0; i<32; i++)
{
var row = bigNumTable.NewRow();
row["the_number"] = Int32.MaxValue;
bigNumTable.Rows.Add(row);
}
try
{
Console.Write($"Summing as Int32: {bigNumTable.AsEnumerable().Sum(row => row.Field<Int32>("the_number"))}");
}
catch (OverflowException)
{
Console.WriteLine("Summing as Int32 resulted in an OverflowException");
}
Console.Write($"Summing after casting to an Int64: {bigNumTable.AsEnumerable().Sum(row => (Int64)row.Field<Int32>("the_number"))}");
Which prints the following to the console:
Summing as Int32 resulted in an OverflowException
Summing after casting to an Int64: 68719476704
If you need to do the summation in another language, you should be able to cast the Int32s to Int64s if necessary, but if you'd even need that depends on the context of the function you're writing.