1

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);

anhdung88
  • 11
  • 4

2 Answers2

0

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;
}
Lourens
  • 1,510
  • 1
  • 13
  • 27
  • my dataset is not complete new, it is returned by an adapter from database. So now I want to use Reflection or other ways to get the dataset's underlying DataColumn.DataType, from that DataType i will cast some value e.g an Int32 value to the right type of the DataColumn.DataType. – anhdung88 Mar 03 '14 at 14:41
  • I've added a way to use reflection to set the types. You will however first have to check which data types you get from your db. – Lourens Mar 03 '14 at 14:58
  • sorry for my bad description, I've repost my code above. The scenario here is I fetch data from a database to my dataset - and I dont know exactly the type mapping between the type of a column on db server to the type in .NET dataset datacolumn, but I know that the column has exact name as "COLUMN1". So now, I want some mechanism to get the exact .NET datatype of that column to cast some input value (such as an integer here) to create new row and insert that row into database. – anhdung88 Mar 03 '14 at 16:13
0

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);
}
Community
  • 1
  • 1
ajthewebdev
  • 432
  • 2
  • 5
  • 17