A Visual Explanation of Different SQL JOINs

One of my standard interview questions to ask beginner- and intermediate-level developers who say that they know SQL is “Please explain a LEFT OUTER JOIN to me.” You’d be amazed how many people who “know SQL” trip over this. Understanding how to return proper sets of data from multiple tables is fundamental.

Jeff Atwood has a great post where he uses the concepts of data sets and Venn Diagrams to visually explain what each type of JOIN returns. Check it out:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Learning to Go with the ‘Flow…

After a slumber of almost five long years, I am finally getting around to resurrecting this blog, and with this resurrection comes a repurposing of sorts.

During this hiatus, I embarked upon a new career, that of a software product manager.  I manage an enterprise-level, business process management software solution called CoreIntegrator® Workflow for a company called Computer Support Solutions, Inc.  The learning process has been a new and exciting one for me, and I am eager to share my experiences, insights, and questions with whomever cares to hear them.

So, first I should offer a little information about CoreIntegrator® Workflow, because I am sure that I will be talking about it from time to time…. 

Earlier versions of CoreIntegrator® Workflow date as far back as 1998.  Previous versions were known as “GPx” and simply “CoreIntegrator”.  Whereas these versions centered in purpose around automating specific Accounts Payable processes, CoreIntegrator® Workflow breaks down these narrow walls, allowing you to automate, simplify, and expedite near any business process that you can think of:

• Accounts Payable
• Accounts Receivable
• Employee Onboarding & Offboarding
• Vacation Requests
• Grant Proposal
• Claims Review
• Corporate Governance
• Recruiting
• Auditing
• Sales Lifecycle
• Help Desk
• Software Lifecycle
• Change Management
• and much, much more!

While I am sure that I will no doubt plug CoreIntegrator® Workflow from time to time, that is not the purpose of this blog.  It will stay committed to offering quality information on a variety subjects, including software development, product management, and business process management (BPM)/workflow, with a smattering of insights on specific topics such as Microsoft Dynamics GP (one of my company’s fortes) and Microsoft SharePoint. 

Now to explain the title of this article, “Learning to Go with the ‘Flow”….  With the publication of this post, I will be official renaming this blog from The Disturbed Buddha to Going with the Flow.  To any student of philosophy, these two titles are in harmony with one another, and the new title better represents the inclusion of the new topic of workflow/business process management (BPM) into the lineup. 

Highlighting a GridView Row When Clicking a CheckBox

You’ve seen it in Yahoo!® Mail and numerous other places.  When you click a CheckBox next to an item, that item’s row changes color.  When you uncheck the box, the row returns to its original color.

 

 

Basically, we just need to attach a bit of JavaScript to handle the click event of each CheckBox in the GridView.  Here is the JavaScript, if you were to see it by itself:

<script type="text/javascript"> 
    if (this.checked) { 
        document.getElementById('theRowID').style.backgroundColor='Blue'; 
    } else { 
        document.getElementById('theRowID').style.backgroundColor=''; 
    } 
</script>

Notice that we’re setting the background color to an empty string when the CheckBox is unchecked.  We do this because we only want to remove the extra styling that we added.  That way, if your GridView has alternating row styles, it will go back to its original color.

 

We are going to attach this event handler during the RowDataBound event of the GridView.  First, we check to make sure that it is a DataRow that we’re dealing with and not a header or footer.  Then we build the JavaScript using a StringBuilder (which you should use any time that you’re doing string concatenation).  We get the row’s ID from the arguments that were passed into the RowDataBound method:  e.Row.ClientID

 

We then just have to use the FindControl method on the current row and add the event and our JavaScript as an attribute to the CheckBox.  Simple as that!

 

VB.NET:

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
    If (e.Row.RowType = DataControlRowType.DataRow) Then 
        Dim script As New StringBuilder 
        script.Append("if (this.checked) {") 
        script.Append("document.getElementById('") 
        script.Append(e.Row.ClientID) 
        script.Append("').style.backgroundColor='Blue';") 
        script.Append("} else {") 
        script.Append("document.getElementById('") 
        script.Append(e.Row.ClientID) 
        script.Append("').style.backgroundColor='';") 
        script.Append("}") 
        CType(e.Row.Cells(0).FindControl("CheckBox1"), CheckBox).Attributes.Add("onclick", script.ToString) 
    End If 
End Sub

I’m sure that you can see how this code could easily be built upon to include fading in/fading out of the color or any other UI effect.

Determining a Browser’s Dimensions with Javascript

This is about as cross-browswer compatible as you can get:

<script type="text/javascript"> 
    var winW, winH; 
    if (self.innerWidth) { 
        winW = self.innerWidth; 
        winH = self.innerHeight; 
    } else if (document.documentElement && document.documentElement.clientWidth) { 
        winW = document.documentElement.clientWidth; 
        winH = document.documentElement.clientHeight; 
    } else if (document.body) { 
        winW = document.body.clientWidth; 
        winH = document.body.clientHeight; 
    } 
</script>

Executing Server-side Code from JavaScript

In the Asp.net forums, I often see the question asked, “Can I call server-side code from the client/JavaScript?” Almost invariably, the responses given are “No, the client cannot access the server,” or “You can only use WebMethods or PageMethods.” The first response is not entirely correct, and unfortunately, WebMethods and PageMethods are static methods and therefore have no way to directly access the page.

This is why I present to you the following “hack”. I call it a hack because there really should be some way built into the ASP.NET AJAX Extensions that allow this approach directly. Instead, it relies on using controls in a manner that they aren’t necessarily intended in order to obtain the desired result. But this “hack” does have a redeeming quality—it’s incredibly easy.

The Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %>         

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" > 
<head runat="server"> 
  <title>My Page</title> 
  <script type="text/javascript"> 
    function myClientButton_onclick() { 
    document.getElementById('myServerButton').click(); 
  } 
  </script> 
  <script runat="server"> 
    Protected Sub myServerButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles myServerButton.Click 
      Response.Redirect("http://www.asp.net/") 
    End Sub 
  </script> 
</head> 
<body> 
  <form id="form1" runat="server"> 
    <input id="myClientButton" type="button" value="Press Me" onclick="return myClientButton_onclick()" /> 
    <asp:Button ID="myServerButton" runat="server" style="display:none;" /> 
  </form> 
</body> 
</html>

Explanation:

What I am doing here is adding a server-side button control (<asp:Button runat=”server” …>). I then make the button invisible by adding style=”display:none;”. In the button’s server-side Click event, I do whatever it is I want to do on the server.

In this example, I am clicking a standard client-side button (<input type=”button” …>) to fire off a server-side redirect, but this could just as easily be called by selecting an item in a DropDownList, typing text into a textbox, etc. Clicking the client button calls the JavaScript .click() event of the server-side button control.

The Next Step:

“What if I need to pass arguments from the client-side script to the server-side script?” Well, this is easily done by putting server-side HiddenField controls on the page. Use the JavaScript to set their values (document.getElementById(‘myHiddenField’).value = “Hello World”;) and then use C# or VB.NET to retrieve them on the server (Dim myVar as String = myHiddenField.Value).

In my next article, I will apply this concept to create a rather easy approach to the Ajax Multi-Stage Download Pattern. Check back soon!

Tested on: Internet Explorer 7, Netscape Browser 8, Firefox 2, Safari 3, Opera 9.

Wrapping ASP.NET AJAX TabContainer Tabs

Check out Nazar Rizvi’s article on how to get your tab headers to wrap onto a second row for your ASP.NET AJAX TabContainer control.  Common problem; easy fix.

http://www.narizvi.com/blog/post/Two-rows-of-tab-headers-in-TabContainer-in-Ajax-Control-Toolkit.aspx

Handling Multiple Asynchronous Postbacks

Sometimes multiple asynchronous postbacks get triggered. Now, I’m not talking about situations where we want to disable a submit button, so that the user doesn’t click it fifty times waiting for something to happen. Instead, I’m referring to situations where we do want each postback to happen in the order it was fired.

However, when a page makes multiple asynchronous postbacks at the same time, the default action is that the PageRequestManager gives the most recent postback precedence. This cancels any prior asynchronous postback requests that have not yet been processed. (Get further explanation.)

So, let’s create a way to “queue up” our asynchronous postback requests and fire them off in order, one by one. First, let’s create an aspx page with three buttons inside of an UpdatePanel:

<asp:ScriptManager ID="ScriptManager1" runat="server" /> 
<asp:UpdatePanel ID="UpdatePanel1" runat="server"> 
    <ContentTemplate> 
        <asp:Button ID="Button1" runat="server" Text="Button" /> 
        <asp:Button ID="Button2" runat="server" Text="Button" /> 
        <asp:Button ID="Button3" runat="server" Text="Button" /> 
    </ContentTemplate> 
</asp:UpdatePanel>

There is no need to wire up any click events in the code-behind for our sample. While you could, all that we are concerned about is that they each cause a postback.

Next, let’s add some deliberate latency into the code so that our postback requests can pile up. Every postback to the server will now take 3 ½ seconds, so that is the fastest each request can be processed.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 
  System.Threading.Thread.Sleep(3500) 
End Sub

Now, let’s look at the JavaScript code that will manage the backed-up postback requests for us. Add this script block after the ScriptManager on the page but before the closing </html> tag.

<script type="text/javascript"> 
    var prm = Sys.WebForms.PageRequestManager.getInstance(); 
    prm.add_initializeRequest(InitializeRequestHandler); 
    prm.add_endRequest(EndRequestHandler);        

    var pbQueue = new Array(); 
    var argsQueue = new Array();       

    function InitializeRequestHandler(sender, args) { 
        if (prm.get_isInAsyncPostBack()) { 
            args.set_cancel(true); 
            pbQueue.push(args.get_postBackElement().id); 
            argsQueue.push(document.forms[0].__EVENTARGUMENT.value); 
        } 
    }       

    function EndRequestHandler(sender, args) { 
        if (pbQueue.length > 0) { 
            __doPostBack(pbQueue.shift(), argsQueue.shift()); 
        } 
    } 
</script>

The Code in Detail

First, we use the PageRequestManager to set up handlers for the beginning and end of each asynchronous request:

var prm = Sys.WebForms.PageRequestManager.getInstance(); 
prm.add_initializeRequest(InitializeRequestHandler); 
prm.add_endRequest(EndRequestHandler);

Queuing up the Postbacks…

Then we create an array to store the originator of each asynchronous postback that cannot be processed immediately, as well as an array to store any event arguments associated with the postback:

var pbQueue = new Array(); 
var argsQueue = new Array();

Then, at the beginning of each asynchronous postback, we check to see if the page is already in an asynchronous postback:

function InitializeRequestHandler(sender, args) { 
    if (prm.get_isInAsyncPostBack()) {...}

If it is, we cancel the new postback request, and instead, add the event target and arguments to our arrays:

args.set_cancel(true); 
pbQueue.push(args.get_postBackElement().id); 
argsQue.push(document.forms[0].__EVENTARGUMENT.value);

…and Executing Them

After each asynchronous postback completes, we check to see if there are any more queued up, and if so, we do a __doPostBack(). pbQueue.shift() pulls the first item out of the array and removes it.

function EndRequestHandler(sender, args) { 
    if (pbQueue.length > 0) { 
        __doPostBack(pbQueue.shift(), argsQueue.shift()); 
    } 
}

And that’s it. Run the page, and randomly click some buttons! If you watch the browser’s status bar, you’ll see the asynchronous postbacks piling up. Then, every 3 ½ seconds, you’ll see one of them being processed! (Remember, the 3 ½ seconds is just an arbitrary time that we added into this demonstration, and it has nothing to do with how the code really works.)

Note: If for some reason, you wanted to execute the asynchronous postbacks in reverse chronological order (i.e., the most recent requests get processed first), just replace the array.shift() command in the EndRequestHandler() with array.pop().

Disabling a Trigger Control During Asynchronous PostBack

Often, we want to disable the control that triggered an asynchronous postback until the postback has completed. This prohibites the user from triggering another postback until the current one is complete.

The Code

First add a ScriptManager to the page, immediately following the <form> tag.

<asp:ScriptManager ID="ScriptManager1" runat="server" />

Then add a Label wrapped in an UpdatePanel. This label will be populated with the date and time on each postback. We’ll also add a Button inside of the UpdatePanel to cause the postback.

<asp:UpdatePanel ID="UpdatePanel1" runat="server"> 
    <ContentTemplate> 
        <asp:Label ID="Label1" runat="server" Text="Label" /><br /> 
        <asp:Button ID="Button1" runat="server" Text="Update Time" OnClick="Button1_Click" /> 
    </ContentTemplate> 
</asp:UpdatePanel>

We’ll also add an UpdateProgress control and associate it with our UpdatePanel just to let the user know that something’s happening.

<asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1"> 
    <ProgressTemplate> 
        Loading... 
    </ProgressTemplate> 
</asp:UpdateProgress>

Next, we’ll add a events in the code-behind to populate the Label and to introduce some latency, simulating a lengthy update to the page.

VB.NET:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 
    Label1.Text = Now.ToString 
End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) 
    System.Threading.Thread.Sleep(4000)  'Pause for 4 seconds. 
End Sub

And finally we’ll add the client-side code to disable our button during the postback. The Javascript disables the button during the beginRequest event of the PageRequestManager and enables it when control has been returned to the browser in the endRequest event. The control causing the postback is returned from the get_postBackElement() method of the BeginRequestEventArgs object which is passed to the function handling the beginRequest event.

Add the follow script after the ScriptManager on the page:

<script type="text/javascript"> 
    var pbControl = null; 
    var prm = Sys.WebForms.PageRequestManager.getInstance(); 
    prm.add_beginRequest(BeginRequestHandler); 
    prm.add_endRequest(EndRequestHandler); 
    function BeginRequestHandler(sender, args) { 
        pbControl = args.get_postBackElement();  //the control causing the postback 
        pbControl.disabled = true; 
    } 
    function EndRequestHandler(sender, args) { 
        pbControl.disabled = false; 
        pbControl = null; 
    } 
</script>

And that’s it!

The Complete Source Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> 
<%@ Register Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" 
    Namespace="System.Web.UI" TagPrefix="asp" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" > 
    <head runat="server"> 
        <title>Untitled Page</title> 
    </head> 
    <body> 
        <form id="form1" runat="server"> 
            <asp:ScriptManager ID="ScriptManager1" runat="server" /> 
            <script type="text/javascript"> 
                var pbControl = null; 
                var prm = Sys.WebForms.PageRequestManager.getInstance(); 
                prm.add_beginRequest(BeginRequestHandler); 
                prm.add_endRequest(EndRequestHandler); 
                function BeginRequestHandler(sender, args) { 
                    pbControl = args.get_postBackElement();  //the control causing the postback 
                    pbControl.disabled = true; 
                } 
                function EndRequestHandler(sender, args) { 
                    pbControl.disabled = false; 
                    pbControl = null; 
                } 
            </script> 
            <asp:UpdatePanel ID="UpdatePanel1" runat="server"> 
                <ContentTemplate> 
                    <asp:Label ID="Label1" runat="server" Text="Label" /><br /> 
                    <asp:Button ID="Button1" runat="server" Text="Update Time" OnClick="Button1_Click" /> 
                </ContentTemplate> 
            </asp:UpdatePanel> 
            <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1"> 
                <ProgressTemplate> 
                    Loading... 
                </ProgressTemplate> 
            </asp:UpdateProgress> 
        </form> 
    </body> 
</html>

Maintain Scroll Position after Asynchronous Postback

Do you want to maintain the scroll position of a GridView, Div, Panel, or whatever that is inside of an UpdatePanel after an asynchronous postback?  Normally, if the updatepanel posts back, the item will scroll back to the top because it has been reloaded.  What you need to do is “remember” where the item was scrolled to and jump back to there after the postback.  Place the following script after the ScriptManager on your page.  And since the _endRequest event of the PageRequestManager happens before the page is rendered, you’ll never even see your item move!

<script type="text/javascript"> 
    var xPos, yPos; 
    var prm = Sys.WebForms.PageRequestManager.getInstance(); 
    prm.add_beginRequest(BeginRequestHandler); 
    prm.add_endRequest(EndRequestHandler); 
    function BeginRequestHandler(sender, args) { 
        xPos = $get('scrollDiv').scrollLeft; 
        yPos = $get('scrollDiv').scrollTop; 
    } 
    function EndRequestHandler(sender, args) { 
        $get('scrollDiv').scrollLeft = xPos; 
        $get('scrollDiv').scrollTop = yPos; 
    } 
</script>

Selecting an AJAX AccordionPane by ID

Typically, if we want to programmatically select a particular AccordionPane within an ASP.NET AJAX Accordion control, we set the SelectedIndex property.  This is great if we know the exact order of our AccordionPanes at all times, but this isn’t always so.  Let’s say, for instance, that you have four panes (making their indices 0, 1, 2, and 3, respectively).  If we make the third one (index 2) invisible, now the fourth one has an index of 2.  So let’s create a method to select the AccordionPane by its ID instead.

VB.NET

Public Sub SetPane(ByVal acc As AjaxControlToolkit.Accordion, ByVal PaneID As String) 
    Dim Index As Integer = 0 
    For Each pane As AjaxControlToolkit.AccordionPane In acc.Panes 
        If (pane.Visible = True) Then 
            If (pane.ID = PaneID) Then 
                acc.SelectedIndex = Index 
                Exit For 
            End If 
            Index += 1 
        End If 
    Next 
End Sub

C#

protected void SetPane(AjaxControlToolkit.Accordion acc, string PaneID) { 
    int Index = 0; 
    foreach (AjaxControlToolkit.AccordionPane pane in acc.Panes) { 
        if (pane.Visible == true) { 
            if (pane.ID == PaneID) { 
                acc.SelectedIndex = Index; 
                break; 
            } 
            Index++; 
        } 
    } 
}

It is interesting to note that I attempted to create a client-side equivalent of this method.  However, the ID is not passed down from the server to the client-side behavior, making it impossible to access that propery from the client.