null column change datatype

Question

I am getting a datatable from my DB with one NULL column. By default the datatype is Int32. If I change the datatype like below it says:

Cannot change DataType of a column once it has data

_object.datatable.Columns[coll].DataType = typeof (decimal);

Is there a way to get the column as decimal or to change the datatype in the code ?

Answer 1

I stumbled on this several times.

You have three options.

1. Create the columns by yourself

   var table = new DataTable();
   table.Columns.Add("id", typeof(int));
   table.Columns.Add("col1", typeof(decimal));
   
   DataAdapter.Fill(table);
   

if you use a DataAdapter or DataReader to fill a table, the columns are only created automatically if you haven't done it by yourself before

1. If you need a different DataType for a relation, just add an expression column

   table.Columns.Add("col1_decimal", typeof(decimal), "col1");
   

that column will be readonly, but will work for Relations.

1. Add another column, copy the data, drop the first column, rename the new one

   table.Columns.Add("tmp", typeof(decimal));
   
   foreach(var row in table.Rows())
       row["tmp"] = row["col1"];
   
   table.Columns.Remove(table.Columns["col1"]);
   table.Columns["tmp"].ColumnName = "col1";
   

this could be a performance bottleneck for many rows.

Answer 2

You can't autoconvert the entire column to the new datatytpe.

You can convert a specific value when rou read it:

decimal dec = (decimal)(int)_object.datatable[rownum][col1];

Note: if the real value is an Int32, you need to unbox it by casting to int, before you can cast to decimal.