Reorder DataTable columns

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?

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;
}

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?

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 :-)

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.

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.

Yes, please.