Export (0) Print
Expand All

SQL Server Projects 

You can use .NET Framework languages in addition to the Transact-SQL programming language to create database objects such as stored procedures and triggers, and to retrieve and update data for Microsoft SQL Server 2005 databases. Development of .NET Framework database objects for SQL Server using managed code has a number of advantages compared with Transact-SQL. For more information, see Advantages of Using Managed Code to Create Database Objects.

To create a database object, you create a SQL Server project, add the required items to the project, and add code to those items. You then build the project into an assembly and deploy it to the SQL Server.

NoteNote

The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure. From more information, see Enabling CLR Integration.

Creating a New Project

Create a new SQL Server project by clicking the File menu, selecting Project, and then selecting SQL Server Project in the New Project Dialog Box. For more information, see How to: Create a SQL Server Project.

After creating the new SQL Server project, the Add Database Reference Dialog Box is displayed. Use this dialog box to add a database reference, or connection, to the project. You can select a database reference that is currently available in Server Explorer/Database Explorer or define a new connection. Only one database reference can be added to the project.

Project Properties

You can change the Assembly name, which is the name of the output file that contains the assembly manifest. If you change the assembly name, the name of the database object in the SQL Server database is also changed.

Adding Items to the Project

New SQL Server projects contain only references and assembly information. To create database objects, you must first add items to the project and then add code to the items. For more information, see Item Templates for SQL Server Projects.

The following table lists items specific to SQL Server projects that you can add.

Building, Deploying, and Debugging

When you build your project, it is compiled into an assembly that can be deployed to the database that is referenced in the project and debugged.

NoteTip

As an alternative to building, deploying, and debugging in three separate steps, you can use the Start command (F5) or the Start Without Debugging command (CTRL+F5) to build the assembly, deploy it to the database, and debug the database object.

Before you can debug the database object, you must add Transact-SQL code to the Test.sql (debug.sql in Visual C++) item. The script in the Test.sql item is essential for debugging because it performs the actions in the database that are required to start and test your database object. When it is added to your project, the Test.sql item contains an outline of code for a script that performs the required action in the database. If your SQL Server project contains more than one database object, the Test.sql item script must run each of the database objects.

This debug script requirement is different from, for example, a Windows Forms project, which creates and runs an independent executable program; database objects run only in response to actions or calls in the database. For example, a trigger might be activated when a new row is inserted into a table. Therefore, the Test.sql script must insert a new row into a table to activate the trigger. The results of the trigger are displayed in the Output Window in Visual Studio so that you can determine whether the trigger is working correctly.

Next, add code to the item that you have added to your SQL Server project and to the Test.sql item. You can find sample code for each database object in topics about the available database item. See the preceding table.

Building a SQL Server Project

When you build your project, it is compiled into an assembly. If you are using Visual Basic, use the following procedure:

To build a Visual Basic SQL Server project

  1. In Solution Explorer, select the project.

  2. On the Build menu, choose Build<ProjectName>.

If you are using Visual C#, Visual C++, or Visual J#, use the following procedure:

To build a Visual C#, Visual C++, or Visual J# SQL Server project

  1. In Solution Explorer, select the solution.

  2. On the Build menu, choose Build Solution.

Deploying the Assembly to a Database

When you deploy the assembly to the database that is referenced in the project, the connection to the database is made, and then the assembly is copied to the database, where it is registered and attributes are set. Because the assembly is deployed to a specific database on a server, if you create a new database reference to a different database on the same server, the assembly must be deployed to that second database before it can be used.

If you are using Visual Basic, use the following procedure.

To deploy a Visual Basic SQL Server Class Library assembly

  1. In Solution Explorer, select the project.

  2. On the Build menu, choose Deploy<ProjectName>.

If you are using Visual C#, Visual C++, or Visual J#, use the following procedure.

To deploy a Visual C#, Visual C++, or Visual J# SQL Server Class Library assembly

  1. In Solution Explorer, select the solution.

  2. On the Build menu, choose Deploy Solution.

Debugging the Database Object

When you debug a database object, the assembly is built, deployed to the database, and debugged. When you debug a database object that has been built and deployed previously, the assembly is built again only if the project has changed since it was built previously. The assembly is always deleted from the database and copied again.

To debug the database object

  1. In Solution Explorer, select the solution.

  2. On the Debug menu, choose Start.

  3. In the Output window, in the Show output from list, choose Database Output to view the results.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft