?
Solved

Reorder DataTable columns

Posted on 2005-05-16
10
Medium Priority
?
6,708 Views
Last Modified: 2008-01-09
I have a populated DataTable with a certain order for the columns.
My question is how do I reorder those columns? All I really want to do is take a specific column named "Date" and move it to the first column in the DataTable.

Some restrictions I have to live with:
- The sql query cannot be changed
- I'd like to avoid having to loop through every single DataRow

0
Comment
Question by:mppeters
8 Comments
 
LVL 33

Expert Comment

by:raterus
ID: 14010571
Can you do any programming before you fill the datatable.  Could you perhaps use a view that would make a select of the original SQL in the order you want?
0
 
LVL 8

Author Comment

by:mppeters
ID: 14010587
Here's the code for placing the "Date" column at the front of the DataTable's columns collection, BUT it loops through every row and imports each one. Any way to avoid this?

public DataTable orderColumns(DataTable dt) {
      DataTable newDt = new DataTable();
      DataColumn[] dcList = new DataColumn[dt.Columns.Count];
      dt.Columns.CopyTo(dcList,0);
      newDt.Columns.Add(new DataColumn("Date",typeof(DateTime)));
      foreach(DataColumn dc in dcList) {
            if (dc.ColumnName.Equals("Date")) continue;
            DataColumn newDc = new DataColumn(dc.ColumnName,dc.DataType);
            newDt.Columns.Add(newDc);
      }
      foreach(DataRow dr in dt.Rows) {
            newDt.ImportRow(dr);
      }
      return newDt;
}
0
 
LVL 8

Author Comment

by:mppeters
ID: 14010701
raterus: Not really.

The thing I forgot to mention is that the column I'm trying to move is not actually part of the sql statement -- it's added at runtime using a callback function just before the DataTable is populated with the data from the query.

So I append this "Date" column to the end of the columns collection. Then it is populated during a callback function that runs when each row is created.

I know there are much simpler ways of doing all this, but these are my "orders" -- handed down to me with instructions not to change the callbacks or the sql queries. There are so many dependencies on these functions with other apps that it would be too much work to change it now.

Given the function I posted, is there a more efficient way of accomplishing the same task?

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:raterus
ID: 14010832
I know of no way, and I searched google (which I'm sure you did as well), and never found a solution there either.  You may just have to use the code you've provided to do this.  The Columns collection of the datatable is readonly, so it looks to be no way to move this around after you've added them.

I know you restricted here as to what you can/can't change, and this is no time to talk about how presentation elements should be abstracted from data elements, so the column order shouldn't really matter :-)
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 14010958
Why are you trying to reorder the columns? If it is for display purposes, as long as you're not trying to AutoGenerateColumns on a DataGrid, there should be no problem changing the display order.
0
 
LVL 8

Author Comment

by:mppeters
ID: 14012434
Ok, I think I've found a better solution. It's not the prettiest, but it works.

I changed the callback function for the table creation to take a copy of the DataTable columns, clear the collection, add the Date column explicitly to the beginning first, then add the rest back in.

Here's the callback for table creation:

public void addTableColumns(DataTable dt) {
      DataColumn[] dcList = new DataColumn[dt.Columns.Count];
      dt.Columns.CopyTo(dcList,0);
      dt.Columns.Clear();
      dt.Columns.Add(new DataColumn("Date",typeof(DateTime)));
      foreach(DataColumn dc in dcList) {
            dt.Columns.Add(dc);
      }
      }

Then, when I do run the query to fill the DataTable, I keep an array of the indexes for each field.

Something like:

public virtual DataTable doQuery(int limit, TableCreationCallback tcFunc, RowCreationCallback rcFunc) {
     
      string commandString = "whatever query";
      SqlDataReader reader=SqlMethods.openSql(Settings.currentSettings["myDB"],commandString);
      if (reader == null)
        return null;
 
      DataTable dt = new DataTable();
      for(int i=0; i < reader.FieldCount; i++) {
        string fieldName = reader.GetName(i);
        dt.Columns.Add(new DataColumn(reader.GetName(i),reader.GetFieldType(i)));
      }
      if (tcFunc != null) {
        // Here's where I add the dynamic columns
        tcFunc(dt);
      }

      object[] values = new object[reader.FieldCount];
      int[] fldOrds = new int[reader.FieldCount];
      for(int i=0; i < reader.FieldCount; i++)
            fldOrds[i] = dt.Columns[reader.GetName(i)].Ordinal;

      if (reader!=null) {
            while (reader.Read()) {
                  dr = dt.NewRow();
                  reader.GetValues(values);
                  for(int i=0; i < reader.FieldCount; i++)
                        if (!(values[i] is DBNull)) dr[fldOrds[i]] = values[i];  // Here's where I fill the row from the database
                  if (rcFunc != null)
                        rcFunc(dr);  // Here's where I fill the dynamic columns for this row
                  dt.Rows.Add(dr);
                  if (dt.Rows.Count>limit) break;
            }    
      }
      return dt;
}
  }

I appreciate you guys sparing me the lectures on separation of data from presentation :) I know there are at least 2 or 3 much better ways of doing this, but alas, I'm just the code monkey right now -- not paid to think, just dance around and press some buttons.
0
 
LVL 8

Author Comment

by:mppeters
ID: 14018176
Yes, please.
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 14240300
PAQed with points (500) refunded

DarthMod
Community Support Moderator
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question