PROCEDURES AND FUNCTIONS IN .NET LANGUAGES // so

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES // so it can be returned as an int int i = i2.Value; return i; } } In the preceding example, the function Edgemust get the value of d1 as a double in order to pass it to the Sqrt method. It must also get the value of i2 as an int in order to return it. Note that best practice says that this method should return some kind of SqlInt. It is returning an intso that it can illustrate the use of the Value property that all the data types in the System.Data.SqlTypeshave. An alternate way to get the value of a SqlTypeas a CLR type is to cast it to it. A cast is more flexible than using the Valueproperty in that it can not only get the value of the SqlTypeas a CLR type, it can also coerce to a different type. The following code is an example of using a cast to get the value of a SqlTypeand coerce it to a CLR type that is different from the one that corresponds to the SqlType. namespace Math { public class Arithmetic { public static int SameAs1(SqlInt64 i) { // this compiles and works as expected // it coerces the SqlInt64 to a Int32 // instead of long, which corresponds to SqlInt64 return (int)i; } public static int SameAs2(SqlInt64 i) { // this produces a compile-time error // because Value is a long return i.Value; } } } A SqlType can be explicitly coerced to its corresponding CLR type and to any type that is compatible with that CLR type. Also, each SqlType can be explicitly coerced to another SqlType. Table 3-2 shows coercions allowed from one SqlType to another. The first column on the left lists the types to be coerced. The rest of the columns note the types to which it can be explicitly coerced by marking an X in it. For example, it shows that a SqlGuidcan be explicitly coerced to a SqlGuid, a SqlBinary, and a SqlString.

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

SYSTEM.DATA.SQLTYPES corresponds to the SqlType. You will need

SYSTEM.DATA.SQLTYPES corresponds to the SqlType. You will need to know this when creating parameters for SQL commands. Chapter 4 discusses using OLE DB and ODBC providers, some of which use SqlDbType. Note that this example and others that follow on the usage of a SqlType work independently of SQL Server. You can use this code in an ordinary console or Windows application to try it out without loading the code into SQL Server. All the examples reference System.dlland System.Data.dll, and should include the following usingstatements. using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; A SqlType has a property named Value. This property contains the value of the SqlType in terms of its corresponding CLR native type. The following code fragment is part of the C# type definition for SqlInt32, showing the definition of its Valueproperty. public struct SqlInt32 : … { … // Value returns the CLR type corresponding to the SqlType, // in this case an int is returned for the SqlInt32 SqlType public int Value {get;} } Every SqlType has this property; the only difference among them is the type returned by Value. In some cases, you will need the CLR type to process the SqlType for example, when you pass it as a parameter to a method that requires a CLR type. The following lines of C# code show a method that extracts a CLR type from a SqlType. public class Limits { // SqlDouble and SqlInt32 used as parameters public static int Edge(SqlDouble d1, SqlInt32 i2) { // gets SqlDouble value as CLR double // so its square root can be calculated if(System.Math.Sqrt(d1.Value) > 3.0) { return 7; } // gets SqlInt32 value as CLR int

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

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Table 3-1:

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Table 3-1: SqlType Correspondence to Other Types SqlType CLR SQL Native SQLDB SqlBinary Byte[] BINARY VARBINARY TIMESTAMP IMAGE Binary VarBinary TimeStamp Image SqlInt64 Int64 BIGINT BigInt SqlInt32 Int32 INT Int SqlInt16 Int16 SMALLINT SmallInt SqlByte Byte TINYINT Byte SqlString String CHAR VARCHAR NCHAR NVARCHAR TEXT NTEXT SYSNAME Char VarChar NChar NVarChar Text NText VarChar SqlDateTime Datetime DATETIME SMALLDATETIME DateTime SmallDateTime SqlDecimal Decimal DECIMAL NUMERIC Decimal Numeric SqlDouble Double FLOAT Float SqlSingle Single REAL Real SqlMoney Decimal MONEY SMALLMONEY Money SmallMoney SqlGuid Guid UNIQUEIDENTIFIER UniqueId SqlBoolean Boolean BIT Boolean

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

SYSTEM.DATA.SQLTYPES The problem that the PulleyDistance method has

SYSTEM.DATA.SQLTYPES The problem that the PulleyDistance method has is that there is no way for the CLR data type double to represent a null. The System.Data. SqlTypes namespace contains data type definitions that more closely mimic the behavior of SQL data type. For example, the PulleyDistance method could be rewritten as shown in Listing 3-6 and handle null parameters in the same way a typical T-SQL function would. Listing 3-6: PulleyDistance Function Using SqlType public class CPulley { public static SqlDouble PulleyDistance( SqlDouble Pulley1Diameter, SqlDouble Pulley2Diameter, SqlDouble BeltLength) { if(Pulley1Diameter.IsNull || Pulley2Diameter.IsNull || BeltLength.IsNull) return SqlDouble.Null … The use of System.Data.SqlTypes in methods that implement T-SQL stored procedures, functions, or triggers is best practice. This section discusses System.Data.SqlTypes. One of the restrictions on methods is that they must use data types for parameters and return values with which SQL Server is familiar. For example, SQL Server could not use a method that had a System.Collections. Hashtableas a parameter. The types used must be a SqlType, a type in the CLR that corresponds to a SqlType, or a user-defined type. (User-defined types will be discussed in Chapter 5.) Table 3-1 shows CLR types that correspond to the types in the System.Data.SqlTypes namespace. User- defined types, discussed in Chapter 5, are an exception to this table. The first column shows types from the System.Data.SqlTypes name- space. The second column shows the corresponding CLR type. The type of a parameter or return value for a method must appear in either the SqlType or CLR Type columns of this table. Note that some programming languages use synonyms for CLR type names. In particular, C# and VB.NET refer to Int32as int, and Int64as long. The third column shows the SQL native type that corresponds to the SqlType. You will need to know this when you add your code to SQL Server, which will be discussed later. The last column shows the SqlDbType that

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

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES { Return

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES { Return a1; } } } Methods used to implement procedures, functions, or triggers may access anything methods in the CLR may access, including files, network connects, and the wide range of capabilities provided by the .NET Framework. They are, however, limited by the permission set given the assembly containing them when it was cataloged, as shown in Chapter 2 and as will be discussed in detail in Chapter 5. Only simple static, public methods from public classes that are not nested can be used to implement procedures, functions, and triggers. There are further restrictions on the parameters these methods can use, which are covered in the next section. System.Data.SqlTypes The PulleyDistancefunction in the previous section, though useful, does not have all the capabilities of a T-SQL function. For example, when the SQL batch in Listing 3-4 is run, it produces an error. Listing 3-4: PulleyDistance with a Null Argument PRINT dbo.PulleyDistance(40, 20, NULL) .Net SqlClient Data Provider: Msg 6569, Level 16, State 1, Line 1 PulleyDistance failed because input parameter 3 is not allowed to be null. As the PulleyDistancemethod is written, there is nothing that can be done to prevent this error, because there is no way for it to check to see if an input parameter is NULL.A T-SQL function can, however, always check an argument for NULL, as is shown in Listing 3-5. Listing 3-5: Function That Tests for NULL CREATE FUNCTION Width(@Size int) RETURNS int AS IF (@Size IS NULL) RETURN NULL RETURN -@Size

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

CLR METHODS IN SQL SERVER between code in

CLR METHODS IN SQL SERVER between code in SQL Server and code in the CLR, the remainder of this chapter uses the terms procedure and function refer to SQL Server stored procedures and functions. The term method refers to a function or procedure in a CLR type. In addition, this chapter will talk about the System.Data.SqlTypes namespace. This namespace contains a number of type definitions, such as SqlInt32. The term SqlTypeis a generic reference for one of these types. The CLR allows methods to be overloaded that is, two methods in the same class with the same name but different parameter types or count. Overloaded methods may not be used to implement a SQL procedure, function, or index. The following code fragment shows an example of overloaded methods. // class is public public class Arithmetic { // method is static and public // but this method and the one that follows it // have the same name so neither can be used for // the implementation of a procedure, function, or index public static int AddTheseNumbers(int a1, int a2) { return a1 + a2; } // method is static and public public static int AddTheseNumbers(int a1, int a2, int a3) { return a1 + a2 + a3; } } The CLR allows type definitions to be nested that is, a type definition that is within the scope of another type definition. Methods from nested type definitions may not be used to implement a SQL procedure, function, or trigger. The method named Invertin the following code fragment is an example of this. // class is public public class Arithmetic { public class Numeric { // Invert may not be used to implement // a procedure, function, or trigger // because it is a member of a nested type public static int Invert(int a1)

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

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Belt P1

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Belt P1 P2 C M30 P12 P29 20.9 M30 P11 P29 20.8 M30 P10 P29 20.6 … The SQL batch in Listing 3-2 first creates the Pulley and Belt tables and then fills the tables with test data. Next it creates a view called Transmissions. It uses the PulleyDistance function to determine the pulley center distance. If the distance is zero, it does not include that row in the view. This function can be written in T-SQL. In a field like mechanical engineering, it is more likely that the experts in the field of pulley/belt transmission would be more familiar with C# or VB.NET than T-SQL. It is also likely that this function might be part of an existing, verified-by-usage code module. Using functions written in a CLR language is a powerful enhancement to SQL Server because it allows experts in a field to use a familiar programming environment and existing code without compromising reliability or security. In order for the function in Listing 3-1 to be used, it must be added to the database that will call it. First, the assembly, which contains the function, must be added to the database, as was shown in Chapter 2. After the assembly as been added, the function itself must be added using the CREATEFUNCTIONcommand, as would be done for any user-defined function added to a database. Listing 3-3 shows the PulleyDistance function being added to a database. Listing 3-3: Adding Functions to a Database CREATE ASSEMBLY Pulleys FROM c:binPulleys.dll CREATE FUNCTION PulleyDistance(@P1 FLOAT, @P2 FLOAT, @Belt FLOAT) RETURNS FLOAT format is assembly.class.function EXTERNAL NAME Pulleys.CPully.PulleyDistance Note that the case is significant in the EXTERNALNAME even if the .NET language used to create the function is not case sensitive (for example, VB.NET). The case of the names of the class and function must match those in the source code. Some notes on terminology for this chapter: Both SQL Server and the CLR can be thought of as having functions and procedures. To distinguish

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

CLR METHODS IN SQL SERVER There are a

CLR METHODS IN SQL SERVER There are a number of formulas that govern the use and operation of pulley/belt transmissions. The formula the PulleyDistance function comes from the McGraw-Hill Machining and Metalworking Handbook, by Ronald Walsh (McGraw-Hill, 1994). Listing 3-2 shows a SQL batch that builds a sample database and the Transmissionsview. Listing 3-2: Pulley/Belt Database CREATE TABLE Pulley ( model CHAR(10), diameter FLOAT ) CREATE TABLE Belt ( model CHAR(10), length CHAR ) GO generate pulley data DECLARE @i INT SET @i = 40 WHILE (@i > 0) BEGIN INSERT INTO Pulley VALUES ( P + CONVERT(VARCHAR, @i), @i) SET @i = @i – 1 END GO -generate belt data DECLARE @i INT SET @i = 30 WHILE (@i > 0) BEGIN INSERT INTO Belt VALUES ( M + CONVERT(varchar, @i), @i * 4) SET @i = @i – 5 END GO CREATE VIEW Transmissions AS SELECT B.model AS Belt, P1.model AS P1, P2.model AS P2, dbo.PulleyDistance(P1.diameter, P2.diameter, B.length) AS C FROM Belt B, Pulley P1, Pulley P2 WHERE dbo.PulleyDistance(P1.diameter, P2.diameter, B.length) > 0 AND P1.Diameter < P2.Diameter GO SELECT * FROM Transmissions Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Our pulley/belt

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Our pulley/belt company uses a database to keep track of the kinds of pulleys and belts they stock. The database has a pulley table that lists each pulley by part number and diameter. It has a belt table that lists each belt by part number and length. Any combination of two pulleys and a belt can be used to make a transmission if the pulleys can be placed far enough part to not touch each other and still allow the belt to go around them. This company wants a view that will show them all the possible transmissions that they can make from the parts that they stock. The transmission view should show the part numbers for the pulleys and belt, and it must also show the distance between the centers of the pulleys. The distance between the pulleys requires a geometric calculation. Listing 3-1 shows a function that calculates the approximate distance between the two pulleys of a pulley/belt transmission, given the pulley sizes and belt length. Listing 3-1: PulleyDistance Function public class CPulley { public static double PulleyDistance( double Pulley1Diameter, double Pulley2Diameter, double BeltLength) { double length = 0; double a = 2.0; double b = BeltLength – 1.57 * (Pulley1Diameter + Pulley2Diameter); double c = System.Math.Pow(Pulley1Diameter – Pulley2Diameter, 2.0); // if this is not positive no chance the pulleys will fit double b1 = Math.Pow(b, 2.0) – (4 * a * c); if(b1 > 0) { length = (b + Math.Sqrt(b1)) / (2 * a); // check that pulleys fit if (length < ((Pulley1Diameter + Pulley2Diameter) / 2)) { // return 0 if pulleys don t fit length = 0; } } // one decimal point is enough return System.Math.Round(length, 1); } } Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

CLR METHODS IN SQL SERVER The CLR is

CLR METHODS IN SQL SERVER The CLR is not better suited for doing set operations. SQL is the clear winner here. However, the CLR can execute SQL expressions, just as T-SQL can and with the same efficiency. Being able to write code in a CLR language will not be a substitute for knowing how write SELECTA.au_fname, A.au_lnameFROMauthorsAJOINtitleauthorsTONA.au_id = T.au_id when you need to find all the authors that have publications. The previous chapter, Chapter 2, explained how assemblies are loaded into SQL Server. This chapter focuses on the basic mechanics of using the methods in those assemblies as stored procedures, scalar-valued functions, and triggers. The chapter that follows this, Chapter 4, explains how these methods can access SQL Server directly, in a performant way. CLR Methods in SQL Server A public static method of a public class can, with some restrictions, be used as a stored procedure, user-defined function, or trigger in SQL Server. We will cover the specific restrictions, but, in general, they limit the method parameters to those that make sense when used inside SQL Server. To illustrate the use of CLR methods in SQL Server, we will look at a database for a company that makes pulley/belt drives. A pulley/belt drive is a kind of transmission; it has two round pulleys with a belt wrapped around them. Lots of kinds of equipment, from lawnmowers to drill presses, use the pulley/belt transmissions. Figure 3-1 shows an example of a pulley/belt drive. A belt, whose length is L, wraps around pulley 1 and pulley 2. D1 is the diameter of pulley 1, and D2 is the diameter of pulley 2. C is the distance from the center of pulley 1 to pulley 2. C Pulley Center Distance Pulley 1 Pulley 2 L Belt Length D1 Pulley 1 Diameter D2 Pulley 2 Diameter Figure 3-1: Pulley/Belt Drive

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