CODEWITHSUNDEEP
c#.netsql-serversqlclruser-defined-types
Matt
Matt

Reputation: 6953

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

Upvotes: 2

Views: 641

Answers (2)

Matt
Matt

Reputation: 6953

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;

Upvotes: 1

Gonzalo.-
Gonzalo.-

Reputation: 12672

Assuming you're using Visual Studio 2010. The problem is that you configured VS to see the results as "hexadecimal". You can change this doing right click in the result view (that little window when you see the variable value), and uncheck hexadecimal view You probably activate it whit a shortcut, and you didn't notice

you can see in the picture what I'm telling you:

enter image description here

(my VS is in spanish, in english it would be something like "Hexadecimal View"

Also you can check this option in the tool bar (only while you're debugging)

enter image description here

Upvotes: 0

Related Questions