带有CLR UDT的SQLBulkCopy为程序集“MyType”中的“MyNamespace.MyType”类型提供了“找不到方法'Read'”

SqlBulkCopy应该能够正常处理SQLCLR UDT（用户定义类型）。我成功使用了DbDataReader和DataTable方法。

这对我有用：

C＃代码 （我使“客户端”成为SQLCLR存储过程）

using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class xtra
{

    [SqlProcedure]
    public static void BcpTest(SqlInt32 TheID, SqlString TheConnectionString)
    {
        System.Data.DataTable _DataTable = new System.Data.DataTable();
        _DataTable.Columns.Add("ID", typeof(Int32));
        _DataTable.Columns.Add("SomeDate", typeof(DateTime));
        _DataTable.Columns.Add("SomeData", typeof(Type_HashTable));

        Type_HashTable _Bob = Type_HashTable.Parse(@"testKey=testVal");
        _DataTable.Rows.Add(TheID.Value, DateTime.Now, _Bob);

        _DataTable.Rows.Add(TheID.Value + 1, DateTime.Now,
           Type_HashTable.Parse(@"testKey2=testVal2"));

        SqlBulkCopy _BulkCopy = new SqlBulkCopy(TheConnectionString.Value);
        _BulkCopy.DestinationTableName = "dbo.BulkCopyUDT";

        try
        {
            _BulkCopy.WriteToServer(_DataTable);
        }
        finally
        {
            _BulkCopy.Close();
        }
    }
}

T-SQL代码

-- DROP TABLE dbo.BulkCopyUDT;
CREATE TABLE dbo.BulkCopyUDT
(
  ID INT NOT NULL CONSTRAINT [PK_BulkCopyUDT] PRIMARY KEY,
  SomeDate DATETIME,
  SomeData [SQL#].[Type_HashTable]
);
GO

GRANT INSERT, SELECT ON dbo.BulkCopyUDT TO [Public];
GRANT EXECUTE ON TYPE::SQL#.Type_HashTable TO [Public];
GO

CREATE PROCEDURE dbo.SqlBulkCopy_Test
(
    @TheID INT,
    @TheConnectionString NVARCHAR(4000) = 
        N'Data Source=(local); Integrated Security=true; Initial Catalog=my_database;'
)
AS EXTERNAL NAME [my_assembly].[xtra].[BcpTest];
GO

ALTER ASSEMBLY [my_assembly] WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

考试

EXEC dbo.SqlBulkCopy_Test 1;

SELECT *, SomeData.ToString() FROM dbo.BulkCopyUDT;

EXEC dbo.SqlBulkCopy_Test 3,
       N'Data Source=(local); User=test; Password=test; Initial Catalog=my_database;';

SELECT *, SomeData.ToString() FROM dbo.BulkCopyUDT;

我也使用SqlBulkCopy和参数化的即席查询从Console App开始工作：

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkCopyUDT
{
    class Program
    {
        static void Main(string[] args)
        {
            int _TheID = Int32.Parse(args[0]);

            string _TheConnectionString = 
              @"Data Source=(local); Integrated Security=true; Initial Catalog=my_database;";
            if (args.Length > 1)
            {
                _TheConnectionString = args[1];
            }

            //DataTable _DataTable = new DataTable();
            //_DataTable.Columns.Add("ID", typeof(Int32));
            //_DataTable.Columns.Add("SomeDate", typeof(DateTime));
            //_DataTable.Columns.Add("SomeData", typeof(Type_HashTable));

            //Type_HashTable _Bob = Type_HashTable.Parse(@"testKey=testVal");
            //_DataTable.Rows.Add(_TheID, DateTime.Now, _Bob);

            //_DataTable.Rows.Add(_TheID + 1, DateTime.Now,
            //   Type_HashTable.Parse(@"testKey2=testVal2"));

            //SqlBulkCopy _BulkCopy = new SqlBulkCopy(_TheConnectionString);
            //_BulkCopy.DestinationTableName = "dbo.BulkCopyUDT";

            //try
            //{
            //    _BulkCopy.WriteToServer(_DataTable);
            //}
            //finally
            //{
            //    _BulkCopy.Close();
            //}

            using (SqlConnection _Connection = new SqlConnection(_TheConnectionString))
            {
                using (SqlCommand _Command = _Connection.CreateCommand())
                {
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText =
                        @"INSERT INTO dbo.BulkCopyUDT (ID, SomeDate, SomeData)
                         VALUES (@MyID, GETDATE(), @MyData);";

                    SqlParameter _ParamMyID = new SqlParameter("@MyID", SqlDbType.Int);
                    _ParamMyID.Value = _TheID;
                    _Command.Parameters.Add(_ParamMyID);

                    SqlParameter _ParamMyData = new SqlParameter("@MyData", SqlDbType.Udt);
                    _ParamMyData.UdtTypeName = "SQL#.Type_HashTable";
                    _ParamMyData.Value = Type_HashTable.Parse(@"testKey3=testVal3");
                    _Command.Parameters.Add(_ParamMyData);

                    _Connection.Open();
                    _Command.ExecuteNonQuery();
                }
            }
        }
    }
}

PS如果直接将数据发送到UDT列，则需要采用二进制形式，因为这是SqlBulkCopy根据源代码传输数据的唯一方式。