使用DataTable值插入另一个DataTable [英] Using DataTable values to insert into another DataTable
问题描述
请原谅我令人作呕的方法：我无法在SQL中执行此操作（因为我的使用是动态的，而且我对SQL的方式不了解，不足以使其创建我想要/需要的动态输出）或EF，因为我出于某种未知原因无法使它正常工作（如果有人举了例子，可以帮助EF和DataTables初学者，请分享）
Forgive my disgusting method: I haven't been able to do this in SQL (as my use is dynamic and I'm not versed in the ways of SQL enough to have it create the dynamic output I'm wanting/needing) or EF as I haven't been able to get it to work for some unknown reason (if anyone has an example that may help a beginner with EF and DataTables please share)
我有一个DataTable（ dt ），其值是我尝试提取，计算然后输入到另一个DataTable（ fDt ）。
I have a DataTable (dt) with values that I'm trying to pull, calculate and then input into another DataTable (fDt).
dt 的示例：
+-------------------------------------------------------+
| ID | CustName | 201501 | 201502 | 201503 | 201504 | ..|
+-------------------------------------------------------+
| 32 | CustOne | 100.00 | 200.00 | 400.00 | 700.00 | ..|
| 56 | CustTwo | | | | 500.00 | ..|
| 89 | CustThree| 222.22 | 333.33 | 444.44 | 555.55 | ..|
| .. | ... | .. | .. | .. | .. | ..|
+-------------------------------------------------------+
我想获取之后 客户名称用于我的计算。
I'm wanting to take the values after CustName to use in my calculation.
然后，计算会得出两者之间的百分比差异上表中的列：
The calculation then gives the percent difference between two of the columns from the previous table:
+-------------------------------------------------------+
| ID | CustName | PerDiff02 | PerDiff03 | PerDiff04 | ..|
+-------------------------------------------------------+
| 32 | CustOne | 0 | 100 | 200 | ..|
| 56 | CustTwo | | | 85.00 | ..|
| 89 | CustThree| 66.66 | 75.00 | 80.00 | ..|
| .. | ... | .... | .... | .... | ..|
+-------------------------------------------------------+
（这些百分比是伪造的，但它们显示了我正在努力实现。）
(The percentages are spoofed, but they show what I'm trying to achieve.)
PerDiff s应该从所示的第二个月开始（ 201502 ）并继续使用当前版本。
The PerDiffs should start from the second month shown (201502) and continue to current.
我相信我已经做到了，但是现在有了代码：
I believe I've done this, however with the code I have now:
for (i = 2; i <= (dt.Columns.Count - 2); i++)
{
for (int j = 0; j < (dt.Columns.Count); j++)
{
//decimal? month1 = dt.Rows[i].Field<decimal?>(j);
//decimal? month2 = dt.Rows[i].Field<decimal?>(j + 2);
decimal? month1 = (decimal?)dt.Rows[i][j];
decimal? month2 = (decimal?)dt.Rows[i][j + 2];
fDt.Rows[i][j - 1] = ((month1 - month2) / month1) * 100;
}
}
我已经添加了两条注释行
I have included two commented out lines as they produce the same error that I'm currently getting with this loop:
指定的转换无效。
Specified cast is not valid.
对于声明 month1 和 month2 的行。
问题：
如何从我的一个数据中获取数据一个方程式中的 DataTable 中的 Cells ，然后输入到另一个 DataTable ？
How can I take the data from one of my Cells in one DataTable in an equation, and then input into another DataTable?
如果不清楚，请告诉我！
If anything is unclear, please let me know!
编辑：
这是我获取数据的整个方法表格：
Here is my entire Method for getting my dataTable:
public DataTable GetPerDiff(DataTable dt)
{
var fDt = new DataTable();
int i;
int fieldCount = dt.Columns.Count;
string[] colHeaders = new string[fieldCount];
for (i = 0; i < fieldCount; i++)
{
colHeaders[i] = dt.Columns[i].ToString();
}
fDt.Columns.Add(colHeaders[0]);
fDt.Columns.Add(colHeaders[1]);
//Get's the data into the new table
for (i = 1; i < dt.Rows.Count-1; i++)
{
fDt.Rows.Add(dt.Rows[i][0], dt.Rows[i][1]);
}
// Gets the column headers for dataTable fDt
for (i = 2; i <= (dt.Columns.Count - 2); i++)
{
string colName = "PerDiff" + dt.Columns[i + 1];
fDt.Columns.Add(colName);
}
for (i = 2; i <= (dt.Columns.Count - 2); i++)
{
for (int j = 0; j < (dt.Columns.Count); j++)
{
//decimal? month1 = dt.Rows[i].Field<decimal?>(j);
//decimal? month2 = dt.Rows[i].Field<decimal?>(j + 2);
decimal? month1 = (decimal?)dt.Rows[i][j];
decimal? month2 = (decimal?)dt.Rows[i][j + 2];
fDt.Rows[i][j - 1] = ((month1 - month2) / month1) * 100;
}
}
return dt;
}
推荐答案
我认为这里的主要问题是循环索引和条件错误，这将导致在检索数据时强制转换无效。您有以下代码：
I think the main problem here is that your loop indexes and conditions are wrong, which leads to the invalid cast when you retrieve the data. You have this code:
for (i = 2; i <= (dt.Columns.Count - 2); i++)
{
for (int j = 0; j < (dt.Columns.Count); j++)
{
//decimal? month1 = dt.Rows[i].Field<decimal?>(j);
//decimal? month2 = dt.Rows[i].Field<decimal?>(j + 2);
decimal? month1 = (decimal?)dt.Rows[i][j];
decimal? month2 = (decimal?)dt.Rows[i][j + 2];
fDt.Rows[i][j - 1] = ((month1 - month2) / month1) * 100;
}
}
内部循环，用于索引表的列，从 j = 0 开始。但是列0包含客户ID，列1包含客户名称。这些都不是小数，因此当您检索 dt.Rows [i] [j] 并将其转换为十进制？它会导致 InvalidCastException 。该循环应从索引2开始（跳过ID和name列），循环条件应为 j<。 dt.Columns.Count-1 ，因为目标表中的列比源表少一列。
The inner loop, which indexes the columns of the table, starts with j = 0. But column 0 contains the customer ID, and column 1 contains the customer name. Neither of these are decimals, so when you retrieve dt.Rows[i][j] and cast it to decimal? it causes an InvalidCastException. This loop should start at index 2 (to skip over the ID and name columns) and the loop condition should be j < dt.Columns.Count - 1 because there is one less column in the destination table than the source table.
下一个问题：看来您打算将外部循环变量 i 索引到rows集合中，但是您要使用索引2（而不是0）开始循环，并且循环条件与列数相反，没有行。因此，您最终将跳过前两行，最后可能会错过很多行，具体取决于您与列比较的行数。看来可能是复制粘贴错误或其他原因。
Next problem: It appears you intend the outer loop variable i to index into the rows collection, but you are starting the loop with index 2 instead of 0 and the loop condition is going against the number of columns, not rows. So you will end up skipping over the first two rows and will probably miss a lot of rows at the end depending on how many rows you have compared to columns. This looks like it might have been a copy-paste error or something.
还有更多内容：
-
如果要从列
j中检索month1，则month2应该是下个月，或者j + 1。您将其作为j + 2。这将给您错误的结果，并最终在上个月引发IndexOutOfRangeException。
If you are retrieving
month1from columnj, thenmonth2should be the next month, orj + 1. You have it asj + 2. This will give you the wrong results and also end up throwing anIndexOutOfRangeExceptionon the last month.
我认为您的减法可能是落后的。如果下个月（ month2 ）比前一个月（ month1 ）有所增加，那么您要显示作为一个正数，对不对？因此，您需要（month2-month1）/ month1 * 100 。您将其作为（month1-month2）... 。
I think your subtraction may be backward. If the later month (month2) represents an increase over the earlier month (month1), then you want to show that as a positive number, right? So you would need (month2 - month1) / month1 * 100. You have it as (month1 - month2)....
您无法正确处理表中的空值。在 DataTable 中，空值由值 DBNull 表示，该值不能转换为十进制？。因此，这可能是 InvalidCastException 的另一个来源。 Field< T> 扩展方法处理 DBNull 和 null 给您，所以我建议您使用它。 （看起来您一次使用了此方法，但此后已将其注释掉了。）但是请注意，如果您对两个可为空的小数进行计算，其中一个或另一个具有 null 值，结果也是 null 。而且，当您尝试在计算后在 fDt 行中设置空值时，您会遇到同样的问题，因为 DataTable 需要 DBNull 而不是 null 。因此，您应该使用 SetField< T> 方法来处理将值存储回表中。
You are not properly handling possible null values in the table. In a DataTable, a null is represented by the value DBNull, which cannot be cast to a decimal?. So that could be another source of an InvalidCastException. The Field<T> extension method handles the conversion between DBNull and null for you, so I would recommend using that. (It looks like you were using this method at one time but have since commented it out.) But note, however, that if you do a calculation on two nullable decimals where one or the other has a null value, the result is also null. And when you try to set the null value in the fDt row after the calculation, you will have the same problem in reverse, because the DataTable requires DBNull instead of null. So you should use the SetField<T> method to handle storing the value back into the table.
您还没有考虑 month1 可能为零的可能性，这可能导致计算中出现 DivideByZeroException 。
You are also not considering the possibility that month1 may be zero, which could lead to a DivideByZeroException in your calculation.
现在您已经发布了其余代码，我注意到了其他一些问题：
Now that you have posted the rest of your code I noticed a few other issues:
-
您没有将列数据类型从第一个表复制到第二个表。
You're not copying the column data types from the first table to the second.
在将行添加到目标表并将客户ID和名称复制到其中的循环中，您将从行索引1而不是0开始。这将跳过第一行。您还会错过最后一行，因为循环条件是 i< dt.Rows.Count-1 而不是 i< dt.Rows.Count 。 （为简单起见，您实际上可以将此代码与外部计算循环结合使用。）
In the loop where you are adding the rows to the destination table and copying the customer IDs and names into it, you are starting with row index 1 instead of 0. This will skip the first row. You will also miss the last row, because your loop condition is i < dt.Rows.Count - 1 instead of i < dt.Rows.Count. (You can actually combine this code with the outer calculation loop for simplicity.)
您的方法返回了错误的表-应该返回 fDt ，而不是 dt 。
Your method is returning the wrong table-- it should return fDt, not dt.
解决所有这些问题（并简化了创建ID和名称列的操作）之后，代码应类似于以下内容，该代码应与您想要的内容非常接近：
After fixing all these problems (and some simplification creating the ID and name columns), the code should look something like this, which should be pretty close to what you want:
public DataTable GetPerDiff(DataTable dt)
{
var fDt = new DataTable();
// Copy columns for customer name and ID
fDt.Columns.Add(dt.Columns[0].ColumnName, dt.Columns[0].DataType);
fDt.Columns.Add(dt.Columns[1].ColumnName, dt.Columns[1].DataType);
// Create the PerDiff columns
for (int j = 2; j < dt.Columns.Count - 1; j++)
{
string colName = "PerDiff" + dt.Columns[j + 1];
fDt.Columns.Add(colName, dt.Columns[j + 1].DataType);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
fDt.Rows.Add(dt.Rows[i][0], dt.Rows[i][1]);
for (int j = 2; j < dt.Columns.Count - 1; j++)
{
decimal? month1 = dt.Rows[i].Field<decimal?>(j);
decimal? month2 = dt.Rows[i].Field<decimal?>(j + 1);
if (month1 != decimal.Zero)
{
fDt.Rows[i].SetField(j, (month2 - month1) / month1 * 100);
}
}
}
return fDt;
}
这篇关于使用DataTable值插入另一个DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
