Reorder DataTable columns

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

LVL 8
mppetersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

raterusCommented:
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
mppetersAuthor Commented:
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
mppetersAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

raterusCommented:
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
Jeff CertainCommented:
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
mppetersAuthor Commented:
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
mppetersAuthor Commented:
Yes, please.
0
DarthModCommented:
PAQed with points (500) refunded

DarthMod
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.