c# - 如何将带有某些 xml 类型列的行批量插入数据库？

Question

我正在尝试将行批量插入数据库。该表的某些列是 XML 类型的，这就是我面临一些问题的地方。

我尝试使用 SqlBulkCopy 来实现此目的，但一直收到 XElement 无法转换为字符串的错误，尽管我从未进行过类型转换。

这是我的表格设计:

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

我的 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; }
}

我希望一次性将批量数据插入到表中。相反，我收到此错误:

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

Answer 1

我认为您应该在代码中使用 XmlDocument 而不是 XElement。 ProcessData、MetaData 和 ResultData 的数据类型应为 XmlDocument。