SQLBulkCopy con CLR UDT fornisce "Impossibile trovare il metodo" Leggi "per digitare" MyNamespace.MyType "nell'assembly" MyType ""

sqlbulkcopy sqlclr sql-server sql-server-2012 user-defined-types

Domanda

Ho scritto un CLR SQL Server di tipo definito dall'utente (UDT) in SQL Server 2012. Sono stato in grado di accedervi tramite script di test SQL e l'ho usato come variabile locale, definito in una tabella e testato attraverso Visual Studio e SQL Server Management Studio.

Abbiamo un servizio che utilizza SQLBulkCopy in modo abbastanza generalizzato per raccogliere i file inseriti in una directory, quindi inserirli nella tabella appropriata. Quando aggiungo il mio UDT come colonna in una di queste tabelle, ricevo un errore dall'invocazione WriteToServer (DataTable).

La colonna UDT viene passata come System.String, nella speranza che il metodo Parse () dell'UDT venga chiamato all'interno di SQL Server per convertirlo nel tipo interno. Ho anche provato a dichiarare la classe UDT all'interno di questo programma client e a passare direttamente i dati come tipo UDT.

In entrambi i casi ricevo questo messaggio di errore (modificato per eliminare i miei nomi proprietari)

Impossibile trovare il metodo "Leggi" per digitare "MyNamespace.MyType" nell'assembly "MyType"

Ho esaminato il maggior numero di domande simili che posso trovare su questo messaggio di errore e generalmente fanno riferimento al formato dell'istruzione CREATE. Inoltre, generalmente si riferiscono alle funzioni CLR, non ai tipi CLR, che sono leggermente differenti. Questo è mio:

CREATE TYPE [dbo]. [MyType]
NOME ESTERNO [MyType]. [MyNamespace.MyType]

Sospetto che questo potrebbe non essere il problema, e che, invece, ha a che fare con il modo in cui SQLBulkCopy interagisce con un UDT SQLCLR. Per questa particolare combinazione è difficile trovare spiegazioni approfondite.

Modifica n. 1 - È una serializzazione personalizzata.

[Serializable]  
[Microsoft.SqlServer.Server.SqlUserDefinedType( Format.UserDefined, MaxByteSize = -1 )]  
public struct MyType: INullable, IBinarySerialize  

Modifica n. 2: viene concessa l'autorizzazione all'esecuzione

GRANT EXECUTE 
ON TYPE :: MyType
TO PUBLIC 

Modifica # 3 - codice di test adattato

CREATE TABLE [dbo].[TestMyType]
(
    [SourceMachine]       [varchar](32)  NULL,
    [Output]              MyType NULL
)

e aggiornato da

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

Questo ha dato lo stesso messaggio di errore che è mostrato sopra.

Modifica n. 4: eliminare SqlBulkCopy dal problema
Ho ricreato il problema utilizzando un INSERT parametrizzato. L'ho configurato per passare l'oggetto UDT dal client al server come parametro che utilizza direttamente un'istanza dell'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();

dando

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'

Risposta accettata

SqlBulkCopy dovrebbe essere in grado di gestire gli UDT di SQLCLR (Tipi definiti dall'utente) correttamente. Sono riuscito a utilizzare entrambi i metodi DbDataReader e DataTable .

Ecco cosa ha funzionato per me:

Codice C # (ho reso il "client" una procedura memorizzata 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();
        }
    }
}

Codice 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

Il 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;

Ho anche ottenuto il funzionamento da SqlBulkCopy Console, utilizzando sia SqlBulkCopy sia una query ad hoc parametrizzata:

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 Se si inviano i dati direttamente a una colonna UDT, è necessario che sia in formato binario in quanto è l'unico modo in cui SqlBulkCopy trasporta, come per il codice sorgente .


Risposta popolare

Ho usato una notazione esplicita dell'interfaccia su due metodi nell'UDT, come questa.

void IBinarySerialize.Read( BinaryReader r )
{
}

void IBinarySerialize.Write( BinaryWriter w )
{
}

Ma dovevano essere definiti così:

public void Read( BinaryReader r )
{
}

public void Write( BinaryWriter w )
{
}

La differenza è stata sufficiente per impedire a SQL Server di identificare il metodo corretto da utilizzare nell'UDT durante SqlBulkCopy e di parametrizzare INSERT quando ha superato l'oggetto MyType completo.

Il problema è iniziato quando ho utilizzato Visual Studio per aggiungere le routine di stub che implementavano l'interfaccia IBinarySerialize . Ho fatto clic con il pulsante destro del mouse sul nome dell'interfaccia in cima alla definizione della struct e ho scelto "Implementa l'interfaccia esplicitamente". Avrei dovuto selezionare "Implementa interfaccia" per generare gli stub del metodo senza i qualificatori.



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow