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.
Prev. page
[1]
2
next page 
