Deploying Your Stored Procedures
In the SQL Server project
version of the sample code, deployment of all the stored procedures is
handled by Visual Studio when the assembly itself is deployed. This is
due to the application of the SqlProcedure attribute to the functions in class StoredProcedures (found in file spTest.cs). The SqlProcedure attribute accepts an optional Name parameter, the value of which is the actual callable stored procedure name. If you do not supply a value for the Name parameter, the name of the .NET function is used as the stored procedure name.
The SqlProcedure
attribute is used only by Visual Studio in SQL Server projects.
Therefore, it has been removed from the source code in the Class Library
project. Deploying the stored procedures from that version of the
source code requires issuing a CREATE PROCEDURE T-SQL command using the new EXTERNAL NAME
clause to specify the assembly, fully qualified class name specifier,
and function name. For example, to load the Class Library version of spContacts, you would issue the following command.
CREATE PROCEDURE spContacts
AS EXTERNAL NAME Chapter03.StoredProcedures.spContacts
The preceding command specifies that function spContacts, in class StoredProcedures, in the loaded assembly with T-SQL name Chapter03, should be registered as a CLR stored procedure callable under the name spContacts.
Note
All necessary CREATE PROCEDURE
commands for the Class Library project version of the sample code are
contained in the CreateObjects.sql script in the Management Studio
project supplied with the sample code. You will need to run that script
in order to execute the various SQL CLR entities implemented in the
Class Library project. |
Note that had the CLR
stored procedure been written in Visual Basic .NET rather than C#, the
class name specifier would change to Chapter03.StoredProcedures. This would necessitate a change to the deployment T-SQL code as follows:
CREATE PROCEDURE spContacts
AS EXTERNAL NAME Chapter03.[Chapter03.StoredProcedures].spContacts
In Visual Basic
projects, the default namespace for a project itself defaults to the
project name, as does the assembly name. The class within the project
must be referenced using the default namespace as a prefix. Because the
class specifier is a multipart dot-separated name, it must be enclosed
within square brackets so that SQL Server can identify it as a single
indivisible name. Because C# projects handle the default namespace
setting a little differently, the namespace prefix is not used in the
class specifier for C# assemblies.
One last point before we
discuss how to test your now-deployed CLR stored procedures. It is
important to realize that the class specifier and function name in the EXTERNAL NAME clause are case-sensitive and that this is true even for assemblies developed in Visual Basic .NET.
Although this point perplexed us quite a bit at first, it does make
sense in hindsight. SQL Server searches for your subs/functions within
your assemblies, not within your source code. In other words, it’s
looking within Microsoft Intermediate Language (MSIL) code, not Visual
Basic .NET or C# source code. Because MSIL is case-sensitive (it has to
be, to support case-sensitive languages like C#), SQL Server must be as
well as it searches for a specific class and sub/function.
The fact that SQL Server
is not case sensitive by default (even though it once was) and that
Visual Basic .NET is not a case-sensitive language is of no import! If
you attempt to register a sub/function and you receive an error that it
cannot be found within the assembly, double-check that the case usage in
your command matches that of your source code.
Testing Your Stored Procedures
With
your assembly and stored procedures now deployed, you’re ready to run
and test them. Typically, you should do this from Management Studio;
however, Visual Studio SQL Server projects allow you to test your SQL
CLR code from Visual Studio itself. When you create a Visual Studio SQL
Server project, a folder called Test Scripts is created as a
subdirectory in your source code directory. Within that subdirectory,
Visual Studio creates a script file called Test.sql. If you look at that
file, you will see that it contains commented instructions as well as
commented sample T-SQL code for testing stored procedures, functions,
and UDTs. It also contains an uncommented generic SELECT command that echoes a text literal to the caller.
Visual Studio connects to
your database and runs this script immediately after your assembly is
deployed, and the output from the script appears in Visual Studio’s
Output window. This allows you to execute any number of T-SQL commands
directly from Visual Studio without having to switch to another tool.
Although this approach is much less interactive than a Management Studio
query window, it allows you to run quick tests against your code. It is
especially useful for regression testing—that is, confirming that a new
version of your assembly does not break older, critical functionality.
The file extension of the
script must be .sql, but otherwise the name of the file is
inconsequential. You can have multiple script files in the Test Scripts
folder. To add a new one, right-click the Test Scripts folder node or
the project node in the Solution Explorer window and select the Add Test
Script option from the shortcut menu. Only one script can be active at
one time, and as soon as you have more than one script, you must specify
which one is active. To make a script active, simply right-click its
node in the Solution Explorer window and select the Set As Default Debug
Script option from its shortcut menu. When you do so, the node is
displayed in bold. You may run or debug a script even if it is not the
active script. To do so, right-click its node in the Solution Explorer
window and select the Debug Script option from its shortcut menu.
Warning
At
press time, there appears to be an anomaly in the working of the test
script facility and the Output window in Visual Studio 2005. Simply put,
if your test script executes a query (whether it be a T-SQL SELECT
command or a call to a stored procedure) that returns a column of type
uniqueidentifier (GUID), the query’s result set will not appear in the
Output window, and execution of the test script might hang Visual
Studio. For this reason, you should avoid calling the sample code CLR
stored procedures spContactsQuick and spContactsSELECT * FROM Person.Contact
query and thus retrieve the rowguid column, which is of type
uniqueidentifier) from your test script and instead test these
procedures from SQL Server Management Studio, where the anomaly does not
occur. You can safely call spContactCount, which simply performs a SELECT COUNT(*) FROM Person.Contact query, from your Visual Studio test script. Alternatively, you can modify spContactsQuick and/or spContacts to select specific columns from the Person.Contact table, making sure that rowguid is not one of them. (both of which perform a |
If
you’re working with the Class Library version of the sample code, you
must test the stored procedures from Management Studio or another SQL
Server query tool. Even if you are working with the SQL Server project
version, you’ll find that testing your SQL CLR code in Management Studio
provides a richer experience and more flexibility.
The script file
TestStoredProcs.sql in the Management Studio project supplied with the
sample code will run both of our CLR stored procedures (spContactCountspContacts).
Open the file in Management Studio, and click the Execute button on the
SQL Editor toolbar, choose the Query/Execute option on the main menu,
or press F5. (You can also right-click the query window and select
Execute from the shortcut menu.) and
When the script runs, you should see the single-valued result of the spContactCount stored procedure appear first, as shown in Figure 2. Note that the column name ContactCount appears on the Results tab and recall that this is a direct result of your using the SqlMetaData object in the CLR code. Below the spContactCount result, you will see the results from the spContacts
stored procedure come in. Because the Person.Contact table has almost
20,000 rows, these results might take some time to flow in.
Even while the results are
coming in, the “Starting data dump” status message should be visible on
the Messages tab (or on the Results tab if you’re using Management
Studio’s Results To Text option). Once all rows have been fetched, you
should see the “Data dump complete” message appear as well. If you get
impatient and want to abort the query before all rows have been fetched,
you can use the Cancel Executing Query button on the SQL Editor toolbar
or the Query/Cancel Executing Query option on the main menu; you can
also use the Alt+Break keyboard shortcut.
We
have yet to cover CLR functions, triggers, aggregates, and UDTs, but
you have already learned most of the skills you need to develop SQL CLR
code. You have learned how to create Visual Studio SQL Server projects
and use its autodeployment and test script features. You have also
learned how to develop SQL CLR code in standard Class Library projects
and to use T-SQL commands and Management Studio to deploy the code for
you. You’ve learned about the subtle differences between deploying C#
code and Visual Basic .NET code, and we’ve covered the case-sensitive
requirements of T-SQL-based deployment.
With all this under your belt, we can cover the remaining four basic SQL CLR entities relatively quickly.
