OVERVIEW OF USER-DEFINED TYPES SQL Server. In any

OVERVIEW OF USER-DEFINED TYPES SQL Server. In any case, even though user-defined types are scalar types, there is no way to use CREATERULEand sp_bindruleto constrain them. Overview of User-Defined Types A user-defined type is implemented by a public CLR class that meets a set of requirements that are discussed later in this chapter. There is no way to implement a user-defined type using T-SQL. This class must implement the three key features of a user-defined type: a string representation, a binary representation, and a null representation. The string representation is the form of the user-defined type that a user will see; for example, the string representation of a Dateis 2/5/1988 . The binary representation is the form of the user-defined type that SQL Server will use to persist its value on disk. And lastly, a scalar in SQL Server must be able to have a null value, so the user-defined type must implement this. The class that implements the user-defined type must be in an assembly that has been cataloged, as described in Chapter 2, into any database that uses it. The CREATE TYPE command is used to add the user-defined type in the class to the scalar type system for a database, much as CREATE PROCEDURE and CREATE FUNCTION are used to add procedures and functions based on CLR methods to a database. User-defined types are similar to DATETIME types in that they are inserted into a column as a string but stored as a stream of bytes. That is, they are entered using their string representation but stored using their binary representation. The following example shows a date being stored as a stream of bytes. CREATE TABLE Dates ( Date DATETIME ) INSERT INTO Dates VALUES ( 10/1/1900 ) SELECT Date, CONVERT(VARBINARY(20), Date) FROM Dates GO 10/1/1900 12:00:00 AM 0×000000000000000000000000000000000000000000000000011100000000 This gives us the best of both worlds. We can write and read dates in a familiar notation and conveniently write predicates like (WHEN 1/1/1997 < InstallDate). User-defined types can also support this convenient comparison feature if their binary representation is appropriately designed, though they are not required to. Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

USER-DEFINED TYPES AND AGGREGATES But not all triples

USER-DEFINED TYPES AND AGGREGATES But not all triples are vectors. Consider this set of data: 10 13 1966, 6 15 1915, 7 4 1776. Writing them as follows might make it clearer that these are dates: 10/13/1966, 6/15/1915, 7/4/1776. Again, from experience, you would know that storing a date in three columns would make it difficult to work with. The triple, in this case, would use a single column. The reason you would want to use a single column is that storing a date in three columns makes no more sense or utility than storing the number 123 in three single-digit columns. The reason you can do this is that the SQL-92 specification realized how important it was to have a scalar type for a date, so it included DATETIME, which SQL Server implements. There are other multifield values that are scalars. Angles, such as latitude and longitude, are written as having four fields: degrees, minutes, seconds, and direction. Geographic Information Systems (GIS) often have values that look like 34 6’12″N, 61 35′ 19″W. Unfortunately, the SQL Server scalar type system is missing the Latitudeand Longitudedata types. User- defined types allow you to make your own Latitude and Longitude types and use them in the same, convenient way you use the DATETIME type. Scalars often have type-specific functions associated with them. For example, there are numerous functions, such as ASIN or CEILING, that are associated with numerical scalars. A user-defined type may also implement functions specifically associated with it. In some cases it may be useful to implement a user-defined type that is based on a built-in scalar type so that you may encapsulate some functionality with it. Alias Types You may be already familiar with the sp_addtype command, which SQL Server has had since well before SQL Server 2005. sp_addtype, along with CREATERULE and sp_bindrule, is used to create an alias type. Alias types do not have the same capabilities as user-defined types and cannot be used in place of them. For example, there is no way to create an alias type for Latitude. An alias type is used to constrain the value in a scalar type such as INT. The same constraints can be obtained by using CHECK constraints, and that is the recommended practice today. Information on CHECK constraints and alias types can be found in Books Online (BOL), which is included with SQL Server. Alias types are deprecated, and the ability to use them to enforce constraints may be removed from a future version of

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

5 User-Defined Types and Aggregates SQL SERVER PROVIDES

5 User-Defined Types and Aggregates SQL SERVER PROVIDES types for scalars like FLOAT and CHAR. These can be represented by a single value such as a number or a string. Some scalars, such as a date, require more than one value to be represented. A date has a month, day, and year value in its representation. SQL Server provides the DATETIMEscalar type for dates. There are other scalars that require more than one value to represent them for which SQL server does not provide a scalar type for example, a dimension, like 12 in, which has a value and a unit. SQL Server 2005 allows a CLR language to be used to add scalar types and aggregates to SQL Server that can be used in the same way as any built-in scalar type or aggregate is. Why Do We Need User-Defined Types? User-defined types in SQL Server 2005 are used to extend its scalar type system. A column in a table is meant to hold a scalar. If you were given a sample set of data that looked like 12 4 9, 13 5 2, 9 14 11, you would say, Those are triples of numbers, so each must be a vector, and I will need three columns to represent each one. You would know from experience and the first normal form that trying to squeeze each triple into a single column would be a false economy.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

WHERE ARE WE? Where Are We? This chapter

WHERE ARE WE? Where Are We? This chapter looked at the SqlServer managed provider in depth. However, in the previous chapter, we stated that T-SQL is almost always best for data-centric code. When, then, is the best time to use the in-process provider? These cases include when it s appropriate to mix .NET-specific logic with data access code. Another case might be where structured exception handling is desired. In addition, because the SQL Server programming model is similar enough (though not identical) between Sql Client and SqlServer providers, some existing .NET code might be moved into the server for locality-of-reference benefits.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

THE IN-PROCESS DATA PROVIDER cmd.CommandText = insert into

THE IN-PROCESS DATA PROVIDER cmd.CommandText = insert into obsoletejobs select * from deleted ; // move the rows int rowsAffected = (int)cmd.ExecuteNonQuery(); } } Because individual SQL statements (including triggers) are transactional, triggers in T-SQL use the ROLLBACKstatement if the logic in the trigger determines that a business rule has been violated, for instance. Although inside a trigger the context transaction is visible, rolling it back produces an error just as in other CLR procedural code. The way to roll back the statement inside a trigger is to issue a ROLLBACK statement as Command.Text through Command.ExecuteNonQuery. An example of this follows. public static void AddToObsolete() { // get the trigger context so you can tell what is // going on SqlTriggerContext tc = SqlContext.GetTriggerContext(); // this trigger is for deletes if (tc.TriggerAction == TriggerAction.Delete) { // Make a command SqlCommand cmd = SqlContext.GetCommand(); // make a command that inserts the deleted row into // the obsoletejobs table cmd.CommandText = insert into obsoletejobs select * from deleted ; // move the rows int rowsAffected = (int)cmd.ExecuteNonQuery(); if (rowsAffected == 0) { // something bad happened, roll back cmd.CommandText = rollback transaction ; cmd.ExecuteNonQuery(); } } }

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

SQLTRIGGERCONTEXT tables for DML statements as well as

SQLTRIGGERCONTEXT tables for DML statements as well as information about which columns were updated, in the case of an update statement. In CLR triggers, this information is made available through the SqlTriggerContext class. You obtain the SqlTriggerContext through the SqlContext, as shown in the following example. SqlTriggerContext has a property to tell you whether the triggering action was an INSERT, an UPDATE, or a DELETE. This is handy if your trigger handles more than one action. The Columns Updated method returns a bitmask of columns by position, just like the T-SQL function of the same name. Finally, because SQL Server 2005 adds DDL and Event triggers to the mix, a property that exposes the EVENTDATA XML structure provides detailed information. The INSERTEDand DELETEDlogical tables work the same way that they do in T-SQL triggers; an interesting behavior is that they are visible from any SqlCommand that you create inside your trigger, not only the context SqlCommand. Because individual SQL statements are always transactional, any SqlCommandother than the context command must get the transaction property from the context and fill it in, as we showed earlier. This is shown in the following code. public static void AddToObsolete() { // get the trigger context so you can tell what is // going on SqlTriggerContext tc = SqlContext.GetTriggerContext(); // this trigger is for deletes if (tc.TriggerAction == TriggerAction.Delete) { // Make a command. This can access the // deleted and inserted logical tables SqlCommand cmd = SqlContext.GetCommand(); // so can this one, if it picks up the context transaction // SqlConnection conn = SqlContext.GetConnection(); // SqlCommand cmd = conn.CreateCommand(); // cmd.Transaction = SqlContext.GetTransaction(); // so can this one, if it picks up the context transaction // SqlCommand cmd = new SqlCommand(); // cmd.Connection = SqlContext.GetConnection(); // cmd.Transaction = SqlContext.GetTransaction(); // make a command that inserts the deleted row into // the obsoletejobs table

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

THE IN-PROCESS DATA PROVIDER // build a command

THE IN-PROCESS DATA PROVIDER // build a command SqlCommand cmd = SqlContext.GetCommand(); // build command text cmd.CommandText = select address, city, state, zip + from authors where au_id = @au_id ; // make a parameter to hold the author id cmd.Parameters.Add( @au_id , SqlDbType.VarChar); // put in the value of the author id cmd.Parameters[0].Value = au_id; // use ExecuteRow because you know only // a single row will come back SqlDataRecord rec = cmd.ExecuteRow(); // make SqlString to hold result // note that if you do not give this // string a value it will be null au_info = new SqlString(); // check to see if lookup was successful if (rec == null) { // lookup was not successful, raise an error cmd.CommandText = Raiserror (50005, 16, 1, + au_id.ToString() + ) with seterror ; cmd.ExecuteNonQuery(); return -50005; } else { // lookup was successful, set au_info to information string au_info = String.Format( {0} {1} {2} {3} , rec[ address ], rec[ city ], rec[ state ], rec[ zip ]); } // nothing to return, either success returned author info in au_info // or error was raised return 0; } SqlTriggerContext SQL triggers have an execution context (represented by SqlContext) just as stored procedures and UDFs do. The environment settings, temporary tables in scope and so on, are available to a trigger. But triggers have additional context information; in T-SQL this information consists of logical

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

EXCEPTION HANDLING A .NET Framework error occurred during

EXCEPTION HANDLING A .NET Framework error occurred during execution of user defined routine or aggregate ExceptionThrower : System.DivideByZeroException: Attempted to divide by zero. at Except1.Class1.ExceptionThrower(). exception catcher calls exception thrower begin tran insert into jobs values( catcher , 10, 10) exec ExceptionCatcher commit In exception catcher A .NET Framework error occurred during execution of user defined routine or aggregate ExceptionThrower : System.DivideByZeroException: Attempted to divide by zero. at Except1.Class1.ExceptionThrower(). Note that, in each case, the transaction will commit. The only thing that would cause the transaction to roll back would be if the call to the standalone ExceptionThrowerwas called from a T-SQL try/catchblock. If the case of ExceptionCatcher, it catches and discards the exception raised by the ExceptionThrower (the error message comes from Exception Catcher s write of e.Message to the SqlPipe. The only unusual thing is that we don t see the messages sent by the ExceptionThrower. For procedure writers looking to expose custom errors through the API, SqlPipe.Send(SqlError) looks promising. However, SqlError doesn t have a constructor; the only errors that can be sent through this call are already existing T-SQL errors. To return a custom error, the tried-andtrue method is best. Define your error to the SQL Server error catalog with sp_addmessage. Use the T-SQL RAISERRORcommand in SqlCommand.Text. Use SqlCommand.ExecuteNonQueryto return the error. This method follows. public static SqlInt32 getAuthorWithErrors( SqlString au_id, out SqlString au_info) { // In SQL Workbench add custom message to be used // when an author cannot be found // sp_addmessage 50005, 16, // author with ssn: %s, not found in the database // go

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

THE IN-PROCESS DATA PROVIDER procedure, that procedure can

THE IN-PROCESS DATA PROVIDER procedure, that procedure can catch your exception without causing an underlying T-SQL exception. The following code in illustrates this. public static void ExceptionThrower() { try { int i = 42; int j = 0; j = i / j; } catch (Exception e) { SqlContext.GetPipe().Send( In exception thrower ); SqlContext.GetPipe().Send(e.Message); throw (e); } } public static void ExceptionCatcher() { try { SqlCommand cmd = SqlContext.GetCommand(); cmd.CommandText = ExceptionThrower ; cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); SqlContext.GetPipe().Send( Shouldn t get here ); } catch (Exception e) { // but it doesn t get here either SqlContext.GetPipe().Send( In exception catcher ); SqlContext.GetPipe().Send(e.Message); } } The results of using the ExceptionThrower procedure, both standalone and from the ExceptionCatcher, are shown in the following code. exception thrower standalone begin tran insert into jobs values( thrower , 10, 10) exec ExceptionThrower commit results in the messages window In exception thrower Attempted to divide by zero. .Net SqlClient Data Provider: Msg 6522, Level 16, State 1, Procedure ExceptionThrower, Line 3

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

EXCEPTION HANDLING Exception Handling One of the reasons

EXCEPTION HANDLING Exception Handling One of the reasons .NET procedures are appealing is that .NET has real structured exception handling. SQL Server 2005 includes improvements in error handling (see BEGIN END TRY, BEGIN END CATCH in Chapter 7, T-SQL Enhancements), but this isn t true structured exception handling. Any error that is caught in T-SQL s try/catch mechanism results in a doomed transaction; this isn t true for .NET procedures, as shown in the following example. public static void EatException() { try { // either one of these works // divide-by-zero exception int i = 42; int j = 0; j = i / j; // or SqlException (comment out previous exception) // cannot commit context transaction SqlContext.GetTransaction().Commit(); } catch (Exception e) { SqlContext.GetPipe().Send( Ate the exception ); SqlContext.GetPipe().Send(e.Message); } } Run this T-SQL code to test it In each case the transaction commits And the row is inserted BEGIN TRANSACTION INSERT INTO jobs VALUES( before exception , 10, 10) EXECUTE EatException COMMIT You can even catch an error in .NET code and rethrow it. However, there is a catch (pun intended). Any unhandled exceptions that make their way out of your CLR procedure result in the same error at the client, whether the client is SQL Workbench or a user application. However, if you are called from a try/catch block inside another .NET stored

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services