How to query a DataTable in memory to populate another data table

Question

I am trying to update a Microsoft report. What he does is write out how many customers are excluded from the conversion process and for what reason. Currently, the program writes all the remote clients back to the server, then requests it to fill out the table of specialties with the results.

Here is the current request:

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 

What I would like to do is not to write DeletedClients to the server, since it already exists in memory in my program as a DataTable, and it just slows down the report and populates the database with information that we do not need to store.

My main question is: Either:

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

OR

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

Answer 1

You can use DataTable.Select to query a DataTable.

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

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 }); 

Answer 2

There are two ways that I can think of to query a data table. The following is an example using both methods.

 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; } } }