I answered a question on the newsgroups on how *exactly* inheritence works when you use it implement UDTs in SQL Server 2005. Also wanted to record the explanation here….
It doesn't work like you'd expect inheritence to when to use T-SQL, because SQL Server is blissfully unaware of the inheritence relationships (they're not recorded in the system views).
So if the class Tiger inherits from the class Cat. Passing an instance of Tiger to the T-SQL stored procedure FeedTheCat:
create procedure FeedTheCat (@thecat Cat) …. — T-SQL here —
wouldn't work.
— (Msg 206: operand type clash dbo.Tiger is incompatible with dbo.Cat)
declare @t tiger
execute feedthecat @t
— or this (Msg 529: explicit conversion … is not allowed)
declare @c cat
set @c = cast(@t as cat)
execute feedthecat @c
If the Cat class has a public instance method called FeedMe and a public field called pawcount (both are inherited by Tiger), this wouldn't work in T-SQL:
— this wouldn't work
declare @t Tiger
print @t.FeedMe()
print @t.pawcount
— this would
declare @c Cat
print @c.FeedMe()
print @c.pawcount
You could, however, access these fields/methods on Tiger from .NET code, As in:
// works fine
Tiger t = new Tiger();
int paws = t.pawcount;
// so does this
SqlString s = t.FeedMe();
Calling it in .NET code *through SqlCommand.ExecuteReader* (CommandText = "select sometiger.pawcount from zootab") wouldn't work.
2 thoughts on “Using inheritence in SQLCLR UDTs”
I’m so glad I discovered your blog.
I’ve been reading your Sql 2k5 book (Addison Wellsley), and I am fairly interested/involved in ADO.NET 2.0/Sql 2k5 myself. I have been reading your book and will be posting a fantastic review on one of the better books I have read recently.
when I read "Inheritance" and UDT, it immediately sprung an idea in my head that I have already recommended to M$, see what you think of it.
The rules for writing UDTs are overtly complex in my eyes. A standard Sql Server DBA who is not .NET friendly will typically be turned off by all the rules you need to follow to create a UDT of one type or the other. My recommendation is to wrap as many of those rules as possible into abstract base classes, and hence lowering the entry point of learning to be able to author a UDT.
Wonder what you’d think of it :).
And yes, I’m gonna add your RSS to my RSS Bandit, glad I discovered your blog.
Hi Sahil,
I actually like the "must implement" stuff, I remind myself it’s so the SQL engine can do a better job optimizing those types than if they were "any old object".
There are a few rules, an abstract base class might help. It would be more of a "reminder" class, on order of the Visual Studio UDT template code. Let’s see…
SqlUserDefinedType attribute
Parse (overriden)
Overriden methods in Object:
ToString
Equals
GetHashCode
Implement INullable (implementation-specific)
Implement IBinarySerialize (implementation-specific)
Implement IComparable (for client, SQL Server won’t use it)
static variable "Null"
Binary input checking function
Comments are closed.