TomBecker.net

Recursive Functions in SQL 2000

Recently I was asked for a list of all the history associated with a row in a database. In this case when a row in the table was updated, we did an insert with the updates and updated the old row with a pointer to the new row. We needed to do this so we could show history when needed without having to create a history table.

So since I didn't know how many ancestors a row could have, I decided to use recursion. And for those of you who never had a programming class with me know that I really don't like recursion, it confuses me. But it had to be done. So below is the recursive function that can be called to get a list of all ancestors of a given row:

CREATE FUNCTION [dbo].[udf_GetAncestors] 

(@CurrID int)  

RETURNS varchar(50)  AS  
BEGIN 

DECLARE
@ancestorIDs varchar(50),
@NextID INT


SELECT  @NextID  = ItemID  
FROM   Items
WHERE RevisedItemID= @CurrID;

SET @ancestorIDs = Convert(varchar, @CurrID);

IF  @NextID IS NOT NULL BEGIN
  SET @ancestorIDs = '''' + @ancestorIDs + ''', ' + dbo.udf_GetAncestors(@NextID);
END

Return @ancestorIDs 

END

Labels: SQL

posted by Tom Becker at 6/26/2009 | 0 Comments

The Fighting Cox

This was the second time we have played this team ( didn't write about the first one), and both results were pretty much the same, a decisive victory for Bottom of the Fifth.

The first inning though had us worried. We were first at bat and we scored a few runs and we started off strong. We get into the field and make some silly errors that put them in the lead. An over throw to second sent the ball just off the dirt behind first base. I run to get it and a hit that should have been held to a single with no one coming into home was converted to a triple and 1 or 2 runs came in.

After the first inning though we got it together a bit. Richie hit a grand slam, and I homered in my third at bat. One of the best hitting nights we've had in a while.

The funniest part of the game had to be our pitcher, Craig. He was pitching well the entire game, but his fielding...well left something to be desired. We had a large amount of slow dribblers go straight to him and for some reason he couldn't pick them up. And when he did, his throws were not very accurate. He threw one to me on first at my shoes, and one to third which went almost straight down and rolled over there. Needless to say we didn't get the outs on that one.

But despite a rough start and some crazy little fielding errors we won the game 12-5.

Next week is a double header, but I'm not sure against who, since all these rain outs have messed with the schedule quite a bit. I guess you'll just have to wait till next week to find out.

Labels: Softball

posted by Tom Becker at 6/24/2009 | 0 Comments

Extension Methods

So while running around playing with Linq, I just happened across another article by ScottGu, that talks about extension methods. And I have decided that they are truly awesome.

Extension methods allow you to add methods to existing types. In ScottGu's article he adds a called IsValidEmailAddress method to the string type. I originally had great plans on using this in a project to add some extension methods to a base type so they would show up on all the children. And then I realized that I could do the same thing by just writing the function in the base class instead. Same result. So the real use for these things are when you cannot modify the type definition.

So below is the extension method that I originally was going to use. It has all the syntax you need and it worked. I just realized that it was overkill, however awesome it may be. Lookup and LookupElement are parent classes for all of the lookup objects in the project. Lookup is a list of lookupElements.

public static string GetTextFromValue(this Lookups.Lookup L, int ID){
     string text = "";
     foreach (Lookups.LookupElement le in L) {
         if (le.Value == ID){
             text = le.Text;
             break;
         }             
     }
     return text;
 }

So after building this a new method would show up in IntelleSense when you used a Lookup object. And you would use it just like you would a normal method call.

Labels: .NET

posted by Tom Becker at 6/12/2009 | 1 Comments

Linq, Lambda Expressions and Predicate Delegates

I have recently been giving the opportunity to play around a bit more with Linq and while playing around with that I have run into the related topics of Lambda Expressions and Predicates. This post will be broken up into 3 sections (can we guess what they are?) and will just briefly provide an over view for each.

Linq

Linq stands for language-integrated query, and it allows you to query your data sets in code instead of heading back to the database in order to get a subset, and without having to loop through code. The syntax is very similar to SQL.

For example lets say I had a list of people, and I wanted to pull back everyone who lived in Richmond. Previously I would have to go back to the database and reselect my list of people or maybe loop through the list and do it that way. But using Linq I can do this much easier.

List<People> myPeeps = new List<People>();
//Fill list
var results = from p in myPeeps
            where p.Location= "Richmond"
            orderby p.Name
            select p;

As you can see this syntax is very similar syntax to SQL. You can now take the results and bind them a control, or do any sort of computations on it.

Lambda Expressions

Lambda Expressions are defined by MSDN as "...an anonymous function that can contain expressions and statements, and can be used to create delegates or expression tree types."

So what exactly that means I'm not to sure, but it makes it real easy to filter things out inline and simplifies the above Linq query a bit. Using the same situation above the query changes to

 myPeeps.Where(p => p.Location== "Richmond");

Pretty simple no? It doesn't have all the same power as Linq since you can really order or use multiple criteria and the like, but it is very simple and easy to use.

Predicates Delegates

Predicates are defined as "...the method that defines a set of criteria and determines whether the specified object meets those criteria." What that means is that you can define a function that returns a boolean to evaluate if your item in a list meets certain criteria. Again using the above example, I define the actual predicate delegate.

 private bool IsRichmonder(People p) {
    return p.Text == "Richmond";
 }

And once that is done, I just pass the predicate into the select clause on my list.

myPeeps.Select(IsRichmonder);

Summary

All of these examples have been pretty simple, but I hope you can see the power of all of these methods when properly leveraged.

I relied on ScottGu's articles to learn about these and to write this post

Labels: .NET

posted by Tom Becker at 6/12/2009 | 0 Comments

Payerpath

So I forgot to write about last weeks games, but we had a double header last week against Commonwealth Chapel. We split the series 1-1. Both games were very close the first game with us only losing by a single run. Looking forward to playing them again as we are very closely matched.

Yesterday we played Payerpath, a team we have played before. We played well with very few errors fielding. We also batted very well, with I think everyone getting on base at least once. We won the game 15-2 I think, I don't have the score book here, and the Wife is no longer our scorekeeper so I don't have access to it.

I must say that the very last play by Will was one to remember, and a good way to end the game. Their batter hit a screamer between first and second base and he just reached out and snagged it. It was a good play and a good way to end the game. But I must say that the most interesting play of the game was when a batter from the other team hit a pop fly between homeplate and the pitcher's mound. Both our catcher, Erika, and pitcher, Craig, run of the ball and neither of them call it. So they both watch it hit the ground between them, when all either one of them had to do was reach out their glove.

So like I said over all a very good game. Next week Bottom of the Fifth will be playing "The Pros" at 7:30.

Labels: Softball

posted by Tom Becker at 6/09/2009 | 0 Comments