Click here to Skip to main content
14,732,816 members
Please Sign up or sign in to vote.
1.30/5 (3 votes)
See more:
How to change the datatype of a datatbale in c# while already having data in.
I have an int field that has to be typeof(string)
Posted
Updated 17-Sep-20 1:06am

Dear Digimanu,

You can't change the data type of a datatable once the data is populated into it.

For your reference follow the link:-

http://stackoverflow.com/questions/2538477/changing-populated-datatable-column-data-types[^]

Hope this will help you out.

Thanks
   
First, I must admit that I don't understand why you don't create the columns with proper data types in the first place. That would save a lot of trouble.

However, to your question, you can for example load the data into a new datatable using datatablereader. Consider the following:
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Col1", typeof(int));
dt.Rows.Add(1);

System.Data.DataTable dt2 = new System.Data.DataTable();
dt2.Columns.Add("Col1", typeof(string));
dt2.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges);
   
Comments
Herman<T>.Instance 3-Jan-12 4:12am
   
that is a cool one: System.Data.LoadOption.OverwriteChanges. That safes a lot of trouble.



You say: "First, I must admit that I don't understand why you don't create the columns with proper data types in the first place".
The reason is that the database gives me this datatable based on a stored procedure that is used for several reports and can hold up to 1 to 15 rows of data. Based on the number of rows I can determine which string has to be set in the place of the int value.
Wendelius 3-Jan-12 8:44am
   
Ok, that makes sense :)
FarhanShariff 6-Mar-14 4:19am
   
I want to change the DataTable type to decimal how to I do that

namespace ReadDataFromCSVFile
{
static class Program
{
static void Main()
{
string csv_file_path = @"C:\Matlab\Sheet1_t168h.csv";
DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
Console.WriteLine("Rows count:" + csvData.Rows.Count);
Console.ReadLine();
}
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();

using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvData.Columns.Add(datecolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);
}
}


return csvData;
}
}
}
#realJSOP 23-Mar-18 9:55am
   
Hey Mike, I realize this is really old, but I have a reason that you don't set the type appropriate on the first-go-round. I was loading/parsing a CSV file, and simply added the parsed data to a datatable object, and initially set the column DataType property to object, and then determined the appropriate types from the data in the datatable. It was only after I got all the code in place that I realized you couldn't set the type after the datatable had rows in it, so I create a new method that was called at the end of the processing chain:

protected virtual void SetColumnTypes()
{
    if (this.ColumnHints != null)
    {
        DataTable cloned = this.ImportedData.Clone();
        for (int i = 0; i < cloned.Columns.Count; i++)
        {
            // ColumnHints is an object that determines the datatypes
            // for the imported CSV fields by iterating all of the
            // rows/columns of the initial datatable
            cloned.Columns[i].DataType = ColumnHints[i].ItemType;
        }
        foreach (DataRow row in this.ImportedData.Rows)
        {
            cloned.ImportRow(row);
        }
        this.ImportedData = cloned;
    }
}


So, as you can see, sometimes there is a need.
Maciej Los 8-May-18 8:28am
   
John, why to use a [foreach] loop to copy data between datatables? You can use [CopyToDataTable()] method instead:
cloned = this.ImportedData.CopyToDataTable();
this.ImportedData = cloned;
#realJSOP 8-May-18 9:29am
   
The details are fuzzy, but I seem to remember that I was getting strange results when I did it that way. As my memory is filled with more holes than Hillary Clinton's claims about her email server, I can't remember exactly what the issue was, although it's likely that I wanted to monitor the process of importing the rows, and simply didn't change back to using the CopyToDataTable method.
Maciej Los 8-May-18 9:32am
   
OK.
Cheers,
Maciej
If you want to convert type of all or some columns to a single type e.g., string type then use the following function:

public DataTable dataTableColsToOtherType(DataTable dt, Type type, List<string> colsForTypeChange = default(List<string>))
        {
            var dt2 = new DataTable();
            foreach (DataColumn c in dt.Columns)
            {
                if (colsForTypeChange != null && colsForTypeChange.Count > 0)
                {
                    if (colsForTypeChange.Contains(c.ColumnName))
                        dt2.Columns.Add(c.ColumnName, type);//Change column type if found in list "colsForTypeChange"
                    else dt2.Columns.Add(c.ColumnName, c.DataType);//No change in Column Type
                }
                else
                {
                    dt2.Columns.Add(c.ColumnName, type);//change all columns type to provided type
                }
                
            }
            dt2.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges);
            return dt2;
        }

to change all columns type call it as:
DataTable dtNew = dataTableColsToOtherType(dtOld, typeof(string));

to change specific columns type call it as:
DataTable dtNew = dataTableColsToOtherType(dtOld, typeof(string), new List<string>() { "INT_COLUMN_1","INT_COLUMN_2" });

Note: Make sure for your desired Column Type and its data type while changing columns to a single type, otherwise you may get exceptions.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900