The asp.net RegularExpressionValidator email regex does not validate all valid e-mailaddresses

In a project I’ve been working on I discovered that the Visual Studo built in regex for email that you get when you work with the RegularExpressionValidator doesn’t work for some email addresses that actually are valid. This goes for email addresses that has for example a hyphen (-) or a dot (.) directly in front of the @, i.e. emailme-@hotmail.com.

The built in regular expression looks like this:

\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

There are a lot of resources on RegEx on the web so I wont walk it through, let’s just take a look at the problem described above and modify it instead of googling up a working one.

The second instance of \w+ means word boundary, 1 or more times. Change this + to a * to say 0 or more times instead. So now the complete regular expression looks like this:

\w+([-+.']\w*)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

Now all I want is a way to save this change in Visual Studio.

A pattern for sharing code between LINQ data objects

(2009-05-02 Important update below!)

Scenario:
You have a lot of tables in your database with columns such as EditDate and UpdateByUserId and you're using Linq as your data access method.

Problem:
Instead of updating these columns all over the place in your code, you want a centralized solution to make sure that these columns are updated whenever an entity is changed.

Discussion and Solution:
Disclaimer: This is what I've come up with, please comment on it should you see any problems with it. This article will assume experience with working with Linq and object oriented programming.

To keep this example lean and simple we will have only one common column between our entities. I will use the Northwind database, however I have edited the Customers and Products table, adding an EditDate column of type datetime.

This of course means that when the Linq to SQL classes are generated (the .dbml file) both the Product and the Customer class will have an EditDate property. My first take on this was to create a superclass and force the Product and Customer classes to inherit from it through a custom partial class definition, like so:

public abstract class Entity
{
    //Property definition copied from Product 
    //class in Northwind.designer.cs, “abstract” added:
    public abstract System.Nullable<System.DateTime> EditDate { get; set; }
}

public partial class Product : Entity
{
}

public partial class Customer : Entity
{
}

However, this gives some errors and warnings:

As you can see in the warnings, a solution to this would be to go in to the dbml-file and change the EditDate property in each class to be an override;

But I don’t really like this solution since this would require me to do this whenever I’ve changed something in the Customer table and regenerates this class… It seems to me that leaving the .dbml file (and it’s designer.cs code behind) as is is more maintainable. This is also the reason why I inherit from my Entity superclass in partial class definitions (of Product and Customer) instead of editing the Northwind.designer.cs file directly. This is possible thanks to the fact that the Linq generated classes are declared as partial.

So, my next approach was to create an interface with the shared property definition and then implement that interface through my partial class definitions;

interface IEntity
{
    //Property definition copied from Product 
    //class in Northwind.designer.cs:
    System.Nullable<System.DateTime> EditDate { get; set; }
}

public partial class Product : IEntity
{
}

public partial class Customer : IEntity
{
}

And this actually compiles just fine. As you can see, my partial class definitions does not implement the EditDate property, but the partial classes in the designer.cs file does.

But hey now, I wanted to share code between my entities. There is no way to share code via an interface… So, we actually have to combine this with a superclass that requires it’s inheritors to implement IEntity. That way we can work with the common column in code inside of that class. Let’s first have a look at the new Entity class;

public abstract class Entity
{
    public Entity()
    {
        if (!(this is IEntity))
        {
            throw new Exception("Class that inherits from Entity must also implement IEntity.");
        }
    }
}

I don’t know any programmatic way of enforcing the combination of class inheritance with a specific interface, so as you can see above I use the default constructor to check if the current object (this) is an IEntity. If not the exception message will hopefully give the developer running this code some hint on what to do. With this validation in place, the Entity will now be able to access members through the IEntity interface.

The Linq entity classes (Product and Customer in the designer.cs file) exposes an event of particular interest, the PropertyChanged event. This event is raised whenever a property value of such an object is changed. Thankfully, the event has the exact same name in all (both) classes, so let’s add this to our IEntity interface so that we can access it in the Entity superclass;

interface IEntity
{
    event PropertyChangedEventHandler PropertyChanged;

    System.Nullable<System.DateTime> EditDate { get; set; } 
}

Now, we can add some code to the Entity class;

public abstract class Entity
{
    private IEntity _entity;

    public Entity()
    {
        if (!(this is IEntity)) { /.../ }

        _entity = (IEntity)this;

        _entity.PropertyChanged += 
            new PropertyChangedEventHandler(_entity_PropertyChanged);
    }

    void _entity_PropertyChanged(object sender, PropertyChangedEventArgs e)
    {
        _entity.EditDate = DateTime.Now;
    }
}

First of all I have added a private class member of type IEntity, this is assigned in the constructor and now I can easily access all the members of the IEntity interface (which actually will run code in the generated designer.cs-file!).

To achieve my goal I hook up a method to the PropertyChanged event and in that method i update the EditDate property.

UPDATE 090502: When debugging the code today I found that the above eventhandler method causes an infinite loop. This is not strange since the change of EditDate also will trigger the PropertyChanged event. To handle this, the following if statement is added to the method;

void _entity_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
    if (e.PropertyName != "EditDate")
    {
        _entity.EditDate = DateTime.Now;
    }
}

The PropertyName property and the PropertyChangedEventArgs gives me the name of the property that got changed causing the PropertyChanged event to trigger. By checking that this isn’t equal to “EditDate” I can update the value of EditDate. This will cause the event to trigger again, but this time e.PropertyName will have the value of “EditDate” so the if statement will be skipped. Should you add code that also updates an UpdatedByUserId field, this too has to be checked in the if statement.

Before we can test this, we must update the partial class definitions so that they inherit from the Entity class;

public partial class Product : Entity, IEntity
{
}

public partial class Customer : Entity, IEntity
{
}

With this in place we can run some simple code to see that it works;

NorthwindDataContext dc = new NorthwindDataContext();
dc.Products.First().ProductName = "Altered Product Name";
dc.Customers.First().ContactName = "Altered Contact Name";
dc.SubmitChanges();

After this code is run, we can have a look in the database and find that the EditDate column of both tables also has been updated on these posts;

Conclusion:
Using this approach I have achieved a solution that allows me to share code between classes generated from the .dbml-file without creating any work when a regeneration of the file is needed. The only thing I have to remember to do is when I add a new entity (table) to the .dbml file that also has these common columns, then I have to make sure I create a partial class definition that makes the class inherit from the Entity class and implement IEntity, and that’s it;

public partial class Order : Entity, IEntity { /* An empty class */ }

No code is needed in this class defintion, the requirements of the IEntity interface should be fulfilled in the generated part of the class (that is based on your db design). (However, these class definitions might come in handy for other purposes.)

Here is a class diagram giving you an overview of the pattern;

The three bottom classes are the ones generated from the .dbml file, remember though that the Product and Customer class are complemented with partial class definitions that makes them inherit from Entity and implement IEntity.

A SQL case: The INTERSECT statement

I decided to explore another SQL case that also is inspired by Facebook. When you visit a friends profile you can see what friends you and he/she have in common.

 

I have extended the database from my previous post to include a FriendRelations table with the following columns and relations to the Persons table;

As in the previous post this is a many-to-many relationship, but it’s a bit special since the foreign keys of the relation table points to the same primary key. This means that a person can be friend with a person in two different ways, either via the PersonId1 column or the PersonId2 column. Let’s have a look at the test data before we continue;

(In my previous post i accidentally named two persons “Spiderman”, one of them is now changed to “Superman”)

So, for example, the first row to the right means that “Spiderman” and “Superman” are friends with each other. Another example is that “Superman” is friends with “Spiderman”, “Catwoman” and “Tintin”.

In this case there will be two given PersonIds, one that represents the currently logged in user (this might come from a session variable) and one that represents the person profile the user is currently visiting (this might come from a querystring variable). In the following SQL commands these IDs will be hardcoded, I will use 4 (“Spiderman”) as the logged in user and 1 (“Superman”) as the ID of the visited profile. If you study the data above you can see that both these persons are friends with “Catwoman”, so this is the final result set we want from our SQL question;

PersonId	PersonName
5	Catwoman

Let’s start with getting all the friends that “Superman” (PersonId = 1) has;

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 
WHERE
    fr.PersonId2 = 1

This will give the following result set;

But wait a minute, “Superman” is also friends with “Tintin”, where did he go? In the above SQL we only get friends where “Superman” is connected through the PersonId2 column of the FriendRelations table. We need to work some more SQL magic to include friends that are connected through PersonId1 as well. Let’s give it a shot;

 SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    fr.PersonId1 = 1 OR fr.PersonId2 = 1

So we add some OR statements to the JOIN and the WHERE, but this resultset still looks a bit odd. It does include “Tintin” but “Superman” isn’t friends with himself and he certainly isn’t friends with himself three times over! However there’s nothing strange with this result, since the SQL above will give us all rows that has any connection with PersonId 1 in the FriendRelations table. So, let’s just filter “Superman” out!

 

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 1 
    AND (fr.PersonId1 = 1 OR fr.PersonId2 = 1)

And now we get all the persons that are friends with “Superman” and nothing else;

Friends with “Superman”

And if we run the very same SQL statement but exchange the 1 for a 4, we get all persons that are friends with “Spiderman”;

Friends with “Spiderman”

As you can see if you compare these result sets, the only friend they have in common is “Catwoman”. To get only that row in the result set we can separate both our SELECT statements (the one with a 1 and the one with a 4) with INTERSECT and only get those rows that both of the SELECT statements have in common;

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 1
    AND (fr.PersonId1 = 1 OR fr.PersonId2 = 1)
    
INTERSECT

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 4 
    AND (fr.PersonId1 = 4 OR fr.PersonId2 = 4)

And finally we get the expected resultset;

Download the database here, this database also includes the final SQL command as a stored procedure.

Note, for an INTERSECT to work, the two SELECT statements has to have identical column lists, at least when it comes to type. In this case it is PersonId (int) and PersonName (varchar).

A SQL case: Relating groups based on membership relations

The other day when I visited a group page on Facebook and looked at the "Related groups" section I started to think about how that SQL question might be implemented.

 
Screen capture from the MSDN Sweden Facebook group.

This is not a list that the group owner can control, instead the list automatically shows groups that has a lot of members in common with the group you're currently visiting.

Without any knowledge of Facebooks design for this, let's assume that the database structure is like this, a very ordinary many-to-many relationship:

To have some test data I filled these tables with the following data.

As you can see the "Comic Book Characters" and the "Superheroes" groups have a lot of members in common while the "Celebrities" group is an island.

So, based on a given GroupId, how can we get a resultset that lists all of the other groups with a column that specifies the amount of shared members? Let's build this step by step, in fact I do this as I'm writing so the final solution is not totally clear to me yet, even though I have some idea. If you want to follow along and run these SQL commands you can download the database file here (this database also includes the final SQL command as a stored procedure).

Let's say that the given GroupId is 2 (so this might come from a querystring or some other input, in these commands the number 2 will be hard coded). This means that the final result we want is this:

GroupId	GroupName	SharedMemberCount
1	Comic Book Characters	4
3	Celebrities	0

Let's start with selecting all groups except the given one;

SELECT
    GroupId, GroupName
FROM
    Groups
WHERE
    GroupId <> 2

Now, we will need a nested select statement to add the third column, I usually put the outer structure in place, and then I start writing the SELECT statement within the parenthesis:

SELECT
    GroupId, GroupName,
    (SELECT something) AS SharedMemberCount
FROM
    Groups
WHERE
    GroupId <> 2

Let's start with just counting the total numbers of members in each group, to do this the Groups table needs an alias so that we can distinguish the two GroupId columns;

SELECT
    GroupId, GroupName,
    (SELECT
        COUNT(*)
    FROM
        PersonGroupMemberships AS pgm
    WHERE
        pgm.GroupId = g.GroupId    
    ) AS SharedMemberCount
FROM
    Groups AS g
WHERE
    GroupId <> 2

This is the current resultset;

 

Now we need to filter the inner select so that it only counts the members that are also members in the given group (GroupId=2).

SELECT
    GroupId, GroupName,
    (SELECT
        COUNT(*)
    FROM
        PersonGroupMemberships AS pgm
    WHERE
        pgm.GroupId = g.GroupId
        AND pgm.PersonId IN 
            (SELECT PersonId
            FROM PersonGroupMemberships 
            WHERE GroupId=2)    
    ) AS SharedMemberCount
FROM
    Groups AS g
WHERE
    GroupId <> 2

And really, this was no brain surgery. We simply say that the PersonId of the rows we count has to have a value that corresponds to any PersonId that is a member of group 2 by using the IN statement and yet another nested SELECT statement.

Next we should add sorting so that the groups with the most shared members comes on top, I also add a TOP statement since we probably just want the most related groups, not all groups. These changes wont make any difference with this testdata, but they would if we had more groups:

SELECT TOP 4
    GroupId, GroupName,
    (SELECT
        COUNT(*)
    FROM
        PersonGroupMemberships AS pgm
    WHERE
        pgm.GroupId = g.GroupId
        AND pgm.PersonId IN 
            (SELECT PersonId
            FROM PersonGroupMemberships 
            WHERE GroupId=2)    
    ) AS SharedMemberCount
FROM
    Groups AS g
WHERE
    GroupId <> 2
ORDER BY
    SharedMemberCount DESC

And now we get the resultset we wanted;

 

To think about
Of course you wouldn't want to display the "Celebrities" group in this case, since it has no members in common with the "Superheroes" group. How could you filter the resultset so that it only includes groups where SharedMemberCount is more than zero? You might wanna use this technique.

(By the way, I have installed SQL Server 2008 Express, and it gives neat intellisense when working with SQL queries! :-)

How to get javascript intellisense for Asp.NET AJAX in a usercontrol (ascx) file

To get javascript intellisense for jQuery and your own scripts inside a usercontrol file you can use the following hack at the top of the ascx-file (after the @Control directive).

<% if (false) { %>
    <script src="scripts/jquery-1.3.2.js" type="text/javascript"></script>
    <script src="scripts/myScripts.js" type="text/javascript"></script>
<% } %>

This simply means that when you work with the file in Visual Studio, it will give you intellisense for these javascript libraries. But in runtime the if statement will be false so the scripts will not be included again (they shouldn't because they should already be included by the page that hosts the usercontrol. Remember, this hack is for Visual Studio javascript intellisense only).

But what about the Asp.NET AJAX framework (whit the $get, $addHandler methods etc...)? One workaround I figured out is to follow these steps:

1. Load an .aspx page that contains a ScriptManager in your browser.
   
2. View the source and copy out the src for a script tag that contains "ScriptResource.axd" (marked text in this image):
   
   
3. Paste this after localhost:port in the address field of the browser so that the script file loads (I only got this to work in Firefox):
   
   
4. If it starts with the comment in the image above, go on to the next step, if not return to step number two and get the next reference that includes "ScriptResource.axd".
   
5. Once you've found the MicrosoftAjax.debug.js file, copy the entire contents, paste it into a .js-file and put it in your project.
   
6. Now you can reference that file from your ascx file and get intellisense for Asp.NET AJAX even in a usercontrol file:

Finally, an appropriate quote: "Oh the troubles we put ourselves through for pretty IntelliSense.".

Introducing the Ajax ColumnListBoxExtender

Today I'm proud to present the first release of my first open source project; The Ajax ColumnListBoxExtender. The idea of the control is to give the possibility to layout an asp:ListBox with a lot of options in multiple columns instead of one single column.

Check out the project homepage at http://ajaxcolumnlistbox.codeplex.com and the live samples and documentation at http://ajaxcolumnlistbox.deap.nu.

I wont go in to the details of how this is developed, but it is open source so feel free to download the code from the project home page and have a look at it. I can say I've learned a lot both about javascript and Asp.NET AJAX developing this.

Lesson blog: Creating a plugin accepting application

Last week we had a lesson describing a simple sample of how you could create an application that accepts plugins, using an interface approach. This is my take on it;

First of all you need to decide quite exactly what the plugin developer should be possible to do. For this example I have created a simple Win Forms application that shows a list of contacts and allows the user to edit the contacts.

The Main Window of the application:

When the user double clicks a contact a simple editing form is opened:

When the user clicks Save this form is closed and any changes are reflected in the main window. But this form is quite dull and simple. Wouldn't it be cool if you could let some other developer attach a plugin to the application that will change the layout and possibly the behavior of this form? That's what we're going to do.

I wont go into the details of how the above application is built, if you want to follow along download this starting point here.

Some considerations has already been made when it comes to the plugin part, so lets have a look at the solution as it looks now;

PluginHost is the Windows Application and PluginLib is a Class Library that is referenced by PluginHost. PluginLib contains the Contact business class that has one string property for each peace of data on each contact (i.e. Firstname, Lastname etc). This is a separate project because the resulting dll is what will be shared both by the Host application and any Plugin "applications".

The host application is made up of two Form classes, MainWindow and DefaultContactEditor. Since the editor window might be replaced by a plugin some efforts has been made to keep as much logic as possible in the MainWindow class. Let's have a look att some of the code in the DefaultContactEditor class;

public Contact ContactToEdit { get; set; }
public event EventHandler ContactSaved;

public void LoadContact(Contact c)
{
    tbFirstname.Text = c.FirstName;
    tbLastname.Text = c.LastName;
    tbEmail.Text = c.Email;
    tbMSN.Text = c.MSN;
    tbHomepage.Text = c.Homepage;
    ContactToEdit = c;
}

private void SaveEditedContact()
{
    ContactToEdit.FirstName = tbFirstname.Text;
    ContactToEdit.LastName = tbLastname.Text;
    ContactToEdit.Email = tbEmail.Text;
    ContactToEdit.MSN = tbMSN.Text;
    ContactToEdit.Homepage = tbHomepage.Text;
    if (ContactSaved != null)
    {
        ContactSaved(this, EventArgs.Empty);
    }
}

The LoadContact method is called from the MainWindow when the edit window is opened, and it fills up the windows textboxes with values from the given Contact object and assigns this object to the class member ContactToEdit. It is placed in a separate method instead of in the constructor, because later on an interface will require this method (ideally the interface should require a constructor receiving a contact object but as far as I know that is not possible).

The SaveEditedContact is called when the user clicks the save button in the edit window. This method ends by triggering the ContactSaved event that the MainWindow is listening to.

Now, let's have a look at some of the code in the MainWindow class;

private void lvContacts_MouseDoubleClick(object sender, MouseEventArgs e)
{
    if (lvContacts.SelectedItems.Count > 0)
    {
        Contact selectedContact = (Contact)lvContacts.SelectedItems[0].Tag;
        DefaultContactEditor editor = new DefaultContactEditor();
        editor.LoadContact(selectedContact);
        editor.ContactSaved += new EventHandler(editor_ContactSaved);
        editor.Show();
    }
}

private void editor_ContactSaved(object sender, EventArgs e)
{
    ((Form)sender).Close();
    DisplayContacts();
}

The first method here is an event handler for the double click event for the ListView control that lists the contacts in the main window. After assuring that one item is selected I get a reference to the selected Contact object and creates a new instance of the edit window. Here you can see the call to LoadContact. Also, you can see that the ContactSaved event is attached to the editor_ContactSaved method. This method will ensure that the edit window is closed when the contact is saved and it calls a method - DisplayContacts - that will redraw the ListView control reflecting any changes that might has been made.

Now to the plugin fixing. First, let's create an interface from some of the members in the DefaultContactEditor class. I call this interface IContactEditor and put it in the PluginLib project;

public interface IContactEditor
{
    Contact ContactToEdit { get; set; }
    event EventHandler ContactSaved;

    void LoadContact(Contact c);
}

After that I make sure that the DefaultContactEditor implements this interface, by adding it to the inheritance list;

public partial class DefaultContactEditor : Form, IContactEditor

Nothing more is needed here since the interface members already are implemented in DefaultContactEditor.

The idea now is to change the code that opens the editor window so that it uses a plugin if available and if not it will use the DefaultContactEditor. Let's start with creating a helper class in the PluginHost application;

public static class PluginUtility
{
    private const string PLUGIN_PATH = @"Plugins\";

    public static IContactEditor GetEditorWindow()
    {
        IContactEditor result = null;

        try
        {
            foreach (string file in Directory.GetFileSystemEntries(PLUGIN_PATH, "*.dll"))
            {
                Assembly dllAssembly = Assembly.LoadFrom(file);

                foreach (Type t in dllAssembly.GetTypes())
                {
                    //Find class that inherits from Form and implements IContactEditor
                    if (t.IsPublic && !t.IsAbstract &&
                        t.IsSubclassOf(typeof(Form)) &&
                        t.GetInterface("IContactEditor") != null)
                    {
                        result = (IContactEditor)dllAssembly.CreateInstance(t.FullName);
                        break; //Exit foreach when plugin is found
                    }
                }

                if (result != null)
                    break; //Exit foreach when plugin is found
            }
        }
        catch { }

        //If result still is null, return default contact editor
        if (result == null)
        {
            result = new DefaultContactEditor();
        }

        return result;
    }
}

First we declare a constant containing the relative path to the folder where the plugin has to be placed. This folder has to be created in the output directory of the application, i.e. bin/Debug.

To keep the example simple this application will only accept one plugin. That one plugin, if existing, will be found in the GetEditorWindow method that returns an IContactEditor. In the first foreach we loop all dll-files within the Plugins directory. Foreach dll we loop the types that exists in the dll and using an if statement we look for classes that are public, not abstract, that inherits from Form and implements IContactEditor. If such a type is found we call the CreateInstance method of the Assembly object, which will create an instance of the found type. This instance is cast to an IContactEditor and assigned to the result variable. After that we make sure we exit both foreach loops. If no plugin is found, the last if will be true and the result variable will be assigned an instance of the DefaultContactEditor class.

Now, the double click event of the Main Windows ListView could be updated as follows;

private void lvContacts_MouseDoubleClick(object sender, MouseEventArgs e)
{
    if (lvContacts.SelectedItems.Count > 0)
    {
        Contact selectedContact = (Contact)lvContacts.SelectedItems[0].Tag;
        //OLD: DefaultContactEditor editor = new DefaultContactEditor();
        IContactEditor editor = PluginUtility.GetEditorWindow();
        editor.LoadContact(selectedContact);
        editor.ContactSaved += new EventHandler(editor_ContactSaved);
        //OLD: editor.Show();
        ((Form)editor).Show();
    }
}

Now lets build a plugin by adding another class library to the solution, I call it EditorPlugin. To speed things up I add a copy of the DefaultContactEditor class to this class library (make sure you change the namespace and the class name to EditorPlugin in the code view and in the designer.cs file!). Since this class inherits from Form I also need to reference System.Drawing and System.Windows.Forms which isn't included from the beginning in a Class Library (the compiler will inform you about this). We also need access to the Contact class and the IContactEditor interface; a reference to the PluginLib will solve this. After that I changed the design of the form as follows;

All that is altered here is a minor layout change, putting together the first and lastname textboxes on one single row. Now, lets build this class library and put the resulting EditorPlugin.dll in the Plugins directory of the PluginHost application.

Now when you run the application this restyled window will be used for editing. Download the complete solution here.

Try for yourself;

• Add a checkbox by the MSN field with the caption "Same as email", setting the MSN textbox to the same value of the email textbox when the checkbox is checked.
• What would you have to inform plugin developers about so that they will be able to develop a plugin for this application?
• Add a dialog in the application that let's the user add new contacts to the list. Make it possible for plugin developers to replace this dialog.

SQL: Select from your select - filtering on calculated columns

Consider this SQL that lists products from the Northwind database;

SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT 
FROM 
    Products

To the right you can see the resulting table. The last column shows a calculated value based on the UnitPrice column multiplied with 1,25.

Now, lets say you would like to filter the result including only products that has a UnitPriceWithVAT that is higher than 100.

You can't do like this;

SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT 
FROM 
    Products
WHERE
    UnitPriceWithVAT > 100 --This wont work

This will only give you the error message "Invalid column name 'UnitPriceWithVAT'.", i.e. the calculated column is not known in the WHERE clause.

There are a few different ways to solve this problem. The most obvious one is to repeat the calculation in the WHERE clause;

--Solution A
WHERE
    UnitPrice*1.25 > 100

But it's always good to avoid repeated code, so to accomplish that we could make use of a temporary table. In MS-SQL this is done with the INTO statement followed by a table name that starts with #. Then we can select from the temporary table and clean up;

--Solution B
SELECT 
    ProductName, UnitPrice, UnitPrice*1.25 AS UnitPriceWithVAT
INTO
    #tempTable    
FROM 
    Products

SELECT * FROM #tempTable WHERE UnitPriceWithVAT > 100

DROP TABLE #tempTable

However, there is an even more efficient and, in my opinion, more elegant way to do this;

--Solution C
SELECT * FROM
    (
        SELECT 
            ProductName, UnitPrice, 
            UnitPrice*1.25 AS UnitPriceWithVAT 
        FROM 
            Products
    ) AS t
WHERE
    UnitPriceWithVAT > 100

In this last version the inner SELECT statement is "turned into a table" that the outer SELECT selects from, making it possible to filter on the calculated column. The important part here is to give the inner SELECT a table alias (AS t), otherwise it will not work.

In this simple example it might seem easier to just repeat the calculation in the where clause (Solution A), but what if you have a SQL-statement with many calculated columns that also should be included in the filtering, and what if you change the calculation in the SELECT clause but forgets to do it in the WHERE clause?

All three solutions will give the same result. Which one do you prefer?

deap.nu released!

Please help me build my site at http://www.deap.nu. The personal homepage I started working on before christmas is finally released in its first version. Parts of it is in swedish but the "jQuery showing off" in the form of a silly word game ;-) is in english. Enjoy.

Causing a stackoverflowexception in the webserver(!)

The other day I was working on some coding at work and after adding quite an ordinary property (as follows) the browser went totally berserk, displaying some error message about "service unavailable" (in red, I might add ;-) and a while later the visual studio debugging prompt let me know that there was an stackoverflowexception in aspnet_wp.exe. That kind of behavior obviously sets one off, and after some misleading googling I finally spotted my fatal error, can you see it?:

private string myProperty;
public string _myProperty 
{
    get { return _myProperty; }
    set { myProperty = value; }
}

Well, what I accidentally did, as you can see above in the get block i return the property itself instead of the private member variable, causing an infinite loop... not good.

By the way, I prefer the following namingconvention when it comes to underscores and capitals, but thats not what goes at work. In the following example (with the same error!) I think the error is easier to spot:

private string _myProperty;
public string MyProperty 
{
    get { return MyProperty; } //Still wrong! Should be: return _myProperty;
    set { _myProperty = value; }
}

There are other reasons to the same error behavior, so google it if this isn't yours.

Implementing TinyMCE spellchecker with GoogleSpell in ASP.Net

A colleague and I spent a few hours this afternoon trying to find a way to implement spellchecking with the TinyMCE WYSIWYG editor. The editor has a spellchecking plugin, but according to the wiki documentation it requires PHP. However, one of the options in the spellchecking plugin is to use the Google Spelling web service and this should of course be possible to call through .net as well. Luckily we stumbled upon the "TinyMCE .Net package" before developing the solution ourselves. It really is strange that there is no comment about this on the TinyMCE Wiki. (I believe I will make one. ;-)

These are the steps you need to take to activate spellchecking in TinyMCE within a .Net website application: (this instruction assumes you have already implemented TinyMCE in your website)

1. Download the TinyMCE .Net Package.
2. In that download you will find a .dll-file - Moxiecode.TinyMCE.dll - add a reference to it from your website project.
3. Add the following tag in the HttpHandlers section in web.config:

   <add verb="GET,HEAD,POST" path="TinyMCE.ashx" type="Moxiecode.TinyMCE.Web.HttpHandler,Moxiecode.TinyMCE" />

4. Go to your javascript initialization code of your tinyMCE and add the bold parts of this snippet:

   tinyMCE.init({
       theme : "advanced",
       mode : "textareas",
       plugins : "spellchecker",
       theme_advanced_buttons3_add : "spellchecker",
       spellchecker_languages : "English=en,+Swedish=sv",
       spellchecker_rpc_url : "TinyMCE.ashx?module=SpellChecker"
   });

   Note that if you already are using the plugins setting, you only have to add "spellchecker" to the comma separated list, the same goes for your button configuring. If you omit the spellchecker_langugages you will get support for English, Danish, Dutch, Finnish, French, German, Italian, Polish, Portuguese, Spanish and Swedish out of the box, with english set as default. If you want to omit some languages use the format above for the languages you wish to support, and add a + in front of the one you want as default. The text before the = character is your custom label for the language and the text after is the ISO code for the language. Don't forget the spellchecker_rpc_url setting that will connect the spellchecking button with the Moxiecode.TinyMCE.dll which handles the call to the google spell service. For a sample of how the spell function looks, se this sample and click the sixth button in the last row (this sample might not use the google service, so the word suggestions etc might be different, but it will give you the look and feel of the spelling function).

I've had some trouble finding good documentation of the Google Spell Service and it seems as if it is no longer updated but still available to developers already using it (which should include the TinyMCE .Net package even though you haven't used it before). However, use at your own risk should Google decide to delete it. This is the only official documentation I can find and that isn't much...

Take control over your wizard

The following is common requirements when building a wizard:

• The first step should show some info before the wizard is actually starting thus the second step should not show a back button.
• The final step, after which saving/etc should occur should actually be the second last step so that the last step can show confirmation/thank you etc.

However, this is not the default behavior of the Wizard control in Asp.NET. The following aspx markup shows the desired behavior through it's content comments, but not through the code;

<asp:Wizard ID="Wizard1" runat="server" DisplaySideBar="false">
    <WizardSteps>
        <asp:WizardStep runat="server" title="Step 1">
            Welcome Step, information before wizard actually is started.
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Step 2">
            Start step, no going back button here.
        </asp:WizardStep>
        <!-- any number of steps here -->
        <asp:WizardStep runat="server" title="Step 3">
            Finish Step, finish button here so FinishButtonClick event 
            can be handled when going forward from here.
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Step 4">
            Thank You Step, no going back button here.
        </asp:WizardStep>
    </WizardSteps>
</asp:Wizard>

(Note: I almost always set DisplaySideBar to false, so that the user is forced to navigate the wizard in a linear way.)

The four steps now looks like this;

Step 1:

Step 2:

Step 3:

Step 4:

As you can see we need to manipulate the second, second last and last step to get the navigating buttons correct, and all we need to do is the following changes (bold);

<asp:Wizard ID="Wizard1" runat="server" DisplaySideBar="false"
    StartNextButtonText="Start">
    <WizardSteps>
        <asp:WizardStep runat="server" title="Step 1" AllowReturn="false">
            Welcome Step, information before wizard actually is started.
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Step 2">
            Start step, no going back button here.
        </asp:WizardStep>
        <!-- any number of steps here --> 
        <asp:WizardStep runat="server" title="Step 3" StepType="Finish">
            Finish Step, finish button here so FinishButtonClick event 
            can be handled when going forward from here.
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Step 4" StepType="Complete">
            Thank You Step, no going back button here.
        </asp:WizardStep>
    </WizardSteps>
</asp:Wizard>

And the steps now looks like this;

Step 1:

Step 2:

Step 3:

Step 4:

In the first step the forward button is still available, however the text of it is changed through the StartNextButtonText property of the Wizard control. Also the AllowReturn property of the first WizardStep is set to false, which causes the second step not to display a back button. For Step 3 the StepType is set to Finish, causing the rendering of a Finish button instead of a Next button, which should hint the user that after that there is no going back. In the final step StepType is set to Complete causing the wizard to not render any buttons at all.

Note that you can insert as many steps as you want between Step 2 and 3, and the user will be able to navigate back and forth between them. Also note that any saving or result calculations that should occur when the user finishes can be handled in the FinishButtonClick event of the Wizard control, and the final Complete step could be used to display the data given/results etc.