How To Change DataType of a DataColumn in a DataTable

Question

I have   DataTable Table   new DataTable  SqlConnection   new System Data SqlClient SqlConnection  Data Source     ServerName     Initial Catalog     DatabaseName     Integrated Security SSPI  Connect Timeout 120     SqlDataAdapter adapter   new SqlDataAdapter  Select   from     TableName  Connection   adapter FillSchema Table  SchemaType Source   adapter Fill Table    DataColumn column   DataTable Columns 0     What I want to do is   Assume currently column DataType Name is  Double   I want it to become  Int32    How do I achieve this

User · Answer

DataTable DT          Rename column to OLD  DT Columns  ID   ColumnName    ID OLD      Add column with new type  DT Columns Add   ID   typeof int        copy data from old column to new column with new type  foreach  DataRow DR in DT Rows     DR  ID     Convert ToInt32  DR  ID OLD           remove  OLD  column DT Columns Remove   ID OLD

User · Answer

Consider also altering the return type   select cast columnName as int  columnName from table

User · Answer

I created an extension function which allows changing the column type of a DataTable  Instead of cloning the entire table and importing all the data it just clones the column  parses the value and then deletes the original            lt summary gt          Changes the datatype of a column  More specifically it creates a new one and transfers the data to it          lt  summary gt           lt param name  column  gt The source column lt  param gt           lt param name  type  gt The target type lt  param gt           lt param name  parser  gt A lambda function for converting the value lt  param gt      public static void ChangeType this DataColumn column  Type type  Func lt object  object gt  parser                  no table  just switch the type         if  column Table    null                        column DataType   type              return                       clone our table         DataTable clonedtable   column Table Clone               get our cloned column         DataColumn clonedcolumn   clonedtable Columns column ColumnName              remove from our cloned table         clonedtable Columns Remove clonedcolumn              change the data type         clonedcolumn DataType   type             change our name         clonedcolumn ColumnName   Guid NewGuid   ToString               add our cloned column         column Table Columns Add clonedcolumn              interpret our rows         foreach  DataRow drRow in column Table Rows                        drRow clonedcolumn    parser drRow column                         remove our original column         column Table Columns Remove column              change our name         clonedcolumn ColumnName   column ColumnName            You can use it like so   List lt DataColumn gt  lsColumns   dtData Columns      Cast lt DataColumn gt         Where i   gt  i DataType    typeof decimal        ToList      loop through each of our decimal columns foreach DataColumn column in lsColumns          change to double     column ChangeType typeof double   value    gt                double output   0          double TryParse value ToString    out output           return output                The above code changes all the decimal columns to doubles

User · Answer

I ve taken a bit of a different approach  I needed to parse a datetime from an excel import that was in the OA date format  This methodology is simple enough to build from    in essence     Add column of type you want Rip through the rows converting the value Delete the original column and rename to the new to match the old  private void ChangeColumnType System Data DataTable dt  string p  Type type           dt Columns Add p     new   type           foreach  System Data DataRow dr in dt Rows                 Will need switch Case for others if Date is not the only one              dr p     new    DateTime FromOADate double Parse dr p  ToString         dr p  ToString                      dt Columns Remove p           dt Columns p     new   ColumnName   p

User · Answer

if you want to change only a column for example from string to int32 you can use Expression property   DataColumn col   new DataColumn  col int    typeof int    table Columns Add col   col Expression    table exist col string      digit string convert to int

User · Answer

Old post  but I thought I d weigh in  with a DataTable extension that can convert a single column at a time  to a given type   public static class DataTableExt       public static void ConvertColumnType this DataTable dt  string columnName  Type newType                using  DataColumn dc   new DataColumn columnName     new   newType                            Add the new column which has the new type  and move it to the ordinal of the old column             int ordinal   dt Columns columnName  Ordinal              dt Columns Add dc               dc SetOrdinal ordinal                   Get and convert the values of the old column  and insert them into the new             foreach  DataRow dr in dt Rows                  dr dc ColumnName    Convert ChangeType dr columnName   newType                   Remove the old column             dt Columns Remove columnName                   Give the new column the old column s name             dc ColumnName   columnName                      It can then be called like this   MyTable ConvertColumnType  MyColumnName   typeof int      Of course using whatever type you desire  as long as each value in the column can actually be converted to the new type

User · Answer

While it is true that you cannot change the type of the column after the DataTable is filled  you can change it after you call FillSchema  but before you call Fill  For example  say the 3rd column is the one you want to convert from double to Int32  you could use   adapter FillSchema table  SchemaType Source   table Columns 2  DataType   typeof  Int32   adapter Fill table

User · Answer

Once a DataTable has been filled  you can t change the type of a column    Your best option in this scenario is to add an Int32 column to the DataTable before filling it   dataTable   new DataTable  Contact    dataColumn   new DataColumn  Id    dataColumn DataType   typeof Int32   dataTable Columns Add dataColumn     Then you can clone the data from your original table to the new table   DataTable dataTableClone   dataTable Clone      Here s a post with more details

User · Answer

Puedes agregar una columna con tipo de dato distinto   luego copiar los datos y eliminar la columna anterior TB Columns Add  quot columna1 quot   GetType Integer       TB Select  quot id id quot   ToList   ForEach Sub row  row  quot columna1 quot     row  quot columna2 quot        TB Columns Remove  quot columna2 quot

User · Answer

Dim tblReady1 As DataTable   tblReady Clone       convert all the columns type to String  For Each col As DataColumn In tblReady1 Columns   col DataType   GetType String  Next  tblReady1 Load tblReady CreateDataReader

User · Answer

I combined the efficiency of Mark s solution - so I do not have to  Clone the entire DataTable - with generics and extensibility  so I can define my own conversion function  This is what I ended up with        lt summary gt          Converts a column in a DataTable to another type using a user-defined converter function       lt  summary gt       lt param name  dt  gt The source table  lt  param gt       lt param name  columnName  gt The name of the column to convert  lt  param gt       lt param name  valueConverter  gt Converter function that converts existing values to the new type  lt  param gt       lt typeparam name  TTargetType  gt The target column type  lt  typeparam gt  public static void ConvertColumnTypeTo lt TTargetType gt  this DataTable dt  string columnName  Func lt object  TTargetType gt  valueConverter        var newType   typeof TTargetType        DataColumn dc   new DataColumn columnName     new   newType           Add the new column which has the new type  and move it to the ordinal of the old column     int ordinal   dt Columns columnName  Ordinal      dt Columns Add dc       dc SetOrdinal ordinal           Get and convert the values of the old column  and insert them into the new     foreach  DataRow dr in dt Rows                dr dc ColumnName    valueConverter dr columnName                  Remove the old column     dt Columns Remove columnName           Give the new column the old column s name     dc ColumnName   columnName      This way  usage is a lot more straightforward  while also customizable   DataTable someDt   CreateSomeDataTable       Assume ColumnName is an int column which we want to convert to a string one  someDt ConvertColumnTypeTo lt string gt   ColumnName   raw   gt  raw ToString

User · Answer

You cannot change the DataType after the Datatable is filled with data  However  you can clone the Data table  change the column type and load data from previous data table to the cloned table as shown below   DataTable dtCloned   dt Clone    dtCloned Columns 0  DataType   typeof Int32   foreach  DataRow row in dt Rows         dtCloned ImportRow row

[c#] How To Change DataType of a DataColumn in a DataTable?

Examples related to c#

Examples related to types

Examples related to datacolumn