The Baker’s Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2)

The Baker’s Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2)

egardless of whether you work in C# or VB.NET, or whether you spend more time on the front-end or back-end of an application, the ability to manage data will directly impact your contributions to a project.

The new .NET generics classes provide you with near-quantum leap in efficiency by allowing you to write simpler code that accomplishes more. ASP.NET 2.0’s ObjectDataSource class helps you to easily integrate middle-tier data-aware classes to data-bound controls on a Web page. Finally, as you saw in Part 1 of this series (and will see again here in Part 2), T-SQL 2005 provides new language capabilities to help database developers be more productive.

In the conclusion of this two-part series on managing data, I will focus on .NET generics, the ASP.NET 2.0 ObjectDataSource, and some more T-SQL 2005 capabilities.

Beginning with the End in Mind: An Entire Potpourri of Data Handling Tips
It’s a typical sunny day as I type this from my back porch, and the data-handling capabilities in Visual Studio 2005 and T-SQL 2005 have never been hotter. I just finished a long road trip of presenting at community events and conferences, and I returned with a ton of energy that I’ll inject into this issue’s tips.

.NET generics classes provide you with near-quantum leap in efficiency by allowing you to write simpler code that accomplishes more. Those whose “holy grail” includes strongly-typed yet flexible code will ultimately fall in love with .NET generics.

So, let’s play ball! Here’s the lineup for the second game of our Baker’s Dozen doubleheader (of course, a baseball lineup has nine players, we have thirteen tips!).

  • Batting first through seventh I’ll show seven tips for circling the bases using .NET generics. The Baker’s Dozen Spotlight will appear in Tip #7: a generic data access class that fills a typed dataset from a stored procedure.
  • The middle of the line-up (Tip 8) will show basic examples to demonstrate the new ObjectDataSource capability in ASP.NET 2.0.
  • The back-end of the lineup will feature (only fitting) five more tips for the back-end database! Tips 9 through 13 pick up where Part 1 left off: More ways to use T-SQL 2005 to work with data.

Tip 1: An Overview of .NET Generics
In many instances, .NET generics can be explained in terms of tasks developers tried to accomplish in Visual Studio .NET 2003.

Many developers used the ArrayList collection class in Visual Studio 2003 to store a list of objects, as illustrated in the code below. In this example, I am simply storing a list of integers: I could just as easily store a collection of form objects, data tables, or other objects.

   // Visual Studio 2003 code   ArrayList list = new ArrayList();   // implicit boxing, must convert value    // type to reference type   list.Add(3);   list.Add(4);   list.Add(5.0);   int nFirst = (int)list[0];   int total = 0;   foreach (int val in list)       total = total + val;

Note these three issues with the snippet:

  • First, regardless of what specific type I store in the ArrayList, the ArrayList stores it as an object. So there is an implicit conversion, or “boxing” operation that occurs.
  • Second, when I retrieve a specific item from the ArrayList, .NET must convert the item from an object back to the source data type (and I must specify the data type). So there is an “unboxing” operation that occurs.
  • Finally, I have no compile-time type safety checking. In the ArrayList, I store two integers and then a decimal to the ArrayList. When I iterate through the collection on the last two lines, .NET will generate an error at run time because I am attempting to cast the decimal value (5.0) as an integer.

In Visual Studio 2005, I can use the new List class from the System.Collection.Generic namespace. The List class addresses the issues in the ArrayList class by allowing me to define the specific type I wish to store for a specific instance of the class.

   // Visual Studio 2005 code   // define the stored data type in the placeholder    List aList = new List();   aList.Add(3);   aList.Add(4);   // The following line generates a compile error   aList.Add(5.0);      // no need for unboxing, value type stored in    // List as an int, not an object   int nFirst = aList[0];      int total = 0;   foreach (int val in aList)       total = total + val;

Because I am defining the stored type when I create an instance of the List class, .NET does not need to perform any boxing or unboxing. .NET knows that I am storing an integer. Additionally, I do not need to cast a specific item in the List.

While it would take a large number of instantiations to measure a difference in performance, the increased efficiency in storage and compile-time safety are more than enough to appreciate the differences between the ArrayList and List classes.

Still, it’s important to know that I’ll pay a small performance penalty by using the ArrayList class. The code below creates an ArrayList, stores a million integers, and iterates through the list. It takes approximately two-tenths of a second to run.

   // Visual Studio 2003 code - takes .2 seconds   ArrayList aBigList = new ArrayList();   DateTime dt1, dt2;   dt1 = DateTime.Now;   for (int nCtr = 0; nCtr < 10000000; nCtr++)      aBigList.Add(nCtr);   int nSum = 0;   foreach (int nn in aBigList)      nSum += nn;   dt2 = DateTime.Now;   TimeSpan ts = dt2.Subtract(dt1);   MessageBox.Show(ts.TotalSeconds.ToString());

When I rewrite the code to use the new List class, the code runs in four-hundredths of a second, or five times faster.

   // Visual Studio 2005 code - takes .04 seconds    List aBigList = new List();   DateTime dt1, dt2;   dt1 = DateTime.Now;   for (int nCtr = 0; nCtr < 1000000; nCtr++)      aBigList.Add(nCtr);   int nSum = 0;   foreach (int nn in aBigList)      nSum += nn;   dt2 = DateTime.Now;   TimeSpan ts = dt2.Subtract(dt1);   MessageBox.Show(ts.TotalSeconds.ToString());

Next I'll take a look at generic methods, which allow developers to create tremendous flexibility in their code. At an MSDN CodeCamp event last year, Carl Franklin (of .NET Rocks! fame) stated that a great use for generics is when you have multiple classes that differ only by type.

Consider some code that compares two values and returns the greater value of the two. I could use it to compare strings, dates, integers, etc. I could write multiple methods, or write one method with multiple overloads, or resort to some other trickery. However, .NET generics allow me to write a single method to cover multiple data types. Here is where .NET generics truly shine.

In the code below, I'll set up a method called CalcMax. Note the use of the letter "T" and the placeholder . I don't need to specifically use the letter T: I could use some other letter or an entirely different word. However T serves as a meaningful designator for a type placeholder.

   public T CalcMax ( T compVal1, T compVal2)    where T : IComparable    {      T returnValue = compVal2;      if (compVal2.CompareTo(compVal1) < 0)          returnValue = compVal1;      return returnValue;   }

In CalcMax, I define a type placeholder for the return value, and a type placeholder for the two parameters. The only rule is that the type passed must implement IComparable, since I am using the CompareTo method of the parameters.

I can call CalcMax several times, specifying different data types each time.

   double dMax = CalcMax(111.11,333.23);   int intMax =  CalcMax(2, 3);   string cMax = CalcMax("Kevin", "Steven");   DateTime dtMax = CalcMax        (DateTime.Today, DateTime.Today.AddDays(1));

Tip 2: Using Generics to Store a Custom Class
I can use the List class to store custom classes as well. The following code shows a simple custom class called BaseballClass, with properties for team name and player name.

   using System;   using System.Collections.Generic;   using System.Text;   namespace GenericsDemo{      public class BaseballClass        {         private string name;         public string Name            {  get  { return name;  }}         private string team;         public string Team           { get { return team; } }         public BaseballClass(string name, string team)         {   = name;   = team;         }         public override string ToString()         {            return team + ", " + name;         }      }   }

Here's the code for a function (PopulateBaseballClass) that creates an instance of the List class to store instances of the BaseballClass.

   List PopulateBaseballClass()   {      List oBaseball = new List();      oBaseball.Add(new BaseballClass("Kenny Rogers", "Tigers"));      oBaseball.Add(new BaseballClass("Michael Young", "Rangers"));      oBaseball.Add(new BaseballClass("Ken Griffey, Jr.", "Reds"));      oBaseball.Add(new BaseballClass("Tom Glavine", "Mets"));      oBaseball.Add(new BaseballClass("David Ortiz", "Red Sox"));      oBaseball.Add(new BaseballClass("Derek Jeter", "Yankees"));      oBaseball.Add(new BaseballClass("Roger Clemens", "Astros"));      oBaseball.Add(new BaseballClass("Roy Oswalt", "Astros"));      return oBaseball;   }

Finally, this code creates an instance of the Baseball class, calls the PopulateBaseballClass function shown above, and then iterates through the collection.

   private void TestBaseballClass()   {      List oBaseball = new List();      oBaseball = this.PopulateBaseballClass();      string cResults = "";      foreach (BaseballClass oRecord in oBaseball)         cResults += oRecord + "
";      MessageBox.Show(cResults);   }
The ObjectDataSource may not handle every situation of a browser-based database application. But learn the capabilities and the limitations of the ObjectDataSource and use it as a starting point.

Tip 3: Sorting with .NET Generics
The generics List class exposes methods for sorting and searching operations. You can even implement your own custom sorting and searching logic. Let's take a look at both.

The code below demonstrates sorting by player name and then by player name within a team. In both instances, I am using a new C# language feature called anonymous methods, to specify the sorting logic in-line.

   private void TestSortBaseballClass()   {      List oBaseball = new List();      oBaseball = this.PopulateBaseballClass();      string cResults = "";         // Anonymous delegate to sort on name      oBaseball.Sort (delegate(BaseballClass f1, BaseballClass f2) {         return f1.Name.CompareTo(f2.Name);         // Anonymous delegate to sort on Team, then Name      oBaseball.Sort (delegate(BaseballClass f1, BaseballClass f2) {         return f1.Name.Insert(0,          f1.Team).CompareTo(f2.Name.Insert(0, f2.Team));   }

Anonymous methods allow you to create code blocks that can be encapsulated in a delegate, and written in-line where you would otherwise reference a method name. You can reduce the coding overhead in instantiating delegates by eliminating the need to create a separate method. Specifying a code block in the place of a delegate can be useful in a situation when creating a method might seem an unnecessary overhead.

The syntax of anonymous methods can initially appear daunting. However, in this example, anonymous methods save me from the work of adding an additional class.

   // In-line anonymous method that compares 2    // incoming values   oBaseball.Sort(delegate(BaseballClass f1, BaseballClass f2) {      return f1.Name.CompareTo(f2.Name);   });      // A second anonymous method, inserts team    // in front of name, to sort on two columns    oBaseball.Sort(delegate(BaseballClass f1, BaseballClass f2) {      return f1.Name.Insert(0, f1.Team).CompareTo(       f2.Name.Insert(0, f2.Team));   });

Tip 4: Searching with .NET Generics and (Optionally) Anonymous Methods
The .NET generics List class exposes methods to perform a Find, FindAll, and FindLast. I can write an anonymous method to implement my own search logic in-line as shown below, or I can build a class that serves as a Predicate (a Boolean-valued function) for the Find methods (see Listing 1 and Listing 2).

   private void TestFindBaseballClass()   {      List oBaseball = new List();      oBaseball = this.PopulateBaseballClass();      string cSearch = "Astros";      BaseballClass oSingleFind =         oBaseball.Find( delegate(BaseballClass f1)          { return f1.Team == cSearch; });      MessageBox.Show("Single find is " + oSingleFind);      string cResults = "";      foreach (BaseballClass oRecord in         oBaseball.FindAll( (delegate(BaseballClass f1)          { return f1.Team == cSearch; })))         cResults += oRecord + "
";      MessageBox.Show("Multiple find is " + oSingleFind);   }

The preceding code uses the same technique as the previous example: An in-line method sets up a delegate to search the populated Baseball class for the first instance of a match. Note that I could use different string functions inside the anonymous method if I wanted to implement a partial string search instead of an exact match.

I can also use the FindAll method of the List class in the same manner. I've specified an in-line delegate and an anonymous method to return a Boolean for each incoming BaseballClass object that equals the search string. The FindAll method allows me to implement filtering capabilities that ADO.NET provides with the DataTable object.

   string cSearch = "Astros";      // Perform a single search on the first hit   BaseballClass oSingleFind = oBaseball.Find(      delegate(BaseballClass f1) {    return f1.Team == cSearch; });      // Perform a find all (almost like a filter)   string cResults = "";   foreach (BaseballClass oRecord in         oBaseball.FindAll((delegate(BaseballClass f1) {          return f1.Team == cSearch; })))      cResults += oRecord + "

For those who prefer to write a separate class (or are using VB.NET, which does not support anonymous methods in Visual Studio 2005), Listing 1 and Listing 2 demonstrate a separate Find class and a code sample for using the Find class.

Tip 5: Using the List Class to Store Other Objects
As stated earlier, I can use the new List class to store any .NET type that I wish. Listing 3 contains an example that stores classes that implement an interface. Suppose I have report classes that all contain a method called GenerateDocument. I can set up a master controlling loop structure that does the following:

  • Define an interface called IDocument that contains one method, GenerateDocument
  • Build multiple report document classes (SimpleClass1, SimpleClass2, etc.) that implement IDocument
  • Instantiate a List object that stores classes that implement the IDocument interface
   List oList =       new List();
  • Create instances of the report document classes (SimpleClass1, etc.) and add the instances to the list
  • Loop through the list and call the GenerateDocument method
   foreach(SimpleInterface.IDocument oClass in oList)      oClass.GenerateDocument();

Special thanks to Rob Hale from GE Healthcare for this one. Rob attended my C# 2.0 session at Boston CodeCamp and came up with the idea for this one. Two thumbs up, Rob!

Tip 6: Other Classes in the Generics Namespace
I've focused on the new List class in Visual Studio 2005. Now I want to talk about three other classes in the System.Collections.Generics namespace to be aware of. Let's take a moment and look at the new Dictionary, SortedDictionary, and Collection classes.

Visual Studio 2005 introduces the new Dictionary class. This abstract class allows developers to map keys and elements in a collection. Some functions in the .NET 2.0 Framework use the Dictionary class: One example is one of the new overloads for creating a new TCP channel. In the example below, I create a new dictionary object and specify that both the key and value will contain a string.

   // Create a new dictionary object with two strings   Dictionary oDict =       new Dictionary();   oDict.Add("secure", "true");   oDict.Add("password", "kevin");   TcpChannel oChannel = new TcpChannel(oDict, null, null);

If you need to sort the dictionary entries by key, you'll be better off using the new SortedDictionary class, which maintains a sort order on key.

Next, while I've used the new List class, Visual Studio 2005 also offers a lightweight Collection class for simple lists that don't require sorting and filtering capabilities.

   Collection oCol = new Collection();   oCol.Add(2);   oCol.Add(3);   oCol.Insert(0,1);

I can also store custom classes into the Collection object.

   Collection oBaseballCollection =    new Collection();   oBaseballCollection.Add(      new BaseballClass("Tom Glavine","Mets"));   oBaseballCollection.Add(      new BaseballClass("Derek Jeter","Yankees"));   foreach (BaseballClass oBaseball in       oBaseballCollection)      MessageBox.Show(oBaseball.ToString());

Finally, .NET generics offers other classes, such as a LinkedList and LinkedListNote, for working with linked list data, as well as a Queue and Stack class.

Tip 7: The Baker's Dozen Spotlight: Building a Data Access Class to Populate Typed Datasets Using .NET Generics
I use a lot of stored procedures and typed datasets. One of my many "holy grail" quests has been to populate a typed dataset directly from a stored procedure.

For a very long time, I used a base method in my data access class to populate a plain vanilla dataset from a stored procedure with parameters. Afterwards I would merge the dataset into a typed dataset. This certainly worked, but it meant additional code and an additional processing step. What I wanted to do was pass an instance of a typed dataset into the base method, and have the base method serve as a factory-to pump out a populated typed dataset.

.NET generics allow me to create such a class and then use it (see Listing 4 and Listing 5). To create a class like this, follow these steps:

  • Create an instance of a typed dataset, and an instance of the Data Access Class (which appears in Listing 5).
  • Create a typed List of SQL parameters for the stored procedure (instead of using ArrayList).
  • Call the data access class method (ReadIntoDs), passing an instance of the typed dataset, the name of the stored procedure, and the typed List of parameters for the stored proc.
  • Create the data access method ReadIntoDs (see Listing 5), and specify a typed placeholder for the first parameter and for the return value. Note the restriction that the parameter must be a dataset since code inside the method will use dataset-specific properties and methods.
   public T ReadIntoDS      (T dsTypedDs, string cStoredProc,       List oParmList)    where T : DataSet
  • Define the standard SqlConnection object, SqlDataAdapter, etc.
  • Elbow grease time! SQL Server returns stored procedure result sets with names of Table, Table1, Table2, etc. When I designed the typed dataset, I might have used more descriptive names (dtClient, dtDetails, etc.) Therefore, I need to map the names Table, Table1, etc., to the names in the typed dataset, using the TableMappings command of the DataAdapter.
  • Fill the dataset from the DataAdapter, and return it.

Tip 8: An Overview of the ASP.NET 2.0 ObjectDataSource
In Part 1 of this series, I discussed the ASP.NET 2.0 GridView, how to bind it to a dataset, and also how to handle sorting and paging. I can also use the ASP.NET 2.0 ObjectDataSource, which allows me to expose data-aware business objects/classes to data-bound controls such as the GridView.

Figure 1: Add a reference to the business object, to use for ObjectDataSource.

I'll take a step-by-step approach to using the ObjectDataSource. In the first example, I'll set up a business object that returns a typed dataset of orders that are "on hold." Then I'll create a Web page with a GridView and define the business object as the direct DataSource for the GridView.

  1. Create a separate project called bzOrders. Build a simple typed dataset called dsOrders, and a method called GetOrdersOnHold that returns a populated instance of dsOrders.
  2. Create a new Web page project and add bzOrders.dll as a reference (see Figure 1).
  3. On the new Web page, create an instance of the ObjectDataSource from the toolbox (see Figure 2).
  4. ?
    Figure 2: From the toolbox, drop an instance of the ObjectDataSource onto the Web page.
  5. Right-click on the new ObjectDataSource and choose the option to configure the data source. Visual Studio 2005 will display the Configure Data Source dialog box (see Figure 3). Select bzObjects as the class that will provide the data.
  6. Next, I need to tell the ObjectDataSource which methods that I want to use for the standard Select, Insert, Update, and Delete operations. For this example, I'm only specifying a Select command, the method GetOrdersOnHold (see Figure 4).
  7. Create a GridView on the Web page and set the DataSourceID property to the ObjectDataSource (see Figure 5). Also set the other GridView properties to the settings in Figure 5, including the options to allow/enable sorting and paging.
  8. At this point, I could actually run the Web page. However, I may want to set column sizes and alignments, customizing the headings, etc. Load the property sheet for the GridView and select the Columns collection. Visual Studio 2005 will display the column/field designer (see Figure 6) for me to customize the GridView.
  9. Finally, Figure 7 shows the results when I run the Web page.
Author's Note: Very important! Note that Figure 7 shows the page links for me to navigate to different pages. Also note that the column headings are set for me to click and sort. When I bind to an ObjectDataSource, Visual Studio 2005 automatically handles the sorting and paging for me! No additional code is required.

Figure 3: Right-click on the new ObjectDataSource to select the business object.
Figure 4: Select the method in the business object for retrieving data.
Figure 5: Create a GridView, and set the DataSourceID and other properties.

Figure 6: The selected fields are already established-you can customize the display.
Figure 7: The final result, with no code!

This tip merely provides an introduction to the ObjectDataSource class. Other tasks I can perform using the ObjectDataSource are as follows:

  • Define a retrieve method in the business object that utilizes a parameter to retrieve a single order. In the ObjectDataSource designer, I can specify the method as the retrieve method and define the parameter to the value of a page control, a FormField value, a QueryString, or a Session variable.
  • Define methods in the business object to perform standard Insert, Update, and Delete functions. Again, in the ObjectDataSource designer, I can map the methods and define any parameters.

Finally, you may be wondering how to "capture" the data that the ObjectDataSource returns. For instance, you may want to display the number of rows in the grid caption. You can utilize the Selected event of the ObjectDataSource.

   protected void ObjectDataSource1_Selected(      object sender, ObjectDataSourceStatusEventArgs e)   {      bzOrders.dsOrders odsOrders =          (dsOrders)e.ReturnValue;      int nRows = odsOrders.dtOrders.Rows.Count;      this.grdOrdersOnHold.Caption =          "Number of rows: " + nRows.ToString();   }

Tip 9: Using the SQL Server COALESCE Function
Suppose I have a form (or Web page) that allows users to retrieve customers based on several input criteria, such as first and last name, address, city, zip, etc. The user may enter one field or multiple fields. I need to write a stored procedure to examine all possible parameters, but only query on those that the user entered.

I could write a stored procedure that examines each parameter, constructs a SQL SELECT string based on parameters the user entered, and execute the string using dynamic SQL. Many SQL developers opt for this approach.

Alternatively I can use the SQL Server COALESCE function as shown in the following code:

   -- This will work in both SQL2000 and SQL2005   -- You can use COALESCE to query on only those search values   -- that are not NULL   DECLARE @city varchar(50), @state varchar(50), @zip varchar(50),           @FirstName varchar(50), @LastName varchar(50),            @Address varchar(50)   SET @FirstName = 'Kevin'   SET @State = 'NY'   SELECT * FROM CUSTOMERS WHERE         FirstName = COALESCE(@FirstName,FirstName) AND        LastName = COALESCE(@LastName,LastName) AND        Address = COALESCE(@Address,Address) AND        City = COALESCE(@City,City) AND        State = COALESCE(@State,State) AND        Zip = COALESCE(@Zip,Zip)

COALESCE is available both in SQL Server 2000 and SQL Server 2005, and gives me an alternative approach that arguably leads to cleaner T-SQL code. For each search condition, I pass COALESCE two values: the search variable, and a value to use if the search variable is NULL. So for any search values that the user did not specify, the search defaults to the column being equal to itself. This approach is still very fast, even when querying against millions of rows.

Tip 10: Ranking in SQL 2005 within a Group
SQL Server 2005 introduced a ROW_NUMBER function to rank result sets. I can also rank results within a group. Suppose I want to rank the top orders by customer, in descending sequence (for orders greater than $500).

Listing 6 shows how I can query the Northwind Orders database for orders greater than $500. I assign a ranking number for each set of customer orders. In the ROW_NUMBER OVER statement, I can define the PARTITION (in this context, somewhat like a group-level definition) as well as the Order to use when ranking the result set.

   ROW_NUMBER() OVER (PARTITION BY CUSTOMERID    ORDER BY  (UnitPrice  * Quantity) DESC)    AS OrderRank

Tip 11: More on T-SQL 2005: Apply, UDFs, and Table-Valued UDFs in Correlated Subqueries
T-SQL 2000 is a bit awkward at allowing developers to integrate table-valued UDFs into queries. Often, developers must query these UDFs into a temporary table and then use the temporary table.

T-SQL 2005 allows developers to more efficiently integrate table-valued UDFs into queries.

T-SQL 2005 allows developers to more efficiently integrate table-valued UDFs into queries. In Part 1 of this series, I showed an example that directly applied the results of a table-valued UDF in the same way I would use a derived table, using the new APPLY operator. The example contained a UDF that returned the TOP N orders from the Northwind database for a specified customer, and applied that directly against all customers in a query.

Here I'll cover another instance where I can use table-valued UDFs more directly. T-SQL 2005 allows me to use a table-valued function in a subquery, where the arguments to the UDF come from columns in the outer query.

Using the Northwind database, suppose I want to know which customers have had at least two orders for more than $5,000 (or five orders for more than a $1,000, etc.). So our first step is to build a table-valued UDF called GetCustOrders_GT_X (see the top part of Listing 7). The UDF contains two parameters (customer ID and dollar threshold), and returns a table variable of orders for that customer that exceed the threshold.

In the next step I run that UDF against every customer in the database, and determine which customers have at least two orders from the UDF. Ideally, I'd like to construct a subquery to pass each customer as a parameter to the UDF. Here's where the power of T-SQL 2005 comes in.

In SQL Server 2000, table-valued functions within a correlated subquery could not reference columns from the outer query. Fortunately, SQL Server 2005 removes this restriction. I can now build a subquery that uses the UDF and passes columns from the outer query as arguments to the UDF (remainder of Listing 7).

Tip 12: SQL 2005 XML Enhancements
SQL Server 2000 contains a tremendous amount of functionality for dealing with XML: SQL Server 2005 extends XML with the new XML data type.

Listing 8 and Listing 9 demonstrate some of the capabilities in working with XML data. Listing 8 shows different ways to insert XML data into standard columns. Listing 9 demonstrates how to search for strings inside an XML column. (Special thanks to the regulars in the Microsoft SQL Server newsgroup for help with the syntax on that one!)

Tip 13: Removing a Restriction on Table Variables in SQL 2005
Microsoft introduced table variables in SQL Server 2000 to the delight of many database developers. However, table variables carried a few restrictions. One restriction was that developers could not insert the results of a stored procedure directly into a table variable. So the following code would not work in T-SQL 2000:

   INSERT @tTable EXEC  

Fortunately, T-SQL 2005 lifts this restriction. I can now execute a stored procedure directly into a table variable (see Listing 10).

Random Musings on Data
I probably sound like a broken record, but at the end of the day, much of our lives are about handling data. As I've shown in this two-part series, Visual Studio 2005 and SQL Server 2005 make our lives easier when working with data-either with major enhancements like generics and the SQL PIVOT command, or more subtle but important changes so that I can filter on distinct rows in ADO.NET.

If you are a developer maintaining Visual Studio 2003 code in the Visual Studio 2005 environment, you may not be able to take advantage of these new data-handling capabilities right away. But you can still study the new functions as time permits, build prototypes with them, and prepare yourself for when the time comes!

Recommended Reading
Ever wish you could just lock yourself in a nice cool room for a week and research all these new capabilities? Unfortunately, the demands of the world often make that difficult. However, if you can spare a few hours, here are some great references

First, CoDe Magazine has run some outstanding articles on T-SQL and XML in SQL Server 2005. Jim Duffy wrote an article in the January/February 2005 issue of CoDe, titled "SQL Server 2005 T-SQL Enhancements." Jim is an outstanding speaker and a well-rounded technology guru.

Shawn Wildermuth has an outstanding article in the May/June 2006 issue of CoDe, titled "Making Sense of the XML DataType in SQL Server 2005." I'd vote for this as article of the year, if such an award existed. Google Shawn's name and you'll find many tremendous online articles on ADO.NET.

Second, Manuel Abadia has written some online articles on the ObjectDataSource. He has also written his own class, called ExtendedObjectDataSource. Check out Manuel's content.

Finally, the book, "Professional .NET 2.0 Generics" (Wiley Publishing) by Tod Golding is excellent and covers generics in detail. The author presents code in both C# and VB.NET.

Next Issue: Closing Thoughts
Have you ever submitted something (an article, a paper, some code, etc.) and thought of some good ideas after the fact? Well, I'm the king of thinking of things afterwards. Fortunately, that's the type of thing that makes blogs valuable. Check my blog for follow-up tips and notes on Baker's Dozen articles?and maybe a few additional treats!

Article Source Code
You can find the full source code for this article on my Web site.


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist