I have a DataTable which has 10 columns, and after I add some rows in it, I want to add it to the database using
My problem is that in the DataTable I have strings and some fields in the database are decimal and date respectively. Is there a way to convert them to the desired data types?
When programmatically adding columns to your DataTable, you can specify type:-
DataTable dt = new DataTable(); dt.Columns.Add("FieldName", typeof(System.Decimal));
These will be honored when you perform the bulk copy.
In response to user comment.
When populating the DataRow with information, an explicit conversion is only required if the source data doesn't match the target field.
If the types are the same, you can just assign it.
// With a decimal field, decimal target Decimal myNum = 123; // Works fine - assuming the Price datacolumn has been set up as a System.Decimal row["Price"] = myNum;
was having the same problem where in I was suppose to copy data from an excel file which had strings and I wanted them to be stored in the database as double. I have pasted the code related to the question below :-
DataTable dt = new DataTable();
//Read Data from First Sheet
connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + SheetName + "] "; oda.SelectCommand = cmdExcel; oda.Fill(dt); connExcel.Close(); Convert.ToDouble(dt.Columns["Product"]);
//where product(product temperature) is the a column whose datatype in the database is float and in the excel file is string.