C. Visual Studio 2005 Integration: SQL Server Projects

THROUGHOUT THE BOOK, everything we have done can be accomplished with nothing more than the command line, a text editor, the command-line compilers, and the command-line utilities that ship with SQL Server 2005. Most developers today, however, are accustomed to using productivity-enhancing development tools—code editors with IntelliSense, tools to define database schemas, and so on. In fact, Microsoft is renowned for its development tools and Integrated Development Environments (IDEs).

Visual Studio 2005

The primary development tool for the great majority of developers working with SQL Server 2005 is Visual Studio 2005. Visual Studio has always had solid integration with SQL Server; with the 2005 releases of SQL Server and Visual Studio, the integration is enhanced by an order of magnitude. Attempting to describe all the integration points among the products would comprise an entire book by itself. In this book, we’ll enumerate most of the integration points and cover one feature in depth: Visual Studio 2005 SQLCLR projects (known as SQL Server Projects).

Some of the Visual Studio 2005 integration is in the SQL Server 2005 tool set. SQL Server Business Intelligence Development Studio is installed with SQL Server, and if Visual Studio 2005 itself is installed on the same machine, the two products will be integrated. SQL Server Business Intelligence Development Studio exposes the following new project types:

• Analysis Services Project

• Import Analysis Services 9.0 Database

• Integration Services Project

• Report Server Project Wizard

• Report Model Project

• Report Server Project

SQL Server Management Studio itself, which is covered in Appendix B, has a Visual Studio 2005 “look and feel” and is built from the same basic framework (it is referred to as an appid of Visual Studio), although it is a stand-alone tool. Visual Studio 2005 Solutions (collections of Projects) cannot include SQL Server Management Studio Projects, and vice versa.

The Visual Studio 2005 product itself (and the comprehensive Visual Studio Team System) contains quite a few integration points with SQL Server 2005 on its own. These include:

• SQL Server Projects for the C#, Visual Basic .NET, and Managed C++ languages.

• Server Explorer.

• Database Projects for SQL Server.

• Designers that work with Toolbox components in Visual Web Designer Projects, Windows Forms Projects, and other projects.

• An XML editor with IntelliSense. If the appropriate XML schemas are referenced, it includes XML schema–based IntelliSense and schema validation.

• An XML Schema editor and an XML Schema inference tool that infers an XML Schema from an XML document.

• An XSLT editor and debugger.

• A SQL Server Extended Stored Procedure Project for C++.

Although database application development projects usually include many of these features, coverage of all these project types is beyond the scope of this book. In general, these projects must support SQL Server 2000 and 7.0 in addition to SQL Server 2005. Visual Studio Database projects include a few enhancements for SQL Server 2005, such as support for database synonyms. The graphic query designer, however, always creates SQL statements using one-part object names at this writing. Because SQL Server 2005 separates users and schemas, this means that you need to hand-edit the queries after generation or that every object must be resolvable using one-part names. Database Projects, in conjunction with Server Explorer, provide support for creation, source control, maintenance, and debugging of the following Transact-SQL (T-SQL) objects:

• Tables

• Views

• Stored Procedures

• Triggers

• T-SQL Scripts

The bulk of this appendix describes the support in Visual Studio 2005 for developing, deploying, and debugging SQL Server 2005 objects written in .NET Framework languages. This is the SQL Server Project project type for managed programming languages and works in conjunction with Server Explorer. In addition, you can always use the Class Library project type, but this project type will not expose automatic deployment or integrated debugging. Custom attributes in the code enable the automatic deployment feature.

Visual Studio 2005 has some new project types for C#, Visual Basic .NET, and Managed C++: the SQL Server Project. You use this kind of project to create CLR functions, stored procedures, triggers, user-defined types, and user-defined aggregates. Figure C-1 shows how to create a new project in Visual Studio 2005.

Figure C-1. Choosing to create a new project

Choosing New > Project will bring up the New Project dialog box, shown in Figure C-2. Note that in this figure, a C#/Database project has been selected and the mouse is ready to click SQL Server Project. Below the Visual Basic .NET and Managed C++ entry in the tree on the right is a similar entry.

Figure C-2. C# database project

At the bottom of the dialog box, you can select your project and solution names, as well as the directory where you want them to reside.

Clicking the OK button in the New Project dialog box will bring up the Add Database Reference dialog box, shown in Figure C-3. Use this dialog box to select a database to deploy the code you are going to write to.

Figure C-3. Add database reference

The Add Database Reference dialog box includes a list of the current data connections that you have configured through Server Explorer. You can also add a reference by clicking the Add New Reference button, which presents the New Database Reference dialog box. This is the same dialog box you’d work with if you added a new data connection in Server Explorer, with the exception that the Microsoft SQL Server (SqlClient) data provider is already selected.

If you add a new reference, a corresponding data connection will be added to Server Explorer.

After you choose a database reference, Visual Studio 2005 will create a project. Its appearance is configurable. If Server Explorer and Solution Explorer are not visible, as they are in Figure C-4, choose View > Server Explorer and View > Solution Explorer, respectively, to display them. This figure shows Server Explorer on the left.

Figure C-4. Server and solution explorers

Debugging must be enabled on the data connection. You can enable it by right-clicking, in Server Explorer, the connection you want to do debugging on, as shown in Figure C-5.

Figure C-5. Enabling debugging

Keep in mind that by enabling debugging, you will affect the other users of that instance of SQL Server 2005.

Now that we have chosen the database reference, the setup of the project is completed. The project consists of three folders: Properties, References, and Test Scripts, as shown in Figure C-6. The Properties folder contains an AssemblyInfo.cs module that contains the suggested attributes for the assembly. The References folder contains the commonly used reference assemblies, System.dll, System.Data.dll, and System.Xml.dll.

Figure C-6. Solution explorer in a C# SQL Server project

Before writing any code, you need to add a new item, which you can do by right-clicking the project name in Solution Explorer. Adding a new item presents a selection of adding a user-defined function, user-defined type, user-defined aggregate, user-defined stored procedure, or user-defined trigger. Figure C-7 shows a user-defined function being added to the project.

Figure C-7. Adding user-defined function

In Figure C-7, we choose the User-Defined Function template, which we give the name Math. That produces a source file called Math.cs, containing a partial public class, UserDefinedFunctions, and a static public function, Math. Visual Studio 2005 creates a skeleton that you can add code to, as shown in Figure C-8.

Figure C-8. Code skeleton

Custom Attributes and Automatic Deployment

SQL Server projects differ from Class Library projects in a few ways:

• There is an extra tab named Database in the Properties page.

• The Add Reference dialog box produces a different choice of libraries to use.

• The Build menu contains a Deploy command.

• There is a Test Scripts folder.

We’ll discuss the Add Reference difference first. In Chapter 2 (SQLCLR) and Chapter 6 (Security), we expounded on the fact that only a subset of the .NET Framework class libraries is “approved” for loading into SQL Server. Only these assemblies appear in the Add Reference dialog box.¹ In addition, assemblies that have already been catalogued in the database appear in the list.

Automatic Deployment

When you choose Build > Deploy, Visual Studio 2005 will build SQL Server DDL statements, connect to the database that you specified when creating the project, and submit the DDL to SQL Server. Depending on which type of SQL Server objects your project contains, the DDL statements include

• CREATE ASSEMBLY

• ALTER ASSEMBLY with ADD FILE option

• DROP ASSEMBLY

• CREATE/DROP PROCEDURE

• CREATE/DROP FUNCTION

• CREATE/DROP TRIGGER

• CREATE/DROP TYPE

• CREATE/DROP AGGREGATE

Note that Visual Studio 2005’s automatic deployment does not, for safety reasons, support ALTER ASSEMBLY except to add files. Automatically deploying the project will use ALTER ASSEMBLY only to add files related to the assembly. All source-code files are added; this gives the DBA the ability to reconstruct the assembly if necessary. Visual Studio 2005 also deploys the debug symbol files (.pdb files), which are required for debugging inside SQL Server. If any database objects reference the items in the assembly or reference the assembly itself, automatic deployment will fail, with a generic error message. References that can cause an automatic deployment to fail include use of a user-defined type as a column in a table or a user-defined function specified in the definition of a computed column.

The exact DDL statements used are based on the project properties and custom attributes on the code. In the Database tab, you can specify whether the PERMISSION_SET clause of CREATE ASSEMBLY should be Safe, External (EXTERNAL_ACCESS in DDL), or Unsafe. Safe is the default for a project, as it is the default in SQL Server. CREATE ASSEMBLY works with the assembly_ bits option rather than using the assembly file. This alleviates permission problems. The connection string of the data connection indicates which SQL Server principal will be used, and this principal must have the appropriate permission in SQL Server, as discussed in Chapter 2. The Output window indicates which assemblies and files were deployed but not which objects, such as stored procedures and triggers, were deployed or the exact DDL generated by automatic deployment. To view the automatic-deployment DDL, you must use a SQL Profiler trace. You can also query the database after the fact.

When you add a new SQLCLR item to your SQL Server Project, you’ll notice that your class or method is decorated with a custom attribute. The attribute is different for each type of item. These attributes live in the Microsoft.SqlServer.Server namespace in System.Data.dll, and a C# using statement is included so that the short class name can be used in the code. Table C-1 shows a list of custom attributes.

Table C-1. Custom Attributes on SQLCLR Items

All these attributes are used for automatic deployment—that is, creating SQL DDL statements when the Deploy command is selected. Some of the attributes are used only for automatic deployment. If you do not use automatic deployment, these attributes are not required. Some of the attributes are used both for deployment and to indicate behaviors of the code to SQL Server. In Chapter 3, for example, we discussed the SqlFunction attribute and how you use this attribute to inform SQL Server about how the function behaved. Each attribute is used for automatic deployment, and each attribute has different properties that are used to construct the deployment DDL.

Data Types and SqlFacet

When using automatic deployment, the data type of the SQL Server parameter or return code that should be specified in the DDL statement is inferred from the type of the .NET Framework method parameter or return code. The mapping of data types to SQL Server types follows the same rules that the SqlClient data provider uses. See Books Online for a complete list and Chapter 3 for an explanation of, and the repercussions of, using SqlTypes instead of “vanilla” .NET Framework data types in method declarations. When you are using automatic deployment of procedures and functions, there are three places where the SQL Server type system requires more accuracy than type inference provides. The .NET Framework System.String class does not specify a maximum length, as the SQL Server does. Also, .NET Framework System.String does not have the capability to require a string to be a fixed length—that is, to specify the difference between the VARCHAR and CHAR data types. If you use String or SqlString in the declaration of a user-defined function or stored procedure, NVARCHAR(4000) is always used at automatic deployment. The final limitation is that the .NET Framework System.Decimal data type is a variable precision and scale data type. You cannot specify the precision and scale of a DECIMAL data type when your procedure or function is defined in SQL Server. SQL Server’s DECIMAL data type is a fixed precision and scale data type. The default value of DECIMAL(18,0) is always used.

The SqlFacet attribute is a custom attribute that allows you to overcome these limitations. It can be used on fields, properties, return values, and parameters. This attribute can be used with all the SQLCLR objects where it makes sense—that is, stored procedures, user-defined functions, user-defined types, and user-defined aggregates. When the methods have been deployed, the behavior in SQL Server follows the ordinary conversion and truncation rules of T-SQL.

Table C-2 shows the fields on each of the custom attributes that are used for deployment, their meaning, and whether the attribute is used only for deployment.

Table C-2. Custom Attributes Properties on SQLCLR Items

Executing and Debugging the Code

To test the code, we can always execute the procedure or function created in the database from inside SQL Server Management Studio. It would be nice if this could also happen from inside Visual Studio 2005, however, and this is where the Test Scripts folder comes in. The Test Scripts folder contains one script, Test.sql, when a project is created. This is the SQL script that will be executed if you choose Debug > Start or press F5. You can add more test scripts to the folder. You can execute any script by right-clicking the script and choosing the Debug Script command. You can also make any script the default debugging script.

If you open the Test.sql file, you’ll see that it contains a series of commented SQL statements. You can uncomment the statement that looks similar to the item you are testing or write test code from scratch. Using the Test Scripts folder enables you to keep your SQLCLR source code and the scripts you use to test it in the same project location. When you are debugging SQLCLR procedures in Visual Studio 2005, the procedure output appears in the Output window.

It is not necessary to run the project from inside Visual Studio 2005 to debug the CLR code. If the debug symbols of the CLR assembly have been loaded into SQL Server, the procedure or function can be executed from inside SQL Server and stepped through in the Visual Studio .NET debugger. To make this work, you need to attach the debugger to the SQL Server process by following these steps. You must have additional permissions on the machine where SQL Server is installed for this to work, and it’s useful mostly when you’re debugging an instance of SQL Server installed on your own desktop.

1. Choose Debug > Attach to Process.
2. In the Attach to Process dialog box, choose the sqlservr.exe process, as shown in Figure C-9.
   

   Figure C-9. Attach to process dialog box

   

3. Make sure that the Show Processes from All Users checkbox is checked.
4. Set breakpoints in the CLR source file.
5. Execute the T-SQL code.

You can also use Visual Studio 2005 to debug T-SQL procedures and functions. To accomplish this directly, in Server Explorer, right-click the procedure or function you want to debug, and choose Step into Stored Procedure or Step into Function.

Visual Studio 2005 gives you the facilities to integrate programming, testing, and debugging SQL Server 2005 code whether that code is written in T-SQL or SQLCLR. Your in-server development can take advantage of the same rich developer environment that you are used to if you use databases in client-side code. You can even have a Solution that consists of one or more Database Projects, one or more SQL Server Projects for SQLCLR code, and one or more client code projects. The integrated debugger even works when you step from client code into T-SQL or SQLCLR code, step through the code, and step back into client code. This provides complete coverage for both server and client development.