1. Enabling CLR Integration
Before you can learn how to use SQL
CLR features, you need to know how to enable them. As with many new
products in the Microsoft Windows Server system family, most advanced
features of SQL
Server are disabled by default. The reasoning behind this is sound:
each additional feature that is enabled provides extra “surface area”
for attacks on security or integrity of the product, and the added
exposure is simply not justified if the feature goes unused.
The SQL
CLR features of SQL Server 2012 are sophisticated and can be very
useful, but they are also, technically, nonessential. It is possible to
build high-performance databases and server-side programming logic
without SQL CLR integration, so it is turned off by default.
Don’t
be discouraged, though; turning on the feature is easy. Microsoft
provides a system stored procedure for enabling or disabling SQL CLR
integration. Connect to the server you’d like to configure in SSDT or SSMS. Then, from a query window, type the following statements, and execute the script.
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
That’s all there is to it! To disable SQL CLR integration, just use a value of 0, instead of 1, as the second parameter value in the sp_configure call.
Tip
Don’t
forget that this will work from any tool that can connect to SQL
Server, not just SSDT and SSMS. In fact, you could issue the previous
command text from your own code using the ADO.NET SqlCommand object’s ExecuteNonQuery method as long as your code can connect to your server and your server can authenticate you as a user in the sysadmin server role.
With
SQL CLR integration enabled, you’re ready to get started writing SQL
CLR code. Before you dive in though, we need to discuss Visual Studio/SQL Server integration and when to use it.
2. Visual Studio/SQL Server Integration
SSDT
and SQL Server 2012 integrate tightly in a number of ways. It’s
important to realize, however, that the use of SSDT is completely
optional and the use of T-SQL
is a sufficient substitute. With the release of SQL Server 2005, T-SQL
was enhanced with new data definition language (DDL) commands for
maintaining CLR assemblies, types, and aggregates, and its existing
commands for stored procedures, triggers, and functions were enhanced
to recognize code within deployed assemblies. Visual Studio can execute
those commands on your behalf. It can also make writing individual SQL
CLR classes and functions easier.
Ultimately, we think all developers should be aware of both SSDT–assisted
and more manual coding and deployment methods. You might decide to use
one method most of the time, but in some situations you’ll probably
need the other as well, so we want to prepare you. As we cover each
major area of SQL CLR programming, we will discuss deployment from both points of view. We’ll cover some general points about Visual Studio integration now, and then we’ll move on to cover SQL CLR development.
SQL Server Database Projects in Visual Studio
The SSDT
SQL Server Database Project type defines templates for the five basic
SQL CLR entities. These templates inject specific code attributes and
function stubs that allow you to create SQL CLR code easily. The
attributes are used by SSDT to deploy your assembly and its stored
procedures, triggers, and so on to your database. Some of them are also
used by SQL Server to acknowledge and properly use your functions,
UDTs, and aggregates.
To test out the new project type and templates, follow this procedure:
Start
Visual Studio 2010, and then create a new project by choosing File |
New | Project, clicking New Project on the toolbar, pressing Ctrl+Shift+N, or clicking the New Project… link on the Visual Studio Start Page.
In the New Project dialog box, shown in Figure 1, click the expand glyph to the left of the Other Languages node in the Installed Templates tree view on the left, click that node’s SQL Server
child node, and then click SQL Server Database Project in the middle
pane. Enter your own project name if you want, and then click OK.
You
can easily add preconfigured classes for the five basic SQL CLR
entities to your project, but you must first decide whether you wish to
use C# or Visual Basic .NET as the programming language for your SQL
CLR Assembly.
Double-click the Properties
node in the Solution Explorer, and then click the SQLCLR tab in the
resulting property sheet designer. Once inside the SQLCLR tab, select
C# from the Language combo box, as shown in Figure 2.
Now
you’re ready to add a CLR entity to your project. You can do this from
the Add New Item dialog box, which you display by selecting Project |
Add New Item from the main menu or by choosing Add | New Item from the
project node’s shortcut menu in Solution Explorer. If you select the SQL
CLR C# (or SQL CLR VB) template type from the “Installed Templates”
list on the left of the Add New Item dialog box, it should appear as
shown in Figure 3.
After
selecting an entity type, a class template for that type will be added
to your project and opened in the code editor window. Additionally,
references to the System, System.Data, and System.Xml assemblies will be added to the project. These references are required by the stubbed code that appears in the SQL CLR class templates.
Once opened, the use of the SQL Server Database Project template adds a Publish option to the Build option of the Visual Studio main menu, which can be used to deploy the assembly and the SQL CLR entities within it.
SSDT
can do a lot of deployment work for you. But as you’ll learn, you can
perform the same tasks on your own and, in certain circumstances, have
more precise control over the deployment process when you do so.
