Error when ResultSet contains byte[]

[leevva]

My ResultSet contains byte[] and i want to get VARBINARY(MAX):

    internal class ContentSearchResultSet
    {
        [Column("content_link_id")]
        public long Id { get; set; }

        ...

        [Column("raw")]
        public byte[] Raw { get; set; }

        ...
    }

When SP result maps to ResultSet I get this exception:

Result Message:
System.TypeInitializationException : The type initializer for 'CodeOnlyStoredProcedure.RowFactory.HierarchicalTypeRowFactory`1' threw an exception. ----> System.NotSupportedException : Could not find the foreign key property on Byte. Expected property named ContentSearchResultSetId, but was not found.

Result StackTrace:
at CodeOnlyStoredProcedure.RowFactory.HierarchicalTypeRowFactory1..ctor() at CodeOnlyStoredProcedure.RowFactory1.Create(Boolean generateHierarchicals) at CodeOnlyStoredProcedure.StoredProcedure1.CreateFactory[TFactory]() at System.Lazy1.CreateValue() at System.Lazy1.LazyInitValue() at System.Lazy1.get_Value() at CodeOnlyStoredProcedure.StoredProcedure1.Execute(IDbConnection connection, CancellationToken token, Int32 timeout) at CodeOnlyStoredProcedure.StoredProcedure1.Execute(IDbConnection connection, Int32 timeout)

[leevva]

Related https://github.com/abe545/CodeOnlyStoredProcedures/issues/38

[abe545]

@leevva, I published a new version to nuget that includes binary support. Can you give it a try and let me know how it works?

[leevva]

Thank you, i'll try it.

2017-01-03 18:44 GMT+04:00 Abraham Heidebrecht notifications@github.com:

@leevva https://github.com/leevva, I published a new version to nuget https://www.nuget.org/packages/CodeOnlyStoredProcedures/2.4.0-pre01 that includes binary support. Can you give it a try and let me know how it works?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/abe545/CodeOnlyStoredProcedures/issues/94#issuecomment-270129164, or mute the thread https://github.com/notifications/unsubscribe-auth/AOTIRB1F64pWBP_nocof-CRdR_J4_wfgks5rOl7bgaJpZM4LPHDP .

[leevva]

Yes, it works!

[leevva]

When SP returns NULL for VARBINARY column i get exception:

An exception of type 'CodeOnlyStoredProcedure.StoredProcedureColumnException' occurred in CodeOnlyStoredProcedure.dll but was not handled in user code

Additional information: Error setting [Byte[]] Raw. Stored Procedure returns [Int32].

[leevva]

Still get exception in 2.4.0-pre02 when blob is NULL Result Message: CodeOnlyStoredProcedure.StoredProcedureColumnException : Error setting [Byte[]] Raw. Stored Procedure returns [Int32].

at CodeOnlyStoredProcedure.RowFactory.ValueAccessorFactory`1.CreateExpressionToGetValueFromReader(IDataReader reader, IEnumerable`1 xFormers, Type dbColumnType)
   at CodeOnlyStoredProcedure.RowFactory.ComplexTypeRowFactory`1.CreateRowFactory(IDataReader reader, IEnumerable`1 xFormers)
   at CodeOnlyStoredProcedure.RowFactory`1.ParseRows(IDataReader reader, IEnumerable`1 dataTransformers, CancellationToken token)
   at CodeOnlyStoredProcedure.StoredProcedure`1.Execute(IDbConnection connection, CancellationToken token, Int32 timeout)
   at CodeOnlyStoredProcedure.StoredProcedure`1.Execute(IDbConnection connection, Int32 timeout)

[abe545]

Right, I haven't yet addressed it. I'll focus on this one for pre03.

[abe545]

Hmm, I just added a unit test, and this should be working. The error message is stating that SQL Sever is returning an INT column. My guess is that this procedure doesn't ever return a value for the column? Maybe you could add a CAST in the sproc, to see if it fixes it? I doubt the [Convert] attribute will work here, let alone the global convert numeric setting. I will look into adding that.

[leevva]

Changed NULL to CAST(NULL AS VARBINARY(MAX)) in SELECT statement and exception disappeared. But my Raw property set to byte[0]. I think it should be set to null

[abe545]

Hey @leevva, sorry, just got a chance to look at this. I just pushed build 2.4.0-pre03 that should fix this. Would you mind checking it out?

[leevva]

It doesn't work in 2.4.0-pre03. Still get byte[0] instead of null

[abe545]

That is very strange. I've tested it with the below sproc, and it returns null for the column.

CREATE PROCEDURE [dbo].[usp_GetEmptyBinary]
AS
BEGIN
    CREATE TABLE #results ([Data] VARBINARY(MAX) NULL)
    INSERT INTO #results VALUES (NULL)
    SELECT * FROM #results
END

I also tried SELECT CAST(NULL AS VARBINARY(MAX)) [Data], and again, it returned null.

Out of curiosity, what database & .NET version you're using?

[leevva]

It's my mistake! I use Automapper for mapping ResultSet to my Domain Model. And it was converted null to byte[0]. I've checked on previous prereleases and it works on both. I apologize for any embarrassment that I have created.

[abe545]

No worries, glad it is working!