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 many 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.
Note
By default, the common language runtime (CLR) integration feature is off in Microsoft SQL Server. It 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. For 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.
Item |
More Information |
|---|---|
Stored Procedure |
|
Trigger |
|
User-Defined Function |
How to: Create and Run a CLR SQL Server User-Defined Function |
User-Defined Type |
|
Aggregate |
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.
Tip
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 important for debugging because it performs the actions in the database that are required to start and test the 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 database object.
This debug script requirement differs from, for example, that of 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 previous table.
Building a SQL Server Project
When you build your project, it is compiled into an assembly. If you are using Visual Basic, follow these steps:
To build a Visual Basic SQL Server project
In Solution Explorer, select the project.
On the Build menu, click Build<ProjectName>.
If you are using Visual C#, Visual C++, or Visual J#, follow these steps:
To build a Visual C#, Visual C++, or Visual J# SQL Server project
In Solution Explorer, select the solution.
On the Build menu, click 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, follow these steps.
To deploy a Visual Basic SQL Server Class Library assembly
In Solution Explorer, select the project.
On the Build menu, click Deploy<ProjectName>.
If you are using Visual C#, Visual C++, or Visual J#, follow these steps.
To deploy a Visual C#, Visual C++, or Visual J# SQL Server Class Library assembly
In Solution Explorer, select the solution.
On the Build menu, click 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
In Solution Explorer, select the solution.
On the Debug menu, click Start.
In the Output window, in the Show output from list, select Database Output to view the results.
See Also
Tasks
How to: Create a SQL Server Project
How to: Create and Run a CLR SQL Server Stored Procedure
How to: Create and Run a CLR SQL Server Trigger
How to: Create and Run a CLR SQL Server Aggregate
How to: Create and Run a CLR SQL Server User-Defined Function
How to: Create and Run a CLR SQL Server User-Defined Type
Walkthrough: Creating a Stored Procedure in Managed Code
How to: Debug a SQL CLR Stored Procedure
Concepts
Introduction to SQL Server CLR Integration (ADO.NET)
Advantages of Using Managed Code to Create Database Objects
Item Templates for SQL Server Projects
Reference
Attributes for SQL Server Projects and Database Objects