The need for T-SQL still exists

Since I began using Linq-To-SQL (L2S) I haven't written nearly as much T-SQL queries as I used to. It's been great. The best part comes when you look at the T-SQL that is generated and how optimized it is. So why would someone new to programming need to learn T-SQL or why would it benefit me to keep up-to-date on this language?

If you look at L2S you'll notice that it's not perfect! At first I didn't care because I loved it so much but now it's starting to bug me. In order to UPDATE a record you have pull back the object from the database. That does a SELECT statement. Then to update it, it does an UPDATE. The update is fine it's the SELECT that is the problem. It's unnecessary in most cases.

For example lets say I have a table:

VoteOptions
- OptionID int
- OptionValue varchar(64)
- Votes int

Lets assume I am not concerned with how many times a person votes/validation/etc. In order to update a record in this table using L2S.

int OptionID = 1;
MyDataContext db = new MyDataContext();
VoteOption option = db.VoteOptions.SingleOrDefault(vo => vo.OptionID==OptionID);
option.Votes += 1;
db.SubmitChanges();

So here we have db.VoteOptions.SingleOrDefault pulling back the entire VoteOption record. This record is small but what if it was bigger? What if we did it thousands of times? What a waste!

We can make a small adjustment here, still use L2S (and T-SQL), and be much more efficient.

int OptionID = 1;
MyDataContext db = new MyDataContext();
db.ExecuteCommand("UPDATE VoteOptions SET Votes=Votes+1 WHERE VoteID={0}", VoteID);

It's also 2 lines of code less. ExecuteCommand and ExecuteQuery methods + T-SQL make for a great combination and because it's built into my data context I still have the ability to use L2S for other stuff. After listening to this podcast about StackOverflow there is a point where Jeff mentions he does this on the site. Just something to keep in mind while using L2S!

Removing a list of strings from another list using Predicates

I was writing some code and wanted to remove a list of strings from another list, except I wanted to do it a few times. After looking at the code I wrote it looked very ugly. Using a new .NET 2.0 feature you can get rid of the ugly foreach loops though and cut it down to one line that looks nice. Here is the example:

List<string> menu = new List<string>();
menu.AddRange(new string[] {“watermelon”, “pizza”, “ice cream”, “soda”, “robbies mom”});

List<string> unavailable = new List<string>();
unavailable.AddRange(new string[] {“ice cream”, “robbies mom”});

//the old way would be a mess like this
List<string> temp = menu;
foreach(string item in temp) {
    foreach(string match in unavailable) {
        if(item == match)
            menu.Remove(item);
    }
}

//using the PredicateListMatch it takes one line
menu.RemoveAll(new PredicateListMatch(unavailable).Match);

This is the PredicateListMatch class that I am using to accomplish this.

using System;
using System.Collections.Generic;

public class PredicateListMatch {
    private List<string> _value;

    public PredicateListMatch(List<string> Value) {
        _value = Value;
    }

    public List<string> Value {
        get { return _value; }
        set { _value = value; }
    }

    public Predicate<string> Match {
        get { return IsMatch; }
    }

    public Predicate<string> NoMatch {
        get { return IsNoMatch; }
    }

    private bool IsMatch(string s) {
        bool match = false;
        foreach (string b in _value) {
            if (s.Trim().ToLower() == b.Trim().ToLower()) {
                match = true;
                break;
            }
        }
        return match;
    }

    private bool IsNoMatch(string s) {
        bool match = true;
        foreach (string b in _value) {
            if (s.Trim().ToLower() == b.Trim().ToLower()) {
                match = false;
                break;
            }
        }
        return match;
    }
}

PageGlimpse .NET library

Last week I heard about this cool service called PageGlimpse that provides screenshots of any website. They have an API available so I wrote a .NET wrapper for it. It is super easy to use and the download comes with a full example.

Download the PageGlimpse .NET API Library

UrlRewriting.NET IncludeQueryStringForRewrite bug fix

I have a bug fix for UrlRewriting.NET. The bug is with rewrite rules that use the IncludeQueryStringForRewrite setting. Take the following example rule:

<add name="Rewrite" virtualUrl="^~/pages/(.*)/(.*)"
rewriteUrlParameter="IncludeQueryStringForRewrite"
destinationUrl="~/pages/$1.asp?title=$2"
ignoreCase="true" />

Let's say I go the url: http://localhost/pages/1/My Title?test=Test1&blah=Blah1

How the module was handling this was taking the first querystring parameter and making it part of my last regex match. If you look at the request.querystring values you would see this:

title = MyTitle?test=Test1
test =
blah = Blah1

What my fix does is check the rewrite url before it is rewritten and if it finds a second question mark changes it to an ampersand. This might not be the best way to fix this but was the easiest since the code was a bit much. The results fixed the querystring values:

title = MyTitle
test = Test1
blah = Blah1

You can download the fix here:
http://files.rushfrisby.com/dotnetrush/code/UrlRewritingNet_20_Source.zip

I also converted the project to .NET 3.5 and changed some build settings for the release version to hopefully get it to run faster.

Yahoo! BOSS .NET Library

For my hack at this years' Yahoo! Open Hack Day I created a library for accessing the BOSS search API. You can get a copy of the library on my website:

Yahoo! BOSS .NET Library

Yahoo! Open Hackday 2008

In 2006 I went to the first Yahoo! Open Hackday and I am heading back Sept 12-13 for round 2. Last time I wrote the Flickr Wallpaper Rotator which as a hit. What kind of "hack" should I come up with this time?

Combine, minify, compress, and cache javascript and CSS

I read this post from Mads Kristensen about how he took ScriptResource.axd and WebResource.axd references and combined them into one script, then minified it, compressed it, and cached it on the server. I gave it a try and got it to work but I ran into a couple of bugs. I fixed the ones I found and also duplicated the process to work with CSS. The CSS version uses cache dependencies on the css files so if you change something the user will get it right away. The link below has the classes and a readme with how to implement it.

CompressorClasses.zip

Enjoy!

C# 3.0 Properties simplified

In versions previous to .NET 3.0 this is what a simple get/set property looked like:

private string _MyString = "";
public string MyString {
     get { return _MyString; }
     set { _MyString = value; }
}

Now in .NET 3.0 you can simplify this by typing:

public string MyString { get; set; }

So we've trimmed a few lines of code down to one and it makes our classes look a whole lot cleaner. These two examples compile exactly the same way so you don't have to worry about any performance issues. The only difference in 3.0 is that you reference your property instead of the private variable which doesn't make a difference one way or the other if you think about it. Yay for .NET!

Download DTS for SQL Server 2005 Express

Somehow in the SQL Server 2005 management studio I had import/export (Launched the DTS Wizard) options when I right clicked on my databases even if they were on an SQL Express version server. When I reformatted my computer and installed the latest SQL express (SP2) I noticed that these menu options were gone! (also they changed the wording of "Modify" to "Design")



I did some searching and found that you can install the DTS wizard through this SQL toolkit:



http://go.microsoft.com/fwlink/?LinkId=65111



Once it is installed you have to run the exe because I still don't see the Import/Export menu option in SMS. The exe is located at C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

AJAX Enabled Website Template Not Showing?

I recently reformatted my laptop and reinstalled everything. I installed Visual Studio 2005 and AJAX Extensions 1.0. When I went to go create a new website I did not see the familiar "AJAX-Enabled Website Template". If you are having the same problem here is the answer!



Download this zip file and put it in:

C:\Documents and Settings\<User Directory>\My Documents\Visual Studio 2005\Templates\ProjectTemplates

The next time you open up visual studio you will see the new template.