Sometimes there is a need to change DataType of ADO.NET DataTable column. If your table is populated as a result of some database operation – you don’t know in advance what type the columns will be. And by design you cannot change the type of the column after the table is populated. Conudrum. Catch 22. Tough luck.

But wait, there’s light at the end of the tunnel. You cannot change the type of the existing column, but you can create a new one.

The solution below is VB.NET version of the one provided in this Stack Overflow answer:

    ''' <summary>
    ''' Converts DataType of ADO.NET DataTable to a new type
    ''' </summary>
    ''' <param name="i_dtTable">DataTable</param>
    ''' <param name="i_sColumnName">Name of the column</param>
    ''' <param name="i_oNewType">New Type of the column</param>
    ''' <returns>True in case of successfull conversion, False otherwise</returns>
    Public Shared Function ChangeDataTableColumnDataType(i_dtTable As DataTable, i_sColumnName As String, i_oNewType As Type) As Boolean

        If i_dtTable.Columns.Contains(i_sColumnName) = False Then Return False

        Dim oOldcolumn As DataColumn = i_dtTable.Columns(i_sColumnName)

        If oOldcolumn.DataType Is i_oNewType Then Return True

        Try
            Dim oNewColumn As New DataColumn("$temporary^", i_oNewType)

            i_dtTable.Columns.Add(oNewColumn)
            oNewColumn.SetOrdinal(i_dtTable.Columns.IndexOf(oOldcolumn)) 'To make sure column is inserted at the same place

            For Each row As DataRow In i_dtTable.Rows
                Try
                    row("$temporary^") = Convert.ChangeType(row(i_sColumnName), i_oNewType)
                Catch
                End Try
            Next
            i_dtTable.Columns.Remove(i_sColumnName)
            oNewColumn.ColumnName = i_sColumnName
        Catch generatedExceptionName As Exception
            Return False
        End Try

        Return True
    End Function

What this does is creates a new DataColumn of desired type, adds it to original table Columns collection, copies data from original column to the new column and then deletes original column. Since it has to traverse entire Rows collection to copy data, it may not be very efficient on very huge DataTables, but then you shouldn’t keep huge amounts of data in in-memory tables.

The only shortcoming of the original method – new column is always added at the end of the collection, which may have undesired effect for example if you bind the DataTable to a grid control which autogenerates grid columns. Suddenly a column that used to display in the middle of the grid is displayed at the end. Suggestion from this Stack Overflow answer to use columns SetOrdinal method to set column’s ordinal position was a perfect solution for this problem.

One scenario where this solution is really useful – if you bind DataTable to Infragistics WebDataGrid with “autogeneratecolumns=false”. By default Boolean DataColumns (that contain True/False data) are rendered as BoundCheckBoxField or checkboxes – which could be unneeded:

Using the above solution with a call like:

ChangeDataTableColumnDataType(dtMyTable, "Done", GetType(String))

will convert Boolean column to String one: