SyntaxErrorException使用DataTable.Compute方法获取转换后的字符串列的总和 [英] SyntaxErrorException using DataTable.Compute method to get sum of a converted string column

问题描述

我有了充满了有效的数字值的字符串类型的列的数据表，我需要得到这些值的总和。请明白，如果我是在我与在这种情况下使用的数据的控制权，我不会用一个字符串列来存储成本，但是那是不幸的现实。我一直在使用DataTable.Compute方法与SUM（转换（[专栏]，'数据类型'））的表情，没有过滤器。问题是，我得到了以下异常：

I have a DataTable that has a string type column filled with valid numeric values and I need to get a sum of those values. Please understand that if I was in control of the data I'm working with in this instance, I would not have used a string column to store "Cost" but that is the unfortunate reality. I've been using the DataTable.Compute method with the "SUM(Convert([Column],'DataType'))" expression and no filter. The problem is that I get the following exception:

System.Data.SyntaxErrorException - 合计参数语法错误：期待一列大吵可能的'儿童'限定词。

System.Data.SyntaxErrorException - Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.

我已经把一个很小的代码块，成功地再现了我遇到的问题。

I've put together a small block of code that successfully reproduces the issue that I'm having.

DataTable dt = new DataTable();
            dt.Columns.Add("Cost", typeof(string));
            dt.Rows.Add(new object[] { "1" });
            dt.Rows.Add(new object[] { "3.25" });
            dt.Rows.Add(new object[] { "0.75" });

            object TotalCost = dt.Compute("Sum(Convert(Cost,'System.Decimal'))", "");

最后一点，我仅限于.NET 2.0，以便使用LINQ通过这个来获得不在目前的一个选项。

One last note, I am restricted to .NET 2.0 so using LINQ to get through this isn't an option at the present time.

推荐答案

那岂不是更简单的定义列的数据类型来存储数字，不是字符串？

Wouldn't it be simpler to define the column data type to store number, than string?

DataTable dt = new DataTable();
dt.Columns.Add("Cost", typeof(System.Decimal));
dt.Rows.Add(1M);
dt.Rows.Add(3.25M);
dt.Rows.Add(0.75M);

decimal TotalCost = (decimal)dt.Compute("Sum(Cost)", "");

修改：如何增加一个新的临时列做转换，总结它向上和？移除

EDIT: How about adding a new temporary column to do the conversion, summing it up & removing it?

DataTable dt = new DataTable();
dt.Columns.Add("Cost", typeof(string));
dt.Rows.Add(new object[] { "1" });
dt.Rows.Add(new object[] { "3.25" });
dt.Rows.Add(new object[] { "0.75" });

// temporary column for converting string to decimal
dt.Columns.Add("CostNumeric", typeof(decimal), "Convert(Cost, 'System.Decimal')");
// using temporary column to do aggregation
object TotalCost = dt.Compute("Sum(CostNumeric)", "");

dt.Columns.Remove("CostNumeric");
Console.WriteLine(TotalCost);

这篇关于SyntaxErrorException使用DataTable.Compute方法获取转换后的字符串列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！