如何在内存中查询一个DataTable以填充另一个数据表

本文关键字：DataTable 一个 填充 数据表 另一个 内存 查询 | 更新日期: 2023-09-27 18:06:14

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

下面是当前查询:

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到服务器，因为它已经存在于我的程序的内存中作为一个数据表，它只是减慢报告和填充数据库的信息，我们不需要保存。

我的主要问题是:

我如何查询一个数据表，使一个新的内存数据表具有相同的结果，如果我写出来的SQL服务器和读取它回到上面的查询?

或

如何在Microsoft Reports中为表中的项目做分组子句，以将=Fields!Reason.Value =Fields!Number.Value =Fields!percentage.Value转换为类似于上述查询返回的结果?

如何在内存中查询一个DataTable以填充另一个数据表

可以使用DataTable。选择查询DataTable 

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

或者对于更复杂的查询，您可以使用LINQ来查询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 });

我可以想到两种方法来查询数据表。下面是一个使用这两种方法的例子:

using System;
using System.Data;
namespace WindowsFormsApplication1
{
    static class Program
    {
        [STAThread]
        static void Main()
        {
            var deletedClients = GetDataTable();
            // Using linq to create the new DataTable.
            var example1 = deletedClients.AsEnumerable()
                                         .Where(x => x.Field<int>("ClinicId") == 1)
                                         .CopyToDataTable();
            // Using the DefaultView RowFilter to create a new DataTable.
            deletedClients.DefaultView.RowFilter = "ClinicId = 1";
            var rowFilterExample = deletedClients.DefaultView.ToTable();
        }
        static DataTable GetDataTable()
        {
            var dataTable = new DataTable();
            // Assumes ClinicId is an int...
            dataTable.Columns.Add("ClinicId", typeof(int));
            dataTable.Columns.Add("Reason");
            dataTable.Columns.Add("Number", typeof(int));
            dataTable.Columns.Add("Percentage", typeof(float));
            for (int counter = 0; counter < 10; counter++)
            {
                dataTable.Rows.Add(counter, "Reason" + counter, counter, counter);
            }
            return dataTable;
        }
    }
}
相关文章：