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.

Regardless 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.

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<int> aList = new List<int>();

aList.Add(3);
aList.Add(4);
aList.Add(5.0);   // Will generate a compile error

// no need for unboxing, value type stored in 
// List<int      >       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<int> aBigList = new List<int>();
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 only differ 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 <T**>.** 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> ( 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<double>(111.11,333.23);
int intMax =  CalcMax<int>(2, 3);
string cMax = CalcMax<string>("Kevin", "Steven");
DateTime dtMax = CalcMax<DateTime> 
    (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. Listing 1 contains a simple custom class called BaseballClass, with properties for team name and player name. Listing 2 shows the code for a function (PopulateBaseballClass) that creates an instance of the List class to store instances of the BaseballClass. Finally, Listing 3 creates an instance of the Baseball class, calls the Populate function in Listing 2, and then iterates through the collection.

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 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.

Listing 4 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.

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 (Listing 5), or I can build a class that serves as a Predicate (a Boolean-valued function) for the Find methods (Listings 6 and 7).

In Listing 5, I use the same technique that I used in Listing 4: 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 + "\r\n";

For those who prefer to write a separate class (or are using VB.NET, which does not support anonymous methods in Visual Studio 2005), Listings 6 and 7 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 8 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<SimpleInterface.IDocument> oList = 
   new List<SimpleInterface.IDocument>();

• 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.

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

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<string, string> oDict = 
   new Dictionary<string, string>();

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<int> oCol = new Collection<int>();

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

I can also store custom classes into the Collection object.

Collection<BaseballClass> oBaseballCollection = 
           new Collection<BaseballClass>();


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 (Listings 9 and 10). 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 10)
• 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 (Listing 10), 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>
  (T dsTypedDs, string cStoredProc, 
     List<SqlParameter> 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.

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.

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

Figure 2: From the toolbox, drop an instance of the ObjectDataSource onto the Web page.

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!

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.

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 I’ve done in Listing 11. 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 12 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. 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 (the top part of Listing 13). 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 13).

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.

Listings 14 and 15 demonstrate some of the capabilities in working with XML data. Listing 14 shows different ways to insert XML data into standard columns. Listing 15 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 <sp_mystoredproc> 

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

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 SQL PIVOTing, 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 at www.manuellabadia.com.

Finally, the book, **Professional .NET 2.0 Generics **(Wiley Publishing) by Tod Golding is an excellent book that 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 (www.TheBakersDozen.net) for follow-up tips and notes on Baker’s Dozen articles…and maybe a few additional treats!

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)
        {
            this.name = name;
            this.team = team;
        }

        public override string ToString()
        {
            return team + ", " + name;
        }
    }
}

List<BaseballClass> PopulateBaseballClass()
{
     List<BaseballClass> oBaseball = new List<BaseballClass>();
     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;
}

private void TestBaseballClass()
{

     List<BaseballClass> oBaseball = new List<BaseballClass>();
     oBaseball = this.PopulateBaseballClass();
     string cResults = "";

     foreach (BaseballClass oRecord in oBaseball)
         cResults += oRecord + "\r\n";
     MessageBox.Show(cResults);
}

private void TestSortBaseballClass()
{

     List<BaseballClass> oBaseball = new List<BaseballClass>();
     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));
}

private void TestFindBaseballClass()
{

     List<BaseballClass> oBaseball = new List<BaseballClass>();
     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 + "\r\n";

     MessageBox.Show("Multiple find is " + oSingleFind);

}

using System;
using System.Collections.Generic;
using System.Text;

namespace GenericsDemo
{
    class FindBaseballClass
    {
        private string team;
        public FindBaseballClass(string team)
        {
            this.team = team;
        }

        // Implement specific search logic - return true or false
        public bool TeamFind(BaseballClass oBaseball)
        {
            return oBaseball.Team == team;
        }

    }
}

private void TestFindBaseballClass()
{

     List<BaseballClass> oBaseball = new List<BaseballClass>();
     oBaseball = this.PopulateBaseballClass();

     FindBaseballClass oFind = new FindBaseballClass("Astros"); 


     BaseballClass oSingleFind = oBaseball.Find(  
          new Predicate<BaseballClass>(oFind.TeamFind));

     MessageBox.Show("Single find is " + oSingleFind);


     string cResults = "";

     foreach (BaseballClass oRecord in oBaseball.FindAll(
               new Predicate<BaseballClass>(oFind.TeamFind)))
        cResults += oRecord + "\r\n";

     MessageBox.Show("Multiple find is " + oSingleFind);

}

public class SimpleInterface
{
   public interface IDocument
   {
       void GenerateDocument ();
   }
}


class SimpleClass1 : SimpleInterface.IDocument
{
   public SimpleClass1() 
   { 
   }

   public void GenerateDocument()
   {
      // Method code goes here!
   }
}


class SimpleClass2 : SimpleInterface.IDocument
{
   public SimpleClass2() 
   { 
   }

   public void GenerateDocument()
   {
      // Method code goes here!
   }
}


List<SimpleInterface.IDocument> oList = 
             new List<SimpleInterface.IDocument>();

SimpleClass1 oClass1 = new SimpleClass1();
SimpleClass2 oClass2 = new SimpleClass2();

oList.Add(oClass1);
oList.Add(oClass2);

foreach (SimpleInterface.IDocument oClass in oList)
      oClass.GenerateDocument();

public class SimpleDataAccess
{

   // Pass the following:
   // 1) An instance of a typed dataset you wish to fill
   // 2) The name of the stored procedure
   // 3) A List of SqlParameters 

   // Function will execute the stored procedure,  
   // fill the typed dataset with the results, and then return it

   public T ReadIntoDs<T> 
             (T oTypedDs, string cSP, List<SqlParameter> oParms) 
                                          where T:DataSet
   {

      SqlConnection oSqlConn = this.GetConnection();
      SqlDataAdapter oSqlAdapt = new SqlDataAdapter(cSP, oSqlConn);

      oSqlAdapt.SelectCommand.CommandType = 
                CommandType.StoredProcedure;

      foreach (SqlParameter oParm in oParmList)
         oSqlAdapter.SelectCommand.Parameters.Add(oParm);


     // A little bit of "elbow grease…
     // SQL Server returns tablenames as "Table", "Table1", etc
     // We need to map in the tablenames from the typed DS
     // So loop through the tables in the Typed DS, get the name,
     // and map it to the corresponding table that SQL will
     // use as the default name

      int nTableCtr = 0;
      foreach (DataTable Dt in dsTypedDs.Tables) {
         string cSource = "";   
         if (nTableCtr == 0)
            cSource = "Table";
         else
            cSource = "Table" + nTableCtr.ToString().Trim();

         oSqlAdapter.TableMappings.Add(
                    cSource, Dt.TableName.ToString());
         nTableCtr++;
      }
     // End of "elbow grease" - we can fill the dataset
     // from the adapter, now that we've mapped our tablenames



      oSqlAdapter.Fill(dsTypedDs);
 
      return dsTypedDs;
   }


   public SqlConnection GetConnection() 
   {
     // New SqlConnection StringBuilder class

      SqlConnectionStringBuilder oStringBuilder = 
           new SqlConnectionStringBuilder();

      oStringBuilder.UserID = "UserID";
      oStringBuilder.Password = "PassWord";
      oStringBuilder.InitialCatalog = "InitialCatalog";
      oStringBuilder.DataSource = "DataSource";

      return new SqlConnection(oStringBuilder.ConnectionString);

   }
}

// Create an instance of a typed DataSet
dsAgingReport odsAgingReport = new dsAgingReport();

// Create an instance of our Data Access Method
SimpleDataAccess oDataAccess = new SimpleDataAccess();

// Use the list class to create a collection of sql parameters
List<SqlParameter> oParms = new List<SqlParameter>();
oParms.Add(new SqlParameter("@dAgingDate", DateTime.Today));
oParms.Add(new SqlParameter("@lShowDetails", true));

// Call ReadIntoDs, passing the typed DS, name of the stored proc
// and the list of parameters
odsAgingReport = oDataAccess.ReadIntoDs(odsAgingReport,
                       "[dbo].[GetAgingReceivables]",oParms);

-- 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)

SELECT  CustomerID, OH.OrderID, OrderDate,  
  (UnitPrice  * Quantity) as Orderamount,
   ROW_NUMBER() OVER (PARTITION BY CUSTOMERID 
    ORDER BY  (UnitPrice  * Quantity) desc ) AS OrderRank
   FROM Orders OH
      JOIN [dbo].[Order Details] OD
            ON OH.OrderID = OD.OrderID
      WHERE  (UnitPrice * Quantity) > 500
        ORDER BY CUSTOMERID, OrderAmount  DESC

-- Results, OrderRank partitioned (reset) on each Customer  

ALFKI   10692   1997-10-03 00:00:00.000   878.00   1
ALFKI   10835   1998-01-15 00:00:00.000   825.00   2

ANTON   10535   1997-05-13 00:00:00.000   1050.00   1
ANTON   10677   1997-09-22 00:00:00.000   936.90   2
ANTON   10535   1997-05-13 00:00:00.000   825.00   3
ANTON   10573   1997-06-19 00:00:00.000   820.00   4
ANTON   10573   1997-06-19 00:00:00.000   702.00   5

AROUT   10953   1998-03-16 00:00:00.000   4050.00   1
AROUT   10558   1997-06-04 00:00:00.000   1060.00   2
AROUT   10707   1997-10-16 00:00:00.000   780.00   3

CREATE FUNCTION [dbo].[GetCustOrders_GT_X]
   (@CustomerID AS varchar(10), @nThreshold AS decimal(14,2))
RETURNS @tOrders TABLE (OrderID int, CustomerID varchar(10), 
                     OrderDate datetime, OrderAmount decimal(14,2))
AS
BEGIN
   INSERT INTO @tOrders
          SELECT  OH.OrderID, CustomerID, OrderDate,
                  (UnitPrice  * Quantity) as Orderamount
   FROM Orders OH
      JOIN [dbo].[Order Details] OD
            ON OH.OrderID = OD.OrderID
           WHERE CustomerID =  @CustomerID AND 
             (UnitPrice  * Quantity)  > @nThreshold  
   ORDER BY OrderAmount  DESC
RETURN
END

Go


DECLARE @nNumOrders int, @nMinAmt decimal(14,2)
SET @nNumOrders = 2
SET @nMinAmt = 5000.00

SELECT CustomerID FROM Customers 
     WHERE  (SELECT COUNT(*) FROM 
     DBO.GetCustOrders_GT_X(CustomerID,@nMinAmt)) >=@nNumOrders

-- ResultsHUNGO
QUICK
ERNSH
SAVEA
RATTC

-- To get the actual orders         >         5000 for these 5 customers, 
-- we can turn the query above into a derived table, and then 
-- use a CROSS APPLY


SELECT MaxOrders.* FROM
   (SELECT CustomerID FROM Customers
      WHERE  (SELECT COUNT(*)  FROM 
        dbo.GetCustOrders_GT_X(CustomerID,@nThresholdAmt)) >= 
          @nNumOrders) as CustList
   CROSS APPLY GetCustOrders_GT_X(CustomerID,@nThresholdAmt) 
     AS MaxOrders

-- First Example of inserting XML into a table of columns
DECLARE @cXMLDoc XML
declare @hdoc int
SET @cXMLDoc = '<AddressType  >
   <AddressRecord
       AccountID = "1" 
       Street="31 Main Dr" City="Philly" State="PA" Zip="12345"/>
   <AddressRecord
       AccountID = "2" 
       Street="1 Wilson Dr" City="Newark" State="NJ" Zip="22222"/>
</        AddressType        >        '

EXEC sp_xml_preparedocument @hdoc OUTPUT, @cXMLDoc

SELECT * FROM OPENXML (@hdoc, '/AddressType/AddressRecord',1)
  WITH (AccountID int,Street varchar(100), City varchar(100), 
        State varchar(10), ZipCode varchar(13))


-- Second example of inserting XML into a table of columns
-- uses the Address tag to specify nested columns

DECLARE @cXMLDoc XML
declare @hdoc int
SET @doc = ' <customer>
<        Customernum        >        48456        </        Customernum        >
<        Firstname        >        Kevin        </        Firstname        >
<        Lastname        >        Goff        </        Lastname        >
<        Address        >
   <city>Allentown</city>
   <state>PA</state>
</        Address        >
</        customer        >        '

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

DECLARE @tTemp TABLE (Customernum int, Firstname char(50), 
    Lastname char(50), City char(50), State Char(10))

insert into @ttemp
  SELECT Customernum,firstname,lastname,city,state
  FROM OPENXML (@idoc, '/customer',2)
  WITH (Customernum int,Firstname varchar(50),Lastname varchar(50),
        city varchar(50) './Address/city', 
        state varchar(50) './Address/state')

-- Performing a partial text search inside an XML column

declare @tTest table (address xml)

insert into @ttest values ('<Address >
   <AddrRecord
       AccountID = "1" 
       Street="31 Main Dr" City="Newark" State="NJ" Zip="11111" />
   </Address>' )

insert into @ttest values ('<Address >
   <AddrRecord
       AccountID = "2" 
       Street="1 Wilson Rd" City="Philly" State="PA" Zip="22222"/>
   </Address>' )

SELECT * FROM @ttest WHERE
 Address.exist('/Address/AddrRecord [contains(@City,"hil")]') = 1

DECLARE @tUser TABLE 
     (UserName varchar(100),Status char(10),UserPK int)

INSERT INTO @tUser exec  [dbo].[cgsValidateUserID] 'KGOFF','KGOFF'
SELECT * FROM @tUser