Я написал SQL Server CLR User-defined type (UDT) в SQL Server 2012. Я смог получить к нему доступ, хотя SQL-скрипты, и использовал его как локальную переменную, определил ее в таблице и протестировал ее через Visual Studio и SQL Server Management Studio.
У нас есть служба, которая использует SQLBulkCopy в довольно обобщенном виде для сбора файлов, которые помещаются в каталог, а затем вставляют их содержимое в соответствующую таблицу. Когда я добавляю свой UDT в качестве столбца в одной из этих таблиц, я получаю сообщение об ошибке из вызова WriteToServer (DataTable).
Столбец UDT передается как System.String в надежде на то, что в SQL Server будет вызываться метод Parse () UDT, чтобы преобразовать его во внутренний тип. Я также попытался объявить класс UDT в этой клиентской программе и передать данные как тип UDT напрямую.
В любом случае я получаю это сообщение об ошибке (отредактированное, чтобы вынуть мои фирменные имена)
Не удалось найти метод «Чтение» для типа «MyNamespace.MyType» в сборке «MyType»
Я рассмотрел столько похожих вопросов, что могу найти об этом сообщении об ошибке, и они обычно относятся к формату инструкции CREATE. Кроме того, они обычно ссылаются на функции CLR, а не на типы CLR, которые немного отличаются. Это мое:
CREATE TYPE [dbo]. [MyType]
ВНЕШНЕЕ ИМЯ [MyType]. [MyNamespace.MyType]
Я подозреваю, что это может и не быть проблемой, и это связано с тем, как SQLBulkCopy взаимодействует с SQLCLR UDT. Для этой конкретной комбинации трудно найти подробное объяснение.
Редактировать # 1 - это обычная сериализация.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType( Format.UserDefined, MaxByteSize = -1 )]
public struct MyType: INullable, IBinarySerialize
Изменить # 2 - Выдается разрешение на выполнение
GRANT EXECUTE
ON TYPE :: MyType
TO PUBLIC
Изменить №3 - адаптированный код тестирования
CREATE TABLE [dbo].[TestMyType]
(
[SourceMachine] [varchar](32) NULL,
[Output] MyType NULL
)
и обновляется
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;
}
Это дало такое же сообщение об ошибке, которое показано выше.
Редактировать # 4 - Исключить SqlBulkCopy из проблемы
Я воссоздал проблему, используя параметризованный INSERT. Я установил его для передачи объекта UDT от клиента к серверу в качестве параметра, который напрямую использует экземпляр 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();
дающий
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
должен иметь возможность обрабатывать 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
и параметризованный запрос ad hoc:
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
передает его в соответствии с исходным кодом .
Я использовал явное обозначение интерфейса для двух методов в UDT, как это.
void IBinarySerialize.Read( BinaryReader r )
{
}
void IBinarySerialize.Write( BinaryWriter w )
{
}
Но они должны были быть определены следующим образом:
public void Read( BinaryReader r )
{
}
public void Write( BinaryWriter w )
{
}
Разницы было достаточно, чтобы SQL Server не смог определить правильный метод для использования в UDT во время SqlBulkCopy и параметризованный INSERT при передаче полного объекта MyType.
Проблема IBinarySerialize
когда я использовал Visual Studio для добавления подпрограмм, которые реализовали интерфейс IBinarySerialize
. Я щелкнул правой кнопкой мыши по имени интерфейса в верхней части определения struct
и выбрал «Осуществить интерфейс явно». Я должен был выбрать «Интерфейс реализации», чтобы сгенерировать заглушки методов без квалификаторов.