Stairway to SQLCLR

Stairway to SQLCLR Level 8: Using Visual Studio to Work around SSDT

,

Welcome back. For those who have been following along, let's pick up where we left off. In Level 6 we put together our test function, deployed it to dev (i.e. our local machine), ran some tests, and encountered a few problems. Then in Level 7 we started fixing those issues, and used various automation features to smoothly handle the proper security setup to allow our Assembly to be set to EXTERNAL_ACCESS. In this Level we will wrap things up by fixing any remaining issues, using additional automation features. Then we will organize the Project a little, create a new LocalDB Instance, and then deploy our project to the new Instance using the main Publish process.

For those who are just now coming into this, the Development topic is spread across 3 Levels, where each article requires any preceding articles in this set of 3. This is not to say that you cannot learn something by reading only this particular article, but that the examples won't work and might not make sense if you don't start with the first article in this set of three Levels – 6, 7, and 8.

PART 3: Refactor Again to Fix Additional Issues

At this point we only have a couple few things left to do. We still need to prevent the error when someone passes in a NULL for the SQL to execute, and now we have a new requirement to add an input parameter to accept a Connection String. Switch back to Visual Studio, open the Project if you had closed it (go to the "File" menu, select "Recent Projects and Solutions ->", and select "C:\...\ StairwayToSQLCLR-06_ConnectionTypeTest.sln").

Issue #3: Exception Occurs When Passing in NULL for @SqlToExecute

The most common way to fix this type of issue is by checking the boolean ".IsNull" property on "SqlToExecute" (all of the Sql* types have this property). Often this is the only way to address this type of issue, but depending on the situation, there might be a better approach. If there is only a single input parameter, or if there are other input parameters but none of them would ever accept a NULL value, then we can make use of the "WITH RETURNS NULL ON NULL INPUT" option of the "CREATE FUNCTION" statement. If this option is enabled, then the function will simply return a NULL, without ever being executed, if a NULL is passed into any of the input parameters. This option is viable for functions that have at least one input parameter that should be able to accept a NULL value.

Unfortunately, just like with the Asymmetric Key and Asymmetric Key-based Login, there is no support in SSDT for setting "WITH RETURNS NULL ON NULL INPUT" (though I have requested it). Once again, we need to get creative. Fortunately, there is a feature that we can use to accomplish this, and it won't be nearly as much work as it was to get the security working.

  1. In the "Solution Explorer" window, right-click on the "StairwayToSQLCLR-06_ConnectionTypeTest" Project, go to "Add >", and select "Script..." (bottom option). The "Add New Item - StairwayToSQLCLR-06_ConnectionTypeTest" modal dialog will pop up.
  2. Select "Post-Deployment Script" in the middle area.
  3. In the "Name" field, replace "Script.PostDeployment1.sql" with "PostDeploy-SetFunctionOptions.sql"
  4. Click the "Add" button.
  5. Replace the pre-populated content with something more to your liking, maybe something similar to: 
    /*
--------------------------------------------------------------------------------------
Post-Deployment Script
--------------------------------------------------------------------------------------
*/
  6. Script out the CREATE FUNCTION definition for the "StairwayToSQLCLR_ConnectionTest" Scalar-valued Function in SSMS by right-clicking on the function in Object Explorer and selecting "Modify".
  7. Copy just the "ALTER FUNCTION" statement that is within the single-quotes.
  8. Back in Visual Studio, paste the "ALTER FUNCTION" statement into the "PostDeploy-SetFunctionOptions.sql" tab.
  9. You can reformat the SQL to look nicer.
  10. On the "WITH EXECUTE AS CALLER" line, add the following: 
        , RETURNS NULL ON NULL INPUT
  11. While we are here, we can add a default value for the @UseImpersonation parameter. Just add "= 0" after "BIT". Please note that:
    1. There is no support in SSDT for setting parameter default values (though I have requested it).
    2. We cannot add a default value for the @SqlToExecute parameter since it is NVARCHAR(MAX), and the blob types -- NVARCHAR(MAX), VARBINARY(MAX), and XML - do not support default values in SQLCLR (I have requested this as well).
  12. Your code should look similar to (code also available in the C:\TEMP\StairwayToSQLCLR\Level-06\Scripts\PostDeploy-SetFunctionOptions_v1.sql script): 
    /*
--------------------------------------------------------------------------------------
Post-Deployment Script
--------------------------------------------------------------------------------------
*/ALTER FUNCTION [dbo].[StairwayToSQLCLR_ConnectionTest]
(
@SqlToExecutee NVARCHAR(MAX), -- defaults not allowed for MAX or XML types
@UseImpersonation BIT = 0
)
RETURNS SQL_VARIANT
WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME [StairwayToSQLCLR-06_ConnectionTypeTest1].[Testing].[ConnectionTypeTest];
  13. Close the "PostDeploy-SetFunctionOptions.sql"  tab
  14. In the "Solution Explorer" window, right-click on "PostDeploy-SetFunctionOptions.sql" and select "Properties" (bottom option). If not already there, the "Properties" window should display just below the "Solution Explorer" window.
  15. The top item in "Properties" should be "Build Action" and it should be set to "PostDeploy". This is how SSDT knows what to do with this file. It is also how you could add a "Not In Build" SQL script, and then go to its "Properties" and change it to be a "PreDeploy" or "PostDeploy" script (or the other way around).

Issue #4: Need Input Parameter For Connection String

  1. Go to the "ConnectionTypeTest.cs" tab if it is still open, or in the "Solution Explorer" window, expand the "StairwayToSQLCLR-06_ConnectionTypeTest" Project and double-click on "ConnectionTypeTest.cs"
  2. Replace the block of code that starts with the first line in the new block below, and ends with the last line in the new block below. Assuming your file has line #1 as "using System; // DBNull", then the block to replace should be line #12 – line #21. After replacing the block of code, what was line #21 should be line #30 (code also available in the C:\TEMP\StairwayToSQLCLR\Level-06\Scripts\ConnectionTypeTest_v2.cs script). 
        public static object ConnectionTypeTest(SqlString SqlToExecute,
        [SqlFacet(MaxSize = 300)] SqlString Connection, // added for Issue #4
        SqlBoolean UseImpersonation)
    {
        object _Output = DBNull.Value;
        WindowsImpersonationContext _ImpersonationContext = null;
        // added for Issue #4 : BEGIN
        string _ConnectionString = Connection.Value.Trim();
        if (_ConnectionString == String.Empty)
        {
            _ConnectionString = "Context Connection = true;"; // default
        }
        // added for Issue #4 : END
        try
        {
            using (SqlConnection _Connection =
                new SqlConnection(_ConnectionString)) // changed for Issue #4
  3. Hit <Control-S> to save.

Some notes:

  • We do not need to worry about someone passing in NULL for "@ConnectionString" causing an error since the fix we did for Issue #3 disallows any NULL s from being sent in.
  • The "[SqlFacet(MaxSize = 300)]" attribute tells SSDT to generate an input parameter type of "NVARCHAR(300)" instead of the default "NVARCHAR(MAX)". Please note that in earlier versions of SSDT, the default T-SQL datatype generated for "SqlString" was "NVARCHAR(4000)" and only "NVARCHAR(MAX)" for "SqlChars". Now both "Sql*" types generate "NVARCHAR(MAX)".

Minor change

Let's say someone wants to restrict execution on just the SQLCLR objects. You could certainly manage the permissions through grants to each particular object. But if you have a lot of objects, or don't want to worry about someone forgetting to add the "GRANT" statement, then SQL Server does provide a mechanism for grouping objects together to manage permissions in one place: Schemas. We can add a Schema named "SQLCLR" and put our objects there. This is very easy to do and does not require a Pre-Release SQL script. SSDT manages many types of objects quite well, including relationships and dependencies between them.

  1. In the "Solution Explorer" window, right-click on the "StairwayToSQLCLR-06_ConnectionTypeTest" Project, go to "Add >", and select "New Item..." (top option). The "Add New Item - StairwayToSQLCLR-06_ConnectionTypeTest" modal dialog will pop up.
  2. On the left side, "SQL Server" should be selected by default. It should be an unfiltered list of all templates in the categories below it. You can either scroll down the list in the middle and select "Schema", or you can select the "Security" category and then scroll down and select "Schema".
  3. In the "Name" field, replace "Schema1" with "SQLCLR".
  4. Click the "Add" button. You should be placed into a new tab in the IDE named "SQLCLR.sql" that has just the "CREATE SCHEMA" line in it.
  5. Close the "SQLCLR.sql" tab.
  6. In the "Solution Explorer" window, right-click on the "StairwayToSQLCLR-06_ConnectionTypeTest" Project, and select "Properties".
  7. Go to the "Project Settings" tab.
  8. In the middle area, under "General", in the "Default schema" field, replace "dbo" with "SQLCLR".
  9. Hit <Control-S> to save.

Manually Update ALTER FUNCTION in PostDeploy Script

Before we publish the changes, we need to fix the ALTER FUNCTION statement that we placed into the PostDeploy SQL script as the fix for Issue #3. We added a new input parameter so the function definition no longer matches. And we changed the Schema that the function is in. This is the main drawback to that type of "solution", but I am not aware of any other way to set such options at the moment.

  1. Find "PostDeploy-SetFunctionOptions.sql" in the "StairwayToSQLCLR-06_ConnectionTypeTest" Project in the "Solution Explorer" window and double-click on it. It should open a tab for editing that file in the IDE and place the cursor in there.
  2. On the "ALTER FUNCTION" line, change "[dbo]" to be "[SQLCLR]".
  3. Add the following line of code between the two input parameters: 
        @Connection NVARCHAR(250) = '', -- default to empty string

    (code also available in the C:\TEMP\StairwayToSQLCLR\Level-06\Scripts\PostDeploy-SetFunctionOptions_v2.sql script)

  4. Hit <Control-S> to save.
  5. Close the "PostDeploy-SetFunctionOptions.sql" tab.

Please note that here we are setting the datatype of @Connection to be NVARCHAR(250), whereas we specified via "[SqlFacet(MaxSize = 300)]" in the C# code that the datatype should be NVARCHAR(300). This difference is designed to help make it clear that if you ever change the signature of the method, you will probably have to update this Post Deploy SQL script.

Helper Function

It looks like we are going to need (or at least want) a simple function to assist with our testing efforts.

  1. In the "Solution Explorer" window, right-click on the "StairwayToSQLCLR-06_ConnectionTypeTest" Project, go to "Add >", and select "New Item..." (top option). The "Add New Item - StairwayToSQLCLR-06_ConnectionTypeTest" modal dialog will pop up.
  2. On the left side, "SQL Server" should be selected by default. It should be an unfiltered list of all templates in the categories below it. You can either scroll down the list in the middle and select "Scalar-valued Function", or you can select the "Programmability" category and then select "Scalar-valued Function".
  3. In the "Name" field, replace "DatabaseScalarFunction1" with "GetLocalServerNameForConnectionString".
  4. Click the "Add" button. You should be placed into a new tab in the IDE named "GetLocalServerName...nectionString.sql" that has a "CREATE FUNCTION" template in it.
  5. Replace the function template with the following T-SQL (code also available in the C:\TEMP\StairwayToSQLCLR\Level-06\Scripts\GetLocalServerNameForConnectionString.sql script): 
    CREATE FUNCTION dbo.GetLocalServerNameForConnectionString (@Template NVARCHAR(1000))
RETURNS NVARCHAR(1000)
AS
BEGIN
    DECLARE @ServerName NVARCHAR(1000);
    SET @ServerName = @@SERVERNAME;

    IF (CONVERT(BIT, SERVERPROPERTY('IsLocalDB')) = 1)
    BEGIN
        -- np:\\.\pipe\LOCALDB#SH666D8E\tsql\query
        -- "SH" at the beginning means "Shared Instance"
        SET @ServerName = N'np:\\.\pipe\' +
                          CONVERT(sysname, SERVERPROPERTY('InstanceName')) +
                          N'\tsql\query';
    END;
    IF (ISNULL(@Template, N'') <> N'')
    BEGIN
        SET @ServerName = REPLACE(@Template, N'{{ServerName}}', @ServerName);
    END;
    RETURN @ServerName;
END;
  6. The "GetLocalServerNameForConnectionString" function will help us avoid a SQLCLR bug that disallows connecting to LocalDB instance via the "(localdb)\..." syntax unless either the Assembly is marked as "UNSAFE" (ours is marked as EXTERNAL_ACCESS) or an AppDomain property that nobody knows about is set.
  7. Hit <Control-S> to save.
  8. Close the "GetLocalServerName...nectionString.sql" tab.

Publish the Changes

  1. Hit <Control-F5> to Build and Publish the project to the dev / test LocalDB instance

That's it. Seriously.

Test the Changes

  1. Go to SSMS.
  2. Open a query tab, connecting to the LocalDB instance.
  3. Copy and paste the following tests into the new query tab, but only run one test at a time (code also available in the C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06-TestQueries-Set03.sql script): 
    USE [StairwayToSQLCLR-06_ConnectionTypeTest];

SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT @@SERVERNAME + N'' : '' + ORIGINAL_LOGIN() + N'' : '' + SESSION_USER;', 0);
/*
Msg 313, Level 16, State 2, Line 30
An insufficient number of arguments were supplied for the procedure or function dbo.StairwayToSQLCLR_ConnectionTest.
*/SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT @@SERVERNAME + N'' : '' + ORIGINAL_LOGIN() + N'' : '' + SESSION_USER;', '', 0);
-- Still works as expected.
------------------------------------------------
-- Try some operations that we might not be sure of
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT @@SERVERNAME + N'' : '' + ORIGINAL_LOGIN() + N'' : '' + SESSION_USER;', '', 1);
/*
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "StairwayToSQLCLR_ConnectionTest": 
System.InvalidOperationException: Data access is not allowed in an impersonated context. 
*/-- This error is expected as it is what we received in the last series of tests.
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT @@SERVERNAME + N'' : '' + ORIGINAL_LOGIN() + N'' : '' + SESSION_USER;',
                                              N'Server=(localdb)\.',
                                              0);
/*
Msg 6522, Level 16, State 1, Line 53
A .NET Framework error occurred during execution of user-defined routine or aggregate "StairwayToSQLCLR_ConnectionTest": 
System.Security.SecurityException: Request failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
   at System.Security.PermissionSet.Demand()
   at System.Data.LocalDBAPI.DemandLocalDBPermissions()
*/-- This error is due to a "bug" in the SQLCLR environment that prohibits using the new "(localdb)\{instance_name}"
-- syntax by default, most likely because the LocalDB API starts an external process (i.e. SQL Server Express
-- LocalDB) if it is not already started. While this syntax actually does work if the Assembly is marked as UNSAFE,
-- there is an App Domain setting that can be enabled to allow this syntax to work when the Assembly is marked as
-- EXTERNAL_ACCESS. Unfortunately, setting that option requires that the Assembly be marked as UNSAFE. The "bug" is
-- that this App Domain option is supposed to be enabled by default. I submitted a Connect item to fix this. But
-- for now, if we want to connect to a LocalDB instance from within SQLCLR, and without marking the Assembly as
-- UNSAFE, then we need to use named-pipes.
GO
SELECT dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}') AS [ConnectionString];
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT @@SERVERNAME + N'' :: '' + ORIGINAL_LOGIN() + N'' :: '' + SESSION_USER;',
                           dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}; Trusted_Connection=true;'),
                           0);

SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT @@SERVERNAME + N'' :: '' + ORIGINAL_LOGIN() + N'' :: '' + SESSION_USER;',
                           dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}; Trusted_Connection=true;'),
                           1);
-- no difference when running LocalDB since it is a background process, not a service, and runs
-- as the Windows Login that stared it. Running on any other edition would show a difference in
-- most cases.
---------
-- Does NULL for @SqlToExecute cause an error:
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(NULL,
                           dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}; Trusted_Connection=true;'),
                           0);
GO

-- What about NULL for @ConnectionString:
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT GETDATE();',
                           NULL,
                           0);
GO

-- What about DEFAULT values:
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SELECT GETDATE();',
                           DEFAULT,
                           DEFAULT);
GO

-- What about EXEC values:
DECLARE @Return SQL_VARIANT;
EXEC @Return = SQLCLR.StairwayToSQLCLR_ConnectionTest N'SELECT GETDATE();';
SELECT @Return;
GO
---------
-- Create a table
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'CREATE TABLE #Test(Col1 INT); SELECT OBJECT_ID(N''tempdb..#Test'');',
                           dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}; Trusted_Connection=true;'),
                           0);
GO

-- Call NEWID() and SET
SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(N'SET NOCOUNT ON; SELECT NEWID();',
                           dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}; Trusted_Connection=true;'),
                           0);
GO
---------
IF (OBJECT_ID(N'dbo.TransactionTest') IS NULL)
BEGIN
    PRINT 'Creating dbo.TransactionTest table...';
    CREATE TABLE dbo.TransactionTest
    (
      TransactionTestID INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_TransactionTest] PRIMARY KEY,
      [Value] NVARCHAR(50) NULL,
 InsertTime DATETIME NOT NULL CONSTRAINT [DF_TransactionTest_InsertTime] DEFAULT (GETDATE())
    );
END;
-- run once, just to get some data in the table:
INSERT INTO dbo.TransactionTest ([Value]) VALUES (N'first!');
SELECT * FROM dbo.TransactionTest;
-- run through the "GO". Let's see if the remotely-executed INSERT is Rolled-back:
BEGIN TRY
    BEGIN TRAN
    INSERT INTO dbo.TransactionTest ([Value]) VALUES (N'before call to StairwayToSQLCLR_ConnectionTest...');
    SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(
                 N'INSERT INTO [StairwayToSQLCLR-06_ConnectionTypeTest].dbo.TransactionTest ([Value]) VALUES (NEWID());',
                           dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}; Trusted_Connection=true;'),
                           0);
    INSERT INTO dbo.TransactionTest ([Value]) VALUES (N'after call to StairwayToSQLCLR_ConnectionTest...');
    SELECT * FROM dbo.TransactionTest;
    ROLLBACK TRAN;
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN;
    END;
    DECLARE @Error NVARCHAR(4000);
    SET @Error = ERROR_MESSAGE();
    RAISERROR(@Error, 16, 1);
    RETURN;
END CATCH;
/*
Msg 50000, Level 16, State 1, Line 164
A .NET Framework error occurred during execution of user-defined routine or aggregate "StairwayToSQLCLR_ConnectionTest": 
System.Data.SqlClient.SqlException: MSDTC on server 'DALI\LOCALDB#SH666D8E' is unavailable.
*/-- MSDTC = Microsoft Distributed Transaction Coordinator. Hence, it is attempting to bind the remote DML statement to
-- the current transaction.
GO

-- Run through the "GO" again. But this time, we added "Enlist=false" to the ConnectionString:
-- First, check the table:
SELECT * FROM dbo.TransactionTest;
BEGIN TRY
    BEGIN TRAN
    INSERT INTO dbo.TransactionTest ([Value]) VALUES (N'before call to StairwayToSQLCLR_ConnectionTest...');
    SELECT SQLCLR.StairwayToSQLCLR_ConnectionTest(
                 N'INSERT INTO [StairwayToSQLCLR-06_ConnectionTypeTest].dbo.TransactionTest ([Value]) VALUES (NEWID());',
                           dbo.GetLocalServerNameForConnectionString(N'Server={{ServerName}}; Trusted_Connection=true; Enlist=false;'),
                           0);
    INSERT INTO dbo.TransactionTest ([Value]) VALUES (N'after call to StairwayToSQLCLR_ConnectionTest...');
    SELECT * FROM dbo.TransactionTest;
    ROLLBACK TRAN;
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN;
    END;
    DECLARE @Error NVARCHAR(4000);
    SET @Error = ERROR_MESSAGE();
    RAISERROR(@Error, 16, 1);
    RETURN;
END CATCH;
SELECT * FROM dbo.TransactionTest;
-- The two statements run natively here were rolled-back, but the remote DML statement
-- did not get rolled-back. If MSDTC was running, the remote DML statement would also
-- get rolled-back.
GO

Part 4: Prepare for Deployment to Production

Now that we have tested (and assured everyone that, of course, QA has signed-off on it), we can deploy the code to another environment. But before we do that, maybe there are a few minor things to clean up or change. Switch back to Visual Studio, open the Project if you had closed it (go to the "File" menu, select "Recent Projects and Solutions ->", and select "C:\...\ StairwayToSQLCLR-06_ConnectionTypeTest.sln").

Make the Changes

The remaining items on our to-do list are: organize the files in our project, make sure that the code (i.e. the Assembly) is optimized by the compiler, and set up a "Production" instance. Fortunately, all three items are quick and easy.

Organize Code Files

During the course of this project we have added several files to our Solution. However, each time we added a file, it was created in the main folder for one of the two Projects. This is probably fine for just a few files, but beyond that you are better off with at least some separation of files. We will skip doing anything with the "KeyInfo" Project since once it is created, there is really no reason for it to change.

  1. In the "Solution Explorer" window, expand the "StairwayToSQLCLR-06_ConnectionTypeTest" Project so that you can see its items.
  2. Left-drag the "OrdinaryFile_DefinitelyNOTaKey.pfx" file up to the "Properties" folder and release the left mouse button.
  3. Right-click on the main "StairwayToSQLCLR-06_ConnectionTypeTest" Project, go to "Add >", and select "New Folder" (3rd from the top). A new folder will be created in "Solution Explorer" and it will be highlighted, ready to edit.
  4. Replace the default name of "NewFolder1" with "Deployment".
  5. Left-drag both "PostDeploy-SetFunctionOptions.sql" and "PreDeploy-GrabSecuritySetup.sql" to the new "Deployment" folder.
  6. Your "Solution Explorer" window should now look like the following:

    39_SolutionExplorer-AfterOrganizingFiles

Make Sure Code is Optimized

In order to promote this code to the Test / UAT or Production environment, we need to change the compiler settings. Of course, the code will still function in the other environments even if we don’t make this change, but it is something that we should change.

  1. In the "Solution Explorer" window, right-click on the "StairwayToSQLCLR-06_ConnectionTypeTest" Project and select "Properties" (bottom option).
  2. Go to the "SQLCLR Build" tab.
  3. In the middle area you should see an option for "Optimize code" that is not checked. For Production-level code, that is an option that we do want to take advantage of. And so we could check that box, but we won't. We don't need to.
  4. In the menu bar at the top you should see a drop-down with just the word "Debug" in it. This drop-down is probably just under the "Team" menu, but definitely above the tabs. In that drop-down, select "Release".
  5. Look at the "Optimize code" option again; it should be checked now. And if you didn't notice, the option above that one for "Define DEBUG constant" is now unchecked whereas it was previously checked:

    40_ProjectProperties-SQLCLRBuild-ReleaseConfig

  6. Just so that the next thing is easier to see, go ahead and set the drop-down that you just changed to be "Release" back to "Debug".
  7. Go to the "Build" tab. If you were wondering, nothing on this tab has changed since we added the "2" to the end of the "Build output file name" value.
  8. Again, please change the drop-down showing "Debug" to instead have "Release" selected.
  9. Two things changed: the values for both "Build output path" and "Build output file name":

    41_ProjectProperties-Build-ReleaseConfig

  10. The "2" is no longer at the end of the "Build output file name". The difference in the filename won't affect anything as we don't have any process expecting that name. However, if we did have some process picking up the final output file, then we would either make sure to use a consistent name across the Configurations, or if possible, we would use the macros that we saw (and are using) in the "Build Events" tab.
  11. Close the Project Properties tab.

You might not have noticed it before, but the drop-down for the "Active Configuration" (which should be currently set to "Release") is still there. You can change between "Debug" and "Release" whenever you want, and without needing to go to Project Properties. We only went to Project Properties so that it would be easier to see the relationship between the "Active Configuration" drop-down and what the effects would be when we changed it. Configurations are incredibly helpful for managing projects that have variations based on environment and/or target system. With a single code-base, you can change the Target .NET Framework version, what files are part of the build, what sections of code (or anything, really) in various files should be included. The "Debug" and "Release" Configurations are just the defaults, but you can remove/rename them, or add more Configurations. And the build process can be fully automated using MSBuild (which makes use of the Configurations), T4 templates, SqlPackage, etc.

Create Mock "Production" Instance

We need, at least in terms of this walk-through, a fake "Production" instance to deploy the project to. We can just create another instance of LocalDB.

  1. Open up a Command Prompt (i.e. "CMD.EXE").
  2. Run the following command: 
    SqlLocalDB create StairwayToSQLCLR -s
  3. You should get output similar to: 
    LocalDB instance "StairwayToSQLCLR" created with version 12.0.4213.0.
LocalDB instance "StairwayToSQLCLR" started.

    Or, the version number might be just "12.0".

  4. Run the following command to see some basic properties and status of this new instance: 
    SqlLocalDB i StairwayToSQLCLR
  5. Close the Command Prompt window.

Publish the Changes to Production

  1. Switch back to Visual Studio
  2. Go to the "Build" menu and select "Publish StairwayToSQLCLR-06_ConnectionTypeTest...". The "Publish Database" dialog should appear.
  3. Click the "Edit..." button. The "Connection Properties" dialog should appear.
  4. In the "Server Name" drop-down/field, enter in (type or paste):

    (localdb)\StairwayToSQLCLR

  5. Click the "OK" button.
  6. The "Target database connection" string should be filled out now. It should look similar to:

    42_PublishDatabase-FilledOut

  7. Click the "Publish" button.
  8. In SSMS, open a new query tab, connecting to "(localdb)\StairwayToSQLCLR"
  9. Test away 🙂

Automation / Continuous Integration

While the topic of automation and Continuous Integration is very important due to many projects needing to work with existing automated build process, it is unfortunately too large of a topic to address in this already lengthy article. The various options for publishing SQLCLR projects will be its own Level, but it won't be the next Level since we first need to cover, individually, the five SQLCLR object types: Stored Procedures, User-Defined Functions, User-Defined Aggregates, User-Defined Types, and Triggers. However, for those who need or want this information more immediately, I have included links to information on the SqlPackage utility and T4 text templates in the "Additional Reading" section, under "Publishing". The SqlPackage utility is used to handle publishing, either immediately or generating the SQL script of required changes. T4 text templates allow for dynamically generating and updating files, either as part of the build process or at any point in time via the command line utility.

Summary

Developing and deploying CLR objects can be a bit confusing, but Microsoft Visual Studio and SSDT reduce a lot of the complexity. And SQL Server Express LocalDB is a wonderful, light-weight testing platform. During the course of this article we covered a lot of ground. We did the following:

  1. Created a Project (which also creates the Solution to contain the initial Project)
  2. Saw where to set the desired properties of the Target database (wherever the Project is Published to)
  3. Published to a development environment.
  4. Added another Project to the Solution
  5. Signed an Assembly
  6. Learned how to avoid setting a database to TRUSTWORTHY ON by creating an Asymmetric Key and a Login based on that Key
  7. Used the Pre- and Post- Build Events
  8. Used macros for dynamic filename and pathname substitution in Build Event scripts
  9. Created Pre- and Post- Deployment SQL scripts
  10. Added regular, non-SQLCLR objects to the Project (a Schema and a User-Defined Function)
  11. Ensured the proper Project build order by setting the Project Dependencies
  12. Learned how to use Configuration Manager to disable a Project from participating in a Publish operation
  13. Used an item from another Project (main Project used .pfx file from KeyInfo Project) without making a copy of it
  14. Changed the Schema for the SQLCLR objects
  15. Saw that the Assembly is loaded into sys.assembly_files
  16. Saw that the "Include Symbols" option controls whether or not the .pdb file is also loaded into sys.assembly_files
  17. Saw some of what Configurations control
  18. Changed the Active Configuration
  19. Added a folder to the Project
  20. Moved some files around in the "Solution Explorer"
  21. Worked around certain limitations with SSDT by using a Post-Deploy SQL script to issue an ALTER FUNCTION
  22. Saw that "Impersonation" both requires EXTERNAL_ACCESS and cannot be used with the Context Connection
  23. Saw that by default, a regular/external connection will attach to the current transaction, if one exists
  24. Created a new instance of LocalDB using SqlLocalDB.exe
  25. Viewed the properties and status of a LocalDB instance using SqlLocalDB.exe
  26. Published to our mock "Production" environment

Hopefully you are now better equipped to interact with people already working on SQLCLR projects as well as being comfortable enough to do a SQLCLR project yourself, which will include working with your team on practical issues such as integrating the release script into the existing build process, and possibly dealing with the need to set your Assembly to EXTERNAL_ACCESS in a Production environment where setting the database to TRUSTWORTHY ON is not an option.

Additional Reading

Tools:

SQL Server Commands, Catalog Views, and Built-in Functions:

Publishing:

Miscellaneous:

About the Author

Solomon Rutzky has been working with databases for YEAR(GETDATE()) - 1996 years, focusing on SQL Server since 2002. He has written several articles for SQL Server Central, including the Stairway to SQLCLR series, and SimpleTalk. Solomon is owner / founder of Sql Quantum Leap, and is the creator of the popular SQLCLR library of functions: SQL#. He does what he can to help others by answering questions on a couple of StackExchange sites and Ask.SqlServerCentral.com.

This article is part of the parent stairway Stairway to SQLCLR

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Related content

Stairway to SQLCLR

Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

In the second level of our Stairway to SQLCLR, we look at how to enable the SQLCLR in SQL Server. We then build an assembly, store procedure, and a function that can be called from your T-SQL code.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (6)

You rated this post out of 5. Change rating

2020-07-09 (first published: )

26,423 reads

Stairway to SQL Server Indexes

Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2

By this stage, you should be familiar with the basics of SQL Server indexes. We've discussed what an Index actually is, as well as some of the most common types you're likely to encounter. Now that we've seen some simple examples of how Indexes can be useful, we're going to delve deeper into nonclustered indexes, as we'll see how they can improve the performance of more complex queries.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2022-11-16 (first published: )

37,539 reads

Stairway to T-SQL Beyond The Basics

Stairway to T-SQL: Beyond The Basics Level 3: Building a Correlated Subquery

This stairway level will expand on the subquery topic by discussing a type of subquery known as a correlated subquery, and explores what a correlated subquery is and how it is different from a normal subquery.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating

2019-05-08 (first published: )

20,387 reads