Better Together

Downloads
46104.zip

The repeated delay of the releases of SQL Server 2005 and Visual Studio 2005 gives DBAs and developers time to work with the beta releases of both products and start to learn how the products' new tools will affect application development. Don't wait until the new products are released to begin designing and planning software that uses their new features; start now. When Microsoft releases SQL Server 2005 and Visual Studio 2005, you'll have applications that can immediately take advantage of the new features these products will offer.

Given the literally dozens of new features, though, developers might have trouble knowing which ones to try using first. Three enhancements in SQL Server 2005 and Visual Studio 2005—the addition of Multiple Active Result Sets (MARS), integration of the .NET Common Language Runtime (CLR) environment, and updates to the Transaction namespace—are specially designed to make the products work together compatibly and effectively. Each of these enhancements directly affects application development in a unique way. MARS provides for improved scalability by reducing the number of connections needed, CLR integration lets developers and DBAs securely extend the capabilities of the database, and the updates to the Transaction namespace finally make automatic transactions seem automatic. These tools are designed to help developers create more flexible applications and help DBAs support applications that take advantage of database capabilities beyond just accessing a collection of rows. Let's look at how you can use each of these new features in applications you're developing today.

The examples in this article work with Visual Studio 2005 Beta 2 and SQL Server 2005 Beta 3 and employ the AdventureWorks sample database that ships with SQL Server 2005. I simplified the configuration of my connection-string by installing SQL Server 2005 and Visual Studio 2005 on the same machine. All the included code samples also work with the SQL Server 2005 February CTP and Visual Studio 2005 February CTP. To make these examples functional, I've provided a set of stored procedures and other custom information that you can install to the default AdventureWorks database by using the code in Web Listing 1, which you can download at InstantDoc ID 46104.

Mission to MARS

One of the better features of SQL Server 2005 is MARS (also one of the best acronyms to come out of Redmond lately). The basic idea behind MARS is that you shouldn't be limited to carrying out sequential commands across a database connection. Instead, a connection should allow for simultaneous processing of multiple commands. MARS lets developers create multiple active database commands across one connection or (more importantly) have multiple result sets or server-side cursors operating simultaneously.

The nicest thing about this new capability is that it requires no server setup (but it does require a change in how developers approach related database commands). Its main drawback is that it's limited to SQL Server 2005, so you can write code that leverages the MARS paradigm only when you know that your database will be in SQL Server 2005.

The advantages of MARS are not only related to performance and scalability but to the simplicity of code. The code at callout A in Listing 1 creates a single connection object, which then creates a reader and an update command. When the reader executes and retrieves each entry, the updated command for that entry is executed. This entire process takes place over one connection. If you attempt to simultaneously access data and process updates with Visual Studio 2003 or SQL Server 2000, you'll find that this kind of simple sequential update based on a server-side cursor doesn't work. (Note that you'll need to remove the Visual Studio 2005 transaction logic for the code in Listing 1 to compile in Visual Studio 2003.)

The CLR in SQL Server

Whereas MARS is an enhancement that causes few concerns and requires no configuration, the CLR is another story. The integration of the CLR in SQL Server 2005 is arguably the most debated SQL Server enhancement. It's also arguably one of the most powerful enhancements. The challenge with the CLR is to understand when to use it—and when not to use it.

The CLR in SQL Server doesn't replace T-SQL. Microsoft marketing information might tell you that you can now use .NET languages exclusively. But in technical presentations, you might hear statements about how T-SQL still outperforms the CLR for most queries. With these two seemingly conflicting messages, the technical community at Microsoft is giving you not a warning about the CLR but solid advice about how to properly use the CLR in SQL Server 2005.

The CLR provides several specific advantages over T-SQL. The first advantage is in creating functions that are processing-intensive. The CLR lets you have functions that encapsulate some business calculation within the database. When you use such functions primarily for calculating (instead of simply retrieving) data, the CLR performs better than T-SQL. The second advantage of the CLR in SQL Server is that it lets you handle complex XML. Third, the CLR lets you create custom User Defined Types (UDTs). Last, the CLR lets you replace inherently unsafe extended stored procedures. Unlike an extended stored procedure, which reaches beyond the control of SQL Server, the default setting for the CLR ensures that your custom T-SQL extensions run within the current security context. (For more information about how to choose when to use the CLR, see the articles by Matt Nunn and Vinod Kumar in Related Reading.) Let's look at how you can create a custom UDT for use in an application.

Because none of the CLR's capabilities is required for a fully functional database, SQL Server 2005 ships with the CLR disabled. So the first step to leveraging the CLR is enabling it. To do so, paste the following standard T-SQL sp_configure stored procedure into a Query window in SQL Management Studio.

sp_configure 'CLR Enabled', 1
GO
RECONFIGURE
EXEC sp_configure

This T-SQL statement enables the CLR and returns your current database-configuration settings. For more information about the sp_configure command, review the Microsoft documentation listed in Related Reading.

Once you've enabled the CLR, you're ready to start building your UDT in Visual Studio 2005. You can use either Visual Basic or C# code; for this example, I use Visual Basic. You start by using the SQL Server Project template in the Visual Basic project types list to create a new project. After you name your project (I named the example SQLServerUDT), Visual Studio asks you to add a database reference, as the dialog box in Figure 1 shows. In the dialog box, you specify the provider to use (in this case, the .NET Framework Data Provider for SQL Server). Then, in the New Database Reference dialog box that Figure 2 shows, you identify a specific development database. Typing (local) in the Server name text box identifies your local SQL Server 2005 instance, which Visual Studio can then associate with the project.

Once you have a new project, you add a class to represent your UDT. Right-click your project and select Add User Defined Type... from the context menu. You'll see a new dialog box containing several class types that you can add to your project. Select the default User Defined Type class, and change the default filename to myPointType.vb. Clicking OK will generate the code that Listing 2 shows.

Note that the default template generates code for your class, including the private properties of your new class at the bottom of the class. Because of space constraints, I won't explain here how to implement the point type; you just need to know that the point type will consist of two integers that you can store as one value by using this UDT. Listing 3 shows how the myPointType.vb source file looks once you give the point type this simple implementation. Note that in the sample implementation, I've followed what I consider to be typical implementation, using Regions that you can collapse so that you can group related parts of your type implementation.

Now that you have an updated class, the next step is to compile the class. The resulting file will have a .dll extension and will be available for inclusion in any project that will reference this custom type; however, you must first deploy the new type to SQL Server. You can accomplish the deployment in two ways. Visual Studio's Deploy option works when you're using your development database, but not with a production database. To use this method, simply go to the Build menu and select Deploy, and Visual Studio automatically compiles and deploys your UDT to your referenced SQL Server database.

The second deployment method, which works whether you're installing your UDT in a development or a production database, is to use T-SQL. Web Listing 2 shows the T-SQL commands to install your UDT in SQL Server. Note that Web Listing 2 includes the directory path on my local machine; you'll need to customize this path for your own machine. Both SQL Server and your custom application need to reference this assembly to recognize the type.

To use the new UDT, simply reference your new type, then create a parameter for your stored procedure of type UDT, as follows:

' Create a parameter of type UDT.
Dim paramUDT = updateCommand.Parameters.Add("@v", SqlDbType.Udt)
paramUDT.UdtTypeName = "myPointType";
paramUDT.Value = new myPointType(4, 22);

This quick introduction gives you the basics for using the .NET CLR to create a custom UDT that your database and your application will recognize. As you gain experience with the CLR, you can learn to leverage the capabilities of XML to further extend your custom data types.

Improving Distributed Transactions

When you're working in .NET and connecting to SQL Server in pre-2005 versions of Visual Studio, if you want a transaction that spans multiple data sources (aka an automatic transaction), you need to put your .NET components in the Enterprise Services .NET namespace, then manually integrate your .NET assemblies with COM+. Visual Studio 2005 simplifies this manual process by introducing a new namespace called System.Transactions. (You can find documentation of this namespace at http://msdn2.microsoft.com/library/system.transactions.aspx or in the .NET Framework Class Library section of MSDN.) The System.Transactions namespace has three main classes that developers will use to manage transactions: Transaction, TransactionScope, and TransactionManager.

In .NET 1.1, you can use the Connection object to call the BeginTransaction method. When you use this method on a SQL Server connection, the Connection object returns a System.Data.SqlClient.SqlTransaction object. You can then associate that object with each of the commands that will participate in the transaction. Recall that SQL Server provides implicit transactions, so you need a transaction object only to encompass the scope of multiple commands.

When you call the BeginTransaction method, ADO.NET uses your connection to call SQL Server and execute the T-SQL Begin Transaction command. Thus, to use the BeginTransaction method, you must sequentially use the same connection object for each command that will participate in the transaction—and this type of transaction isn't compatible with COM+ transactions.

The first thing to know about using the System.Transactions namespace is that you'll still be able to create a manual transaction on SQL Server without using the BeginTransaction method. The transaction you create can still be considered manual because you code the start and finish of the transaction as well as associating the transaction with each command that will participate.

Visual Studio 2005's System.Transactions.Transaction object provides a way for you to manually enlist a command into a transaction. The code at callout B in Listing 1 contains an example of how to manually create a System.Transactions.CommittableTransaction object. (The CommittableTransaction object is one of two implementations of the virtual Transaction base class.) The code creates a Connection object and opens the connection. Once you have an open connection to the database, the connection object uses your CommitableTransaction object to enlist a transaction. This is the start of your transaction, and you can then execute one or more database commands that will be part of this transaction. Finally, when you finish accessing the database, you can either commit or roll back your transaction.

Although the ability to manually associate your new transaction object with a connection is similar to existing transaction logic, behind the scenes, you've actually changed the transaction paradigm. The code at callout B actually creates a distributed transaction, which in Visual Studio 2003 would require Enterprise Services. Notice that this project doesn't reference the System.EnterpriseServices namespace. In Visual Studio 2005, you need to include only the System.Transactions library as part of your project references to create transactions that span multiple data sources. Not needing to use Enterprise Services means that your code is simpler and that you don't need to integrate your assembly with COM+. Now you can create within .NET a distributed transaction that spans multiple connections. Remember, too, that your connection object must remain open—although dormant—until the transaction is complete, as shown by the order of the commands in the sample code.

Although the System.Transactions.Transaction object simplifies managing distributed transactions, Visual Studio 2005 goes further and provides the tools to automate distributed transactions. The TransactionScope object provides a way to automatically associate with the current transaction each connection object created within its scope. As the code at callout C in Listing 1 shows, the code for using the TransactionScope object is in some ways even simpler than the code for manually creating a System.Transaction.Transaction object. The code at callout C starts by using a new Visual Basic language element, the Using command. You should always create the TransactionScope object within the context of a Using command to ensure that you've explicitly defined the scope of the transaction. After doing so, you can create and open as many connections as you want, and each command that executes within the context of your active transaction scope is automatically associated with your current transaction.

The code at callout C could create multiple transactions, but because the example uses only one data source, I left only one connection active. The code opens the connection and executes an Update command. The next step is to set the transaction's status. Note that setting the status is different from committing your transaction. When you work with the TransactionScope object, the transaction isn't committed until the End Using statement. The Complete or Dispose method calls don't actually commit or roll back your transaction. Instead, they indicate what should happen when the TransactionScope reaches its end. When you use a TransactionScope object instead of a Transaction object, you no longer need to associate the various connections with your transaction; instead, .NET automatically creates the association.

The third primary object in the System.Transactions namespace, TransactionManager, isn't actually designed for processing transactions so much as it is available to provide your developers an interface to the distributed-transaction coordinator. Working with this object is beyond the scope of this article, but the purpose of the object is to provide a set of static methods that you can use to access one or more transaction managers. Thus, developers can register transaction-management utilities other than the default managers that .NET provides, which means you can extend your transaction coordination beyond COM+.

Get Ready to Go

This article has introduced only three of the features that developers can use in SQL Server 2005 and Visual Studio 2005. Many more new features—such as the ability to work with XML, create truly asynchronous database queries, and improve error handling—will further enhance the applications you build. The key is that regardless of whether you're a developer or an administrator, now is the time to start working with these products. You can use the final beta versions of SQL Server 2005 and Visual Studio 2005 as the basis for new development, so you'll have 2005-ready applications to use as soon after release as possible.

Related Reading

VINOD KUMAR "Making the Most of the CLR," May 2005, InstantDoc ID 45673 MATT NUNN "Seeing the CLR's Potential," May 2005, InstantDoc ID 45753 MICROSOFT "Multiple Active Result Sets (MARS) in SQL Server 2005," http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.asp "sp_configure," http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp