4 The In-Process Data Provider AS A COMPLEMENT

4 The In-Process Data Provider AS A COMPLEMENT to the ability to run .NET code inside SQL Server 2005, SQL Server 2005 includes a new kind of data provider that runs inside the database itself. This gives .NET code that runs inside the database, such as user-defined functions and stored procedures, access to the database, just as ADO.NET gives client-side applications access to the database. In the past, extensions to SQL Server written as extended stored procedures had this capability, but only by making an inefficient connection to the database, just as client applications did. The new provider is different in this respect; it gives .NET code running inside the database access to the database that is almost on a par with the efficiency of T-SQL, without the overhead of a connection. The SQL Server Programming Model Accessing data from your applications normally involves using a provider to connect to the data source and subsequently to retrieve, insert, update, or delete data in the database. You have probably used ODBC drivers, OLE DB providers, or ADO.NET in your applications in the past. Your application, even if written in .NET, ran outside the database, and that access technology you used made a connection to the database to pass commands to it and retrieve results. Now you can write .NET code that runs inside the database in the form of a stored procedure, user-defined function or any of the other .NET

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

WHERE ARE WE? Where Are We? This chapter

WHERE ARE WE? Where Are We? This chapter shows that CLR functions can be used to implement T-SQL stored procedures, functions, and triggers. CLR languages will typically be better suited to doing numeric computations and also offer a familiar programming environment for problem space experts that typically don t write T-SQL applications. The System.Data.SqlTypes namespace provides data types that can be used in the CLR but have the properties of the corresponding data types in SQL Server.

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 In summary,

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES In summary, to create a TVF, you make a class that implements both ISqlReader and ISqlRecord. You also create a static function that creates an instance of this class and returns it through the ISqlReader or ISql Record interface. Then you catalog the function as you would any other CLR function in SQL Server, noting that it returns a TABLE. The ISql Record interface must return the appropriate data types for the columns you specify in this table. Triggers Triggers in SQL Server may be implemented by a CLR method. Methods that implement a trigger must return a void and have no parameters. The following code is an example of a C# method that can be used as a trigger. public class Triggers { public static void OnMyUpdate() { … code that checks/modifies database } } Since a method that implements a trigger has no inputs or outputs, the only thing of consequence it can do, just as it is for a T-SQL trigger, is check what the operation that caused the trigger did to the database and possibly prevent it, change it, or roll it back. To do these operations, the CLR code must use SQL commands. Chapter 4 discusses using SQL commands as part of stored procedures, functions, and triggers implemented using a CLR language. The CLR function that implements a trigger does so in a context called the SqlTriggerContext. The SqlTriggerContext is discussed in Chapter 4. It makes available to the method the INSERTED and DELETED logical tables that triggers use to determine what operation was done. Triggers implemented using a CLR method are added to a database using a syntax similar to the one used for adding T-SQL triggers. The following code is an example of adding a trigger to a database. CREATE TRIGGER OnMyUpdate ON Invoices AS EXTERNAL NAME Triggers.CInvoices.OnMyUpdate The behavior and operation of CLR triggers is the same as for T-SQL triggers.

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

TABLE-VALUED FUNCTIONS { return previous_; } return null;

TABLE-VALUED FUNCTIONS { return previous_; } return null; } } public int FieldCount { get { return 2; } } public string GetName(int i) { if (i == 0) { return next ; } if (i == 1) { return previous ; } return null; } public SqlMetaData GetSqlMetaData(int i) { SqlMetaData md = new SqlMetaData( Data , SqlDbType.Int); return md; } public int GetInt32(int i) { return this[i]; } } Assuming that both the Bernoulli and the BData classes are in an assembly named TVFunc.dll, the script that follows catalogs them into SQL Server. CREATE ASSEMBLY TVFunc FROM D: SamplesTVFuncTVFuncbinTVFunc.dll CREATE FUNCTION Bernoulli(@preStart INT, @start INT, @count INT) RETURNS TABLE (next INT, previous INT) AS EXTERNAL NAME TVFunc.CBernoulli.Bernoulli Note that the CREATE FUNCTION command specifies the schema of the table returned by the Bernoullimethod.

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

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES } return

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES } return previous_; } } public bool NextResult() { // TODO: Add BData.NextResult implementation throw new NotImplementedException(); } // many methods removed for clarity. // see full example on Web site public bool HasRows { get { if (maxCount_ > 0) { return true; } return false; } } public bool Read() { // just leave things as they are // for the first time through if (count_ != 0) { int p = current_; current_ += previous_; previous_ = p; } // move to next number in sequence count_ = count_ + 1; if (count_ > maxCount_) { // done with sequence return false; } return true; } object System.Data.Sql.ISqlRecord.this[int i] { get { if (i == 0) { return current_; } if (i == 1)

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

TABLE-VALUED FUNCTIONS Once SQL Server has called Read(),

TABLE-VALUED FUNCTIONS Once SQL Server has called Read(), it will then want to get the values for the fields read. It does this by using one of the accessors that are part of the ISqlRecord interface. There is an accessor for each type supported by SQL Server. Both of the columns the TVF returns are SqlInt32s, so it will call the GetSqlInt32method and pass in the column that it wants. This is shown next, along with a helper accessor to get the column values. int this[int pos] { get { if (pos == 0) { return current_; } return previous_; } } public SqlInt32 GetSqlInt32(int i) { return new SqlInt32(this[i]); } There are quite a few details in the implementation of ISqlReader and ISqlRecord, but the basic implementation of the BDataclass is shown next. class BData: ISqlReader, ISqlRecord { // the previous number in the sequence int previous_; // the current number in the sequence int current_; int count_ ; int maxCount_; internal BData(int preStart, int start, int count) { previous_ = preStart; current_ = start; maxCount_ = count; this.count_ = 0; } int this[int pos] { get { if (pos == 0) { return current_;

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

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES The following

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES The following code shows the BData class and its constructor and Read()implementation. class BData: ISqlReader, ISqlRecord { // the previous number in the sequence int previous_; // the current number in the sequence int current_; // position in sequence int count_ ; // length of sequence int maxCount_; internal BData(int preStart, int start, int count) { previous_ = preStart; current_ = start; maxCount_ = count; this.count_ = 0; } public bool Read() { // just leave things as they are // for the first time through if (count_ != 0) { // calculate next number in // sequence and remember last int p = current_; current_ += previous_; previous_ = p; } // move to next number in sequence count_ = count_ + 1; if (count_ > maxCount_) { // done with sequence return false; } return true; } } The constructor just saves the first and second numbers in the sequence and the length of the sequence. Each time Read()is called, it just calculates the next number in the sequence. Note that the first time Read() is called, it does not do a calculation; it just leaves the initial values in place. SQL Server will keep on calling Read()to get a row until Read()returns false.

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

TABLE-VALUED FUNCTIONS next previous – 8 5 13

TABLE-VALUED FUNCTIONS next previous – 8 5 13 8 21 13 The next column shows a number in the sequence, and the previous column shows the number that comes before it. The implementation of the function itself is straightforward. It creates an object that has an ISqlReaderand an ISqlRecordinterface. It is shown next. public class CBernoulli { [SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)] public static ISqlReader Bernoulli (int preStart, int start, int count) { // create object that implements both // ISqlReader and ISqlRecord BData b = new BData(preStart, start, count); ISqlReader dr = b; //return the ISqlReader return dr; } } The BData object is the more difficult part. In order to work properly, it will have to maintain some state that it updates with each call to ISqlReader.Read(). The constructor must pass in the first two values of the sequence and how long the sequence should be. Each call will change the state to the next value in a Bernoulli sequence. SQL Server also has to be able to find the type of each column in the table produced by the TVF. The BData object has to implement the GetSqlMetaData method. This method is passed in an int and must return the SqlDbType of the column through an instance of a SqlMetaData class. There are also numerous other methods, mostly based on SqlTypes that also have to be implemented.

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 Table-Valued Functions

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Table-Valued Functions A user-defined function implemented with a CLR language can return a table, just as any user-defined function in T-SQL can. These are called table-valued user-defined functions, or TVFs. A TVF must return an ISql Reader or ISqlRecord interface. These interfaces are implemented by the SqlDataReader, discussed in Chapter 4, which is used to return the results of a tabular query. Complete coverage of the implementation of ISqlReader and ISql Recordare beyond the scope of this book, but we will cover the key points. A TVF acts like a cursor on a set of records. Each time you call Read, you advance the cursor to the next record. While the cursor is on a record, it allows you to access the fields in that record. The implementation of ISqlReader implements the cursor that moves over a set of records, and ISqlRecordprovides access to the fields in each record. For ISqlReader, the principal elements you must implement are the Read() method and the HasRows and FieldCount properties. Read() advances the cursor and returns true if the resulting cursor has not gone beyond the last row. HasRowsreturns a booleanthat indicates that there is at least one row. And FieldCountreturns an intthat indicates the number of fields in a row. To illustrate how to implement a table-valued function, we will implement one that produces a sequence of numbers called a Fibonacci sequence. The sequence starts with two numbers, and each number after the second one is the sum of the previous two. Here is an example. 1 2 3 5 8 13 21 1+2 2+3 5+3 8+5 13+8 The TVF we will create is a function called Fibonacci that will return two columns, one called next and one called previous. It will take as input the first two numbers we want in our sequence and the length of the sequence. The following SQL script runs our function and produces a table with six rows. select next, previous from dbo.Fibonacci(1,2,6) next previous – 2 1 3 2 5 3

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

SCALAR-VALUED FUNCTIONS Table 3-6: Usage of SqlFacetAttribute Type

SCALAR-VALUED FUNCTIONS Table 3-6: Usage of SqlFacetAttribute Type IsFixedLength MaxSize Precision Scale IsNullable SqlBoolean N N N N Y SqlByte N N N N Y SqlInt16 N N N N Y SqlInt32 N N N N Y SqlInt64 N N N N Y SqlSingle N N N N Y SqlDouble N N N N Y SqlDateTime N N N N Y SqlMoney N N N N Y SqlGuid N N N N Y SqlDecimal N N Y Y Y SqlString Y Y N N Y SqlBinary Y Y N N Y SqlXml N N N N Y SqlBytes Y Y N N Y SqlChars Y Y N N Y Embedded UDTs N N N N Y string Y Y N N Y Byte[] Y Y N N Y Char[] Y Y N N Y decimal N N Y Y N

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