Using the Context Connection in SQLCLR
Obviously, one of the most common data access scenarios is when you want a CLR stored procedure or function to connect to the same server on which it's executing. One option is to create a connection using SqlClient, specifying a connection string that points to the local server, and open the connection. That works, but it creates a separate
connection. As a result, you have to specify credentials for login, you'll create a different database session, there's a separate transaction context, the connection won't be able see your temporary tables, etc. But remember that your stored procedure or function code is running inside SQLCLR because some application already connected
to this SQL Server and executed a SQL statement to invoke it. To use that same connection, you'll need to access the context connection.
The context connection, naturally enough, lets you execute SQL statements in the context in which your code was invoked. To obtain the context connection you simply need to use the new "context connection" connection string keyword, as shown in the example below:
using(SqlConnection conn = new SqlConnection(
// Use the context connection
|Making a connection to the same server in which your SQLCLR code is running is as simple as creating a new SqlConnection with a "context connection=true" string parameter.|
That's the actual code requiredit's that simple. This release also marks a change from the earlier architecture that included a separate SqlServer and SqlClient namespace, and is designed to simplify the coding experience and to help developers leverage as much of their syntax knowledge from ADO.NET as is possible going forward.
Choosing Between T-SQL and .NET
When choosing between T-SQL and .NET code, your primary concern should be choosing the most appropriate tool for the job. First, here's an example where a SQLCLR function is appropriate to extend functionality in SQL Server. What if you wanted to implement a function in SQL Server to validate a string? Normally, you might use a regular expression for in a .NET application for such a validation, but in this case, you need to run this code as part of a SQL script to process and clean some data. Here's an example of a function (see the downloadable code
) deployed to SQL Server to enable the use of regular expressions:
public static bool ValidatePhoneNumber(
validate = false;
validate = true;
After deploying this function to the server using the Visual Studio integration features for SQLCLR deployment, you can access the function using a simple T-SQL statement such as:
SELECT [AdventureWorks].[dbo].[ValidatePhoneNumber] (
The preceding statement will evaluate to true
, but if the phone number were improperly formatted it would evaluate to false
. Building this kind of functionality in T-SQL would not only be tedious and error-prone, but would also be extremely difficult to debug and would inevitably suffer from performance-related issues.
Not a Panacea
But SQLCLR isn't a panacea, and developers should not use the new features indiscriminately. Although using the new functionality may be interesting, developers will want to be careful when evaluating the features and determining whether or not to take advantage of them when designing and building new applications. Because T-SQL is specifically designed for direct data access and data manipulation, it should still be your first choice for writing code that revolves primarily around data access scenarios and set-based operations.
In addition, while T-SQL excels at data access and database management functions, it is not a fully featured programming language. For example, T-SQL does not provide support for collections, arrays, for each
loops, or for organizing code into classes. Some of these features can be simulated in T-SQL, but managed code has integrated support for all of those features, and may provide a better solution. Here are a few key things to think about when evaluating .NET code versus T-SQL for your applications:
|Because T-SQL is specifically designed for direct data access and data manipulation, it should still be your first choice for writing code that revolves primarily around data access scenarios and set-based operations.|
- Does the application require the data to be manipulated before the results can be consumed or displayed? If so, lean towards .NET code and SQLCLR.
- Does the application rely on advanced set-based operations, including the ability to pivot or sort the data in different ways? If so, lean towards T-SQL.
- Does the application require the use of extensive computation or custom algorithms as part of the result set calculations? If so, lean towards .NET code and SQLCLR.
- Does the application require development support from DBAs or others who may not be familiar with .NET languages, or does it leverage significant investments in existing T-SQL code? If so, lean towards T-SQL.
Depending on your scenario, the additional features provided by managed code may provide a compelling reason to implement your database logic using managed code. In addition, you can take advantage of the object-oriented features of .NET languages such as C# or VB.NET and of features such as inheritance, polymorphism, encapsulation, and organizing code into classes separated into namespaces. If you're working on a large project with a significant amount of code that should run on the server, these features can help you organize and manage the associated development and maintenance processes. Managed code is also better for calculations and complicated logic than T-SQL, and features extensive support for many complex tasks such as large string manipulations and regular expressions that T-SQL does not support directly.
|At this point, you should probably restrict your use of managed code to CPU-intensive operations and procedures that feature complex logic, and to code that can benefit from the extensive set of class libraries that are part of the .NET Framework.|
The Base Class Library (BCL) in the .NET Framework includes classes that provide functionality for advanced arithmetic operations, cryptography, file access and manipulation, and much more. Another benefit of managed code is type safety, which provides assurance that code accesses types in defined and permissible ways. Before executing managed code, the CLR will verify that the code is safe. The CLR can help ensure that your database code does not manipulate unmanaged memory. However, you should use common sense and make sure that you're making good use of your existing code investments and building an application that can be supported by a broad number of people. Don't jump to managed code simply because it is new.
Best practices dictate using T-SQL when the code will perform primarily data access with little or no procedural logic. At this point, you should probably restrict your use of managed code to CPU-intensive operations and procedures that feature complex logic, and to code that can benefit from the extensive set of class libraries that are part of the .NET Framework.