I have written a SQL Server CLR User-defined type (UDT) in SQL Server 2012. I have been able to access it though SQL test scripts, and have used it as a local variable, defined it in a table, and tested it through Visual Studio and SQL Server Management Studio.
We have an service that uses SQLBulkCopy in a fairly generalized fashion to pick up files that are placed in a directory, then insert their contents to the appropriate table. When I add my UDT as a column in one of those tables, I receive an error from the WriteToServer( DataTable ) invocation.
The UDT column is being passed as a System.String, in the hope that the UDT's Parse() method will be called within SQL Server to convert it to the internal type. I have also tried declaring the UDT class within this client program, and passing the data as the UDT type directly.
In either case I receive this error message (edited to take out my proprietary names)
Could not find method 'Read' for type 'MyNamespace.MyType' in assembly 'MyType'
I have reviewed as many similar questions that I can find about this error message, and they generally refer to the format of the CREATE statement. Also, they generally refer to CLR functions, not CLR types, which are slightly different. This is mine:
CREATE TYPE [dbo].[MyType]
EXTERNAL NAME [MyType].[MyNamespace.MyType]
I suspect this might not be the issue, and that, instead, it has to do with how SQLBulkCopy interacts with a SQLCLR UDT. For this particular combination it's difficult to find any in-depth explanation.
Edit #1 - It is custom serialization.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType( Format.UserDefined, MaxByteSize = -1 )]
public struct MyType: INullable, IBinarySerialize
Edit #2 - Execute permission is granted
GRANT EXECUTE
ON TYPE :: MyType
TO PUBLIC
Edit #3 - adapted testing code
CREATE TABLE [dbo].[TestMyType]
(
[SourceMachine] [varchar](32) NULL,
[Output] MyType NULL
)
and updated by
try
{
DataTable dataTable = new DataTable( "[TestMyType]" );
dataTable.Columns.Add( "SourceMachine", typeof( System.String ) );
dataTable.Columns.Add( "Output", typeof( MyNamespace.MyType ) );
dataTable.Rows.Add( "Ron1", MyNamespace.MyType.Parse( "This is string 1" ) );
dataTable.Rows.Add( "Ron2", MyNamespace.MyType.Parse( "This is string 2" ) );
dataTable.Rows.Add( "Ron3", MyNamespace.MyType.Parse( "This is string 3" ) );
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy( conn );
sqlBulkCopy.DestinationTableName = "[TestMyType]";
sqlBulkCopy.WriteToServer( dataTable );
}
catch ( Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
throw;
}
This gave the same error message that is shown above.
Edit #4 - Eliminate SqlBulkCopy from the issue
I have recreated the issue using a parameterized INSERT. I set it up to pass the UDT object from the client to the server as a parameter that directly uses an instance of the UDT.
string sInsert = "INSERT INTO TestMyType VALUES (?, ?)";
SqlCommand command = new SqlCommand(sInsert, conn);
SqlParameter parm1 = new SqlParameter("SourceMachine", "This is Machine 01");
SqlParameter parm2 = new SqlParameter("Output", MyNamespace.MyType.Parse( "This is INSERT 01" ) );
parm2.UdtTypeName = "MyType";
command.Parameters.Add(parm1);
command.Parameters.Add(parm2);
int nResult = command.ExecuteNonQuery();
giving
A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in System.Data.dll
Additional information: Could not find method 'Read' for
type 'MyNamespace.MyType' in assembly 'MyType'
SqlBulkCopy should be able to handle SQLCLR UDT's (User-Defined Types) just fine. I have succeeded using both DbDataReader and DataTable methods.
Here is what worked for me:
C# code (I made the "client" a SQLCLR stored procedure)
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 code
-- 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
The Test
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;
I also got this working from a Console App, using both SqlBulkCopy and a parameterized ad hoc query:
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();
}
}
}
}
}
P.S. If sending the data directly to a UDT column, then it needs to be in binary form as that is the only way that SqlBulkCopy transports it, as per the source code.