SQL Server 2012 : Exploring SQL CLR - Visual Studio/SQL Server Integration

11/12/2013 8:02:20 PM

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


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.


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:

  1. 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.

  2. 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.

    The Visual Studio 2010 New Project dialog box with the SQL Server Database Project type selected.

    Figure 1. The Visual Studio 2010 New Project dialog box with the SQL Server Database Project type selected.

    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.

  3. 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.

    The SQL CLR property sheet with C# selected as the programming language.

    Figure 2. The SQL CLR property sheet with C# selected as the programming language.

  4. 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.

    The Visual Studio SQL Server Database Project Add New Item dialog box, with SQL CLR C# templates displayed.

    Figure 3. The Visual Studio SQL Server Database Project Add New Item dialog box, with SQL CLR C# templates displayed.

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.

Automated Deployment

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.

  •  SQL Server 2008 : DBCC validation - Removing corruption
  •  SQL Server 2008 : Mirroring in action (part 3) - Suspending and resuming mirroring, Initiating failover
  •  SQL Server 2008 : Mirroring in action (part 2) - Monitoring database mirroring
  •  SQL Server 2008 : Mirroring in action (part 1) - Mirroring setup
  •  SQL Server 2008 : High availability with database mirroring - Failover options
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 4) - Iterative Controls
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 3) - List Controls
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 2) - Data-Binding Properties
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 1) - Feasible Data Sources
  •  SQL Server 2008 : Database mirroring overview, Mirroring modes
    Most View
    Microsoft SharePoint 2010 Web Applications : Presentation Layer Overview - Ribbon (part 1)
    The Cyber-athletic Revolution – E-sports’ Era (Part 1)
    Windows Server 2003 : Implementing Software Restriction Policies (part 4) - Implementing Software Restriction Policies - Creating a Path Rule, Designating File Types
    Sql Server 2012 : Hierarchical Data and the Relational Database - Populating the Hierarchy (part 1)
    Two Is Better Than One - WD My Cloud Mirror
    Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 3) - List Controls
    Windows 8 : Configuring networking (part 5) - Managing network settings - Understanding the dual TCP/IP stack in Windows 8, Configuring name resolution
    Nikon Coolpix A – An Appealing Camera For Sharp Images (Part 2)
    Canon PowerShot SX240 HS - A Powerful Perfection
    LG Intuition Review - Skirts The Line Between Smartphone And Tablet (Part 2)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
    Top 10
    Review : Acer Aspire R13
    Review : Microsoft Lumia 535
    Review : Olympus OM-D E-M5 Mark II
    TomTom Runner + MultiSport Cardio
    Timex Ironman Run Trainer 2.0
    Suunto Ambit3 Peak Sapphire HR
    Polar M400
    Garmin Forerunner 920XT
    Sharepoint 2013 : Content Model and Managed Metadata - Publishing, Un-publishing, and Republishing
    Sharepoint 2013 : Content Model and Managed Metadata - Content Type Hubs