June 18, 2011, 6:35 a.m.
posted by geist
Creating Database Objects in Managed Code
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.
26. SQL Server Project.
The SQL Server Project directly supports the creation of the following database objects:
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.
The Start of a Managed Code Stored Procedure
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.
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.
Managed Code for Updating Employee Login Values
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.
T-SQL for Updating Employee Login Values
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.