Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5

Exporting normalized relational data from database to flat file format

Sometimes you need to export relational normalized data into flat files where a single row comes from various tables. For example, say you want to export all customer records along with their work and home address, and primary phone number in a single row. But the address and contact information are coming from different tables and there can be multiple rows in those table for a single customer. Sometimes there can be no row available in address/phone table for a customer. In such a case, neither INNER JOIN, nor LEFT JOIN/OUTER JOIN will work. How do you do it?

Solution is to use OUTER APPLY.

Consider some tables like this:

Customer Table

Contact table

We need to create a flat file export from this where the output needs to look like:

There are some complex requirement in the output:

• If customer has multiple phone, then it needs to select the one which is flagged as primary.
• If customer has no home address, then it needs to show “No home address” instead of NULL.
• It needs to tell if the phone address we got is primary phone or not.

The query to generate this will be:

SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName,
    c.DOB,

    'HomeAddress' = 
    CASE 
        WHEN home.ContactValue IS NULL THEN 'No Home Address'
        ELSE home.ContactValue
    END,
    work.ContactValue,
    phone.ContactValue as PrimaryPhone,
    'IsPhonePrimary' = 
    CASE 
        WHEN phone.IsPrimary = 1 THEN 'Yes'
        ELSE 'No'
    END
FROM Customer c

OUTER APPLY (
    SELECT TOP 1 ContactValue from Contact WHERE CustomerID = c.CustomerID
    AND ContactType = 'HomeAddress'
    ORDER BY IsPrimary DESC
) AS home

OUTER APPLY (
    SELECT TOP 1 ContactValue from Contact WHERE CustomerID = c.CustomerID
    AND ContactType = 'WorkAddress'
    ORDER BY IsPrimary DESC
) AS work

OUTER APPLY (
    SELECT TOP 1 ContactValue, IsPrimary from Contact WHERE CustomerID = c.CustomerID
    AND ContactType = 'Phone'
    ORDER BY IsPrimary DESC
) AS phone

All the tricks are in the OUTER APPLY blocks. OUTER APPLY selects the row that needs to appear as the value of the columns in the output after the customer table fields.

The primary address is selected by reverse ordering the rows selected from Contact table by IsPrimary field. Thus the rows having True comes first.

Website diagnostics page to diagnose your ASP.NET website

Whenever you change web.config file or deploy your website on a new environment, you have to try out many relevant features to confirm if the configuration changes or the environment is correct. Sometimes you have to run a smoke test on the website to confirm if the site is running fine. Moreover, if some external database, webservice or network connectivity is down, it takes time to nail down exactly where the problem is. Having a self-diagnostics page on your website like the one you see on your printer can help identify exactly where’s the problem. Here’s a way how you can quickly create a simple self-diagnostics page in a single page without spending too much effort. This diagnostics page tests for common configuration settings like connection string, ASP.NET Membership configurations, SMTP settings, <appSettings> file paths and URLs items and some application specific settings to confirm if the changes are all correct.

Read how to build such a diagnostics page:

http://www.codeproject.com/KB/aspnet/selfdiagnostics.aspx

Vote for me if you find this useful.

WatinN to automate browser and test sophisticated ASP.NET AJAX sites

WatiN is a great .NET library for writing automated browser based tests that uses real browser to go to websites, perform actions and check for browser output. Combined with a unit test library like xUnit, you can use WatiN to perform automated regression tests on your websites and save many hours of manual testing every release. Moreover, WatiN can be used to stress test Javascripts on the page as it can push the browser to perform operations repeatedly and measure how long it takes for Javascripts to run. Thus you can test your Javascripts for performance, rendering speed of your website and ensure the overall presentation is fast and smooth for users.

Read this article for details:

http://www.codeproject.com/KB/aspnet/watinajaxtest.aspx

I have written some extension methods for WatiN to help facilitate AJAX related tests, especially with ASP.NET UpdatePanel, jQuery and dealing with element positions. I will show you how to use these libraries to test sophisticated AJAX websites, like the one I have built - Dropthings, which is a widget powered ASP.NET AJAX portal using ASP.NET UpdatePanel, jQuery to create a Web 2.0 presentation. You can simulate and test UpdatePanel updates, AJAX calls and UI update and even drag & drop of widgets!

You can see the implementation of automated tests in my open source project codebase.

Tests written using WatiN replaces the need for human driven tests and thus sheds significant time off your regular regression test suite. Moreover, it empowers developers with a way to quickly run regression tests whenever they need to, without waiting for human QA resource’s availability. When you hook it with xUnit like test frameworks and integrate with your continuous build, you can run UI tests automatically to test all the UI scenarios overnight after your nightly build and generate reports without requiring any manual intervention.

User story is worthless, Behavior is what we need

User Story is suitable for describing what user needs but not what user does and how system reacts to user actions within different contexts. It basically gives product team a way to quantify their output and let their boss know that they are doing their job. As a developer, you can’t write code from user stories because you have no clue on what what is the sequence of user actions and system reactions, what are the validations, what APIs to call and so on. As a QA, you can’t test the software from user stories because it does not capture the context, the sequence of events, all possible system reactions. User stories add little value to dev lifecycle. It only helps product team understand how much work they have to do eventually and it helps finance team get a view on how much money people are talking about. But to UI designers, solution designers, developers, they are nothing but blobs of highly imprecise statements that leave room for hundreds of questions to be answered. The absence of “Context” and “Cause and Effect”, and the imprecise way of saying “As a...I want... so that...” leaves room for so many misinterpretations that there’s no way development team can produce software from just user stories without spending significant time all over again analysing the user stories. Software, and the universe eventually, is all about Cause and Effect. The Cause and Effect is not described in a user story. 

Unlike user stories, the “Behavior” suggested by Behavior Driven Development (BDD) is a much better approach because the format of a behavior (Givencontext, When event, Then outcome), when used correctly, lets you think in terms of sequence of events, where the context, event and outcome are captured for each and every action user or system does, and thus works as a definite spec for designing the UI and architecture. It follows the Cause and Effect model, thus can explain how the world (or your software) works. It can be so precise that sometimes a behavior work as guideline for a developer to write a single function! Not just the develoeprs, even the QA team can clearly capture what action they need to perform and how the system should respond. However, to get the real fruit out of behaviors, you need to to write them properly, following the right format. So, let me give you some examples on how you can write good behaviors for UI, business layer, services and even functions and thus eliminate repeated requirement analysis that usually happens throughout the user-story driven development lifecycle.

Read more about how user stories suck and if behavior is used throughout the development lifecycle, it can greatly reduce repeated requirement analysis effort and can make the communication between product, design, development and QA team much more effective:

http://www.codeproject.com/KB/architecture/userstorysucks.aspx 

If you like it, vote for it!