SQLbulkcopy column conversion

Question

_{Monday, February 1, 2016 5:06 AM}

Hi,

I am trying to use SQLbulkcopy in C# to insert XML data into SQL dataset.  Some columns in the SQL table are defined as an int32 type.  I am getting type conversion error when I use SQLbulkcopy.Columnmappings.Add method to insert XML string into column defined int32.  How can I convert SQLbulkcopy.Columnmappings.Add into int32 before insert into SQL table?

Thanks,

Brian

All replies (8)

_{Monday, February 1, 2016 6:09 PM ✅Answered}

Brian Ho  : Can you further check for mismatch datatypes from you datatable like below ?

  foreach (DataColumn col in dtJob.Columns)
            {
                Console.WriteLine("{0} {1}", col.ColumnName, col.DataType);
            }

_{Monday, February 1, 2016 7:51 PM ✅Answered}

Hope the below helps..add this above your using block..

  dtJob.Columns.Add("NewJobID", typeof(Int32));
            dtJob.Columns.Add("NewJobclass", typeof(Int32));
            foreach (DataRow objRow in dtJob.Rows)
            {
                objRow["NewJobID"] = Convert.ToInt32(objRow["JobID"]);
                objRow["NewJobclass"] = Convert.ToInt32(objRow["Jobclass"]);
            }
            dtJob.Columns.Remove("JobID"); dtJob.Columns.Remove("Jobclass");
            dtJob.Columns["NewJobID"].ColumnName = "JobID";
            dtJob.Columns["NewJobclass"].ColumnName = "Jobclass";
            dtJob.AcceptChanges();
            foreach (DataColumn col in dtJob.Columns)
            {
                Console.WriteLine("{0} {1}", col.ColumnName, col.DataType);
            }

_{Tuesday, February 2, 2016 2:52 AM ✅Answered}

Hi Brian_Ho,

>> It throw an exception when it gets to    objRow["NewJobclass"] = Convert.ToInt32(objRow["Jobclass"]); because some Jobclass data is blank in the XML doc..  Otherwise, it works!

When the “objRow["Jobclass"]” equals “”,not null, the “Convert.ToInt32()” method would happen FormatException error. So, you’d better first check whether the “objRow["Jobclass"]” is “” or not. The following code you could have a look.

Convert.ToInt32(objRow["Jobclass"]==""? null: objRow["Jobclass"]);

Best Regards,

Weibo Zhang

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

_{Monday, February 1, 2016 5:32 PM}

What is your data source (DataTable? DataReader?) and how you are populating with XML. This is where you need to perform your conversion.

You can't map and/or export until the column data in question is compatible with the destination column type.

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Monday, February 1, 2016 5:57 PM}

                 XDocument XDocJob  = new XDocument();
                 XDocJob = GetInformation("job", "text/xml");
        
                 using (SqlConnection con = new SqlConnection(CS))
                 {
                     con.Open();
                   
                     DataSet dsJob = new DataSet();
                     dsJob.ReadXml(XDocJob.CreateReader());
                     DataTable dtJob = dsJob.Tables["Job"];
                
                     using (SqlBulkCopy bc = new SqlBulkCopy(con))
                     {
                         bc.DestinationTableName = "JobTitle";
                         bc.ColumnMappings.Add("name", "Name");
                         bc.ColumnMappings.Add("id", "JobID");
                         bc.ColumnMappings.Add("jobclass", "Jobclass"); 
                         bc.ColumnMappings.Add("description", "Description");
                         bc.ColumnMappings.Add("active", "Active");
                         bc.WriteToServer(dtJob);
                     }

Above is my snippet.  Source data is coming from XDoxument in memory.  Destination table is SQL table.  JobID and Jobclass are defined as int32 in the JobTitle SQL table.  It is failing on WriteToServer because of the mismatch data type.  

Thanks,

Brian

_{Monday, February 1, 2016 7:22 PM}

I know which one is mismatch.  It is the JobID and Jobclass.  If I change the table definition to nvarchar from int32, it will load.  The question is how to modify the SQLbulkcopy without changing the column definition?  There are other applications expecting int32 from the table.

Thanks,

Brian

_{Monday, February 1, 2016 10:55 PM}

Thanks for the snippet.  It throw an exception when it gets to    objRow["NewJobclass"] = Convert.ToInt32(objRow["Jobclass"]); because some Jobclass data is blank in the XML doc..  Otherwise, it works!

Brian

_{Tuesday, February 2, 2016 4:16 AM}

Putting condition check solve the problem.

Thank you! 

Brian