Sorting a DataTable by Changing Column Type

Farhan Shariff

I want to know how to acess the table8_sorted outside of the for loop
when I try to use it I get error
does not exists in current context

//--------------------------------------------------------------------------------------------
DataTable table8; //table8 used for sorting and then later for plotting
table8 = table7.Copy(); //table7 is the unsorted table

// cloning table8 is cloned to change the type of columns

DataTable dtSorted = table8.Clone();
for (int w = 0; w < table8.Columns.Count; w++)
{
dtSorted.Columns[w].DataType = typeof(Double);
}
foreach (DataRow row in table8.Rows)
{
dtSorted.ImportRow(row);
}

// sorting dtSorted to get table8_sorted (sorted table)

for (int loop = 0; loop < dtSorted.Columns.Count; loop++)
{
DataView dataview = dtSorted.DefaultView;
dataview.Sort = dtSorted.Columns[loop].ColumnName;

DataTable table8_sorted = dataview.ToTable();
//how to acess this out side of the loop
}

//Does not exists in current context
table8_sorted.Columns.Add("plot", typeof(double));
double terms = table8_sorted.Rows.Count;

int t = 1;

for (int i = 0; i < terms; i++)
{

double ap;
ap = (t - 0.3) / (terms + 0.4);
table8_sorted.Rows[i]["plot"] = ap;

t++;
}

26 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
OK, the following should create a new table (table7_sorted) in which the original string columns are all individually sorted:

DataTable table8 = table7.Copy(); // temporary table for sorting purposes

int count = table8.Columns.Count; // original count

// add temporary columns which convert strings to doubles to table8
for(int i = 0; i < count; i++)
{
DataColumn dc = table8.Columns[i];
string name = "dbl_" + dc.ColumnName; // prefix with dbl_
string expr = "Convert(`" + dc.ColumnName + "`,'System.Double')";
table8.Columns.Add(name, typeof(double), expr);
}

DataTable table7_sorted = table7.Copy(); // create table7_sorted

DataView dv = table8.DefaultView; // create a view from table8

// sort by each temporary column in turn
// and copy sorted string column values to table7_sorted
for(int i = count; i < table8.Columns.Count; i++)
{
string name = table8.Columns[i].ColumnName; // temporary double column
string name2 = name.Replace("dbl_", ""); // original string column
dv.Sort = name;
int j = -1;
foreach(DataRowView drv in dv)
{
j++;
table7_sorted.Rows[j][name2] = drv[name2];
}
}
0
Palle Technologies12y ago
You are trying to use local variable which is in the scope of for loop outside of it . For using the same out side of for loop , declare it out side of the for loop . See the sample code .

DataTable table8_sorted=null;
for (int loop = 0; loop < dtSorted.Columns.Count; loop++)
{
DataView dataview = dtSorted.DefaultView;
dataview.Sort = dtSorted.Columns[loop].ColumnName;
table8_sorted = dataview.ToTable();
//how to acess this out side of the loop
}
0
Farhan Shariff12y ago
Man you are the Best :) ... You're a necessary piece to this puzzle. Thank you for always being there to cheer and to guide.
0
Farhan Shariff12y ago
previously I had the code something like which sorted the entire table
here also table1 rows[Par Name] are compared with table2 [columns] and new table is buit which is sorted... I want to follow something like this but I have only one final table which I have to sort

private static DataTable CompareTwoDataTable3(DataTable table6, DataTable table9)
{
DataTable table10 = new DataTable();
DataRow dr = null;
string filterExp = string.Empty;

for (int i = 0; i < table6.Rows.Count; i++)
{

string col = table6.Rows[i]["Par Name"].ToString();
if (table9.Columns.Contains(col))
{
if (!table10.Columns.Contains(col))
{
table10.Columns.Add(col, typeof(double));
filterExp = filterExp + col + " asc ,";
}

for (int j = 0; j < table9.Rows.Count; j++)
{
if (table10.Rows.Count != table9.Rows.Count)
{
dr = table10.NewRow();
table10.Rows.Add(dr);
}
table10.Rows[j][col] = table9.Rows[j][col];
}

}

}

DataTable resultDt_1 = table10.Clone();
for (int m = 0; m < table10.Columns.Count; m++)
{
DataView dv = new DataView(table10);
dv.Sort = filterExp.Split(',')[m];
table10 = dv.ToTable();
for (int i = 0; i < table10.Rows.Count; i++)
{
if (resultDt_1.Rows.Count != table10.Rows.Count)
{
resultDt_1.Rows.Add();
}
resultDt_1.Rows[i][m] = table10.Rows[i][m];
}

}
0
Vulpes12y ago
The only way to guarantee that all columns would end up sorted is to have a separate table for each column.

Otherwise you have to have a primary sort column and the remaining columns are then subsidiary to that.
0
Farhan Shariff12y ago
What can be done ? Not at the same time but finally everything should be sorted right.
0
Vulpes12y ago
"Yeah but once the first column is sorted then the next column has to be sorted right."

Not necessarily.

Suppose you have two columns and these three rows:

1, 2
-1, 3
-1, 2

Then after sorting you'll have:

-1, 2
-1, 3
1, 2

So the second column isn't sorted and can't be sorted at the same time as the first column is sorted unless you move values between rows.

0
Farhan Shariff12y ago
Yeah but once the first column is sorted then the next column has to be sorted right. I want a sorted table with all columns sorted in Asc order which I later use to plot to get a cumulative graph for which I need a values in Asc Order in all columns
0
Vulpes12y ago
They are getting sorted on my model.

Sorting first takes place on the first column.

If any values in the first column are the same, it then sorts those rows on the second column and so on.

It can't, of course, sort all columns simultaneously unless values are to be moved between rows.
0
Farhan Shariff12y ago
Wait only First column is getting sorted rest all the columns are not sorted why is this happening ?
0
Farhan Shariff12y ago
Yes :) :) Thank you for Everything your patience ,time ... Got the table sorted
0
Vulpes12y ago
It seems that the actual column name is :

CON_RES:RES_ADCI2_N_(ADCI2_N)@ADCI2_N[1]

and the Expression property parser doesn't like that because it ends with a square bracket which is used to 'escape' column names which contain non-alphanumeric characters.

Fortunately, you can also escape them using a pair of back ticks so try this instead:

string expr = "Convert(`" + dc.ColumnName + "`,'System.Double')";
0
Farhan Shariff12y ago
I am running it as console application table7 in your code is table8 in mine
http://imgur.com/ST8jMAR
0
Vulpes12y ago
OK, can you put a MessageBox.Show in here and tell me what the expression property is just before you hit the line that fails:

for(int i = 0; i < count; i++)
{
DataColumn dc = table7.Columns[i];
string name = "dbl_" + dc.ColumnName; // prefix with dbl_
string expr = "Convert(" + dc.ColumnName + ",'System.Double')";
MessageBox.Show(expr);
table7.Columns.Add(name, typeof(double), expr);
sort += name + ",";
}
0
Farhan Shariff12y ago
its the name of the column
0
Vulpes12y ago
As usual, I'm baffled by that :(

When I put that column name into a little model I made, it works fine.

Is :RES_ADCI2_N_ the actual name of a column or is there something before the colon and/or after the final underscore?

0
Farhan Shariff12y ago
table7.Columns.Add(name, typeof(double), expr);
Syntax error: Missing operand after ':RES_ADCI2_N_' operator.
0
Vulpes12y ago
OK, it's a different approach to yours but try this:

string sort = "";
int count = table7.Columns.Count; // original count

// add temporary columns which convert strings to doubles
for(int i = 0; i < count; i++)
{
DataColumn dc = table7.Columns[i];
string name = "dbl_" + dc.ColumnName; // prefix with dbl_
string expr = "Convert(" + dc.ColumnName + ",'System.Double')";
table7.Columns.Add(name, typeof(double), expr);
sort += name + ",";
}

sort = sort.TrimEnd(','); // get rid of final comma
DataView dv = table7.DefaultView;
dv.Sort = sort;
DataTable table8 = dv.ToTable();

// remove temporary columns if necessary
for(int i = table8.Columns.Count - 1; i >= 0; i--)
{
if(table8.Columns[i].ColumnName.StartsWith("dbl_"))
{
table8.Columns.RemoveAt(i);
}
}

0
Farhan Shariff12y ago
All the columns(entire datatable) , column names are not defined and can be anything but all have numeric values
0
Vulpes12y ago
Are you just sorting on a single column or multiple columns here?
0
Farhan Shariff12y ago
I want to just sort the orginal table8 in asc order.

I am cloning it to change its columns data type (to double) so -ve values are also considered and after cloning I thought of Sorting it in Asc order.

Is it possible to sort the data after cloning when I am adding the data.
0
Vulpes12y ago
I don't understand why that should be so as you're assigning the sorted view (after conversion back to a table) to table8_sorted each time around the for loop.

In fact you're doing exactly what you were doing before except now the variable is declared external to the for loop, not within it.
0
Farhan Shariff12y ago
But the table8_sorted becomes unsorted again ,

dataview.ToView will it not change the datatable

when i checked

table8_sorted.Columns.Add("plot", typeof(double)); //sorting is undone
0
Vulpes12y ago
You may have misunderstood what I meant.

Here it is in actual code:

DataTable table8_sorted = null;

for (int loop = 0; loop < dtSorted.Columns.Count; loop++)
{
DataView dataview = dtSorted.DefaultView;
dataview.Sort = dtSorted.Columns[loop].ColumnName;
table8_sorted = dataview.ToTable(); // now assigning to 'external' variable
}

0
Farhan Shariff12y ago
I assigned it to null, But inside for loop I try to assign it it says already assigned cannot give different meaning ?
0
Vulpes12y ago
Just declare table8_sorted outside the for loop and set it to null.

Then in the for loop assign to that variable not a variable which is local to the for loop.
0