How to bulk insert rows with some columns of xml type into the database?

Viewed 194

I'm trying to bulk insert rows into the database. Some of the columns of this table are of XML type and that's where I am facing some issues.

I tried using SqlBulkCopy to achieve this but keep on getting the error that XElement cannot be converted to a string although I'm never doing a type cast.

This is my table design:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TasksQueue]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Type] [int] NOT NULL,
    [ProcessData] [xml] NOT NULL,
    [MetaData] [xml] NULL,
    [SubmittedBy] [int] NULL,
    [Status] [int] NOT NULL,
    [ResultData] [xml] NULL,
    [SubmittedDate] [datetime] NULL,
    [ProcessedDate] [datetime] NULL,
    [ProcessingTime] [varchar](28) NULL,
    [DistrictID] [int] NULL,
    [Name] [nvarchar](128) NULL,
 CONSTRAINT [PK_TasksQueue] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

My code in C#:

public static void CreateBulkTasks(List<Task> BulkTasks)
{
        string connectionString = ConfigurationManager.AppSettings[AppSettingsKeys.QwizdomOnlineDBConnectionString];
        _obj = new object();

        lock (_obj)
        {
            DataTable tableMember = new DataTable();
            DataTable tableLicense = new DataTable();

            var x = BulkTasks[0].MetaData;

            tableMember.Columns.Add("Type", typeof(Int32));
            tableMember.Columns.Add("ProcessData", typeof(XElement));
            tableMember.Columns.Add("MetaData", typeof(XElement));
            tableMember.Columns.Add("SubmittedBy", typeof(Int32));
            tableMember.Columns.Add("Status", typeof(Int32));
            tableMember.Columns.Add("ResultData", typeof(XElement));
            tableMember.Columns.Add("SubmittedDate", typeof(DateTime));
            tableMember.Columns.Add("ProcessedDate", typeof(DateTime));
            tableMember.Columns.Add("ProcessingTime", typeof(string));
            tableMember.Columns.Add("DistrictID", typeof(Int32));
            tableMember.Columns.Add("Name", typeof(string));

            foreach (var task in BulkTasks)
            {
                tableMember.Rows.Add(
                    task.Type,
                    task.ProcessData,
                    task.MetaData,
                    task.SubmittedBy,
                    task.Status,
                    task.ResultData,
                    task.SubmittedDate,
                    task.ProcessedDate,
                    task.ProcessingTime,
                    task.DistrictID,
                    task.Name
                );
            }

            using (System.Data.SqlClient.SqlBulkCopy bulkCopyGroupMembers = new System.Data.SqlClient.SqlBulkCopy(connectionString))
            {
                bulkCopyGroupMembers.DestinationTableName = "[TasksQueue]";
                bulkCopyGroupMembers.ColumnMappings.Add("Type", "Type");
                bulkCopyGroupMembers.ColumnMappings.Add("ProcessData", "ProcessData");
                bulkCopyGroupMembers.ColumnMappings.Add("MetaData", "MetaData");
                bulkCopyGroupMembers.ColumnMappings.Add("SubmittedBy", "SubmittedBy");
                bulkCopyGroupMembers.ColumnMappings.Add("Status", "Status");
                bulkCopyGroupMembers.ColumnMappings.Add("ResultData", "ResultData");
                bulkCopyGroupMembers.ColumnMappings.Add("SubmittedDate", "SubmittedDate");
                bulkCopyGroupMembers.ColumnMappings.Add("ProcessedDate", "ProcessedDate");
                bulkCopyGroupMembers.ColumnMappings.Add("ProcessingTime", "ProcessingTime");
                bulkCopyGroupMembers.ColumnMappings.Add("DistrictID", "DistrictID");
                bulkCopyGroupMembers.ColumnMappings.Add("Name", "Name");
                bulkCopyGroupMembers.WriteToServer(tableMember);
    }
}

public class Task
{
    public int? ID { get; set; }
    public TasksQueueType Type { get; set; }
    public XElement ProcessData { get; set; }
    public XElement MetaData { get; set; }
    public int? SubmittedBy { get; set; }
    public string SubmittedByName { get; set; }
    public TasksQueueItemStatus Status { get; set; }
    public XElement ResultData { get; set; }
    public int? DistrictID { get; set; }
    public DateTime SubmittedDate { get; set; }
    public DateTime? ProcessedDate { get; set; }
    public TimeSpan? ProcessingTime { get; set; }
    public bool? HighPriority { get; set; }
    public string Name { get; set; }
}

I expect the bulk data to be inserted into to table in a single go. Instead I get this error:

An exception of type 'System.InvalidCastException' occurred in System.Data.dll but was not handled in user code

Additional information: Unable to cast object of type 'System.Xml.Linq.XElement' to type 'System.String'.

2 Answers

I think you should use XmlDocument instead of XElement in your code. The data type for ProcessData, MetaData and ResultData should be XmlDocument.

I've managed to do this by serializing the XElement to string and setting the column to string in DataTable. I already had a model created by Linq to SQL, so I prepared a second model with string in place of XElement and bulk copied that.

Related