Sort a DataTable in .NET with thousands of records using C# .Net-CodePudding

I am running a pretty intensive data query (hence the long timeout) but wanted to know how to sort a DataTable correctly.

My code starts like so:

SqlDataAdapter da = new SqlDataAdapter();

SqlCommand command = new SqlCommand(sql, conn);
command.CommandTimeout = 36000;

da.SelectCommand = command;

DataTable dt = new DataTable();
da.Fill(dt);

DataView dv = new DataView(dt);

// Sorting
dv.Sort = "theId DESC";

int i = 0;

while (dt.Rows.Count > 0 && i < 5)
{
    DataRow row = dt.Rows[i];
    string theId = row["theId"].ToString();
    i  ;
}

The theId has about 2,000 records with that are numerically sequential from 1 to 2,000.

The caveats or not using an ORDER BY in the SQL are due to the exception:

ERROR: Exception occurred
The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes, etc.

I also cannot on-the-fly create a #TempTable due to max column size of 1,024.

The reasons the query is written a certain way are due to limitations of the system and network I am given, and cannot be adjusted nor can the query be run locally.

How do I get the sorted DataTable to work as that is what needs to be passed into the next function which is also outside of my control due to it being an api call to another system?

CodePudding user response：

You can't really sort a DataTable. You do it via a DataView, as you have done, but that only sorts the view, not the table. If you then access the data via the Rows collection, you won't see the sort. You have to access the data via the DataView.

There's actually no need to create a DataView explicitly though. Every DataTable already has a DataView in its DefaultView property. When you bind a DataTable in WinForms, that's where the dusplayed data comes from. That's how you can sort the data in a DataGridView or via a BindingSource.

When you use the Rows collection, you get DataRow objects. When you use the DefaultView, you get DataRowView objects. They are quite similar in many ways but also have some differences. If you just want the data, you can index either using the column name and get the value, so they work the same way in that regard. If you access the data via the DefaultView but need the DataRow for some reason, you can access it via the Row property of the DataRowView. Try this code to see how it all works:

var table = new DataTable();

table.Columns.Add("Name", typeof(string));

table.Rows.Add("Peter");
table.Rows.Add("Paul");
table.Rows.Add("Mary");

Console.WriteLine("Rows, unsorted");

foreach (DataRow row in table.Rows)
{
    Console.WriteLine(row["Name"]);
}

Console.WriteLine("DefaultView, unsorted");

foreach (DataRowView rowView in table.DefaultView)
{
    Console.WriteLine(rowView["Name"]);
}

table.DefaultView.Sort = "Name";

Console.WriteLine("Rows, sorted");

foreach (DataRow row in table.Rows)
{
    Console.WriteLine(row["Name"]);
}

Console.WriteLine("DefaultView, sorted");

foreach (DataRowView rowView in table.DefaultView)
{
    Console.WriteLine(rowView["Name"]);
}

Console.WriteLine("DefaultView to DataRow, sorted");

foreach (DataRowView rowView in table.DefaultView)
{
    Console.WriteLine(rowView.Row.Field<string>("Name"));
}

You'll be able to see from that that the data in the Rows collection is the same before and after the sort whereas the DefaultView shows data in the specified order. The last loop shows how you can access the DataRows from the DataRowViews and then use them where DataRowViews can't be, e.g. in LINQ to DataSet methods.

Note that, if you really do require the DataTable itself to be sorted then you can call ToTable on the DataView to generate a new DataTable. The new table will contain data based on the Sort and RowFilter properties of the DataView and you can also omit some columns if you want. You can also specify that only distinct records should be included.