Setting the order of rows stored in a DataTable

[Guest]

Is there a way to specify the sort order of how physical rows are stored in a
DataTable? I thought specifying a primary key would define the order but it
doesn't. Using a DataView every time to insure the required sorted order
unecessarily complicates the code.

Tom

 

[Marina Levit [MVP]]

Not sure why you would think that defining a primary key would mean the data
is always sorted in that order. If you have a primary key in a database,
that doesn't mean the rows are actually stored in sorted order.

To keep rows sorted by their primary key is a lot of work - for .NET, as it
would be for a database like SQL Server. If you want the physical order to
be sorted, you are going to have to do this yourself, or use a DataView,
etc. But there is no automated feature for this.

 

[Earl]

I've found that if you desire the ordering of physical rows, then there must
exist a real-world reason why. Thinking of this from the perspective of the
objects that you've modeled in the database, there should probably be a
column with an indicator of the ordering. A couple of simple examples:
Typically someone wants the order in the database to print out on an order
form to make the order look as it does on paper. In that case, a column
should exist in a table with a name such as Order.LineItem. Or perhaps you
have items that are stored by sequential numbers in the real world, perhaps
something like Widgets.LocationNumber. While someone might quibble that this
is not relational, indeed it is, as the column is an attribute of the
entity. Once you have that structure in place, then it is trivial to pull
the item and Sort it by the ordering column.

 

[Marina Levit [MVP]]

If the primary key defines the sort, then that is preferrable to having a
separate column to keep track of sequence. Imagine inserting a new row at
the very top, and having to renumber every single record below. If this is
all being done in a DataTable, you will literally need to loop through every
row updating the sequence number.

If you just go by the column that defines the sort in the first place, then
when you need to add a row, you are all done. The next time you retrieve the
data and sort, you will be all done.

 

[Miha Markic [MVP C#]]

Is there a way to specify the sort order of how physical rows are stored
in a
DataTable? I thought specifying a primary key would define the order but
it
doesn't. Using a DataView every time to insure the required sorted order
unecessarily complicates the code.

It depends on the order server returns the rows (usually ORDER BY clause
defines the order).

 

[Earl]

I agree if it is a matter of sequence. If on the other hand, there is a
real-world reason why he needs this (as in my examples), then he would need
a separate column to indicate that sequence (or as you call it, "the column
that defines the sort in the first place"). His primary keys may not be in
sequence, and there will certainly be gaps once the system is in place.

 

[Kevin Yu [MSFT]]

Hi Tom,

There is no pre-defined way in .NET framework for us to physically sort the
rows in certain order. A DataTable is just like a database table. It stores
a set of data. When you need them to be filtered or ordered, you can use
DataView.

Is there any particular reason that you need to physically sort the
DataTable?

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Kevin,

I was hoping to control the in memory structure of the DataTable but appears
it is always a heap. The ability for the designer to control the internal
storage structure can be very important for performance tuning. It's a "data
dude" thing.

Tom

 

[Kevin Yu [MSFT]]

Hi Tom,

I understand your requirement to control the underlying structure of
DataTable storage. However, if you need to sort it physically, you have to
create your own method to achieve this. In this case, you can use DataView
to do the sort if you need sorted data in your app. It simulates database's
tables and indexes which will gain performance in your app.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)