My SQL CLR UDTs are not displayed correctly in the database, or are they?

Question

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

Answer 1

Hi Ieyasu,

I am having the same problem now in 2012... As it has been 5 years, I am guessing you know the answer by now. Would you be so kind as to tell me what is causing this, so that I can fix it?

Thanks

Answer 2

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;

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

Thank you very much Dan. This is very helpful. Do you know if there is a way to override the default behaviour though?

Answer 4

No, SQL Server always returns data in the native format as requested the query.  For example, an integer column is actually a 4 byte binary value and a datetime column is an 8 byte binary value.  Applications like SSMS convert the native value to a string for display purposes. 

With a SQLCLR type, a generic query tool like SSMS doesn't know how you might want to display the data so it just converts the native binary value to a hex string.  The ToString method is intended to be used when a more user-friendly value is desired, either as part of the query or invoked in a client application that references the UDT.  Ieyasu's ToString implementation returns the territory abbreviation but it could just as well return the proper name. 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/