SQL Server如何存储/传输十进制流

Question

我在MS-TDS协议上工作，我想要做的是读/写TDS流，并从它获取/设置一个System.Data.DataTable。除了decimal varchar(max) varbinary(max)列之外，我已经让所有的C#类型都正常工作了

在C#中，我得到了12345678912345678912.3456789m和1234567891234567891.23456789m的以下结果

var bits = decimal.GetBits(12345678912345678912.3456789m)
using (var ms = new System.IO.MemoryStream())
{
    foreach (var b in bits)
        ms.Write(BitConverter.GetBytes(b), 0, 4);
    var decimalBytes = ms.ToArray();
}

 10 15 5F 04 7C 9F B1 E3 F2 FD 1E 66 00 00 00 07 00
 10 15 5F 04 7C 9F B1 E3 F2 FD 1E 66 00 00 00 08 00

这些对我来说完全有意义，最后一个字节是十进制大小(位置)，它是7和8，值部分是相同的。但在sql中，它们看起来完全不同：

SELECT CAST(CAST(12345678912345678912.3456789 AS decimal(38,15)) AS varbinary)
SELECT CAST(CAST(1234567891234567891.23456789 AS decimal(38,15)) AS varbinary)
results:
0x260F 0001 0075 AA3F 0AF2 2A3A DB18 0560 B060 0200
0x260F 0001 80D8 5D06 01E5 9D52 7C82 0070 DE3C 0000

在TDS流DataType=[0x6A,0x11,0x26,0x0F] decimal(38,15)中，sql server返回以下内容：

0x81, // token "COLMETADATA"
0x03, 0x00, 0x00, 0x00, // 3 columns

0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0xa7, 0xff, 0xff, 0x09, 0x04, 0xd0, 0x00, 0x34, // 0xA7=BIGVARCHRTYPE  0xffff=MAX, 
0x0e, 0x56, 0x00, 0x41, 0x00, 0x52, 0x00, 0x43, 0x00, 0x48, 0x00, 0x41, 0x00, 0x52, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x58, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "VARCHARMAX_Col"

0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0x6a, 0x11, 0x26, 0x0f, // 0x6A=DECIMAL  0x11=Size, precision = (38,15)
0x0b, 0x44, 0x00, 0x45, 0x00, 0x43, 0x00, 0x49, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x4c, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "DECIMAL_Col"

0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0xa5, 0xff, 0xff, // 0xA5=BIGVARBINTYPE 0xffff=MAX
0x0d, 0x56, 0x00, 0x41, 0x00, 0x52, 0x00, 0x42, 0x00, 0x49, 0x00, 0x4e, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x58, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "VARBINMAX_Col"

0xd1, // token "ROW"
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x41, 0x41, 0x41, 0x41, 0x42, 0x00, 0x00, 0x00, 0x00, // varchar(max) value, how to read this ?
0x11, 0x01, 0x00, 0x92, 0xa8, 0x7c, 0x7e, 0xe4, 0x25, 0x1a, 0x0e, 0xab, 0x6b, 0x4d, 0x82, 0x84, 0x04, 0x00, // decimal value, how to read this ?
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x61, 0x61, 0x61, 0x61, 0x62, 0x00, 0x00, 0x00, 0x00, // varbinary(max) value, how to read this ?

0xd1, // token "ROW"
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x41, 0x41, 0x41, 0x41, 0x43, 0x00, 0x00, 0x00, 0x00,
0x11, 0x01, 0x00, 0x75, 0xaa, 0x3f, 0xa6, 0x63, 0x9d, 0x02, 0x1b, 0x91, 0x57, 0xa1, 0xa6, 0x73, 0x00, 0x00,
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x61, 0x61, 0x61, 0x61, 0x63, 0x00, 0x00, 0x00, 0x00,

0xd2,  // token "NBCROW"
0x05,  // NullBitmap 00000101:  1st and 3rd columns are null
0x11, 0x01, 0x50, 0x24, 0xc2, 0x2a, 0xf2, 0x77, 0xd5, 0x97, 0x0f, 0x03, 0xee, 0xf5, 0x02, 0x00, 0x00, 0x00,

第一行的十进制值为

0x11, 0x01, 0x00, 0x92, 0xa8, 0x7c, 0x7e, 0xe4, 0x25, 0x1a, 0x0e, 0xab, 0x6b, 0x4d, 0x82, 0x84, 0x04, 0x00, 

0x11应该是长度，0x01表示正值(来自这篇文章的How does SQL Server store decimal type values internally?)问题:如何将这些字节解析为十进制值？我应该如何将varchar(max) varbinary(max) value写入TDS ROW TokenStream？任何帮助我们都将不胜感激

Answer 1

我想通了。不同之处在于TDS stream使用第一个字节作为符号并使用固定小数点，而decimal.GetBits使用最后一个字节中较少的位作为符号和小数点

// write decimal to TDS stream
static byte[] DecimalBytes(decimal dec, int precision = 15)
{
    var round = decimal.Round(dec, precision);
    var valueToWrite = round;
    var sign = round < 0 ? (byte)0x00 : (byte)0x01;
    // get the string
    var str = round.ToString();
    // string without Decimal point
    var numbers = str.Replace(".", string.Empty);
    var dotIdx = str.IndexOf('.');
    if (dotIdx > 0)
    {
        // there must be {precision} digits on the right side of "."
        var padding = precision - (numbers.Length - dotIdx);
        // padding numbers with '0' to precision length
        numbers = numbers.PadRight(numbers.Length + padding, '0');
    }
    else
    {
        numbers = numbers.PadRight(numbers.Length + precision, '0');
    }
    if (!decimal.TryParse(numbers, out valueToWrite))
    {
        throw new ArgumentOutOfRangeException($"Invalid decimal value for Database Type decimal(38,{precision})");
    }
    using (var ms = new System.IO.MemoryStream())
    {
        var bits = decimal.GetBits(valueToWrite);
        ms.WriteByte(0x0D); // length=13
        ms.WriteByte(sign);
        ms.Write(BitConverter.GetBytes(bits[0]), 0, 4);
        ms.Write(BitConverter.GetBytes(bits[1]), 0, 4);
        ms.Write(BitConverter.GetBytes(bits[2]), 0, 4);
        return ms.ToArray();
    }
}