Now that the basic overview of what’s available is complete, it’s time to get into some code! The example used in this chapter will be a dynamic cross-tabulation of some sales data in the AdventureWorks sample database that’s included with SQL Server 2005 and 2008. Given the data in the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables, the goal will be to produce a report based on a user-specified date range, in which the columns are sales months and each row aggregates total sales within each month, by territory.
Before starting work on any CLR routine, the developer should ask, “Why should this routine be programmed using the CLR?” Remember that in most cases, T-SQL is still the preferred method of SQL Server programming, so give this question serious thought before continuing. In this case, the argument in favor of using a CLR routine is fairly obvious. Although this problem can be solved using only T-SQL, it’s a messy prospect at best. In order to accomplish this task, the routine first must determine in which months sales occurred within the input date range. Then, using that set of months, a query must be devised to create a column for each month and aggregate the appropriate data by territory.
This task is made slightly easier than it was in previous versions of SQL Server, thanks to the inclusionof the PIVOT operator. This operator allows T-SQL developers to more easily write queries that transform rows into columns, a common reporting technique known as either pivoting or cross-tabulating. However, PIVOT doesn’t provide dynamic capabilities, so the developer still needs to perform fairly complex string concatenation to get things working. Concatenating strings is tricky and inefficient in T-SQL. Using the .NET Framework’s StringBuilder class is a much nicer prospect. Avoiding complex T-SQL string manipulation is argument enough to do this job within a CLR routine.
Once the determination to use a CLR routine has been made, the developer next must ask, “What is the appropriate type of routine to use for this job?” Generally speaking, this will be a fairly straightforward question; for instance, a CLR user-defined type and a CLR user-defined trigger obviously serve quite different purposes. However, the specific problem for this situation isn’t so straightforward. There are two obvious choices: a CLR table-valued function and a CLR stored procedure.
The requirement for this task is to return a result set to the client containing the cross-tabulated data. Both CLR table-valued functions and CLR stored procedures can return result sets to the client. However, as will be discussed in the next chapter, CLR table-valued functions must have their output columns predefined. In this case, the column list is dynamic; if the user enters a 3-month date range, up to four columns will appear in the result set—one for each month in which there were sales, and one for the territory sales are being aggregated for. Likewise, if the user enters a 1-year date range, up to 13 columns may be returned. Therefore, it isn’t possible to predefine the column list, and the only choice is to use a CLR stored procedure.
Starting aVisual Studio 2008 SQL Server Project
Once you have decided to program a CLR routine, the first step is to start Visual Studio 2008 and create a new project. Figure shows the menu option to pick to launch the New Project Wizard.
Opening a new project in Visual Studio
Visual Studio includes a project template for SQL Server projects, which automatically creates all of the necessary references and can create appropriate empty classes for all of the SQL Server CLR routine types. Although you could use a Class Library template instead and do all of this manually, that’s not an especially efficient use of time. So we definitely recommend that you use the SQL Server Project template when developing CLR routines.
Figure shows the SQL Server Project template being chosen from the available database project templates. On this system, only C# has been installed; on a system with VB .NET, the same option would appear under that language’s option tree.
Selecting the SQL Server Project template
This project has been named SalesCrossTabs, since it’s going to contain at least one cross tabulation of sales data (perhaps more will be added in the future). A single SQL Server project can contain any number of CLR routines. However, it’s recommended that any one project logically group only a small number of routines. If a single change to a single routine is made, you should not need to reapply every assembly referenced by the database.
After clicking the OK button, you are presented with the New Database Reference dialog box, as shown in Figure. By specifying a connection at this point, the project is easier to deploy and test from within the Visual Studio environment. For this example, we are making a connection to the AdventureWorks database on our local server.
Create a new database reference if the correct one doesn’t already exist.
At this point, a new, blank project has been created and is ready to have some code added. Right-click the project name in Solution Explorer, click Add, and then click Stored Procedure, as shown in Figure.
Adding a stored procedure to the project
The final step in adding the new stored procedure is to name it. Figure shows the window that will appear after clicking Stored Procedure. The Stored Procedure template is selected, and the procedure has been named Get Sales Per Territory ByMonth. Developers should remember that, just as in naming T-SQL stored procedures, descriptive, self-documenting names go a long way toward making development and maintenance easier.
Naming the stored procedure
Anatomy of a Stored Procedure
After the new stored procedure has been added the project, the following code will be appear in the editing window:using System;
Notice that the Microsoft.SqlServer.Server and System.Data.SqlTypes namespaces have been automatically included in this project. Both of these namespaces have very specific purposes within a routine and will be necessary within most SQL Server CLR projects.
The Microsoft.SqlServer.Server namespace is necessary, as previously mentioned, for the attributes that must decorate all routines to be hosted within SQL Server. In this case, the GetSalesPerTerritoryByMonth method has been decorated with the SqlProcedure attribute. This indicates that the method is a stored procedure. The method has also been defined as static. Since this method will be called without an object instantiation, it would not be available if not defined as static. The Microsoft.SqlServer.Server namespace is also included in order to provide access to the calling context, for data access and returning data.
The System.Data.SqlTypes namespace provides datatypes that correspond to each of the SQL Server datatypes. For instance, the equivalent of SQL Server’s INTEGER datatype isn’t .NET’s System.Int32 datatype. Instead, it’s SqlTypes .SqlInt32 . Although these types can be cast between each other freely, not all types have direct equivalents. Many of the SQL Server types have slightly different implementations than what would seem to be their .NET siblings. For that reason, and to provide some insulation in case of future underlying structural changes, it’s important to use these types instead of the native .NET types when dealing with data returned from SQL Server, including both parameters to the routine and data read using a SqlDataReader or DataSet object.
Aside from the included namespaces, note that the return type of the GetSalesPerTerritoryByMonth method is void. SQL Server stored procedures can return either 32-bit integers or nothing at all. In this case, the stored procedure won’t have a return value. That’s generally a good idea, because SQL Server will override the return value should an error occur within the stored procedure; so output parameters are considered to be a better option for returning scalar values to a client. However, should a developer want to implement a return value from this stored procedure, the allowed datatypes are SqlInt32 and SqlInt16.
Most stored procedures will have one or more parameters to allow users to pass in arguments that can tell the stored procedure which data to return. In the case of this particular stored procedure, two parameters will be added to facilitate getting data using a date range (one of the requirements outlined in the section “Programming a CLR Stored Procedure”). These parameters will be called StartDate and EndDate, and each will be defined as type SqlDateTime.
These two parameters are added to the method definition, just like parameters to any C# method:[Microsoft.SqlServer.Server.SqlProcedure]
In this case, these parameters are required input parameters. Output parameters can be defined by using the C# ref (reference) keyword before the datatype. This will then allow developers to use SQL Server’s OUTPUT keyword in order to get back scalar values from the stored procedure.
Unfortunately, neither optional parameters nor default parameter values are currently supported by CLR stored procedures.
Defining the Problem
At this point, the stored procedure is syntactically complete and could be deployed as is; but of course, it wouldn’t do anything! It’s time to code the meat of the procedure. But first, it’s good to take a step back and figure out what it should do.
The final goal, as previously mentioned, is to cross-tabulate sales totals per territory, with a column for each month in which sales took place. This goal can be accomplished using the following steps:
The Sales.SalesOrderHeader table contains one row for each sale, and includes a column called OrderDate—the date the sale was made. For the sake of this stored procedure, a distinct list of the months and years in which sales were made will be considered. The following query returns that data:SELECT DISTINCT
Once the stored procedure has that data, it needs to create the actual cross-tab query. This query needs to use the dates from Sales.SalesOrderHeader. For each month, it should calculate the sum of the amounts in the LineTotal column of the Sales.SalesOrderDetail table. And of course, this data should be aggregated per territory. The TerritoryId column of the Sales.SalesOrderHeader table will be used for that purpose.
The first step in creating the cross-tab query is to pull the actual data required. The following query returns the territory ID, order date formatted as YYYY-MM, and total line item amount for each line item in the SalesOrderDetail table:SELECT
Figure shows a few of the 121,317 rows of data returned by this query.
Unaggregated sales data
Using the PIVOT operator, this query can be turned into a cross-tab. For instance, to report on sales from June and July of 2004, the following query could be used:SELECT
Figure shows the results of this query. The data has now been aggregated and crosstabulated. Note that a couple of the values are null, which indicates a territory that did not have sales for that month.
Cross-tabulated sales data
In the actual stored procedure, the tokens representing June and July 2004 will be replaced by tokens for the actual months from the input date range, as determined by the StartDate and EndDate parameters and the first query. Then the full cross-tab query will be concatenated. All that’s left from the three steps defined previously is to return the results to the caller. You have a couple of choices for how to tackle that challenge.
Using the SqlPipe
As mentioned in previous sections, SqlContext is an object available from within the scope of CLR routines. This object is defined in the Microsoft.SqlServer.Server namespace as a sealed class with a private constructor, so you don’t create an instance of it; rather, you just use it. The following code, for instance, is invalid://This code does not work -- the constructor for SqlContext is private
Instead, just use the object as is. To use the SqlPipe, which is the object we need for this exercise, the following code might be used://Get a reference to the SqlPipe for this calling context
So what is the SqlPipe object? This object allows the developer to send data or commands to be executed from a CLR routine back to the caller.
The Send() Method
The Send() method, which as you can guess is used to actually send the data, has three overloads:
The Send() methods can be called any number of times during the course of a stored procedure. Just like native T-SQL stored procedures, CLR procedures can return multiple result sets and multiple messages or errors. But by far the most common overload used will be the one that accepts SqlDataReader. The following code fragment shows a good example of the utility of this method:command.CommandText = "SELECT * FROM Sales.SalesOrderHeader";
In this fragment, it’s assumed that the connection and command objects have already been instantiated and the connection has been opened. A reader is populated with the SQL, which selects all columns and all rows from the Sales.SalesOrderHeader table. The SqlDataReader can be passed to the Send() method as is, and the caller will receive the data as a result set.
Although this example is quite simplistic, it illustrates the ease with which the Send() method can be used to return data back to the caller when the data is already in a SqlDataReader object.
The ExecuteAndSend() Method
The problem with sending a SqlDataReader back to the caller is that all of the data will be marshaled through the CLR process space on its way back. Since, in this case, the caller generated the data (it came from a table in SQL Server), it would be nice to be able to make the caller return the data on its own—without having to send the data back and forth.
This is where the ExecuteAndSend() method comes into play. This method accepts a SqlCommand object, which should have both CommandText and Parameters values (if necessary) defined. This tells the calling context to execute the command and process the output itself.
Letting the caller do the work without sending the data back is quite a bit faster. In some cases, performance can improve by up to 50 percent. Sending all of that data between processes is a lot of work. But this performance improvement comes at a cost; one of the benefits of handling the data within the CLR routine is control. Take the following code fragment, for example:command.CommandText = "SELECT * FROM Sales.ERRORSalesOrderHeader";
This fragment is similar to the fragment discussed in the previous Send() method example. It requests all of the rows and columns from the table, and then sends the data back to the caller using the Send() method. This work is wrapped in a try/catch block. The developer, perhaps, can do something to handle any exceptions that occur. And indeed, in this code block, an exception will occur. The table Sales.ERRORSalesOrderHeader doesn’t exist in the AdventureWorks database.
This exception will occur in the CLR routine—the ExecuteReader() method will fail. At that point, the exception will be caught by the catch block. But what about the following code fragment, which uses the ExecuteAndSend() method:command.CommandText = "SELECT * FROM Sales.ERRORSalesOrderHeader";
Remember that the ExecuteAndSend() method tells the caller to handle all output from whatever T-SQL is sent down the pipe. This includes exceptions. So in this case, the catch block is hit, but by then it’s already too late. The caller has already received the exception, and catching it in the CLR routine isn’t especially useful.
So which method, Send() or ExecuteAndSend(), is appropriate for the sales cross-tabulation stored procedure? Given the simplicity of the example, the ExecuteAndSend() method makes more sense. It has greater performance than Send(), which is always a benefit. And there’s really nothing that can be done if an exception is encountered in the final T-SQL to generate the result set.
Putting It All Together: Coding the Body of the Stored Procedure
Now that the techniques have been defined, putting together the complete stored procedure is a relatively straightforward process.
Recall that the first step is to get the list of months and years in which sales took place, within the input date range. Given that the pivot query will use date tokens formatted as YYYY-MM, it will be easier to process the unique tokens in the CLR stored procedure if they’re queried from the database in that format. So the query used will be slightly different from the one shown in the “Defining the Problem” section. The following code fragment will be used to get the months and years into a SqlDataReader object://Get a SqlCommand object
This code uses the same SqlCommand and SqlDataReader syntax as it would if this were being used for an ADO.NET client. Keep in mind that this code won’t work unless the System.Data.SqlClient namespace is included with a using directive. The only difference between this example and a client application is the connection string, which tells SQL Server that this should connect back to the caller’s context instead of a remote server. Everything else is the same—the connection is even opened, as if this were a client instead of running within SQL Server’s process space.
As a result of this code, the reader object will contain one row for each month in which sales took place within the input date range (that is, the range between the values of the StartDate and EndDate parameters). Looking back at the fully formed pivot query, you can see that the tokens for each month need to go into two identical comma-delimited lists: one in the outer SELECT list and one in the FOR clause. Since these are identical lists, they only need to be built once. The following code handles that://Get a StringBuilder object
A StringBuilder is used in this code instead of a System.string. This makes building the list a bit more efficient. For each row, the value of the YYYY_MM column (the only column in the reader) is enclosed in square brackets, as required by the PIVOT operator. Then a comma and a space are appended to the end of the token. The extra comma after the final token is removed after the loop is done. Finally, SqlDataReader is closed. When working with SqlDataReader, it’s a good idea to close it as soon as data retrieval is finished in order to disconnect from the database and save resources.
Now that the comma-delimited list is built, all that’s left is to build the cross-tab query and send it back to the caller using the ExecuteAndSend() method. The following code shows how that’s done://Define the cross-tab query
Note that we are using the same command object as we did for building the comma-delimited list of months in which sales took place. This command object already has the StartDate and EndDate parameters set; since the cross-tab query uses the same parameters, the parameters collection doesn’t need to be repopulated. Just like when programming in an ADO.NET client, the connection should be closed when the process is finished with it.
At this point, the CLR stored procedure is completely functional as per the three design goals, so it’s ready for a test drive.
Testing the Stored Procedure
Visual Studio makes deploying the stored procedure to the test SQL Server quite easy. Just rightclick the project name (in this case, SalesCrossTabs) and click Deploy, as shown in Figure.
Deploying the assembly to the test server
Once the procedure is deployed, testing it is simple. Log in to SQL Server Management Studio and execute the following batch of T-SQL:USE AdventureWorks
If everything is properly compiled and deployed, this should output a result set containing cross-tabulated data for the period between May 1, 2004, and July 1, 2004. Figure shows the correct output.
Cross-tabulated sales data for the period between May 1, 2004, and July 1, 2004
Note that running the stored procedure might result in the following message:
Execution of user code in the .NET Framework is disabled. Use sp_configure "clr enabled" to enable execution of user code in the .NET Framework.
If this happens, execute the following batch of T-SQL to turn on CLR integration for the SQL Server:USE AdventureWorks
Using the sp_configure system stored procedure to enable CLR integration is required before CLR routines can be run in any database. Keep in mind that enabling or disabling CLR integration is a serverwide setting.
Once the stored procedure is running properly, it will appear that the stored procedure works as designed! However, perhaps some deeper testing is warranted to ensure that the procedure really is as robust as it should be. Figure shows the output from the following batch of T-SQL:USE AdventureWorks
Attempting to cross-tabulate sales data for the period between May 1, 2005, and July 1, 2005
Debugging the Procedure
What a difference a year makes! Luckily, since this stored procedure is being coded in Visual Studio, the integrated debugging environment can be used to track down the problem. In Solution Explorer, expand the Test Scripts node and double-click Test.sql. This will open a template that can contain T-SQL code to invoke CLR routines for debugging. Paste the following T-SQL into the Stored Procedure section:EXEC GetSalesPerTerritoryByMonth
Now return to the code for the managed stored procedure and put the cursor on the first line:
SqlCommand command = new SqlCommand();
Pressing the F9 key will toggle a breakpoint for that line.
Before starting the debug session, open Server Explorer by selecting View ➤Server Explorer, as shown in Figure. In Server Explorer, right-click the database connection being used for this project and make sure that both Application Debugging and Allow SQL/CLR Debugging are checked, as shown in Figure.
Opening Server Explorer in Visual Studio
Allowing SQL/CLR debugging for the project’s database connection
Once debugging is enabled, press the F5 key, and Visual Studio will enter debug mode. If all is working as it should, the breakpoint should be hit, and code execution will stop on the first line of the stored procedure.
Use the F10 key to step through the stored procedure one line at a time, using the Locals pane to check the value of all of the variables. Stop stepping through on this line: yearsMonths.Remove(yearsMonths.Length - 2, 1);
Look at the value of the yearsMonths variable in the Locals pane—it’s empty. No characters can be removed from an empty string!
As it turns out, this stored procedure wasn’t coded properly to be able to handle date ranges in which there is no data. This is definitely a big problem, since the output requires a column per each month in the input date range that sales occurred. Without any data, there can be no columns in the output. The stored procedure needs to return an error if no data is present.
Throwing Exceptions in CLR Routines
Any exception that can be thrown from the CLR will bubble up to the SQL Server context if it’s not caught within the CLR routine. For instance, the sales cross-tab stored procedure could be made a bit more robust by raising an error if yearsMonths is zero characters long, instead of attempting to remove the comma:if (yearsMonths.Length > 0)
Instead of getting a random error from the routine, a well-defined error is now returned—theoretically. In reality, the error isn’t so friendly. As shown in Figure, these errors can get quite muddled. Not only is the error returned as with native T-SQL errors, but the call stack is also included. And although that’s useful for debugging, it’s overkill for the purpose of a well-defined exception.
Standard CLR exceptions aren’t formatted well for readability.
A better option might be to use a native T-SQL error, invoked with the RAISERROR() function. A batch can be sent using SqlPipe.ExecuteAndSend(), as in the following code fragment:if (yearsMonths.Length > 0)
Alas, as shown in Figure, this produces an even worse output. The T-SQL exception bubbles back into the CLR layer, where a second CLR exception is thrown as a result of the presence of the T-SQL exception.
RAISERROR alone doesn’t improve upon the quality of the exception.
The solution, as strange as it seems, is to raise the error using RAISERROR but catch it so that a second error isn’t raised when control is returned to the CLR routine. The following code fragment shows how to accomplish this:if (yearsMonths.Length > 0)
After catching the exception, the method returns. If it were to continue, more exceptions would be thrown by the PIVOT routine, as no pivot columns could be defined. Figure shows the output this produces when run with an invalid date range. It’s quite a bit easier to read than the previous exceptions.
Catching the exception in the CLR routine after firing a RAISERROR yields the most readable exception message.
The following is the complete code for the sales cross-tab stored procedure, including handling for invalid date ranges:using System;
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.