强类型数据集作为用户定义的表类型参数

Question

我有一个存储过程用于处理对一个被定义为的表进行插入、更新和删除操作

CREATE TABLE [dbo].[TestTable](
    [Id] [int] PRIMARY KEY NOT NULL,
    [Data] [nvarchar](50) NOT NULL,
    [ChangeDate] [datetime] NULL)

使用存储过程

CREATE PROCEDURE MergeTest
    @Testing TestTableType readonly
AS
BEGIN
    MERGE INTO Testing as Target
    USING (SELECT * FROM @Testing) AS SOURCE
        ON (Target.Id = Source.Id)
    WHEN MATCHED THEN
        UPDATE SET 
            Target.Data = Source.Data,
            Target.ChangeDate = Source.ChangeDate
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (Data, ChangeDate)
        VALUES (Source.Data, Source.ChangeDate)
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE;

    RETURN 0;
END

UDT类型为

CREATE TYPE TestTableType AS TABLE(
    [Id] [int] PRIMARY KEY NOT NULL,
    [Data] [nvarchar](50) NOT NULL,
    [ChangeDate] [datetime] NULL)

我正在尝试使用这个结构来从C#中进行批量插入等操作。使用以下代码可以实现：

using (SqlConnection connection = new SqlConnection(@"..."))
{
    connection.Open();

    DataTable DT = new DataTable();
    DT.Columns.Add("Id", typeof(int));
    DT.Columns.Add("Data", typeof(string));
    DT.Columns.Add("ChangeDate", typeof(DateTime));

    for (int i = 0; i < 100000; i++)
    {
        DT.AddTestRow((i + 1), (i + 1).ToString(), DateTime.Now);
    }

    using (SqlCommand command = new SqlCommand("MergeTest", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@Testing", DT);
        command.ExecuteNonQuery();
    }
}

然而，当我更改这些行时

DataTable DT = new DataTable();
DT.Columns.Add("Id", typeof(int));
DT.Columns.Add("Data", typeof(string));
DT.Columns.Add("ChangeDate", typeof(DateTime));

to

DataSet1.TestDataTable DT = new DataSet1.TestDataTable();

当我尝试使用强类型的DataTable结构作为用户定义表类型参数时，我遇到了一个Argument Exception错误：

不存在从对象类型TestBulkInsertDataset.DataSet1+TestDataTable到已知托管提供程序本机类型的映射。

有没有办法将强类型的DataTable作为用户定义的表类型参数使用？

- Manatherin

Answer 1

找到了答案。当使用强类型数据表时，您必须为参数指定类型。参数行变为：

var testingparam = command.Parameters.AddWithValue("@Testing", DT);
testingparam.SqlDbType = SqlDbType.Structured;

然后一切都正常工作。

- Manatherin

Answer 2

你尝试过类似这样的东西吗？

DataSet1.TestDataTable DT = new DataSet1.TestDataTable();

// Fill data table

DataTable DT1 = DT;  // DataSet1.TestDataTable should be a subclass of DataTable

using (SqlCommand command = new SqlCommand("MergeTest", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@Testing", DT1);
    command.ExecuteNonQuery();
}

如果键入的数据集仍然是常规数据集和数据表的子类，我认为这应该可以工作。

预计时间：既然那样不起作用，那么这个怎么样？

DataSet DS1 = new DataSet();
DS1.Merge(DT, false, MissingSchemaAction.Add);

// etc.

command.Parameters.AddWithValue("@Testing", DS1.Tables[0]);

假设这个方法可行（你可能需要调整Merge方法的重载来获得所需的结果），你将会在你的DataSet中得到一个DataTable，它具有DT和DataSet1.TestDataTable的模式和数据，但只是类型为DataTable。

- Ann L.

---

我已经尝试过这个方法和command.Parameters.AddWithValue("@Testing", (DataTable)DT1);，但是添加参数似乎会取消变量的装箱。 - Manatherin

谢谢回复。我添加了另一个建议。它不是很整洁，但可能是一个解决方法。 - Ann L.

谢谢您的建议。我似乎已经解决了这个问题（我已经将我的解决方案发布为答案）。 - Manatherin