如何正确序列化可排序的SQLCLR用户定义类型？

I am building a custom User-Defined Type in C# for use with SQL CLR. See this reference.

我在C＃中构建自定义用户定义类型以与SQL CLR一起使用。请参阅此参考。

The underlying data can be represented in 4 bytes using a regular signed 32-bit integer. Here is the implementation:

底层数据可以使用常规带符号的32位整数以4个字节表示。这是实施：

[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true,
                    IsFixedLength = true, MaxByteSize = 4)]
public struct MyUDT : INullable, IBinarySerialize
{
    private int _value;

    public int Value
    {
        get { return _value; }
    }

    public MyUDT(int value)
        : this()
    {
        _value = value;
    }

    public override string ToString()
    {
        return _value.ToString(CultureInfo.InvariantCulture);
    }

    [SqlMethod(OnNullCall = false)]
    public static MyUDT Parse(SqlString s)
    {
        int i = int.Parse(s.Value);
        return new MyUDT(i);
    }

    public bool IsNull { get; private set; }
    public static MyUDT Null
    {
        get { return new MyUDT { IsNull = true }; }
    }

    public void Read(BinaryReader r)
    {
        var bytes = r.ReadBytes(4);

        if (BitConverter.IsLittleEndian)
            Array.Reverse(bytes);

        int i = BitConverter.ToInt32(bytes, 0);
        _value = i ^ unchecked((int) 0x80000000);
    }

    public void Write(BinaryWriter w)
    {
        int i = _value ^ unchecked((int) 0x80000000);
        var bytes = BitConverter.GetBytes(i);

        if (BitConverter.IsLittleEndian)
            Array.Reverse(bytes);

        w.Write(bytes);
    }
}

Of course, I will add other methods that will make this more useful, but this is the minimum that will compile and publish.

当然，我将添加其他方法，使其更有用，但这是编译和发布的最小值。

I need to make sure that when this type is used for comparisons, such as when it is a column in a table, that it is sorted correctly. There will be both positive and negative values.

我需要确保当这种类型用于比较时，例如当它是表中的列时，它是正确排序的。将有正面和负面的价值观。

As you can see, I had to reverse the byte ordering, because the endianness is backwards. I also had to adjust the bytes so that zero is represented as 0x80000000, and -1 becomes 0x7FFFFFFF. Otherwise the values don't get sorted properly.

正如您所看到的，我不得不反转字节顺序，因为字节顺序是向后的。我还必须调整字节，以便将零表示为0x80000000，并且-1变为0x7FFFFFFF。否则，值无法正确排序。

However, this is not how SQL Server integers normally work. For example:

但是，这不是SQL Server整数正常工作的方式。例如：

SELECT convert(varbinary, -16)  -- 0xFFFFFFF0
SELECT convert(varbinary, 16)   -- 0x00000010

Yet these sort just fine! So I can only conclude that there are other sorting types than just raw order of bytes. But I can't find any way to control that in my UDT. Is it possible?

然而这些都很好！所以我只能得出结论，除了原始的字节顺序之外还有其他排序类型。但我找不到任何方法来控制我的UDT。可能吗？

All of the examples I've found show something like this:

我发现的所有例子都显示如下：

public void Read(BinaryReader r)
{
    _value = r.ReadInt32();
}

public void Write(BinaryWriter w)
{
    w.Write(_value);
}

But that writes in a completely different format. It's like SQL Server's format, but the endianness is reversed (1 is 0x01000000 and -16 is 0xF0FFFFFF). So things get sorted completely wrong. So are the examples wrong? Is there a bug somewhere?

但是它以完全不同的格式写入。它类似于SQL Server的格式，但字节顺序相反（1为0x01000000，-16为0xF0FFFFFF）。所以事情排序完全错误。那些例子错了吗？某处有错误吗？

I have also considered using Format.Native and omitting the custom serialization. That sort of works, but I end up with 5 byte values, where zero looks like 0x8000000000 and -1 looks like 0x7FFFFFFF00. It is automatically converting just like I did, but it stores an extra byte for the IsNull boolean field. It won't accept having that marked as [NonSerialized], and it won't let me remove that. Nullability is a requirement, so it has to be there.

我还考虑过使用Format.Native并省略自定义序列化。这样的工作，但我最终得到5字节值，其中零看起来像0x8000000000和-1看起来像0x7FFFFFFF00。它像我一样自动转换，但它为IsNull布尔字段存储了一个额外的字节。它不会接受标记为[NonSerialized]，并且不会让我删除它。可空性是一项要求，因此它必须存在。

Surely there is a simpler way than having to do bit manipulation?

当然有一种比进行位操作更简单的方法吗？

Another concern is that my type isn't directly convertible to an int within SQL. Although, I'm not sure if I need that or not.

另一个问题是我的类型不能直接转换为SQL中的int。虽然，我不确定我是否需要。

See also, a related (but different) question on DBA.SE.

另请参阅DBA.SE相关（但不同）的问题。

1 个解决方案

#1

I suspect you want to implement IComparable and set IsByteOrdered to false. This would eliminate the need to play games by converting zero to 0x80000000 and -1 to 07FFFFFFF.

我怀疑你想要实现IComparable并将IsByteOrdered设置为false。这将通过将零转换为0x80000000和-1至07FFFFFFF来消除玩游戏的需要。

I'll finish off by saying, I'm not even a beginner at this stuff. Never done it myself, but I glanced over the documentation here: http://msdn.microsoft.com/en-us/library/ms131082.aspx

我最后说，我甚至不是这个东西的初学者。从来没有自己做过，但我在这里浏览文档：http：//msdn.microsoft.com/en-us/library/ms131082.aspx

#1