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:
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;