Sort a datatable and create a new datatable

[Fredrik Rodin]

All,

I've been looking around for a solution to my problem for a couple of days
now.

In short, here's my situation:

1.
I'm getting a result from a component back as a datatable and I have no
option to sort the different columns during the call to the component.

Code-snippet:
Dim dTable As DataTable

Dim x As New MyComponent

'---between these two lines I add some parameters and properties
....x.blabla = ""
'---

2.
I then execute the component:

dTable = x.Execute()

So faar so good. No SQL invloved (only within the component).

Now comes the tricky part. I need to sort the result and display it.
However, I can't just use the .Sort in DataRow and get a 1-D array back. I
have properties in the component that I need to get back via an index. I've
already tried to re-build it using the array but it's simply not working.

So I thought I could create a 1-D array and then copy it to a second
DataTable. Something like this:

dTable.Execute()
Dim dRow As DataRow
dRow = dTable.Select(NOTHING, "COLUMN_NAME ASC, COLUMN_NAME2 DESC")

I now have a 1D array that contains my sorted data from the DataTAble
Now I would like to create a new DataTable from the 1D array.

Does anybody have any idea how to solve it?

Thanks in advance,
Fredrik

 

[Michael Mayer [C# MVP]]

Now comes the tricky part. I need to sort the result and display it.
However, I can't just use the .Sort in DataRow and get a 1-D array back. I
have properties in the component that I need to get back via an index. I've
already tried to re-build it using the array but it's simply not working.

I'm afraid I don't know what you mean in the above paragraph.

Without understading that, I'll still venture to guess that you should use a
DataView to see a sorted view of your DataTable. That is:

DataView dv = new DataView(table)
dv.Sort = (sort expression)

If you have rows that you need to get to via an index (like you want the
53rd row of the sorted dataview) then things get complicated. The main way
to access the rows is through the GetEnumerator() method. You'd then have to
call .MoveNext() 53 times before using Current to get the row. You could
build a simple method to do that, and I doubt you'll notice any performance
problems unless you're calling it A LOT.

If you are calling that function a lot to index by rows (and you aren't
sorting very often), then it might make sense to copy all the data to a new
table (but I don't think that makes sense if you are sorting a lot). If you
decide a second table is worth it, then you'll probably have to write some
code like the following to copy a sorted dataview to a new table.

DataTable sortedTable = new DataTable();
foreach (DataColumn col in table) {
sortedTable.AddColumn (col.Name, col.Type); // something like this
}
foreach (DataRowView drv in dv) { // using dv from above
sortedTable.AddRow ( // get values from drv );
}

Neither of these solutions are ideal, but I can't think of any other "slick"
way of doing it.

 

[Alvin Bruney [MVP]]

Your post went unanswered. Have you resolved this issue?

 

[paul.mariotti]

Try this code, I use it frequently

private void SortDataTable(DataTable dt, string sort)
{
DataTable newDT = dt.Clone();
int rowCount = dt.Rows.Count;

DataRow[] foundRows = dt.Select(null, sort);
// Sort with Column name
for (int i = 0; i < rowCount; i++)
{
object[] arr = new object[dt.Columns.Count];
for (int j = 0; j < dt.Columns.Count; j++)
{
arr[j] = foundRows[j];
}
DataRow data_row = newDT.NewRow();
data_row.ItemArray = arr;
newDT.Rows.Add(data_row);
}

//clear the incoming dt
dt.Rows.Clear();

for (int i = 0; i < newDT.Rows.Count; i++)
{
object[] arr = new object[dt.Columns.Count];
for (int j = 0; j < dt.Columns.Count; j++)
{
arr[j] = newDT.Rows[j];
}

DataRow data_row = dt.NewRow();
data_row.ItemArray = arr;
dt.Rows.Add(data_row);
}
}

// Or, if you already have a row array, try this

public void DataTableFromRows(DataRow[] foundRows,
DataTable dtout)
{
int rowCount = foundRows.Length;

dtout.Clear();

for (int i = 0; i < rowCount; i++)
{
object[] arr = new object[dtout.Columns.Count];
for (int j = 0; j < dtout.Columns.Count; j++)
{
arr[j] = foundRows[j];
}
DataRow data_row = dtout.NewRow();
data_row.ItemArray = arr;
dtout.Rows.Add(data_row);
}
}