This section discusses another namespace, the Microsoft. SqlServer .Server namespace, The Microsoft sqlServer. Server namespace includes classes, interfaces, and enumerations specific to the .NET Framework. However, many of the System. Data, SqlClient classes are also needed within server-side code as you will see, The following table lists the major classes from the Microsoft. SqlServer. Server namespace and their functionality.
This namespace also includes several attribute classes: SqlProcedureAttribute, SqlFunctionAttribute, SqlUserDefinedAttribute, and SqlTriggerAttribute. These classes are used for deployment of stored procedures, functions, user-defined types, and triggers in SQLServer, When deploying from VISualStudio, it is required that you apply these attributes. When deploying the database objects using SQLstatements, these attributes are not needed but they help, because some properties of these attributes inference the characteristics of the database objects.
You see these classes in action later in this chapter when writing stored procedures and user-defined functions is discussed, but first, the following section looks into creating user-defined types with C#.
User-defined types (UDTs) can be used similarly to normal SQL Server data types to define the type of a column in a table. With older versions of SQL Server, it was already possible to define UDTs, Of course, these UDTs could be based only on SQL types, such as the ZIP type shown in the following code. The stored procedure sp_addtype allows you to create user-defined types. Here the user-defined type ZIP is based on the CHAR data type with a length of 5. NOT NULL specifies that NULL is not allowed with the ZIP data type, By using ZIP as a data type, it is no longer necessary to remember that it should be 5 char long and not null:
EXECsp_addtype ZIP ‘CHAR(5)’, ‘NOT NULL’
With SQL Server 2005 and later, UDTs can be defined with CLR classes, However, this feature is not meant to add object orientation to the database; for example, to create a Person class to have a Person data type, SQL Server is a relational data store, and this is still true with UDTs, You cannot create a class hierarchy of UDTs, and it is not possible to reference fields or properties of a UDTs type with a SELECT
statement, If properties of a person (for example, Firstname or Lastname) must be accessed or a list of Person objects must be sorted (for example, by Firstname or Lastname, it is still better to-define columns for first name or last name inside a Persons table or to use the XML data type, UDTs are meant for very simple data types. Before .NET, it was also possible to create custom data types; for example, the ZIP data type. With UDTs it is not possible to create a class hierarchy, and they are not meant to get complex data types to the database. One requirement of a UOT is that it must be convertible to a string, because the string representation is used to display the value. How the data is stored within SQL Server can be defined: either an automatic mechanism can be used to store the data in a native format, or you can convert the data to a byte stream to define how the data should be stored.
Next, you look at how to create a user-defined type. You create a SqlCoordinate type representing the world coordinates longitude and lattiude for easily defining the location of places, cities, and the like. To create CLR objects with Visual Studio you can use the visual Studio 2008 SQL Server Project (in the category Visual C# Database). Select the Solution Explorer and add a UDT by using the User-Defined Type template, Name the type Sql Coordinate, With the template, the base functionality of a custom type is already defined:
Because this type can also be used directly from client code, it is a good idea to add a namespace, which is not done automatically, The struct SqlCoordinate implements the interface INullable. The interface INullable is required for UDTsbecause database types can also be null. The attribute (SqlUserDefinedType] is used for automatic deployment with Visual Studio for UDTs, The argument Format Native defines the serialization format to be used. Two serialization formats are possible: Format .Nat i ve and Format
. UserDef ined. Forma t .Nat i ve is the simple serialization format where the engine performs
serialization and deserialization of instances. This serialization allows only blittable data types (blittable
data types have the same memory representation in managed and native code). With the Coordinate
class, the data types to serialize are of type int and 0001, which are blittable data types, A string a blittable data type. Using Format User Defined requires the interface IBinarySerialize to be implemented. The IBinarySerialize interface provides custom implementation for user-defined types, Read (I and Wri te (I methods must be implemented for serialization of the data to a BinaryReader and a BinaryWriter.
Blittable data types have the same memory representation in both managed and unmanaged memory, Conversion is not needed with ‘blittable data types, Blittable data types art byte, style, short, ushort, int, uint, long, ulong, and combinations of these data types such as arrays and structs that contain only these data types.
public struct SqlCoordinate : lNullable
private int longitude;
private int latitude;
private bool isNull;
The attribute, [SqlUserDefinedType] allows setting several properties, which are shown in the following table.
To represent the direction of the coordinate, the enumeration orientation is defined:
public enum Orientation
This enumeration can be used only within methods of the struct Coordinate, not as a member field because enumerations are not blittable. Future versions may support enums with the native format in SQLServer, The struct Coordinate specifies some constructors to initialize the longi tude, lati tude, and isNull variables. The variable isNull is set to true if no values are assigned to longitude and lattitude which is the case in the default constructor, A default constructor is needed with UDTs With the worldwide coordination system, longitude and latitude are defined with degrees, minutes, and seconds. Vienna, Austria has the coordinates 48′ 14′ longitude and 16′ 20′ latitude. The symbols ” ” and ” represent degrees, minutes, and seconds, respectively, With the variables longitude and latitude, the longitude and latitude values are stored using seconds, The constructor with seven integer parameters converts degrees, minutes, and seconds to seconds, and sets the longitude and latitude to negative values if the coordinate is based in the South or West:
The lNullable interface defines the property IsNull, which must be implemented to support nullability, The static property Null is used to create an object that represents a null value, In the get accessor a Coordinate object is created, and the is Null field is set to true:
A UDT must be converted from and to a string. For conversion to a string, the ToString () method of the Object class must be overridden, The variables longitude and latitude are converted in the following code for a string representation to show the degrees, minutes, and seconds notation:
The string that is entered from the user is represented in the SqlString parameter of the static method Parse ( ) First, the Parse () method checks if the string represents a null value, in which case the Null property is invoked to return an empty Coordinate object. If the SqlString s does not represent a null value, the text of the string is converted to pass the longitude and latitude values to the Coordinate constructor:
After building theassembly,itcan’bedeployed with SQL Server Configuration of the UDT in SQL Server can eitherbe done with VisualStudio 2008 usirtgtheBuild¢ Deploy Projectmenu or using these SQL commands:
CREATE ASSEMBLY Samplerypes FROM
CREATE TYPE Coo”rdinate EXTERNALn NAME
With EXTERNAL NAME, the name of the assembly as well as thename of the class,including the namespace, must be set, Now, it is possible to create a table called Cities that contains the data type Sql Coordinate, as shown in Figure 30-2.
Using UDTs from Client-side Code
The assembly of the UDT must be referenced to use the UDT from client-side code, Then it can be used like any other type on the client.
Because the assembly containing the UDT`S is used both from the client and from the SQL Server, it is a good idea to put UDT`s in a separate assembly from the other SQL Server extensions such as stored procedures and functions.
In the-sample code, the SELECT statement of the Sql Corrunand object references the columns of the cities table that contains the Location column, which is of type Sql coordinate, Calling the method To String () invokes the To String () method of the Sql Coordinate class to display the coordinate value in a string format:
Of course, it is also possible to cast the returned object from the SqlDataReader to a SqlCoordinate type for using any other implemented methods of the Coordinate type:SqlCoordinate coordinate. (SqlCoordinate)readerI2l;
Running the application produce:’ the following output:
Ulan Bator 47’SS’O’N,106’SS’O’E
With all the great functionality of UDTs, you have to aware important restriction.
before deploying new version of UDT, the existing version must bt dropped.
This is possible only if all columns using tire type moved, Don’t plan on using UDTs for types that you change frequently.
An aggregate is a function that returns a single value based on multiple rows. Examples of built·in aggregates are COUNTA,VGa,nd SUMC. returns the record count of all selected records, AVG returns the average of values for a column of selected rows, and SUMreturns the sum of all values of a column .All built·in aggregates work only with built-in value types.
A simple usage of the built·in aggregate AVG is shown here to return the average unit price of all products from the Adventure Works sample database by passing the ListPrice column to the AVG aggregate in the SELECT statement:
SELECTAVG(LiltPrice) AS ‘average lilt price’
\fhe result from the SELECTgives the average list price of all products:
, average li8t price
The SELECTstatement returns just a single value that represents the average of all LiatPrice column val1J(!S. Aggregates can also work with groups. In the next example, the AVGaggregate is combined with the GROUP By clause to return the average list price of every product line:
SELECTProductLine, AVG(ListPrice) AS ‘average list price’
The average list price is now grouped by the product line:
ProductLine average list
For custom value types, and if you want to do a specific calculation based on a selection of rows, you can
create a user-defined aggregate.
Creating User-Defined Aggregates
To write a user-defined aggregate with CLR code, a simple class with the methods Init ( ), Accumulate (), Merge (), and Terminate () must be implemented, The functionality of these methods is shown in the following table.
The code sample shows how to implement a simple user-defined aggregate to calculate the sum of all rows in every group, For deployment with Visual Studio, the attribute [SqlUserDefinedAggregate is applied to the class SampleSum, As with the user-defined type, with user-defined aggregates the format for storing the aggregate must be defined with a value from the Format enumeration. Again, Format Native is for using automatic serialization with blittable data types.
In the code sample the variable sumis used for accumulation of all values of a group. In the Init ( ) method, the variable sum is initialized for every new group to accumulate. The method Accumulate ( ), which is invoked for every value, adds the value of the parameter to the sun: variable. With the Merge ( ) method, one aggregated group is added to the current group. Finally, the method Terminate () returns the result of a group:
You can ust the Aggregate temple from Visual Studio to the core codeb for building the user defined aggregate, The template from Visual Studio creates a struct that uses the Sql String type as a parameter and mum type with the Accumulate and Terminate methods, You Can change the type to a type that represents the requirement of your aggregate, In the example, the SqlInt32 type isused.
Using User-DefIned Aggregates
The user-defined aggregate can be deployed either with Visual Studio or with the CREATE AGGREGATE statement, Following the CREATE AGGREGATE is the name of the aggregate, the parameter (ivalueint), and the return type. EXTERNAL NAME requires the name of the assembly and the.NET type including the namespace.
CREATE AGGREGATE [Samp1eSum] (iva1ue intI RETURNS [int] EXTERNAL NAME (Demo] .[Samp1eS]
After the user-defined aggregate has been installed, it can be used as shown in the following SELECT statement, where the number of ordered products is..returned by joining the Product and PurchaseOrderDetail tables, For the user-defined aggregate, the OrderQty column of the Order PurchaseOrderDetail table is defined as an argument:
SELECT Purchasing.PurchaseOrderDet;i1.ProductID AS Id,
Production.Product.Name AS Product,
dbo.Samp1eSum(~rchasing.PurchaseOrderDetai1.0rderOty) AS Sum
FROM Production. Product INNER JOIN
Purchasing.PurchaseOrderOetai1.ProductID = Production.Product.ProductID
GROUP BY Purchasing.PurchaseOrderOetail.ProductID. Production.Product.Name
ORDER BY Id
An extract of the returned result that shows the number of orders for products by using the aggregate function SampleSum is presented here:
Id Product Sum
1 Adjustable Race 154
2 Bearing Ball 150
4 Headset Ball Bearings 153
317 LL Crankarm 44000
318 ML CrankaI1ll 44000
319 HL Crankarm 71500
320 Chainring Bolts 375
321 Chainring Nut 375
322 Chainring 7440