My SQL CLR UDTs Are Not Displayed Correctly In The Database, Or Are They?

Hi,

I'm new to SQL CLR programming and have recently implemented a few simple test UDTs. Typically I provide a property called Value and, if applicable, a method to display that value in a different format. For example, I havea UDT representing Australian states: the Value property returns the acronym (e.g. VIC) and the ProperName() method returns the full name (e.g. Victoria).


However, when I do a regular query, say SELECT * FROM Customers, the values in the column in which I am using the UDT are displayed as hex (I believe), for example '0x008064'.


Is this behaviour the result of a faulty implementation on my part or is it just the way SQL Server displays a non-native data type?

Here's the essential implementation code:

[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 58, IsFixedLength = false)]
public struct udt_au_stateId : INullable, IBinarySerialize
{
private bool _isNull;
private AU_StateId _state;
private enum AU_StateId { ACT, NSW, NT, QLD, SA, TAS, VIC, WA }

public void Read(BinaryReader r)
{
SetStateId(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(_state.ToString());
}

public SqlString Value
{
get { return _state.ToString(); }
set
{
if (!SetStateId(value.ToString()))
throw new ArgumentException("'" + value.ToString() + "'" + " is not a valid Australian state or territory.");
}
}

public SqlString ProperName()
{
return GetProperName();
}

public override string ToString()
{
if (_isNull)
return "<NULL>";
else
return _state.ToString();
}

public bool IsNull
{
get { return _isNull; }
}

public static udt_au_stateId Null
{
get
{
udt_au_stateId u = new udt_au_stateId();
u._isNull = true;
return u;
}
}

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

/* some implementation details omitted */



Best regards,
Ieyasu