Aaron Li's Blog

SQL Server: Retrieve Top n Records Per Group

Aaron Li — Thu, 21 Feb 2008 23:16:08 GMT

My senario: Get latest NumberOfPosts posts for every discussion which meet myCondition

        SELECT
            DiscussionTopPosts.PostID
        From (
            SELECT p.DiscussionID, p.PostID, ROW_NUMBER() OVER (PARTITION BY p.DiscussionID ORDER BY p.DateCreated DESC) AS RowNumber
            FROM Posts p
            WHERE
                p.DiscussionID IN
                (
                SELECT d.DiscussionID
                FROM Discussions d
                Where myCondition
                )
            AND p.IsActive = 1
            GROUP BY p.DiscussionID, p.PostID, p.DateCreated
            )
            DiscussionTopPosts
        WHERE
        DiscussionTopPosts.RowNumber <= @NumberOfPosts

for more reference, see http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx

Coding for Different Browsers

Aaron Li — Wed, 13 Feb 2008 21:50:36 GMT

--- C# ---

string vBrowser = System.Web.HttpContext.Current.Request.Browser.Browser.ToLower();
    // or string vBrowser = Request.Browser.Browser.ToLower(); //for local page

            switch (vBrowser)
            {
                case "firefox":
                    do something ...
                    break;
                case "ie":
                    do something ...
                    break;
                case "safari":
                    do something ...
                    break;
                default:
                    do something ...
                    break;
            }

Find a Control When Multi-level Master Pages are Used

Aaron Li — Wed, 13 Feb 2008 18:33:41 GMT

When a page is using a master page, and that master page is using another master page, and so on, then how to find a control on the page?

For example, my pages could have three-level master pages. Let's call the third-level master page Master3, which contains a ContentPlaceHolder called PlaceHolder3; Master3's master page is Master2, which contains PlaceHolder2; and Master1, PlaceHolder1. I want to find the panel "pnlTest" on the content page.

Here is my function.

    public bool FindPageControl()
    {
        System.Web.UI.Page oPage = HttpContext.Current.Handler as System.Web.UI.Page;
        System.Web.UI.MasterPage oMasterPage = null;
        System.Web.UI.WebControls.ContentPlaceHolder oPlaceHolder = null;
        System.Web.UI.WebControls.Panel oPnlTest = null;
        if (oPage.Master.Master != null)
        {
            if (oPage.Master.Master.Master != null)
            {
                oMasterPage = (System.Web.UI.MasterPage)oPage.Master.Master.Master;
                oPlaceHolder = (System.Web.UI.WebControls.ContentPlaceHolder)oMasterPage.FindControl("PlaceHolder1").FindControl("PlaceHolder2").FindControl("PlaceHolder3"); //to make the logic strict, better find the control step by step
            }
            else
            {
                oMasterPage = (System.Web.UI.MasterPage)oPage.Master.Master;
                oPlaceHolder = (System.Web.UI.WebControls.ContentPlaceHolder)oMasterPage.FindControl("PlaceHolder1").FindControl("PlaceHolder2");
            }
        }
        else
        {
            oMasterPage = (System.Web.UI.MasterPage)oPage.Master;
            if (oMasterPage != null)
                oPlaceHolder = (System.Web.UI.WebControls.ContentPlaceHolder)oMasterPage.FindControl("PlaceHolder1");
        }

        if (oPlaceHolder != null)
            oPnlTest = (System.Web.UI.WebControls.Panel)oPlaceHolder.FindControl("pnlTest");
        else
            oPnlTest = (System.Web.UI.WebControls.Panel)oPage.FindControl("pnlTest");

        if (oPnlTest != null)
            return true;

        return false;
    }

By this example, it is not difficult to figure out how to find a control on any level master page or content page.

Named Anchor in IE and Firefox

Aaron Li — Fri, 25 Jan 2008 18:34:49 GMT

Generally, the following code can lead the hyper link to a named anchor

<a name="aMyGoTo"></a>
<a href="#aMyGoTo">go to the defined point</a>

However, in IE, sometimes it seems the named anchor doesn't work. Here is the solution.

To replace
<a name="aMyGoTo"></a>
with
<span style="position:absolute;"><a id="aMyGoTo"> </a></span>

In the case a div or other tag with style sheet coming right after the <span>named anchor</span>, a more secure way is put a <div> tage outside of the span.

<div><span style="position:absolute;"><a id="aMyGoTo"> </a></span></div>

Set Focus On Page Load

Aaron Li — Fri, 07 Dec 2007 01:23:04 GMT

Scenario:

I have a masterpage, and there is a search text box on it. For most pages, when the page is loaded, I want to set focus on the search box; for other pages, to a control on that page.

Rather than set focus on every page, I create a pulic function in a class. For those pages the focus is set to a specific control, this function is called.

    public void SetFocus(string vControl)
    {
        string strJS;
        strJS = "<script>" +
                    "var o = document.getElementById('" + vControl + "'); " +
                    "if((o != undefined) && (o != null)){ " +
                    "if(window.attachEvent) " +
                        "window.attachEvent('onload',new function(){o.focus();}); " +
                    "else " +
                        "window.addEventListener('load',new function(){o.focus();},true); }" +
                "</script>";
        ((Page)System.Web.HttpContext.Current.Handler).ClientScript.RegisterStartupScript(this.GetType(), "CSSetFocus", strJS);
    }

Meanwhile, on the master page, I have the following javascript code right after the search box control. The position of this piece of code is important. If it is before the search box control, getElementById() gets nothing; if it is located somewhere after the javascript code generated by SetFocus(), then the focus always stays on the search box control.

<script>
    var o = document.getElementById('<%=txtSearch.ClientID%>'+'_Input');
    if((o != undefined) && (o != null)) o.focus();
</script>

Of course, .NET can set focus by this.TextBox1.Focus(); however, if multiple master pages are used, I prefer using my own javascript code to control when and where the focus is set.

Optional Parameter and Overridden Function

Aaron Li — Tue, 20 Nov 2007 21:48:45 GMT

C# doesn't support optional arguments/parameters. It's done by overriding the function.
Optional arguments/parameters are used in VB.NET, and they must be listed last and must have a default value.

Link and Target

Aaron Li — Mon, 01 Oct 2007 23:44:00 GMT

Today, I tried to control a html link with a tag and a function (in c#) like the following,

    <a href="<%#GetTopLink(Eval(FN_URL).ToString())%>"><%#Eval(FN_NAME)%></a>

    protected string GetTopLink(string vUrl)
    {
        if (<true>)
            return vUrl;
        else
            return "javascript: window.open('" + vUrl + "','new'); void(0);";
    }

It doesn't seems it's a very smart way. A reminding to myself,

    <a href="http://www.microsoft.com" target="_blank">go</a>
    <a href="http://www.microsoft.com" target="_self">go</a>
    <a href="javascript: window.open('http://www.microsoft.com', '_blank'); void(0);">go</a>

Make your own Javascript trim() function

Aaron Li — Thu, 06 Sep 2007 01:19:35 GMT

add this single line into Javascript portion,

String.prototype.trim = function() { return this.replace(/^\s+|\s+$/g, ""); };

then test with

var mystring="test ";
alert(mystring.trim());

ClientScript.GetPostBackClientHyperlink() and __doPostBack()

Aaron Li — Fri, 10 Aug 2007 05:51:17 GMT

Senario 1: there is a databound control on the page, for example, a Repeater, and for every row, there is a Delete button/link.

Senario 2: on the same page, there is a Delete All button/link.

In Senario 1, we want to know which row to delete after the postback.

In Senario 2, if we use asp button and related event handler, generally the process is Page_Load() where databound happens; then OnClick() event handler where to delete; after that we have to redo the databound which cause an extra round trip to database; as a result, we want to process the deletion right away when Page_Load() is called, then bind data, so only bind data to the databound control for once.

ClientScript.GetPostBackClientHyperlink() or __doPostBack() will help us with these kinds of problems.

ClientScript.GetPostBackClientHyperlink() and __doPostBack() do the same thing, the difference is ClientScript.GetPostBackClientHyperlink() speaks server side language, which means it can recognize server side controls; however, __doPostBack() is javascript.

Let's see how they perform the same function. Suppose there is a Literal control on the page. Later we'll explain what this control is for.

<asp:Literal ID="ltrDeleteSingle" runat="server" EnableViewState="false"/>

in the code behind, set
string s = ClientScript.GetPostBackClientHyperlink(ltrDeleteSingle, "150");

guess what's the value of s? Here it is,
s="javascript:__doPostBack('ctl00$ctl00$ltrDeleteSingle','150')"

Perhaps you have a diffferent string rather than 'ctl00$ctl00$ltrDeleteSingle' on your page, anyway, it is the ltrDeleteSingle's UniqueID on client side.

Let's go back to our page,

In the HTML portion,

<asp:Repeater ID="rptUserList" runat="server" EnableViewState="false">
<ItemTemplate>
...
<a href="javascript:__doPostBack('<%=ltrDeleteSingle.UniqueID%>','<%#Eval("UserID")%>')">Delete</a>

</ItemTemplate>
</asp:Repeater>
<a href="javascript:__doPostBack('<%=ltrDeleteAll.UniqueID%>','')">Delete All</a>

<asp:Literal ID="ltrDeleteSingle" runat="server" EnableViewState="false"/>
<asp:Literal ID="ltrDeleteAll" runat="server" EnableViewState="false"/>

Of course, we can replace javascript:__doPostBack() with

<a href="<%#ClientScript.GetPostBackClientHyperlink(ltrDeleteSingle, Eval("UserID"))%>">Delete</a>
<a href="<%#ClientScript.GetPostBackClientHyperlink(ltrDeleteSingle, ""))%>">Delete All</a>

In the c# portion,

protected void Page_Load(object sender, EventArgs e)

{

if (IsPostBack)

{

if (!string.IsNullOrEmpty(Request.Form["__EVENTTARGET"]))

{

if (Request.Form["__EVENTTARGET"].Equals(ltrDeleteSingle.UniqueID))

{

if (!int.TryParse(Request.Form["__EVENTARGUMENT"], out UserID))

else

ToDelete(UserID);

}

else if (Request.Form["__EVENTTARGET"].Equals(ltrDeleteAll.UniqueID))

{

ToDelete(0);

}

ToBindData();

}

Shortly, ClientScript.GetPostBackClientHyperlink() and __doPostBack() give HTML controls the ability to fire postback. By anlyzing Request.Form["__EVENTTARGET"], different actions can be taken and performance could be improved.

We find, ltrDeleteSingle and ltrDeleteAll play the role of indicator so that the code knows who causes the postback then decides what to do. For those controls with AutoPostBack property, for example, dropdownlist or button, if set AutoPostBack =true, the control's UniqueID will be automatically passed in the form hidden field __EVENTTARGET.

Checkbox Counter and JavaScript

Aaron Li — Sun, 17 Jun 2007 23:59:30 GMT

Scenario:

I have a repeater with people’s name listed. Next to every name, there is a checkbox used to select people. Every time, only 2 people can be selected.

HTML portion,

<asp:Repeater ID="Repeater1" runat="server" EnableViewState = "false">

<asp:CheckBox ID="CheckBox1" runat="server" onclick="CheckedChanged(this.checked)" />

<%#Eval("Name")%>

</ItemTemplate>

</asp:Repeater>

Javascript is used to count how many people have been selected.

function getObj(name)

{

if (document.getElementById) // test if browser supports document.getElementById

{

this.obj = document.getElementById(name);

}

else if (document.all) // test if browser supports document.all

{

this.obj = document.all[name];

}

else if (document.layers) // test if browser supports document.layers

{

this.obj = document.layers[name];

}

function CheckedChanged(IsChecked)

{

var objTotalChecked = new getObj('TotalChecked');

var intTotalChecked = eval(objTotalChecked.obj.value);

if (IsChecked)

{

objTotalChecked.obj.value = intTotalChecked + 1;

}

else

{

objTotalChecked.obj.value = intTotalChecked - 1;

}

if (objTotalChecked.obj.value >= 2 )

alert("You can only talk to 2 people. Now you have selected " + objTotalChecked.obj.value +".");

}

In addition, when the page or part of the page is submitted, on server side, I would validate the total number of selected people.

Page Life Cycle In ASP.NET 2.0

Aaron Li — Sun, 27 May 2007 22:16:07 GMT

Page Life Cycle In ASP.NET 2.0

Stage	Event	What To Do	What Done
Initialization	Page PreInit	- change themes, master pages - access properties for a profile	master pages, themes, etc. are applied
	Control Init Page Init	- read or initialize properties of controls in the control hierarchy. - add dynamic controls that need to utilize view state	Controls are initialized with declarative properties.
	Page InitComplete	- access controls on the page	Begin to track view state changes
Page Load	Page PreLoad		View state data saved from the previous page are populated to the controls Postback data are restored
	Page Load Control Load
Raise Postback Event			Control events are handled
	Page LoadComplete
PreRender	Page PreRender
	Page PreRenderComplete		All controls have been added to the page and the page is ready to be rendered
Save View State			View state and control state have been saved
	Page SaveStateComplete
Render			HtmlTextWriter is initialized and the page is rendered to the browser
Unload	Control Unload Page Unload

View State and TextBoxes, CheckBoxes, DropDownLists

Aaron Li — Sun, 20 May 2007 21:20:48 GMT

View State and TextBoxes, CheckBoxes, DropDownLists

There is a common misunderstanding among developers that view state is somehow responsible for having TextBoxes, CheckBoxes, DropDownLists, and other Web controls remember their values across postback. I was one of them.

This is not the case, because the values are identified via posted back form field values, and assigned in the LoadPostData() method for those controls that implement IPostBackDataHandler. See Scott Mitchell’s article for detail.

In the page lifecycle, LoadPostBackData happens after LoadViewState stage. They are independent. The value of a textbox is restored in LoadPostBackData stage. If the view state for this textbox is enabled, then what happens in LoadViewState? Yes, the textbox could got a value, but that could be the wrong one, because the information in the view state represents the state before submission. For example, if the textbox’s value is “Hello my world” before you take any action to the page; at this moment, in the viewstate, it is “Hello my world”. Then from the browser, you change it to “Hello your world”, and submit the page. A new trip of the page life cycle happens. As we have known now, LoadViewState happens before LoadPostBackData. Guess what is the textbox’s value in the LoadViewState stage gotten from view state? It is “Hello my world” rather than “Hello your world”. Fortunately, LoadViewState follows, so that you can get the correct “Hello your world”. Untill the moment before the control is rendered, the viewstate is updated to “Hello your world”. Have you seen enabling the ViewState for such a control only wastes the resource if your application cares performance a lot?

Here we only talk about changing the value for this textbox. If we need to change it’s BackColor, for example, then viewstate is useful; or use other method to remember the changed BackColor if ViewState is not enabled.

Here is the list of controls which could be form fields meanwhile implement IpostBackDataHandler. Welcome to let me know if I miss any.

CheckBox.

CheckBoxList

DropDownList

ImageButton

ListBox

RadioButton

RadioButtonList

SelectionList

TextBox

HtmlInputCheckBox

HtmlInputFile

HtmlInputHidden

HtmlInputImage

HtmlInputRadioButton

HtmlInputText

HtmlSelect

HtmlTextArea

While Custom Validator Does Not Fire

Aaron Li — Fri, 11 May 2007 06:29:12 GMT

Custom Validator Does Not Fire

I have code like this,

<asp:TextBox ID="txtEmailBody" Runat="server" />
<asp:CustomValidator ID="valEmailBody"
                           Runat="server"
                           ControlToValidate=" txtEmailBody "
                           Display="None"
                           OnServerValidate="Validate_EmailBody "
                           ErrorMessage="*" />

CustomValidator event does not fire if the validated control is empty.

If the validated control is a required field, it is easy to fix this problem by adding a RequiredFieldValidator control in addition to the CustomValidator. This means we have to use a RequiredFieldValidator rather than include the logic to checking the control not empty in the CustomValidator.

However, if the validated control is not always a required field, for example, in my case, in certain situation, the email body can be empty; but not in other situations. To make the CustomValidator work for this case, we can leave the ControlToValidate property of the custom validator blank. This will force the validator to fire on every round trip although it could somehow affect the performance, but at least the custom validator gets processed.

Script Callback

Aaron Li — Fri, 20 Apr 2007 07:02:44 GMT

What is the difference between a callback and a postback?

A callback is a special postback, so a round-trip always occurs; however, unlike the classic postback, the script callback doesn't redraw the whole page. ViewState is not updated during a callback, it is for postback.

How to make a callback?

In the client side JavaScript code, if GetCallbackEventReference() method is reference, then when the JavaScript code is executed, a channel to the server is opened and an HTTP request is sent to the remote ASP.NET page.

How does the ASP.NET runtime know this HTTP request is a Callback rather than a Postback?

After the ASP.NET runtime get a HTTP request, it looks for a __CALLBACKID entry in the Request collection. If such an entry is found, the runtime concludes that a callback invocation is being made.

GetCallbackEventReference() syntax

public string GetCallbackEventReference (

string target,

string argument,

string clientCallback,

string context,

string clientErrorCallback,

bool useAsync

)

there are overloads such as

ClientScriptManager.GetCallbackEventReference (Control, String, String, String)

ClientScriptManager.GetCallbackEventReference (Control, String, String, String, Boolean)

GetCallbackEventReference() Parameters

target

The name of a server Control that handles the client callback. The control must implement the ICallbackEventHandler interface and provide a RaiseCallbackEvent method (which could be the page itself).

argument

An argument passed from the client script to the server RaiseCallbackEvent method.

clientCallback

The name of the client event handler that receives the result of the successful server event.

context

Client script that is evaluated on the client prior to initiating the callback. The result of the script is passed back to the client event handler.

clientErrorCallback

The name of the client event handler that receives the result when an error occurs in the server event handler.

useAsync

true to perform the callback asynchronously; false to perform the callback synchronously.

Data-Bound Controls (2) - Built-in Layout

Aaron Li — Sat, 31 Mar 2007 04:00:00 GMT

Built-in Layout

We have talked about the templates for the following data-bound controls

1. GridView

2. DataList

3. Repeater

4. DetailsView

5. FormView

When the data-bound controls are finally rendered as html tags to the user’s browser, they have different built-in layout. Knowing this help us make decision of what control to use for certain requirement.

The final default html for each of these controls is a HTML table, but with different rows and columns. Sometimes the table has border, sometimes not.

The final layout could be affected by adapters or themes if they are applied. The following is the default, built-in layout of these controls

GridView

<tr>

<th scope="col">HeaderText for Field1</th>

<th scope="col">HeaderText for Field2</th>

</tr>

<tr>

<td>VALUE for Field1 for the First Record</td>

<td>VALUE for Field2 for the First Record</td>

</tr>

<tr>

<td>VALUE for Field1 for the Second Record</td>

<td>VALUE for Field2 for the Second Record</td>

</tr>

......

</table>

*** Please notice, for this table, border="1"

DataList

<tr>

<td>

Content Defined in the HeaderTemplate

</td>

</tr>

<tr>

<td>

Content Defined in the ItemTemplate(first record)

</td>

</tr>

...... more <tr></tr> for more records

<tr>

<td>

Content Defined in the FooterTemplate

</td>

</tr>

</table>

Repeater

Repeater has no built-in layout or styles.

We must explicitly declare all HTML layout, formatting, and style tags within the templates of the control.

DetailsView

DetailsView is rendered as a multi-row(<tr>) and two-column(<td>) HTML table.

<tr>

<td>HeaderText for the First Field</td>

<td>Value for the First Field</td>

</tr>

<tr>

<td>HeaderText for the Second Field</td>

</tr>

......

</table>

*** Please notice, for this table, border="1"

FormView

Formview is rendered as a one-row(<tr>) and one-column(<td>) HTML table.

<tr>

Content Defined in the ItemTemplate

</td>

</tr>

</table>

*** Well formatted HTML tags can be used in ItemTemplate.