3. Deploying Your Stored Procedures
In the SQL Server Database 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 methods in the StoredProcedures class (found in the spTest.cs file). 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 method is used as the stored procedure name.
The SqlProcedure attribute is used only by Visual Studio when auto-deploying SQL
CLR assemblies. Therefore, it does not appear in 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 EXTERNAL NAME
clause to specify the assembly, the fully qualified class name
specifier, and the method name. For example, to load the Class Library
version of spContacts, you would issue the following command:
CREATE PROCEDURE spContacts
AS EXTERNAL NAME SQLCLRDemo.StoredProcedures.spContacts
The preceding command specifies that the spContacts method, found in the class named StoredProcedures, in the loaded assembly with T-SQL name SQLCLRDemo, 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 SSMS project supplied with the chapter’s sample code on
the book’s companion website. You will need to run that script in order
to utilize the various SQL CLR entities implemented in the Class
Library project. Before running the script, you will need to edit the CREATE ASSEMBLY
command at the top so that the assembly’s file specification points to
the location on your system where the assembly has been installed. By
default, the script looks for the assembly in the
C:\Demos\VS\SQLCLRDemoManual\SQLCLRDemo\bin\Debug folder on your PC.
Note that if the CLR stored procedure had been written in Microsoft Visual Basic .NET rather than C#, the class name specifier would change to SQLCLRDemo.StoredProcedures. This would necessitate a change to the deployment T-SQL code as follows:
CREATE PROCEDURE spContacts
AS EXTERNAL NAME SQLCLRDemo.[SQLCLRDemo.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 SQL CLR stored procedures. It is important to realize that the class specifier and method 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 may seem perplexing at first, it does make sense.
SQL Server searches for your methods 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 within an assembly for a specific class and method.
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 importance! If you attempt to register a method 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.
4. Testing Your Stored Procedures
The TestStoredProcs.sql script file in the SSMS project supplied with this chapter’s sample code will run both CLR stored procedures (spContactCount and spContacts).
Open the file in SSMS, and then click the Execute button on the SQL
Editor toolbar, choose Execute from the Query menu, press F5, or press Ctrl-E. (You can also right-click anywhere inside the query window and select Execute from the shortcut menu.)
When the script runs, you should see the single-valued result of the spContactCount stored procedure appear first, as shown in Figure 4. Notice 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.Person table has almost 20,000 rows, on certain machines, these results might take some time to retrieve.
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 the Results To Text option in SSMS). After all the rows
have been fetched, you should see the “Data Dump Complete” message
appear as well.
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 a Visual Studio SQL Server
Database Project and use SSDT’s CLR assembly auto-deployment features.
You have also learned how to develop SQL CLR code in standard Class
Library projects and how to use T-SQL commands to deploy the code for
you. You’ve learned about the subtle differences between deploying C#-
and Visual Basic .NET-based SQL CLR assemblies, 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.
