C# SqlBulkCopy types mismatch

Question

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 SqlBulkCopy.

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?

How do you add the columns to the datatable? – Arion Feb 15 '12 at 12:20 — Arion, Feb 15 '12 at 12:20

Paul Alan Taylor · Accepted Answer · 2012-02-15T14:10:17.703

4

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.

EDIT

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.

e.g.

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

edited Feb 15 '12 at 14:10

answered Feb 15 '12 at 12:23

Paul Alan Taylor

10,474
1
26
42

Ok, and then, adding a row, how will that look like? I have all the info I need in a string[], so I assume I have to convert it before adding it. For example: DataRow row = dataTable.NewRow(); row["Price"] = Convert.ToDecimal(blanketInfo[4]); dataTable.Rows.Add(row); I will need to do that for all the data I have, or is there another way? – Rocshy Feb 15 '12 at 12:29
Depends on what type blanketInfo[4] is. If the type doesn't reflect your target field type, then you will need to convert it. However, if it is already a decimal, you can just assign it. – Paul Alan Taylor Feb 15 '12 at 14:06

score 1 · Answer 2 · answered Feb 15 '12 at 12:19

Sounds like you may be creating the columns in the DataTable in code (as opposed to Filling via a DataAdapter which would set the column data types appropriately).

If that's the case, then can you not just create the DataColumns with the correct data types to start with, instead of all strings...and then ensure when you add rows to it, you convert the data into the appropriate type?

i.e. get to the point where your DataTable has the correct types and data in correctly, so SqlBulkCopy can then blast it into the DB with data already in the correct form.

score 0 · Answer 3 · answered Jan 07 '15 at 07:28

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.

C# SqlBulkCopy types mismatch

3 Answers3