Accessing CLR UDTs Elsewhere

Okay, most of us write applicationsnot just T-SQL stored procedures and the like. This means I'm going to need to access CLR UDTs in a number of ways from my Windows Forms, ASP.NET, and Web Service applications. Before I wander too far away from the UDT discussion, I really need to discuss how you can leverage your shiny new UDT from "elsewhere"outside of a T-SQL code block.

To illustrate the issues, I created the LoanRequest sample application that's used later on to fire a UDT trigger. It accesses the Products table via a parameter-driven stored procedure (naturally) and permits the user to select one of the "products" by lot and attempt to borrow money to buy it. LoanRequest also executes another stored procedure that accepts a typICurrencyV2 input parameter. I'll show how to "prepare" the Parameter object so that that it's recognized by ADO.NET. Along the way, I show how to reference variables and their public properties instantiated as a UDT.

Referencing a UDT

The following steps walk you through the process of creating this sample application:

1.	To get started, create a Windows Forms application using Visual Studio. Sure, this same approach works with any type of application.
2.	Next, click on the "Show All Files" icon in the Project explorer. This exposes the "References" list.
3.	Right-click on References and click "Add Reference". This exposes the "Add Reference" dialog in Visual Studio (as shown in Figure 13.83). If the typICurrencyV2 DLL is not shown in the "Recent" or "Projects" tab, you'll have to dig for it. Click on the "Browse" tab and drill into the bin directory of the project used to construct your UDT. You're looking for the UDT DLL built by the Visual Studio build process. Once you find it, click OK. Figure 13.83. Browsing for the typICurrencyV2 DLL. By adding this reference, you're simply pointing to a DLL that's been deployed on SQL Server and visible through access to the database where it's in use.
4.	You might want to add an Imports or include (C#) to your project to assist Intellisense in finding the referenced UDT.

Instantiating a UDT Variable

When you declare a program variable, the typICurrencyV2 appears in the list of visible datatypesassuming you've added a reference to the UDT DLL to your project. Yes, you'll have to prefix the reference with the "project" (assembly) name, as shown in Figure 13.84.

Figure 13.84. Declaring a variable as the CLR UDT typICurrencyV2.

If you forget to prefix the assembly name, the auto-correct feature of Visual Basic .NET can fill it in for you (as shown in Figure 13.85).

Figure 13.85. Auto-correct can fix the reference to the UDT.

As you write code to reference the public properties exposed by your UDT, Intellisense kicks in again to let you pick from a list of visible UDT properties and methods (as shown in Figure 13.86).

Figure 13.86. Intellisense exposes the UDT public properties.

IMHO

This Intellisense dialog is the only way to determine the public properties of a CLR UDT, short of using Reflection.

Once you've instantiated the UDT-typed variable, you can fill it from an untyped Object returned from a DataTable by using CType (cast in C#) to coerce the datatype, as shown in Figure 13.87. Once the UDT-typed variable is populated with data, you can pick off the public properties by using normal dot notation.

Figure 13.87. Instantiating a CLR UDT-typed variable and accessing its public properties.

Walking Through the LoanRequest Code

To set up the SqlCommand instance used to execute the AddLoan stored procedure, I need to configure the Parameters collection to accept my UDT value. Since AddLoan expects one of the input parameters to be cast as typICurrencyV2, I'll have to set the Parameter datatype to the UDT type. This is done in two steps, as shown in Figure 13.88. On line 41, I set the SqlParameter datatype to SqlDbType.Udt. On line 42 I set the SqlParameter.UdtTypeName to my UDT type's name. Note that I use the fully-qualified name "dbo.typICurrencyV2", as I did when I declared T-SQL variables. Now I'm ready to set the SqlParameter value in code without having to worry about misunderstood references or type coercion issues.

Figure 13.88. Configuring a SqlParameter to accept a CLR UDT.

It turns out that the AddLoan stored procedure asks for two different values typed as typICurrencyV2. Both the @LoanAmount and the @PaidToDate SqlParameter instances require special UDT handlingor do they? For the @PaidToDate SqlParameter, I simply pass a varchar large enough to hold the string-representation of the UDT value.

Figure 13.89. Configuring a UDT typed parameter to accept a string.

To set up the parameter values, I need to do a bit of object instantiation work, as shown in Figure 13.90. In this code, I instantiate a new variable (typICPaidtoDate) as typICurrencyV2, as I want to use the same currency configuration but 0 as the money value. Note how the public properties can be addressed using dot notation (lines 138 and 139).

Figure 13.90. Preparing values to be applied to the stored procedure parameters.

Once the variables containing the typed values are configured, I can set the stored procedure input parameters, as shown in Figure 13.91. Note that on line 154, I simply use the UDT's own ToString function to return the string format of the typICurrencyV2 value and use it to set the input SqlParameter value.

Figure 13.91. Setting the input parameter values and executing the stored procedure.

Now that I've ventured off the road to better understand the subtleties of UDT management and coding, I'm ready to take that UDT to an even higher hill and throw it off to see how well it works in an accumulator.