Welcome back. In the previous level, Level 5: Development (Using .NET within SQL Server), we looked at the various nuances of SQL Server's CLR Integration feature. Prior to that we learned about what SQLCLR can do, the general structure of it, and the various security mechanisms that affect it. But now, right now, we will FINALLY get our hands dirty (so to speak) by creating and publishing a project (well, in a little bit, actually ;-). If, however, you are already familiar with developing and deploying SQLCLR objects and plan on skipping this level, then please, at the very least, read tomorrow's article — Level 7 — which addresses an important security issue that has been neglected for too long.

The goal of these next three Levels is to leave the reader with an understanding of the various steps and pieces involved in producing a full project. If anyone is going to put the information in this series into practice and create one or more SQLCLR objects at work, then only showing how to get the standard RegEx function to the development DB and doing a few simple tests isn't enough. When we add real code to existing systems we need to deal with reality: we encounter real problems and need to fit into real Software Development Life Cycles (multiple environments, existing automated build processes, stricter security, etc).

With that goal in mind, the flow of this article is structured like an Agile project. We will create a project, deploy it to our development server, test it, go back and refactor to fix any issues, and finally promote the project to the next environment. We will also make use of some features that will allow us to adapt to existing build processes and network setups. Finally, we will take time to handle security issues properly (ASYMMETRIC KEY) instead of taking the easy way out (TRUSTWORTHY ON), and there will be much rejoicing.

While we will not go into great depth on any particular feature, we will be exposed to a good number of features. This extended walk-through will allow you to be more productive in your future SQLCLR endeavors. But, in order to gain this valuable insight, this article had to be longer than usual. So, it is being broken up into 3 days. The 3 separate articles will not be independent and will require the other parts. However, it will be worth working through these three articles, in order, and by the end I believe you will appreciate why this approach was taken.

The Tools

There are a few tools that we will need in order to develop an assembly, namely: Visual Studio (VS), SQL Server Data Tools (SSDT), and SQL Server Express LocalDB (a.k.a. LocalDB).

Visual Studio

If you already have Visual Studio (VS) installed then great. It needs to be at least Visual Studio 2012 (VS2012) due to changes that were made related to how SQL Server projects are handled. If you have Visual Studio 2012 or newer, then you can skip to the SQL Server Data Tools section.

If you work at a company that has an MSDN subscription, or at least uses Visual Studio, then you can possibly ask to have it installed on your computer if there is an available license (and, of course, if it is VS2012 or newer).

The last option is to download and install the free "Community" version of Visual Studio 2015. The "Community" version seems to require free registration to not expire after the 30 day trial. This version is free to individuals for any purpose, or to organizations "in a classroom learning environment, for academic research, or for contributing to open source projects". A link to the full legal terms is provided at the end of this article. Or there is also a link for "Free Visual Studio" in the upper, right-hand corner of that page, and clicking on that link takes you to a page to "Join Visual Studio Dev Essentials". That looks like a great program, though it is unclear what the terms of that program are.

SQL Server Data Tools

SQL Server Data Tools (SSDT) is a set of components and utilities to assist in development and deployment of database projects. It comes with SQL Server Express LocalDB (see below) which provides a convenient dev / testing environment. SSDT allows for easy configuration of the dev / testing environment and handles most aspects of the deployment process (often referred to as "publishing"), including the generating of stand-alone, incremental release scripts. SSDT can do many things, but that is out of scope for this article. Links to more information can be found in the "Additional Reading" section at the end of this article. Technically, SSDT could be installed by itself without Visual Studio (i.e. stand-alone), but given that Visual Studio (at least the version that we need) is free, it is best to have that installed. And it comes with Visual Studio, starting in VS2012 Professional and higher, and then added to additional editions in VS2013.

If you do install VS2015, it should also install the following:

• Microsoft System CLR Types for SQL Server 2014 (12.0.2402.29)
• Microsoft SQL Server Data Tools (14.0.50616.0)

If you already have Visual Studio installed and need to get or update SSDT, keep in mind the following notes from the SSDT Download page ( https://msdn.microsoft.com/en-us/library/mt204009.aspx ):

“A good rule of thumb for choosing a version of SSDT is to target the SQL platform you're using, followed by Visual Studio version as a secondary consideration”

and:

“If possible, install Visual Studio first, followed by SSDT. This helps avoid a situation where newer templates could be overwritten by older templates.”

SQL Server Express LocalDB

SQL Server Express LocalDB, introduced in SQL Server 2012, is a light-weight version of SQL Server Express that replaces User Instances, which were introduced in SQL Server 2005 (see "Additional Reading" section for more info). LocalDB instances are created per each user, and the instance and their databases are installed into a user's local profile directory. By default these instance are private, but they can be shared with other users on the same server.

LocalDB runs as a background process for each user that starts their own private instance rather than as a service that is shared by all users and would have its own security context. Connectivity is only through Named Pipes; external connectivity is not available. However, specifying the correct named pipe to connect to can be problematic due to the instance name changing each time it starts (i.e. instance name = LOCALDB#{8_character_alphanumeric_id}, named pipe = np:\\.\pipe\LOCALDB#{8_character_alphanumeric_id}\tsql\query).

Fortunately, a new special syntax for the Server Name was added to use in connection strings. You can specify (LocalDB)\InstanceName in SSMS, SQLCMD, and via the SqlConnection class in .NET applications (though be aware that this was not available until .NET Framework version 4.0.2). This syntax provides two benefits:

1. It allows for using a consistent server name in your code
2. It will automatically start the LocalDB instance if it is not already running.

LocalDB can be started, stopped, and otherwise managed via the SQLLocalDB.exe utility. Additionally, LocalDB will be automatically started when using the (LocalDB)\InstanceName connection string syntax, and it will automatically stop about 5 minutes (the default value) after the last connection closes.

If you don't already have it, Microsoft SQL Server 2014 Express LocalDB (12.1.4213.0) should be installed when you install SSDT.

Part 1: Let's Get This Party Started

Optional Setup

All of the code for this article (C# code, T-SQL code, T-SQL tests, CMD script, etc) is contained within the article, as well as in the .zip file attached to the bottom of the article in the "Resources" section. There is no need to download and expand the .zip file for any part of this article. However, if you prefer to have the code without needing to copy and paste out of a web page, then please do the following:

1. Create the following folder on your hard-drive:  C:\TEMP\StairwayToSQLCLR
2. Expand the .zip file into the C:\TEMP\StairwayToSQLCLR folder
3. The top-level folder within the .zip file is Level-06, hence:
   1. The "test" scripts will be located in:  C:\TEMP\StairwayToSQLCLR\Level-06
   2. All source files, including "before" and "after" versions of the two files that we will be changing, will be located in:  C:\TEMP\StairwayToSQLCLR\Level-06\Scripts

Final Environment Setup

If you are opening Visual Studio for the first time:

1. It will first ask you to sign in with your MSN / Live account. For now you can just click the "Not now, maybe later." link at the bottom. By not signing in you are limited to a 30-day trial, else there is no time limit.
2. Then it will ask you to choose an "environment". I am using C# in this series of articles so you might pick "Visual C#" from the "Development Settings" drop-down.
3. Now click the "Start Visual Studio" button.

The first thing we need to do within Visual Studio, and we should only have to do this once, is make sure that we have the latest version of SSDT:

1. Go to the "Tools" menu
2. Select "Extensions and Updates..."
3. Expand "Updates" (left nav)
4. Go to "Product Updates"
5. Select the entry for "SQL Server Data Tools" (or whatever has a similar name). If this item is not in the list of updates, then either hit the "esc" key or click the "Close" button to exit.
6. Click the "Update" button within that entry
7. It will download a file to your computer called "SSDTSetup.exe"
8. Run "SSDTSetup.exe" and it will download and install the updated version (as of 2016-01-15 the current version is 14.0.50730.0)
9. Once it is done installing, close and re-open Visual Studio

Brief Project Overview

Now we are reading to build a project. Rather than creating a super-simple stored procedure to just print "Hello World", or even a simple RegEx or string splitter function (there are plenty of examples of those floating around), why don't we create a function to help us test certain aspects of working with SQLCLR. This way we can learn some things while already in the process of learning other things. It's a win-win-win situation (I threw in the extra "win" to be even more positive).

We will be testing what can be done over the in-process Context Connection. To that end, we will be creating a simple-enough scalar function that accepts input parameters for a query to execute and whether or not to use Impersonation. We will run a few tests, come back to make a few changes, and then run a few more tests.

And We're Off...

1. Start by going to the "File" menu, then selecting "New ->", and then select "Project..."  (or just hit <Control + Shift + N>). The "New Project" dialog should now be displayed.
2. In the expandable menu on the left side of the "New Project" dialog, expand "Installed", then expand "Templates", then expand "Other Languages", and finally select "SQL Server". In prior versions of Visual Studio / SSDT, the "SQL Server" group was directly under "Templates".
3. In the drop-down at the top, select the .NET Framework version that corresponds to the minimum version of SQL Server where this code will be deployed. You can see the SQL Server to .NET Framework version mapping in a chart towards the top of the previous Level (Level 5: Using .NET within SQL Server) . This "target framework version" can be changed later.
4. For "Name:" enter in: StairwayToSQLCLR-06_ConnectionTypeTest
5. For "Location:" enter in: C:\TEMP\StairwayToSQLCLR\Level-06 (no part of this path needs to exist ahead of time; any part of it that does not exist will be created by Visual Studio).
6. The "Solution name:" field will auto-populate.
7. There is no need to click on "SQL Server Database Project" as it is the only item in the group.
8. The "Create directory for solution" option should be checked.
9. The "New Project" dialog window should now look similar to:
10. Click the "OK" button.

The next step is to add a .NET code file to the project to be the scalar function. On the right side of the Visual Studio IDE you should see a smaller, dockable window titled "Solution Explorer":

The "Solution Explorer" shows all of the Projects and their items (files, references, etc), and we will be coming back to this window often.

1. In "Solution Explorer", right-click on "StairwayToSQLCLR-06_ConnectionTypeTest" item, just below the top-level "Solution..." item. Go to "Add >", and then select "New Item..." (it should be the top entry in the list). The "Add New Item" dialog should now be displayed:
2. In the expandable menu on the left side of the "Add New Item" dialog, expand "Installed", then expand "SQL Server", then select "SQL CLR C#", and finally select "SQL CLR C# User Defined Function".
3. In the "Name:" field, replace "SqlFunction1.cs" with "ConnectionTypeTest.cs"
4. Click the "Add" button.

The "Solution Explorer" window should now look similar to:

Notice that three items appeared automagically under "References": System, System.Data, System.Xml. These three references are .NET Framework libraries that contain that is at least being used by the SQLCLR API, and most likely will contain some classes used in any custom code.

The main editor window should now be open with the contents of "ConnectionTypeTest.cs":

Paste in the following code to replace the entire current contents of "ConnectionTypeTest.cs" (code also available in the C:\TEMP\StairwayToSQLCLR\Level-06\Scripts\ConnectionTypeTest_v1.cs script):

using System; // DBNull
using System.Data.SqlClient; // SqlConnection & SqlCommand
using System.Data.SqlTypes; // SqlString & SqlBoolean
using System.Security.Principal; // WindowsImpersonationContext
using Microsoft.SqlServer.Server; // SqlFunction, DataAccessKind, & SqlContext
public class Testing
{
    [SqlFunction(Name = "StairwayToSQLCLR_ConnectionTest",
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.Read)]
    public static object ConnectionTypeTest(SqlString SqlToExecute,
        SqlBoolean UseImpersonation)
    {
        object _Output = DBNull.Value;
        WindowsImpersonationContext _ImpersonationContext = null;
        try
        {
            using (SqlConnection _Connection =
                new SqlConnection("Context Connection = true;"))
            {
                using (SqlCommand _Command = _Connection.CreateCommand())
                {
                    _Command.CommandText = SqlToExecute.Value;
                    if (UseImpersonation.IsTrue)
                    {
                        _ImpersonationContext =
                            SqlContext.WindowsIdentity.Impersonate();
                    }
                    _Connection.Open();
                    if (_ImpersonationContext != null)
                    {
                        _ImpersonationContext.Undo();
                    }
                    _Output = _Command.ExecuteScalar();
                }
            }
        }
        finally
        {
            if (_ImpersonationContext != null)
            {
                _ImpersonationContext.Undo();
                _ImpersonationContext.Dispose();
            }
        }
        return _Output;
    }
}

Some notes regarding the C# code shown above:

• The class needs to be "public", else you will be able compile the DLL but it won't generate any T-SQL wrapper objects in order to expose the methods (i.e. functions) in this class.
• The "[SqlFunction(Name = "StairwayToSQLCLR_ConnectionTest", DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]" part is an attribute. This particular attribute, SqlFunction, indicates that this is a SQLCLR function. The "Name" property of this attribute instructs SSDT to use that value instead of the name of this method – ConnectionTypeTest – when generating the T-SQL to create the wrapper object; it is not used by SQL Server. The "DataAccess" and "SystemDataAccess" properties instruct SQL Server to allow access to non-system objects and system objects, respectively; they are not used by SSDT. The values of those two data access properties should match what is returned by the OBJECTPROPERTYEX built-in function when passing in values of "UserDataAccess" and "SystemDataAccess" for the "property" input parameter.
• The return type of "object" equates to a SQL type of SQL_VARIANT.

Next we need to check a few final configuration items before we build and deploy this code for testing. Go to the "Project" menu, and select "StairwayToSQLCLR-06_ConnectionTypeTest Properties...". There are several tabs within "Project Properties" (arranged vertically on the left side), and we will look at most of them, but we will skip "SQLCLR Build", "SQLCMD Variables", "Reference Paths", and "Code Analysis". Opening "Project Properties" will automatically place you in the "Project Settings" tab:

In the main area there is a drop-down for "Target platform". This option controls the SQL syntax used when generating the publish / deploy scripts. It is also used to check the actual version of SQL Server when deploying, which can generate an error if attempting to deploy to an older version of SQL Server than what is specified here (this check can be disabled). For example:

Deploy72002: A project which specifies SQL Server 2014 as the target platform cannot be published to SQL Server 2012.

The option for "Create script (.sql file)" controls whether or not a full, from-scratch, create script is generated. The {prefix}_Create.sql script should always create the database and assembly. Whether or not the "create" script changes any database properties, and whether or not any of the CREATE statements are present for the T-SQL wrapper objects that reference the SQLCLR code, depend on various other options in the other tabs.

Please note that this "create" script is just that: what it takes to create the objects in your project (both SQLCLR and regular T-SQL objects) assuming that the database does not already exist, and this script will drop the database if it does already exist.

This script is not the script used to publish your project, which is why it is optional. The publishing process creates an incremental deploy script that won't drop the current database (unless you enable that option, which is disabled by default) and will only generate the statements needed to bring the target database up to the current state of your project. Please also note that when "Building" (not even "Publishing") a project, a "create" script will always be created (if this option is checked) since the current state of the target database is not relevant when doing a full wipe and replace. However, "Building" the project will not create an incremental deployment script if no changes need to be made.

The value in the "Default schema" field refers to the schema in which the T-SQL wrapper objects will be created (e.g.  "MySchema.MyClrFunction"). This schema must either exist prior to publishing the project, or must be added as an object to the project so that it can be created by the publishing process.

If you click on the "Database Settings..." button, a modal dialog will appear:

Here you can set the specific properties of what the intended target database is, or at least should be. These properties can be used by the deploy / publish process to ensure that the target database's properties match these properties. If you will be deploying SQLCLR objects to an existing database, it would be best to make sure that the settings here match the existing database's settings. The process that publishes your project to the linked dev / test instance will use these settings to set the target database. Hence testing will be more accurate if that testing database matches the Production target database as closely as possible.

For now we will not be making any changes to the "Database Settings" so you can "Cancel" out of that dialog if you had opened it. In the main area in the "Project Settings" tab, check the option for "Create script (.sql file)" so that we can take a quick look at what is produced when we build the project.

Next we will go to the SQLCLR tab.

The "Assembly name" field sets the name of the DLL file that is created, and subsequently the name of the Assembly. If you look closely you might notice that the default name of the Assembly (and of the "Default namespace" which we are not concerned with in this article) does not match exactly the name of the project (as shown on the left side of the title bar on the top). Can you spot the difference? The dash? Correct! We created the project with "...CLR-06_Con..." yet here it is showing as "...CLR_06_Con...". But we are allowed to change it back. And we should probably make the "Assembly name" slightly different by than the project name, by adding a "1" to the end of it, so that we can more easily see where this value is used. If we make both changes then the "Assembly name" should now be: "StairwayToSQLCLR-06_ConnectionTypeTest1".

The selected "Target framework" should match the value that was chosen when this project was initially created. It can be changed here if necessary.

Click on the "Assembly Information..." button, and the "Assembly Information" modal dialog will appear. This information is meta-data stored with the DLL / Assembly. Please fill in the fields as shown below:

Before clicking the "OK" button, take a quick look at the "Solution Explorer" window on the right side (or scroll up to review the image). Notice that the "Properties" item does not have anything under it. Now click the "OK" button in the "Assembly Information" dialog. The dialog will go away and the "Solution Explorer" window now looks like:

Notice that the "Properties" item (it's really a folder) now has a file under it: "AssemblyInfo.cs". And Visual Studio has also automatically opened the "AssemblyInfo.cs" file in the editor, leaving you with:

If the "AssemblyCompany" and "AssemblyCopyright" lines contain the name of the company that manufactured your computer, just replace that company name with something else, such as your name. Looking at the three tabs going across the top of the editor, we can see that there is an asterisk (*) to the right of the "AssemblyInfo.cs" and "StairwayToSQLCLR...onnectionTypeTest" titles, indicating that there are unsaved changes in each of those tabs. Save both tabs at the same time by either clicking the double-disk icon (just below and between the "Project" and "Build" tabs), going to the "File" menu and selecting "Save All", or hitting <Control-Shift-S>. Now close the "AssemblyInfo.cs" tab by clicking the "X" on the right side of that tab.

The active tab should again be "StairwayToSQLCLR...onnectionTypeTest". If not, please select that tab. Continuing on, the "Permission level" drop-down allows for setting the Assembly to one of the following values: SAFE, EXTERNAL_ACCESS, or UNSAFE. For now we will keep the setting at "SAFE".

The "Assembly owner" field should be empty by default. Not setting a value here will cause the Assembly to be owned by "dbo". Even though we will be using "dbo" to own the Assembly, my preference is to still enter in a value of "dbo" here just to have it clearly stated.

The "Generate DDL" option controls whether or not the T-SQL wrapper objects that reference the code in the Assembly will be created. If this option is disabled, Building and Publishing will only include the CREATE ASSEMBLY statement in the generated SQL script.

The "Include Symbols" option controls whether or not the .pdb file, needed for debugging, is deployed to SQL Server along with the Assembly. This option is checked by default since the current / active Configuration is set to "Debug". The Active Configuration can be seen, and set, in the drop-down to the left of the drop-down set to "Any CPU", just under the "Team" menu.

We will skip the "Signing..." button for the moment, but come back to it later in this article.

We are now finished with the "SQLCLR" tab. We will be skipping the "SQLCLR Build" tab, but will come back to it later in this article.

Next we will go to the "Build" tab.

The "Build output path" value is the physical location of the files generated by the Build process. Certain files will always be there, such as the DLL of the Assembly, and if the "Create script (.sql file)" option is checked, then you will also have a "create" SQL script. If you Publish (which includes deploying to dev) the project, then you will possibly have an incremental build script, but only if changes were necessary to get the target database in sync with the project on your machine. By default, the output path contains a folder named for the Configuration that is active when the build process is started. This is why "Debug" shows up in the output path.

The "Build output file name" value is used to name the SQL scripts (i.e. the "create" and/or incremental build scripts). It is also used to name the .dacpac file, which contains database meta-data related to your project. In order to more easily see the relationship between this value and the files that get generated, add a "2" to the end of the "Build output file name" so that it is: "StairwayToSQLCLR-06_ConnectionTypeTest2".

We will skip the "Build Events" tab for the moment, but come back to it later in this article.

Next we will go to the "Debug" tab. The main area has enough options to have a scroll bar. Scroll down and you should see the following:

The "Target Connection String" value is used when Publishing the project to dev for testing. By default it will use a LocalDB instance and the database specified for the "Initial Catalog" keyword was created when we created this project. If you would like to point to a different instance for testing, you can click the "Edit..." button and make any changes in the Connection Properties dialog that will pop up. If you do change the Target instance, you don't need to worry about creating the database since the Publishing process will take care of that for you.

The "Deploy database properties" option controls whether or not SQL statements will be generated in the deployment script(s) to make the Target database conform to the options set in the "Database Settings..." area in the "Project Settings" tab.

If you click on the "Advanced ..." button a modal dialog for "Advanced Deployment Settings" will pop up. These settings allow for fine-tuning the deployment process. We are not going to make any changes here, but you should at least be aware of these options. Please cancel out of that dialog if you opened it.

We are now ready to Build. Go up to the "Build" menu and select "Build Solution" (it should be the top option), or just hit <F6>. An "Output" window should appear, covering the lower half of the IDE. Please note that we never saved the changes that we made on the "Build" tab when we added the "2" to the end of the "Build output file name" value. Yet, if you look at the two tabs, there is no asterisk (*) on either of them. This is because Visual Studio will automatically save all changes before starting the Build process.

What did the Build process do, exactly? Open up Windows Explorer and go to the following directory:

C:\TEMP\StairwayToSQLCLR\Level-06\StairwayToSQLCLR-06_ConnectionTypeTest\StairwayToSQLCLR-06_ConnectionTypeTest\bin\Debug

You should see the following 4 files:

Already we can see which files got their name from the "Assembly name" field on the "SQLCLR" tab, and which files got their name from the "Build output file name" field on the "Build" tab. At this point there is no incremental deployment SQL script since we did not attempt to Publish the project. The "create" SQL script was created, but only because it is not an incremental script. And, our code has not yet been deployed to SQL Server.

Go back to Visual Studio. If you click anywhere in the main area (i.e. above the "Output" window), the "Output" window should collapse to the bottom area of the IDE. We want to get our code into SQL Server, so either go to the "Debug" menu and select "Start Without Debugging", or just hit <Control-F5>, and the code will be published to the LocalDB testing database. If you switch back to Windows Explorer again, you should now see one additional file in that same folder, "StairwayToSQLCLR-06_ConnectionTypeTest2.sql", which is the incremental deployment script.

And, if you take a look at the date column (the"created" and "modified" times should be the same values per each file), the new file has a more recent timestamp, but the other 4 files have not been updated. This is because nothing has changed. But if you resave the .cs file, even without making any changes such that you only updated the modified time of the source code file, and hit <Control-F5> again, it will rebuild the project.

Now would be a good time to take a break, take a nap maybe, get some sweet tea, chillax a bit.

"All work and no play makes Jack a dull boy"

Hopefully you haven't forgotten our fake reason for doing this project (the real reason is, of course, to learn about building these projects, but that's too "meta" for the moment): we want to be able to test certain behaviors of SQLCLR code. And since we spent this much time getting the (small) project into a database, we might as well play with it a little, right?

To start testing, open up SQL Server Management Studio (SSMS) and open a new query tab to the Instance specified as the "Data Source" in the "Target Connection String" on the "Debug" tab of "Project Properties". For me it is "(localdb)\ProjectsV12", but I have upgraded several versions of Visual Studio on my laptop. New installs might use the default instance of "(localdb)\." which might also be "(localdb)\MSSQLLocalDB" (the default instance name changed from being "v11.0" in SQL Server 2012 to "MSSQLLocalDB" in SQL Server 2014). Next, run the following:

USE [StairwayToSQLCLR-06_ConnectionTypeTest];
SELECT assembly_id, name, clr_name FROM sys.assemblies WHERE assembly_id <> 1;
SELECT * FROM sys.assembly_files WHERE assembly_id <> 1;

The first query should return a single row showing a "name" of "StairwayToSQLCLR-06_ConnectionTypeTest1" and a "clr_name" of "stairwaytosqlclr-06_connectiontypetest1, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil". Please note that the name ending in "ConnectionTypeTest1", found in both columns, is taken from the "Assembly name" field on the "SQLCLR" tab of "Project Properties". You can also see that the version showing in the "clr_name" field is just "0.0.0.0", even though we did set some version numbers in the Assembly Info area (we will have to fix that later – Issue #1). And, because we did not sign the Assembly, the "publickeytoken" value in the "clr_name" column is "null".

The second query should return two rows. This table, assembly_files, holds the actual compiled code of each Assembly. This is why there are no external dependencies with SQLCLR code, and how the Assemblies get backed up with the database. The first row, file_id = 1, is the Assembly itself, and the value in the "content" column is the same set of bytes that are in the DLL file. The second row is the .pdb file that is required for debugging, and was deployed to SQL Server because the "Include Symbols" option was checked on the "SQLCLR" tab of "Project Properties".

Copy and paste the following block of queries into SSMS (code also available in the C:\TEMP\StairwayToSQLCLR\Level-06\TestQueries-Set01.sql script). The examples are meant to be tested individually and there are comments in each section to explain what is going on.

USE [StairwayToSQLCLR-06_ConnectionTypeTest];
SELECT assembly_id, name, clr_name FROM sys.assemblies WHERE assembly_id <> 1;
SELECT * FROM sys.assembly_files WHERE assembly_id <> 1;
------------------------------------------------
-- Queries that work when using the Context Connection
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT GETDATE();', NULL);
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT COUNT(*) FROM sys.objects;', NULL);
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
DECLARE @Test INT = 5,
        @Counter INT = 0;
WHILE (@Counter < 10)
BEGIN
  SET @Test *= 7;
  SET @Counter += 1;
END;
SELECT @Test;
';
SELECT dbo.StairwayToSQLCLR_ConnectionTest(@SQL, NULL); -- 1412376245
GO
------------------------------------------------
-- Is the Context Connection part of the same transaction?
-- When the value of "transaction_id" changes, that denotes a different Transaction.
SELECT transaction_id FROM sys.dm_tran_current_transaction;
GO 3
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT transaction_id FROM sys.dm_tran_current_transaction;', NULL);
GO 3

-- transaction_id should be the same across the next 3 SELECTs.
BEGIN TRAN;
SELECT transaction_id FROM sys.dm_tran_current_transaction;
SELECT transaction_id FROM sys.dm_tran_current_transaction;
SELECT transaction_id FROM sys.dm_tran_current_transaction;
ROLLBACK;

-- transaction_id should also be the same across the next 4 SELECTs.
BEGIN TRAN;
SELECT transaction_id FROM sys.dm_tran_current_transaction;
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT transaction_id FROM sys.dm_tran_current_transaction;', NULL);
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT transaction_id FROM sys.dm_tran_current_transaction;', NULL);
SELECT transaction_id FROM sys.dm_tran_current_transaction;
ROLLBACK;
GO
------------------------------------------------
-- Try some operations that cannot be done in T-SQL UDFs
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT NEWID();', NULL);
/*
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "StairwayToSQLCLR_ConnectionTest": 
System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'SELECT WITHOUT QUERY' within a function.
*/SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SET NOCOUNT ON;', NULL);
/*
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "StairwayToSQLCLR_ConnectionTest": 
System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'SET ON/OFF' within a function.
*/CREATE TABLE #StairwayTest (Col1 INT);
INSERT INTO #StairwayTest (Col1) VALUES (1),(10),(100);
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'INSERT INTO #StairwayTest (Col1) VALUES (2),(20),(200);', NULL);
/*
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "StairwayToSQLCLR_ConnectionTest": 
System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'INSERT' within a function.
*/SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT SUM(Col1) FROM #StairwayTest;', NULL); -- 111
/*
  Wait. This one actually worked. Wouldn't this fail in a T-SQL UDF?
*/GO
CREATE FUNCTION dbo.TempTable ()
RETURNS INT
AS
BEGIN
DECLARE @ReturnValue INT;
SELECT @ReturnValue = SUM(Col1)
FROM #StairwayTest;
RETURN @ReturnValue;
END;
GO
/*
Msg 2772, Level 16, State 1, Procedure TempTable, Line 8
Cannot access temporary tables from within a function.
*/-- Why yes. It certainly would fail in a T-SQL UDF. Score 1 for SQLCLR :-)
GO
------------------------------------------------
-- Try some operations that we might not be sure of
SELECT dbo.StairwayToSQLCLR_ConnectionTest(N'SELECT GETDATE();', 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.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib,
       Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at System.Data.SqlServer.Internal.ClrLevelContext.get_WindowsIdentity()
*/-- So we need to set the Assembly to EXTERNAL_ACCESS. In order to do that
-- we will need to sign the Assembly, create an Asymmetric Key from the
-- Assembly, and then create a Login from the Asymmetric Key.
-- (Issue #2)
SELECT dbo.StairwayToSQLCLR_ConnectionTest(NULL, 0);
/*
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "StairwayToSQLCLR_ConnectionTest": 
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException: 
   at System.Data.SqlTypes.SqlString.get_Value()
*/-- We should probably prevent this error from happening.
-- (Issue #3)

END OF DAY 1

( To be continued tomorrow when we start fixing the issues we found today in testing. We will focus mainly on the security issue, learning about Visual Studio features to assist in automation, and using those features to handle security properly and in a manner that can easily be used in your other SQLCLR projects. )

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.