Additions to UDTs

There are plenty of additions you can make to UDTs to allow them to integrate with SQL Server and applications. Details of these are beyond the scope of this book, but it's worth pointing out areas that you might want to look at to enhance your usage of UDTs.

The first area is the addition of methods to provide extra functionality. For example, the latitude could have ToDMS and FromDMS methods to output and accept values stored in degrees, minutes, and seconds. This would be useful when creating client applications, because it would allow you to display and accept data in different formats. The downloadable samples implement these two methods so that you can see how they would work.

The second area is that of comparison of UDT instances. While native serialization allows automatic comparison, you can override this, if necessary, by implementing the Equals method and overriding the == and != operators. Within these, you can explicitly define how one instance of a UDT compares to another.

A more interesting issue is creating user-defined aggregates that extend the existing aggregates supported by SQL Server. For example, consider the latitude type; what does the MAX aggregate mean on a value that represents a latitude? MAX would, in fact, compare using internal values, so the North Pole would be the maximum value, as it has the highest latitude (90 degrees). What if you wanted to have the maximum per hemisphere? Aggregates like MAX and MIN make even less sense when applied to longitudes, where -180 is the minimum and 180 the maximum, with 0 being Greenwich in England. You could create aggregates called FurthestNorth and FurthestSouth, or FurthestEast and FurthestWest, which while providing similar results to MAX and MIN, make code much more readable. You could also create aggregates for TopLeft, TopRight, BottomLeft, and BottomRight, to provide bounds for a range of longitude and latitudes.

Creating a UDT in Visual Studio 2005

To create an SQL type in Visual Studio 2005, you select the SQL Server Project from the Database project types, as shown in Figure 5.6, where the project name is GeoAssemblythis will be the assembly name within SQL Server when the project is deployed. You can then add items to your project, and for a UDT, you pick User-Defined Type object, which creates a template for your UDT.

Figure 5.6. Creating a SQL Server project

You can have multiple types within the same project and include other SQL Server objects such as stored procedures, aggregates, and functions. Compilation behaves in exactly the same way as with other projects.

Deploying the UDT to SQL Server 2005

Before deploying to SQL Server, you must make sure that the SQL Server CLR is enabled in the database, which it isn't by default. You can do this with the SQL Server Surface Area Configuration Tool or via the following SQL statements:

sp_configure 'clr enabled', '1' GO reconfigure GO

To deploy your types to SQL Server, you simply select the Deploy option from the Build menu. This will compile your project, create a standard assembly, and deploy this into SQL Server. You should note that the assembly is stored inside of SQL Server, so you can deploy the MDF and LDF files to target servers without having to deploy any .NET assemblies.

If you make changes to your code, you will have to redeploy the assembly, which may not be possible if there are types in use. For example, if you have created a table with UDT columns, then the existing assembly cannot be dropped, because the type is in use. If you are unsure about which assemblies you have in a database, you can use the Object Explorer, as shown in Figure 5.7, which shows the GeoMapping assembly.

Figure 5.7. The SQL Server 2005 Object Explorer

The context menu for the assembly will allow you to view the dependencies, as seen in Figure 5.8, which shows that the GeoMapping assembly is used in the tripDetails table.

Figure 5.8. Viewing the dependencies for an assembly

Assemblies can be added to SQL Server without the use of Visual Studio by way of new SQL statements. You should consult the SQL Server 2005 documentation for details about this.

Creating Tables with UDT Columns

Once the assembly is deployed, you can use the UDTs as you would any other types, as shown in Figure 5.9, where the UDTs appear alongside the standard column types.

Figure 5.9. Adding a UDT column to a table

You can also use the UDTs in SQL to create tables, as shown in Listing 5.26 where Longitude and Latitude are used in the same way as other types.

Listing 5.26. Creating a Table with UDT Columns

CREATE TABLE [dbo].[TripDetails](       [TripDetailID] [int] IDENTITY(1,1) NOT NULL,       [TripID] [int] NOT NULL,       [WaypointID] [smallint] NOT NULL,       [Description] [nvarchar](255)       COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,       [Lng] [dbo].[Longitude] NOT NULL,       [Lat] [dbo].[Latitude] NOT NULL ) ON [PRIMARY]

This is actually an important pointUDTs are types, they aren't just a bolt-on extension. They are first-class types within SQL Server, and they can be used anywhere a standard type is used.

Inserting Data into UDT Columns

Inserting data into a UDT column depends upon the Parse method of the UDT, because it is the Parse method that dictates the format accepted for the column insertion. In the latitude type shown earlier, the Parse method accepts a string containing the decimal representation of the latitude and would be used like this:

INSERT INTO TripDetails(TripID, WaypointID, Description, Lat, Lng) VALUES(1, 1, 'Bellingham Harbour', '48.7566', '-122.4946')

You can see that there is nothing unusual about this statementthe longitude and latitude values are as expected. If the Parse method accepted, for example, a SqlDecimal type, the values could be inserted without quotes. If data was expected to be inserted in a different format, such as DDMMSSH, then the insert statement might be:

INSERT INTO TripDetails(TripID, WaypointID, Description, Lat, Lng) VALUES(1, 1, 'Bellingham Harbour', '484523N', '1222940W')

You can implement multiple Parse methods to handle different type formats.

Accessing UDT Columns

Accessing UDT columns depends upon the client application knowing what the data type is. For example, in SQL Server Management Studio (SSMS), you cannot simply perform the following SQL query and get the results you expect:

SELECT * FROM TripDetails

The reason is that SSMS is a client application, and it doesn't know anything about the Longitude and Latitude typesthese are embedded within the database itself. You can run the above query, but the UDT columns will come back in their binary format. To see the explicit values,. you have to do one of two things The first is to explicitly name the columns and use the ToString method:

SELECT Description Lng.ToString(), Lat.ToString() FROM TripDetails

This isn't always convenient, so you can add the assembly to the Global Assembly Cache (GAC), making it (and therefore types defined within it) globally accessible. Note that to add assemblies to the GAC they must be strongly signed, and this should be done before the assembly is added to SQL Server. You cannot deploy an assembly, strongly name it, and then add it to the GAC. You must strongly name it first, before deployment, because the assembly signatures differ otherwise.

The same rule applies to other client applications such as ASP.NET. The assembly must either be in the GAC or referenced from within your application project (i.e., in the bin directory). Once this is done, you can access the column as you would any other column. For example, consider Listing 5.27, which shows a SqlDataReader iterating over the tripDetails:

Listing 5.27. Using a UDT in Client Code

protected void Page_Load(object sender, EventArgs e) { using (SqlConnection conn = new   SqlConnection(ConfigurationManager.ConnectionStrings[   "IllustratedASPNET20ConnectionString"].ConnectionString)) {   SqlCommand cmd = new SqlCommand("Select * from TripDetails", conn);   conn.Open();   SqlDataReader rdr =                 cmd.ExecuteReader(CommandBehavior.CloseConnection);   Latitude lat;   while (rdr.Read())   {     lat = (Latitude)rdr["Lat"];     Response.Write(lat.ToString() + "<br />");   }   rdr.Close(); }

You can see that the column is accessed like non-UDT columns, and the ToString method is called to convert the internal value to text. You can use UDT columns directly in databound controls, as shown in Figure 5.10, which shows a grid bound to some details containing waypoints for a sailing trip. These are integrated with the Microsoft Virtual Earth control to allow the user to select the waypoint and the map to display the appropriate image. You can now see how you can build some really cool mapping applications using SQL Server 2005 as a store for custom mapping types.

Figure 5.10. Databinding with UDT columns

If you wanted to insert data, perhaps using a stored procedure and parameters, then the parameter type should be defined as SqlDbType.Udt and the UdtTtypeName property set to the type of the UDT, as shown in Listing 5.28.

Listing 5.28. Using a UDT as a Parameter

SqlParameter param = new SqlParameter("@Lat", SqlDbType.Udt); param.UdtTypeName = "dbo.Latitude"; param.Value = new Latitude(48.7566); cmd.Parameters.Add(param);

This simply tells ADO.NET that the parameter is of a custom type, and defines what that type is.