THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Overloaded Methods/Constructors in UDTs

I spoke at the Dallas .NET User Group meeting last night about the CLR integration in SQL Server 2005. During the Q&A session at the end, I was asked for some more detail about creating overloaded methods and constructors in a User-Defined Types (UDT). Here is some information regarding the topics brought up at that meeting. 

Fact #1: You can create overloaded methods in your UDTs, but keep in mind that...

  • Overloaded methods are not directly usable by T-SQL
  • However, overloaded methods are directly usable within the type or by a .NET application.

For example, the following can certainly be defined with a UDT

public SqlInt32 AddAB(SqlInt32 A, SqlInt32 B) { return new SqlInt32(AB(A.Value, B.Value)) ; }
public Int32 AB(Int32 A, Int32 B) { return A + B; }
public String AB(String A, String B) { return A + B; }

You cannot call the AB method from within T-SQL, but you can call AddAB which in turn calls the first AB method within the type. You can also use the AB method from a .NET application (data client, for example) that also uses the type.

Fact #2: You can create overloaded constructors in your UDTs, but keep in mind that...

  • Constructors with parameters are not directly usable by T-SQL
    • For a reference type (class), T-SQL calls only the parameterless constructor
    • For a value type (struct), T-SQL does not call any constructors
  • However, constructors with parameters are directly usable within the type or by a .NET application.

The following code snippet shows two constructors, the Bytes property, and the required Parse method which is used T-SQL to create a new instance of the type and assign an initial value to the instance (from string data). T-SQL does not implement a new operator and thus, Parse allows for the same functionality. The static Parse method in turn uses one of the overloaded constructors for this struct to create and return the instance.

public struct MyType: INullable, IComparable, IBinarySerialize
{
    internal Boolean _isNull;
    internal Byte[] _bytes;

    public MyType (SqlString s)
    {
        this._bytes= s.GetNonUnicodeBytes();
        this._isNull = false;
    }

    public MyType(SqlBytes b)
    {
        this._bytes= new Byte[b.Value.Length];
        Array.Copy(b.Value, this._bytes, b.Value.Length);
        this._isNull = false;
    }

    public static MyType Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        return new MyType(s);
    }  

    public SqlBytes Bytes
    {
        get { return (new SqlBytes(this._bytes)); }
        set
        {
            if (value.IsNull == false)
            this._bytes = value.Value;
        }
    }
    // Other UDT code not shown for space reasons...
}

The constructor with the SqlString parameter is indirectly used by T-SQL (via the Parse method). And in this case (assuming you trust me about the rest of the code), the constructor with the SqlBytes parameter is not used by T-SQL. The following code shows an example of using this type in T-SQL:

declare @mytype MyType
-- Type's Parse method is called here
set @mytype = CAST(0x000000010000000500000256 as varchar(12))
select @mytype.Bytes
--returns 0x000000010000000500000256 as expected

The Parse method only will accept a SqlString as its sole parameter (or the UDT will not work in SQL Server). And so I explicitly cast the varbinary value to varchar. T-SQL, however, will implicitly cast for you (if possible, of course), and so, in this case, the following code also works:

declare @mytype MyType
-- Type's Parse method is called here
set @mytype = 0x000000010000000500000256
select @mytype.Bytes
--returns 0x000000010000000500000256 as expected

Some creative coding, however, will allow you to create static methods that act as new operators. For example, if we add this method into the type:

    public static MyType NewFromBytes(SqlBytes b)
    {
        if (b.IsNull)
            return Null;
        return new MyType(b);
  }

We could now call this code to create the type instance and not worry about any implicit conversion that might occur:

declare @mytype MyType
-- Type's Parse method is never called
set @mytype = MyType::NewFromBytes(0x000000010000000500000256)
select @mytype.Bytes
--returns 0x000000010000000500000256 as expected

Well, I hope that gives you some insight into UDTs in SQL Server and T-SQL.

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mike from Visual Studio Learning.com said:

Thanks for posting this article I found it very useful.

July 7, 2010 10:13 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement