Search code examples
c#.netsql-serversqlclruser-defined-types

ToString() of SQL CLR UDT gives a strange result


I am new to CLR UDT and I think for the most part I am okay, but I am have a really annoying issue currently where the rows do not display the correct values. I have a gender data type and I input "Male" or "Female", but the values in the results window show as "0x044D616C65" and "0x0646656D616C65" respectively. Here is my UDT:

[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 512)]
public struct Gender : INullable, IBinarySerialize
{
    private bool m_Null;
    private SqlString genderName;

    public override string ToString()
    {
        return Convert.ToString(genderName);
    }

    public bool IsNull
    {
        get { return m_Null; }
    }

    public static Gender Null
    {
        get
        {
            Gender h = new Gender();
            h.m_Null = true;
            return h;
        }
    }

    public static Gender Parse(SqlString s)
    {
        if (s.IsNull)
        {
            return Null;
        }
        Gender u = new Gender();

        bool isValid = false;
        string str = s.ToString().Trim();
        if (str.StartsWith("M") || str.StartsWith("F"))
        {
            isValid = true;
        }

        if (isValid)
        {
            u.genderName = new SqlString(str);
        }
        else
        {
            throw new SqlTypeException("Gender not valid");
        }

        return u;
    }

    public void Write(BinaryWriter writer)
    {
        writer.Write(genderName.ToString());
    }

    public void Read(BinaryReader reader)
    {
        genderName = new SqlString(reader.ReadString());
    }
}

EDIT

Images to clarify:

Table Design

SQL Results View


Solution

  • I Got the answer in this link. Dan Guzman wrote:

    SQL Server returns the serialized binary value for SQLCLR types by default. If you want the displayable value for an ad-hoc query tool like SSMS, you'll need to use the ToString method on the column:

     SELECT *, MySqlClrUdt.ToString() FROM Customers;