Creating Database Objects in Managed Code | Microsoft Visual Studio 2005 Unleashed

Database objects are commonly implemented using some dialect of the SQL language. This is true with SQL Server as well. SQL Server 2005, however, introduces the capability of authoring SQL objects in managed code. So, instead of using Transact SQL, you can actually write your stored procedures, queries, views, and so on using your favorite .NET language.

The key enabler in Visual Studio that makes this happen is the SQL Server Project. Not to be confused with the formerly discussed Database Project, the SQL Server Project is a language-specific project type that provides class templates for implementing managed code versions of database routines.

Starting a SQL Server Project

In the Add New Project dialog box, SQL Server projects are located under the Database category within each language. Creating a new SQL Server project kicks off the same Add Database Reference dialog box that you have already seen with the Server Explorer and with the Database Project; the new project structure laid down by the project wizard is shown in Figure 15.26.

Figure 15.26. SQL Server Project.

The SQL Server Project directly supports the creation of the following database objects:

Stored procedures
Triggers
Aggregates
User-defined functions
User-defined types

The following sections look at how to go about creating a straightforward stored procedure.

Creating a Stored Procedure in C#

First, you add a stored procedure item to your project by using the Project menu and selecting Add Stored Procedure. A new class will be added to the project. Listing 15.1 shows the base code that shows up within the new class file. You can add your custom code to the static void routine UpdateEmployeeLogin.

Listing 15.1. The Start of a Managed Code Stored Procedure

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public static void UpdateEmployeeLogin()     {         // Put your code here     } };

Managed code objects in SQL Server all leverage the .NET Framework data classes (that is, ADO .NET) to do their work. This means that stored procedures that you write will end up instantiating and using classes like SqlConnection and SqlCommand. The code that you write is identical to data access code that you would write from within any other .NET project type: class libraries, web projects, and Windows forms projects. Because the common denominator is the use of ADO .NET classes, developers don't need to learn another language (like T-SQL) to perform work in the database.

Note

It's outside the scope of this chapter to cover the relative merits or disadvantages of writing your database objects in managed code as opposed to T-SQL. Check out the whitepaper available on MSDN titled "Using CLR Integration in SQL Server 2005" by Microsoft. Although fairly old (it was written in November 2004), it is a great treatment of this subject and is highly recommended reading.

Listing 15.2 shows a fleshed out C# routine that will update the AdventureWorks Employee table with login information. None of this code is complicated and can be easily understood (and written) by anyone with C# data access experience.

Listing 15.2. Managed Code for Updating Employee Login Values

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public static void UpdateEmployeeLogin(SqlInt32 employeeId, SqlInt32 managerId, SqlString loginId, SqlString title, SqlDateTime hireDate, SqlBoolean currentFlag)     {         using (SqlConnection conn =               new SqlConnection("context connection=true"))         {             SqlCommand UpdateEmployeeLoginCommand =               new SqlCommand();             UpdateEmployeeLoginCommand.CommandText =                 "update HumanResources.Employee SET ManagerId = " +              managerId.ToString() +                 ", LoginId = '" + loginId.ToString() + "'" +                 ", Title = '" + title.ToString() + "'" +                 ", HireDate = '" + hireDate.ToString() + "'" +                 ", CurrentFlag = " + currentFlag.ToString() +                 " WHERE EmployeeId = " + employeeId.ToString();             UpdateEmployeeLoginCommand.Connection = conn;             conn.Open();             UpdateEmployeeLoginCommand.ExecuteNonQuery();             conn.Close();         }     } };

One line of code, however, deserves a more detailed explanation. The SqlConnection object is created like this:

SqlConnection conn = new SqlConnection("context connection=true")

The connection string "context connection=true" tells the data provider engine that the connection should be created in the same context as the calling application. Because this routine will be running inside a database, that means you will be connecting to the host database and will run using the context (transactional and otherwise) of the calling application. Because you are piggy-backing on the context of the database that the routine is running in, you don't need to hard-code a full SQL connection string here.

For comparison purposes, Listing 15.3 shows the same update query implemented in T-SQL.

Listing 15.3. T-SQL for Updating Employee Login Values

ALTER PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]     @EmployeeID [int],     @ManagerID [int],     @LoginID [nvarchar](256),     @Title [nvarchar](50),     @HireDate [datetime],     @CurrentFlag [dbo].[Flag] WITH EXECUTE AS CALLER AS BEGIN     SET NOCOUNT ON;     BEGIN TRY         UPDATE [HumanResources].[Employee]         SET [ManagerID] = @ManagerID             ,[LoginID] = @LoginID             ,[Title] = @Title             ,[HireDate] = @HireDate             ,[CurrentFlag] = @CurrentFlag         WHERE [EmployeeID] = @EmployeeID;     END TRY     BEGIN CATCH         EXECUTE [dbo].[uspLogError];     END CATCH; END;

Building and Deploying the Stored Procedure

When you build your SQL Server project, the typical compilation process takes place. Assuming that your code will build, you can now deploy the resulting assembly to the database. Use the Build menu to access the Deploy command.

After the assembly has been deployed, you can test it by calling it from an application or from a query window. For detailed information on how to call managed assemblies and write them, consult the SQL Server 2005 Books Online.