如何在内存中查询一个DataTable来填充另一个数据表 [英] How to query a DataTable in memory to fill another data table

问题描述

我正在尝试更新 Microsoft 报告.它的作用是写出有多少客户被排除在转换过程之外，以及出于什么原因.目前，该程序将所有已删除的客户端写回服务器，然后向服务器查询以将结果填充到专业表中.

I am trying to update a Microsoft report. What it does is write out how many clients where excluded from a conversion process and for what reason. Currently the program writes all of the deleted clients back to the server then queries it back to fill a specialty table with the results.

这是当前查询:

SELECT  DeletedClients.Reason, 
        COUNT(DeletedClients.Reason) AS Number, 
        CAST(CAST(COUNT(DeletedClients.Reason) AS float) 
            / CAST(t.Total AS float) 
            * 100 AS numeric(4, 1)) AS percentage
FROM DeletedClients CROSS JOIN
    (SELECT COUNT(*) AS Total
    FROM DeletedClients AS DeletedClients_1
    WHERE (ClinicID = @ClinicID)) AS t
WHERE (DeletedClients.ClinicID = @ClinicID) 
    AND (DeletedClients.TotalsIdent = @ident)
GROUP BY DeletedClients.Reason, t.Total
ORDER BY Number DESC

我想要做的不是将 DeletedClients 写入服务器，因为它已经作为 DataTable 存在于我的程序中的内存中，它只会减慢报告的速度并用我们的信息填充数据库不需要保存.

What I would like to do is not write DeletedClients to the server as it already exists in memory in my program as a DataTable and it is just slowing down the report and filling the database with information we do not need to save.

我的主要问题是:

如何查询数据表以创建新的内存数据表，其结果与我写出 SQL 服务器并使用上述查询读回一样?

How do I query a data table to make a new in memory data table that has the same results as if I wrote out the the SQL server and read it back in with the query above?

或

如何在 Microsoft Reports 中为 Tablix 中的项目执行 group by 子句以转换 =Fields!Reason.Value =Fields!Number.Value =Fields!percentage.Value 类似于上面查询的返回结果?

How in Microsoft Reports do you do a group by clause for items in a Tablix to turn =Fields!Reason.Value =Fields!Number.Value =Fields!percentage.Value into something similar to the returned result from the query above?

推荐答案

可以使用DataTable.Select查询DataTable.

You can use DataTable.Select to query the DataTable.

DataTable table = GetDataTableResults();
DataTable results = table.Select("SomeIntColumn > 0").CopyToDataTable();

或者对于更复杂的查询，可以使用LINQ来查询DataTable:

Or for more complex queries, you can use LINQ to query the DataTable:

DataTable dt = GetDataTableResults();

var results = from row in dt.AsEnumerable()
              group row by new { SomeIDColumn = row.Field<int>("SomeIDColumn") } into rowgroup
              select new
              {
                  SomeID = rowgroup.Key.SomeIDColumn,
                  SomeTotal = rowgroup.Sum(r => r.Field<decimal>("SomeDecimalColumn"))
              };                    

DataTable queryResults = new DataTable();
foreach (var result in query)
    queryResults.Rows.Add(new object[] { result.SomeID, result.SomeTotal });

这篇关于如何在内存中查询一个DataTable来填充另一个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！