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