Field order on #region IBinarySerialize

Question

I created IBinarySerialize region in order to create my CLR for my user defined aggregate. I am trying to replicate my XIRR function in C# just to learn how to work with CLRs. I put Write and Read in the same order but when I tried to build I get Error:

Severity Code Description Project File Line Suppression State
Error CS1503 Argument 1: cannot convert from 'System.Data.SqlTypes.SqlDateTime' to 'bool' CustomAggregates D:\Transfer\CustomSqlAggregates\CustomAggregates\XIRR.cs 255 Active

The error is coming in from the write section.

I can't seem to find what I am missing in this arrangement following some examples that I have seen in examples. Here is the get/set for the struct

[Serializable]
[SqlUserDefinedAggregate(
    Format.Native,
    IsInvariantToDuplicates = false, // Receiving the same value again changes the result
    IsInvariantToNulls = false,      // Receiving a NULL value changes the result
    IsInvariantToOrder = true,      // The order of the values affects the result
    IsNullIfEmpty = true,            // If no values are given the result is null
    MaxByteSize = -1,                // Maximum size of the aggregate instance. -1 represents a value larger than 8000 bytes, up to 2 gigabytes
    Name = "XIRR"             // Name of the aggregate
    )]
public struct XIRR : IBinarySerialize
{

    /// <summary>
    /// Used to store the product
    /// </summary>
    public SqlDouble Result { get; private set; }
    public SqlDouble Cashflow { get; private set; }
    public SqlDateTime CashflowDateTime { get; private set; }
    public bool HasValue { get; private set; }
    public List<CashItem> CashFlows { get; private set;}

    ...

    #region IBinarySerialize
    /// <summary>
    /// Writes the values to the stream in order to be stored
    /// </summary>
    /// <param name="write">The BinaryWriter stream</param>
    public void Write(System.IO.BinaryWriter write)
    {
        write.Write(Result);
        write.Write(Cashflow); //Line - 255
        write.Write(CashflowDateTime);
        write.Write(HasValue);
    }
    /// <summary>
    /// Reads the values from the stream
    /// </summary>
    /// <param name="read">The BinaryReader stream</param>
    public void Read(System.IO.BinaryReader read)
    {
        Result = new SqlDouble(read.ReadDouble());
        Cashflow = new SqlDouble(read.ReadDouble());
        CashflowDateTime = new SqlDateTime(Convert.ToDateTime(read.ReadString()));
        HasValue = read.ReadBoolean();
    }
    #endregion IBinarySerialize
}

Thanks in advance.

Please let me know if you want me to provide further information.

Please show the declaration of CashFlowDateTime with its datatypen also please point out line 255 from D:\Transfer\CustomSqlAggregates\CustomAggregates\XIRR.cs — Scott Chamberlain, Sep 30 '19 at 22:59
By the way, style guides almost unanimously recommend not using regions. They were invented for one specific purpose - hiding designer code in .net 1.0 winforms. — Stephen, Sep 30 '19 at 23:02

Solomon Rutzky · Answer 1 · 2019-09-30T23:50:00.577

0

The problem is most likely a mismatch in your types. What are the exact types of Result, Cashflow, and CashflowDateTime? According to the Read method they are Sql* types. Is that really how they are declared? Or are they declared as Double and DateTime?

At the very least I think you are handling the CashflowDateTime incorrectly in both directions. Assuming that CashflowDateTime is truly a SqlDateTime, then I am guessing that you might need to replace this line in the Write method:

write.Write(CashflowDateTime);

with the following two lines:

write.Write(CashflowDateTime.DayTicks);
write.Write(CashflowDateTime.TimeTicks);

And then, replace this line in the Read method:

CashflowDateTime = new SqlDateTime(Convert.ToDateTime(read.ReadString()));

with the following (which reconstructs the SqlDateTime from the "DayTicks" and "TimeTicks" values):

CashflowDateTime = new SqlDateTime(read.ReadInt32(), read.ReadInt32());

Also:

The #region IBinarySerialize doesn't do anything functional here. Removing that would not affect the code.
For more info on working with SQLCLR, please see: SQLCLR Info

edited Sep 30 '19 at 23:50

answered Sep 30 '19 at 23:42

Solomon Rutzky

46,688
9
128
171

I have IBinarySerialize during the creation of the struct. SQL Server is throwing an error saying it is expecting Serialized in the code. I get the same error for first 3 of them. public struct XIRR : IBinarySerialize { ///
/// Used to store the product ///
public SqlDouble Result { get; private set; } public SqlDouble Cashflow { get; private set; } public SqlDateTime CashflowDateTime { get; private set; } public bool HasValue { get; private set; } public List CashFlows { get; private set;} – WinstonKyu Oct 02 '19 at 22:44
sorry I don't know how to property format in the comment. Thanks for your help in advance. My C# is still very weak and I have been using posted examples online to work on this CLR. – WinstonKyu Oct 02 '19 at 22:48
@WinstonKyu Please add what you have in the first comment into the exist example code in the question, including whatever decorators (e.g. `[Serializable]`, `[SqlUserDefinedAggregate(...)]` )you have just above the `public struct XIRR...` line. Thanks. Also, regarding formatting, when you are typing in a comment, there is a "help" link-button to the right of the comment text area. It will expand to provide some basic info with links to more detailed info. – Solomon Rutzky Oct 03 '19 at 17:36
sorry for the delay. I added the beginning get/set to the code. If I remove IBinarySerialize from my struct, my C# code compiles fine but it errors out on SQL Server when I try to assign it to the aggregate function. – WinstonKyu Oct 09 '19 at 18:02
@WinstonKyu Thanks for adding that info. We still need the decorators (e.g. `[Serializable]`, `[SqlUserDefinedAggregate(...)]` ). Also, why are you using `struct` instead of `class`? Using `struct` I believe requires setting another option somewhere about field order or something like that. – Solomon Rutzky Oct 09 '19 at 19:06
I have been sampling from CLR codes that I found to write up the code and they all use struct. I added the summary up top. – WinstonKyu Oct 09 '19 at 20:53

Field order on #region IBinarySerialize

1 Answers1