Developer .NET UPDATE--ADO.NET 2.0 Features--March 4, 2005

This Issue Sponsored By
This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for Developer .NET UPDATE.

SQL Anywhere Studio from iAnywhere Solutions

ALERT: How A Hacker Breaks Into Your Web Apps

Scott Guthrie to Keynote Connections in Orlando

1. Developer .NET Perspectives

  • Cool New Features in ADO.NET 2.0
  • 2. Events and Resources

  • Last Chance--SQL Server Administration for Oracle DBAs Web Seminar
  • Get Ready for SQL Server 2005 Roadshow in a City Near You
  • The Security Event Log: The Unofficial Guide
  • Featured Thread: Question About Provided Low-Level Services
  • 3. Announcements

  • Get Windows IT Pro at 44% Off!
  • Get SQL Server Magazine and Get Answers
  • 4. New and Improved

  • New Tools to Debug Code and Catch Security Vulnerabilities

  • Sponsor: SQL Anywhere Studio from iAnywhere Solutions
    8 million deployed seats. 1,000 application partners. 20,000 developers. 1,000 customers. FREE for developers. We know it sounds too good to be true, but it isn't. The free Developer Edition has all the features of SQL Anywhere Studio and doesn't expire at any time. So you can evaluate and use it at no cost until you're ready to join the thousands of developers who continue to use SQL Anywhere as their database of choice. With low maintenance requirements, high performance, open tools and technology integration, and a low cost of ownership, SQL Anywhere powers thousands of single- and multi-user desktop, client server, remote office and mobile applications worldwide. Download your FREE SQL Anywhere Studio Developer Edition today at

    1. Developer .NET Perspectives

    by Bill Sheldon, [email protected]

    Cool New Features in ADO.NET 2.0
    In the last several columns, I've been covering SQL Server 2005's new features. I haven't finished discussing those features, but I want to take a short break and discuss ADO.NET 2.0 instead. ADO.NET 2.0 introduces a host of new features, the majority of which are specific to SQL Server 2005. There are also features that, while specific to SQL Server 2005, are available for previous versions of SQL Server. ADO.NET 2.0 even offers new features that are common across the ADO logic regardless of the underlying database or access method. Because there are so many new features in ADO.NET 2.0, let's build on our recent discussions of SQL Server 2005 by looking at some features that are specific to SQL Server 2005.

    The first feature I want to discuss is ADO.NET 2.0's support for custom user-defined types (UDTs). As I mentioned in my column "Leveraging the CLR's Power" (, you can use the Windows .NET Framework 2.0 and the embedded Common Language Runtime (CLR) in SQL Server 2005 to create custom UDTs. When you use Visual Studio 2005 to create an assembly, you can leverage that same assembly on SQL Server 2005 and the .NET client that will consume your data. ADO.NET 2.0 references the custom assembly you create to enable the client to consume your custom type. The result is that all the information about your custom UDT, which is defined in SQL Server 2005, is available on your .NET client. This is important because your custom UDT will have custom behavior. By having the class available both on the client and server, this behavior is carried with the data. The key is that a UDT doesn't define just a custom structure; it defines both a custom structure and custom behavior. And ADO.NET 2.0 is the means by which you can communicate a UDT's behavior outside the database.

    Speaking of data types, SQL Server 2005 introduces a new length parameter, MAX, that applies to three existing data types. The three affected data types that are fully supported by not only SQL Server 2005 but also ADO.NET 2.0 are varchar(MAX), nvarchar(MAX), and varbinary(MAX). The only difference between these new data types and their existing counterparts (i.e., varchar(#), nvarchar(#), and varbinary(#)) is that you aren't specifying the variable length, so you aren't limited to the maximum length of a row in SQL Server. Thus, for example, the varchar(MAX) data type allows up to 2GB of text data. Just like a Text column, a varchar(MAX) column acts like it's part of the existing row because it spans the 8K row limit of SQL Server, but the varchar(MAX) column data is actually stored separately from the remaining row data.

    From what I can tell, the new MAX length parameter is more sizzle than steak. The Text, nText, and Image types already support very large data elements, and there isn't any significant difference in behavior between them and varchar(MAX), nvarchar(MAX), and varbinary(MAX). However, for those who have been looking for what is essentially an unlimited variable character field that isn't called a text field, this new parameter will meet your needs.

    For a demonstration of how to work with varchar(MAX), nvarchar(MAX), and varbinary(MAX), see the Microsoft article "Working with Large Value Types in ADO.NET" ( link will change after Visual Studio 2005 is released). In the "Retrieving Large Value Types as Parameter Values" section at the bottom of the article, you'll find a Visual Basic (VB) example in which a varchar(MAX) column is returned as an output parameter. This example provides the best demonstration.

    For me, a more useful feature in ADO.NET 2.0 is Multiple Active Result Sets (MARS), which lets you simultaneously reference multiple database commands across a single connection. MARS lets you use multiple commands without each of those commands needing its own connection.

    You can use two data readers to get an idea of how MARS works. (This feature is generally described in relation to data readers simply because Datasets and DataTables are disconnected constructs.) There are times that you might want to step through more than one table at the same time. For example, suppose you want to associate two different command objects with the same connection object. If you retrieve the first row from the first data reader, then attempt to access the second data reader, you'll get an error when using ADO.NET. You'll get the same result for any command that uses a connection object already in use by an open data reader. In ADO.NET 2.0, MARS resolves this problem by letting you have multiple different SQL commands actively working against your database.

    Perhaps one of the best aspects of MARS is that it doesn't require any effort on the developer's part--it's more of an enabling technology. Think about the ability to truly share a connection across multiple simultaneous threads and you'll get an idea of how MARS could significantly impact performance in applications such as Microsoft IIS.

    Another enabling feature is SQL Query Notifications. Let me clarify here that I'm not referring to SQL Server's Notification Services (, which is an entirely different class of notifications.

    Under ADO.NET 2.0, you can have applications notify SQL Server 2005 when any of the table data associated with a query changes. This feature essentially removes the need to carry out the same polling logic that every developer who has ever cached data has been forced to implement. Instead of having to repeatedly check to see whether anything has changed or watch for a triggered column to be updated, you can just tell SQL Server 2005 to notify you when a change occurs.

    There are two options for Query Notifications: the automated dependency option and the manual notification request. The automated dependency option does the most to free the average developer from worrying about updates. You register for this update when you execute your command; SQL Server 2005 will then contact you when there's an update. Your application isn't going to be sitting around waiting for SQL Server 2005 to notify you of an update, so these notifications occur as events within your application. The really cool thing is that these events don't require you to maintain a connection to SQL Server 2005. You register with SQL Server 2005 to receive an event. This is event-based programming--the same type of events you receive from a mouse click or when an ASP.NET control is accessed--only these events come to your application from the database. SQL Server 2005 raises the appropriate event to your application, and your application can simply respond to the event by refreshing the cached data.

    The manual notification request is similar to the traditional polling method. Why use it? Well, in some cases, you might not be able to remain on the network with SQL Server constantly, so what you're doing is leveraging the plumbing provided by Microsoft to set up the traditional flag to indicate that a table has been modified. SQL Server 2005 automatically handles the setup and monitoring of your flag. After you receive your notification, you can process the results automatically.

    SQL Query Notifications are a great new feature that's coming with ADO.NET 2.0 and SQL Server 2005. An excellent article that goes in-depth into the Query Notifications feature is at the following URL:

    ALERT: How A Hacker Breaks Into Your Web Apps
    Learn why 70% of today's successful hacks involve Web Application attacks such as: SQL Injection, XSS, and Parameter Manipulation. All undetectable by Firewalls and IDS! Get the Top Most Critical Code-Based Web Application Vulnerabilities

    2. Events and Resources

    (brought to you by SQL Server Magazine)

    Last Chance--SQL Server Administration for Oracle DBAs Web Seminar
    Sign up now for this free, 1-hour Web seminar and get a quick start in mapping Oracle database-management skills, knowledge, and experience to SQL Server database management. Learn about the varying similarities and differences between Oracle and SQL Server and get a preview of real- world tips and techniques for managing these associated technologies. Register now!

    Get Ready for SQL Server 2005 Roadshow in a City Near You
    Get the facts about migrating to SQL Server 2005. SQL Server experts will present real-world information about administration, development, and business intelligence to help you implement a best-practices migration to SQL Server 2005 and improve your database computing environment. Receive a 1-year membership to PASS and 1-year subscription to SQL Server Magazine. Register now!

    The Security Event Log: The Unofficial Guide
    Randy Franklin Smith is one of the foremost authorities on the Windows Security Event Log and a respected trainer who teaches Monterey Technology Group's "Security Log Secrets" course. In his article in the March issue of Windows IT Pro, Randy shines a light on this dark and mysterious corner of cryptic event IDs and codes and inaccurate Microsoft documentation. Here's your chance to ask Randy your questions about the Event Log and get answers Microsoft doesn't provide. Join the chat on March 16th at noon EST. For details, visit

    Featured Thread: Question About Provided Low-Level Services
    Forum member savanted1 is seeking the answer to the following question: What low-level services does C# provide to derived classes within the confines of the Windows .NET Framework? If you know the answer, go to the Windows .NET Framework forum at

    3. Announcements

    (brought to you by SQL Server Magazine)

    Get Windows IT Pro at 44% Off!
    Windows & .NET Magazine is now Windows IT Pro! Act now to get an entire year for just $39.95--that's 44% off the cover price! Our March issue shows you what you need to know about Windows Server 2003 SP1, how to get the best out of your IT staff, and how to fight spyware. Plus, we review the top 10 features of Mozilla Firefox 1.0. This is a limited-time, risk-free offer, so click here now:

    Get SQL Server Magazine and Get Answers
    Order SQL Server Magazine today and get unlimited online access to every article ever published in the magazine! You'll get thousands of problem-solving solutions, expert tips, tricks, and the latest insider notes to help you get all the answers you need when you need them. Sign up today:

    Hot Spot: Scott Guthrie to Keynote Connections in Orlando
    Scott Guthrie will demonstrate the upcoming ASP.NET 2.0 release in his keynote presentation at Microsoft ASP.NET Connections conference in Orlando, March 20-23, 2005. Microsoft ASP.NET Connections features over 100 sessions on both the current and future versions of ASP.NET, Visual Studio .NET, and SQL Server.

    4. New and Improved

    (by Karen Bemowski, [email protected])

    New Tools to Debug Code and Catch Security Vulnerabilities
    Compuware released two new DevPartner products: DevPartner Fault Simulator and DevPartner SecurityChecker. DevPartner Fault Simulator tests and debugs error-handling code in both native and .NET managed code without disrupting the application operation or debugging environment. The simulator tricks the target application into believing a fault is present so that the reaction can be monitored without affecting other running applications or the underlying OS. DevPartner SecurityChecker enables developers to quickly locate and fix security vulnerabilities in ASP.NET applications. DevPartner SecurityChecker uses three modes of analysis: compile-time analysis, run-time analysis, and integrity analysis. DevPartner Fault Simulator costs $5,000 per concurrent user. DevPartner SecurityChecker costs $10,000 per concurrent user. Volume discounts are available for both products. For more information, contact Compuware at 313-227-7300 or 800-521-9353.

    Contact Us

  • About Developer .NET Perspectives -- [email protected]
  • About technical questions --
  • About product news -- [email protected]
  • About your subscription -- [email protected]
  • About sponsoring an UPDATE -- contact Richard Resnick, [email protected]
  • Developer .NET UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.

    View the SQL Server Magazine Privacy Policy

    SQL Server Magazine is a division of Penton Media, Inc. 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

    Copyright 2005, Penton Media, Inc. All Rights Reserved.

    Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.