Programming Microsoft SQL Server 2005: Overview of SQL CLR - Visual Studio/SQL Server Integration

1/22/2011 4:25:38 PM
Visual Studio 2005 and SQL Server 2005 integrate tightly in a number of ways. It’s important to realize, however, that the use of Visual Studio integration is completely optional and the use of T-SQL is a sufficient substitute. T-SQL has been enhanced with new DDL commands for maintaining CLR assemblies, types, and aggregates, and its existing commands for stored procedures, triggers, and functions have been 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 Visual Studio–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, 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 Projects in Visual Studio

The combination of Visual Studio 2005 and SQL Server 2005 on the same development machine provides a special SQL Server Project type in Visual Studio and, within projects of that type, defined 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 Visual Studio 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, user-defined types (UDTs), and aggregates.

To test out the new project type and templates, start Visual Studio 2005 and create a new project by using the File/New/Project... main menu option, the New Project toolbar button, the Ctrl+Shift+N keyboard accelerator, or the Create Project... hyperlink on the Visual Studio Start Page. In the New Project dialog box (Figure 1), select Database from the Project types tree view on the left (the Database node appears under the parent node for your programming language of choice; in Figure 1, the language is C#), and click the SQL Server Project icon in the Templates list on the right. Enter your own project name if you’d like, and click OK.

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

Next, the Add Database Reference dialog box appears (Figure 2).

Figure 2. The Add Database Reference dialog box

Because Visual Studio provides automated deployment of your SQL CLR code, it must associate your project with a specific server and database via a database reference (connection). Any database connections that have already been defined in the Server Explorer window appear in this window, as does an Add New Reference button that allows you to define a new connection, if necessary. Pick an existing connection or define a new one, and then click OK. The project opens.


If no data connections have already been defined in the Server Explorer window, the New Database Reference dialog box will appear in place of the Add Database Reference dialog box. In the New Database Reference dialog box, you may specify server, login, and database details for a new database connection that will be used by your project as its database reference and added to the Server Explorer as a new data connection.

You can easily add preconfigured classes for the five basic SQL CLR entities to your project. You can do this in a number of ways: directly from the Project menu or from the Add submenu on the Server Explorer’s project node shortcut menu (Figure 3).

Figure 3. The Server Explorer project node shortcut menu and its Add submenu

You can also add the preconfigured classes from the Add New Item dialog box (Figure 4), which is available from the Project/Add New Item... option on the main menu, or the Add/New Item... option on the Solution Explorer project node’s shortcut menu.

Figure 4. The Visual Studio SQL Server project Add New Item dialog box

Automated Deployment

Once opened, SQL Server projects add a Deploy option to the Visual Studio Build menu. In addition, the Play (Start Debugging) button and the Start Debugging, Start Without Debugging, and Step Over options on the Debug menu (and their keyboard shortcuts F5, Ctrl+F5, and F10, respectively) all deploy the project assembly in addition to performing their listed function.

Visual Studio can do a lot of deployment work for you. But as you’ll learn, you can do so on your own and, in certain circumstances, have more precise control over the deployment process when you do so.

SQL CLR Code Attributes

A number of .NET code attributes are provided for SQL CLR developers; these are contained in the Microsoft.SqlServer.Server namespace. Many of them are inserted in your code when you use the various templates in the SQL Server project type, as is a using statement for the Microsoft.SqlServer.Server namespace itself. If you choose to develop code without these templates, you must add the appropriate attributes, and optionally the using statement, yourself. Although all these attributes are provided in the same namespace, some are used exclusively by Visual Studio and others are used by both Visual Studio and SQL Server.

Covering all SQL CLR attributes and their parameters would itself require an entire chapter, so our coverage will be intentionally selective. Specifically, we will provide coverage of the SqlProcedure, SqlFunction, SqlTrigger, SqlUserDefinedAggregate, and SqlUserDefinedType attributes. We will not cover the SqlFacet and SqlMethod attributes.

Just as certain attributes are not covered here, we cover only some of the parameters accepted by the attributes that we do cover. And in some cases, we cover only certain of the possible values that can be passed to these attributes. For example, SqlFunction accepts several parameters but the only ones we will cover are Name, FillRowMethodName, and TableDefinition. For SqlUserDefinedAggregate and SqlUserDefinedType, we will cover only a single value setting for the Format parameter, and will not cover the several other parameters those two attributes accept.

The coverage we provide will be more than sufficient for you to implement basic, intermediate, and certain advanced functionality with all the basic five SQL CLR entities. The attributes and parameters that we won’t cover are useful mostly for optimizing your SQL CLR code, and they are well documented in SQL Server Books Online and articles on MSDN.

Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Top 10
Free Mobile And Desktop Apps For Accessing Restricted Websites
TOYOTA CAMRY 2; 2.5 : Camry now more comely
KIA SORENTO 2.2CRDi : Fuel-sipping slugger
How To Setup, Password Protect & Encrypt Wireless Internet Connection
Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
Backup & Restore Game Progress From Any Game With SaveGameProgress
Generate A Facebook Timeline Cover Using A Free App
New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th