.net dynamic set value for data column in a dataset

Question

I have this case of data conversion in .NET dataset: I have a new database, I've queried a table into my dataset but I dont know exactly datatype of DataColumn of DataTable in the dataset and I want to insert some new data without knowing exactly the DataColumn.DataType at the design time. So, my idea is that I can use reflection to get the type of the DataColumn to insert the new data precisely. Would somebody please tell me whether can I do that and how to?

SomeDbDataAdapter da = new SomeDbDataAdapter("select * from table1", conn);
                DataSet ds = new DataSet();
                da.Fill(ds);

                Int32 i = 1;

                DataRow dr = ds.Tables[0].NewRow();
                PropertyInfo colInfo = dr.GetType().GetProperty("COLUMN1");
                Type t = ds.Tables[0].Columns["COLUMN1"].DataType;
                colInfo.SetValue(dr, Convert.ChangeType(i, t), null);

                ds.Tables[0].Rows.Add(dr);

                SomeDbCommandBuilder builder = new SomeDbCommandBuilder(da);
                builder.GetInsertCommand();

                DataRow[] rows = ds.Tables[0].Select("", "", DataViewRowState.Added);
                da.Update(rows);

Answer 1

The .NET DataRow will automatically try to cast your value to the target type. The following code runs fine, even though the column type is int

DataSet ds = new DataSet();
ds.Tables.Add();
ds.Tables[0].Columns.Add("COLUMN1", typeof(int));

DataRow dr = ds.Tables[0].NewRow();

Type t = ds.Tables[0].Columns["COLUMN1"].DataType; 

dr["COLUMN1"] = "4";

However adding an incorrect value will yield an exception

dr["COLUMN1"] = "four";

EDIT:

For sql types that have a parse method you can use reflection. Some types like SqlString, dont require manual parsing and can be handled by the DataRow

DataSet ds = new DataSet();
ds.Tables.Add();
ds.Tables[0].Columns.Add("COLUMN1", typeof(SqlInt32));

DataRow dr = ds.Tables[0].NewRow();

string value = "4";

Type t = ds.Tables[0].Columns["COLUMN1"].DataType;

MethodInfo methodInfo = t.GetMethod("Parse");

if (methodInfo != null)
{
    dr["COLUMN1"] = methodInfo.Invoke(null, new object[] { value });
}
else
{
    dr["COLUMN1"] = value;
}

Answer 2

Reference: https://stackoverflow.com/a/9028087/2919255

DataSet dsCloned = ds.Clone();
dsCloned.Tables[0].Columns[4].DataType = typeof(string);
foreach (DataRow row in ds.Tables[0].Rows) 
{
    dsCloned.Tables[0].ImportRow(row);
}