Building and Deploying a CLR Executable

Once your code is ready to test, you need to double-check the spelling of the function name to make sure it matches the code specification. Since the Visual Studio compilers (perhaps foolishly) assume you know what you're doing, it won't help you spell the entry point of the class you're creating. To build your application, choose "Build" from the project right-click menu or "Build Solution" from the Build menu. This instructs Visual Studio to compile the runtime DLL for the executable. If this succeeds, you're almost ready to deploy.

Preparing to Deploy a CLR Executable Function

The deployment phase installs the CLR executables into the targeted SQL Server database you specified earlier. To view or re-address this database, right-click the Project and choose "Properties". This opens the familiar tabbed dialog (as shown in Figure 13.10) that is the GUI interface for the assemblyinfo(.vb/.cs) configuration file. Click the "Database" tab to view the current Connection String. You can change this manually or click on Browse to re-open the Data Connection chooser dialog you saw earlier in Figure 13.5.

Figure 13.10. Addressing the target database.

Tip

Sure, it would be useful to visit the SQL Server database to see if the new CLR executable might collide with an existing executable.

Exploring Executables with the Server Explorer

The Server Explorer can be used to view and manage CLR executables, as shown in Figure 13.11. Note that the Server Explorer lists the deployed (and visible) executables along with the Assemblies used to create these executables. Yes, this means the source code used to build your function, stored procedure, aggregate, or User-Defined type is stored in the targeted database.

Figure 13.11. Using the Server Explorer to view (and manage) SQL Server CLR Executables.

On closer inspection, you can see that the Server Explorer categorizes the executables by intermixing T-SQL functions, stored procedures, and types with CLR executables. This can be a bit confusing, especially since all of the function types are rolled together. Note that while the function parameters are shown, you need to drill into the property page to view the data type.

Tip

Missing from this tree view is any reference to ordinary SQL Server User-Defined types.

Exploring Objects with SQL Server Management Studio

In contrast, the SQL Server Management Studio Object Explorer breaks out functions into categories based on the type of data returned, as shown in Figure 13.12. In this case, functions are broken down by "Table-valued" functions (which return a Table-type variable); "Scalar-valued" functions, which return a single value; "Aggregate" functions, which return an aggregated value; and "System" functions, which are implemented by SQL Server. This approach makes SQL Server Management Studio a more appealing choice for developers who need to manage large numbers of executables.

Figure 13.12. Using the SQL Server Management Studio Object Explorer to view deployed executables.

Note that the SQL Server Management Studio Object Explorer also exposes the executable parameters along with the parameter datatype and default. This explorer also lists the CLR "User-Defined Types" (not to be confused with ordinary SQL Server "User-Defined Data Types").

Managing Executable Dependencies

Remember, I'm exploring existing CLR executables to see if my new CLR function will collide with an existing function (possibly created by someone else on the team). So what if the CLR executable you're about to deploy already exists? Well, in this case, you have some issues to deal with:

• If the executable has been referenced in a stored procedure or other executable, then you need to consider what impact changing or updating the existing executable will have on the functionality of the existing code. You can use the SSMS Object Explorer to determine any dependencieswhich you might miss from a visual inspection. For example, if you right-click on an existing function, you can choose "Show dependencies" to expose the "Object Dependencies" dialog (shown in Figure 13.13).

  Figure 13.13. Viewing object dependencies using SQL Server Management Studio.

  

  In this case, the deployed function is dependent on a stored procedure TextCLRTempFunctions, which might break if you change or drop the function. Does this stop SQL Server from dropping the executable on demand? Nope. SQL Server ignores dependencies when removing functions and stored procedures.

• If the executable is a UDT, then things get even uglier. In this case, dependencies are cast in stone as far as SQL Server is concernedyou won't be able to change a UDT once it's committed to a SQL Server table. This means your test scripts will likely create the test table using the new UDT, populate it with test data, dump its contents with a SELECT (or use it in a stored procedure), drop the table, and delete the UDT. I'll discuss easier ways to do this a bit later in this chapter.

Deploying a CLR Executable Function

If you think it's okay to deploy (your new function won't break existing functionality), you can proceed to deployment. Note that the default Solution Configuration automatically builds and deploys the CLR executable being developed when you click on the green debug arrow or press F5^[6]. During the (build) deploy cycle, the Visual Studio Output (build) window displays messages returned from the compiler as Visual Studio builds and deploys your CLR executable. Note that several files in the assembly are posted to the server, where they're added to the Assemblies in the target database:

^[6] Some keyboard layouts re-assign the function keys. Both the Visual Basic .NET and C# keyboard assignments use F5 to start debugging.

• The compiled executable DLL, which contains the binary executable.

• The project .PDB file, which contains the "program database" that holds debugging information and project state information used by SQL Server to step through the executable during interactive debugging. This file is altered to reflect the type of DLL being deployed (debug or production). Note that production builds cannot be interactively debugged.

• The executable source code file(s). For Visual Basic .NET projects, these are the .vb files; for C# projects, these are the .cs files.

• The assemblyinfo.(vb/cs) file, which names and describes the assembly. This file contains the assembly GUID, copyright, and version information. This file is generated via the project properties page.

It's always a good practice to check the Output Build window to see if the build and deploy phases completed before continuing. Yes, your application should deploy without issue unless there are frozen dependencies that block completion of the process. Of course, this step requires access to the designated SQL Server and sufficient rights to create the executables. Don't take this for granted. Most DBAs don't grant these rights to just anyone.

As you move back and forth from Visual Studio to SQL Server, you'll want to right-click and choose "Refresh" to ensure that just-added objects are visible in the Object and Server Explorers.

Profiling the Deployment Process

A tremendous amount of work goes on behind the scenes when you deploy your CLR executable. It's well beyond your "need to know," as far as I'm concerned, but if you're interested or have some issue that can't seem to be resolved by letting Visual Studio deploy your CLR executable, it might be helpful to understand what's going on behind the curtains when you click "Deploy" (as witnessed by the SQL Profiler):

• The current CLR Assembly is dropped (if it exists).

• The new CLR Assembly is created and the binary code is uploaded to the server.

• The Assembly class's entry points are exposed as functions (or stored procedures or whatever they are).

Yes, you can use the Profiler to watch these operations flash by as you deploy your CLR executable, and you can learn enough about the process to replicate it using your own T-SQL code, if necessary. Some authors would have you think that using T-SQL is the only way to deploy CLR Assemblies, but they're not aware of what Visual Studio can door they don't care. The following steps walk you through the process of creating deploy scripts (leveraging an existing CLR Assembly) to deal with complex deployment issuesor to simply document what you've done. That's something that makes DBAs happy, and we all know how important that is.

1.	T-SQL requires that you drop any previous version of the Assembly before replacing it. Apparently, the ALTER ASSEMBLY does not permit overwriting an existing assembly. Using SQL Server Management Studio, right-click on a CLR assembly that's already been deployed and choose "Script Function" and "Drop to New Query Editor Window". This generates a T-SQL script, as shown in Figure 13.14, that drops the assembly. Don't execute this script just yet. Figure 13.14. The T-SQL script to drop an existing assembly.
2.	Next, we need to create a new assembly. Using SQL Server Management Studio, right-click on a CLR assembly that's already been deployed and choose "Script Function" and "Create to New Query Editor Window". This generates a T-SQL script (shown in Figure 13.15) that creates a new ASSEMBLY. Figure 13.15. Creating a new Assembly from the binary representations of the component files. Note that this script passes the binary representation of the component files. Of course, this would be kinda tough to do if all you have are the files themselves. An easier (and more practical approach) would be to generate the assembly from the component files, as done in Figure 13.16. Note that I moved the project to a shorter path for readability. I don't ordinarily save projects in the C:\ root directoryexcept on long weekends. Figure 13.16. Creating a new assembly from the component files. The CREATE ASSEMBLY operator in T-SQL also permits me to indicate the level of trust I grant to the Assembly. I discuss these trust settings and the potential security risk they impose later in this chapter. Suffice it to say that a PERMISSION_SET SAFE project can do little or no harm. It means the code being executed is not at all trusted to keep out of trouble and cause damage. This is the default security setting.
3.	Next, you'll need to create the entry points for your DLL as functions in SQL Server. This can be done using SQL Server Management Studio by right-clicking on a CLR executable that's already been deployed. Choose "Script Function" and "Create to New Query Editor Window". This generates a T-SQL script (shown in Figure 13.17) that references the CLR executable source file (clsTempConversions.vb) and other configuration files (as previously discussed) already uploaded to the server in the CREATE ASSEMBLY step. Figure 13.17. Scripting an existing CLR executable.

No, as I said, in most cases, you don't need to go through all of this when deploying your CLR executable.