Building a Type-Ahead Dropdown Control

The ASP.NET DropDownList control does not offer type-ahead functionality. Often users have to scan through hundreds of items before making a selection. This article shows how to easily implement type-ahead functionality in a dropdown that will be a hit with your users.

Read more ……

Two-way data binding in 3-Tier web application

In this article I would like to examine two-way data-binding in 3-tier web application and how using XLib library can substantially decrease the amount of data-binding code without compromising the amount of control you have over it. I start by describing the ways data flow has been handled in 3-tier applications and how XLib improves upon it.

Read More…

Display Hierarchical Data with TreeView in ASP.NET 2.0

I’m going to provide a simple example how to display hierarchical data from SQL Server database in the TreeView. A requirement is that the implementation should not be dependant on the hierarchy level in the database. It means that the TreeView implementation should be capable of displaying data from any level, no matter how deep.

Listing #1 : TreeviewEx.aspx

 <asp:TreeView ID="tvCategoryList" runat="server" ImageSet="BulletedList3" Width="100%"
        ShowExpandCollapse="True" NodeWrap="True" OnTreeNodePopulate="tvCategoryList_TreeNodePopulate"
        ExpandDepth="2" PopulateNodesFromClient="False">
        <ParentNodeStyle Font-Bold="True" />
        <RootNodeStyle Font-Bold="True" CssClass="Table_Title_Label_Black12" />
        <HoverNodeStyle Font-Underline="True" ForeColor="#5555DD" />
        <SelectedNodeStyle Font-Underline="True" HorizontalPadding="0px" VerticalPadding="0px"
            ForeColor="#5555DD" />
        <NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black" HorizontalPadding="0px"
            NodeSpacing="0px" VerticalPadding="0px" />
    </asp:TreeView>

Next, we start looking at the code in the TreeviewEx.aspx,cs file. We want to populate the root level. Here’s the code.

Listing #2

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            PopulateRootLevel();
        }
    }

This happens by connecting to the database, querying the first set of nodes (having null as the parent id), and creating TreeNode objects with the PopulateNodes routine, which follows next.

Listing #3

 private void PopulateRootLevel()
    {
        DataTable dt = BAL_Category.GetParentCategory();// Get Parent CategoryList From Database
        PopulateNodes(dt, tvCategoryList.Nodes);
    }

Next, we want to create the routine to populate the child nodes of a given node. This happens with the PopulateSubLevel method.

Listing #4

 private void PopulateSubLevel(int parentid, TreeNode parentNode)
    {
        // Get CategoryList by ParentID From Database
        DataTable dt = BAL_Category.GetChildCategory(parentid);
        PopulateNodes(dt, parentNode.ChildNodes);
    }

Here, the idea is the same as with the root level, but with the distinction that only child nodes of the given node are queried and populated with the PopulateNodes method (described earlier). The trick to triggering the populating of the child nodes is as follows.

Listing #5

protected void tvCategoryList_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
    PopulateSubLevel(Int32.Parse(e.Node.Value), e.Node);
}

TreeNodePopulate is raised for a TreeNode which is expanded by the user for the first time. Due to PopulateNodesFromClient (TreeView) and PopulateOnDemand (TreeNode) settings, this happens with the client-side callback mechanism which is handled by the ASP.NET Page framework. This means that populating does not involve a postback. And, due to better cross-browser support in ASP.NET 2.0, this also works for other browsers such as Firefox. Note that clicking on the node does cause a postback because I haven’t modified the select action of the populated tree nodes from the defaults.

Listing #6

private void PopulateNodes(DataTable dt, TreeNodeCollection nodes)
    {
        foreach (DataRow dr in dt.Rows)
        {
            TreeNode tn = new TreeNode();
            tn.Text = dr["Category"].ToString();
            tn.Value = dr["CategoryID"].ToString();
            int NoofChild = Convert.ToInt32(dr["childnodecount"].ToString());
            if (NoofChild != 0)
            {
                tn.PopulateOnDemand = true;
                tn.SelectAction = TreeNodeSelectAction.None;
            }
            else
            {
                tn.PopulateOnDemand = false;
                if (dr["TotalDoc"].ToString().Equals("0") == false)
                {
                    tn.NavigateUrl = "~/CategoryWiseProductList.aspx?ID=" + dr["CategoryID"].ToString();
                    tn.Text = tn.Text + " (" + dr["TotalProduct"].ToString() + ")";
                }
                else
                {
                    tn.NavigateUrl = "javascript:alert('Product is not Available Under this Category.')";
                }
            }
            nodes.Add(tn);
        }
    }

Hope this will Help you !!!

Happy Programming

Debugging SQL Server 2005 Stored Procedures in Visual Studio

Here is 2 important link :

• With Microsoft SQL Server 2000 it was possible to debug stored procedures from directly within Query Analyzer (see Debugging a SQL Stored Procedure from inside SQL Server 2000 Query Analyzer for more information). With SQL Server 2005, however, this functionality was moved out of SQL Server Management Studio and into the Visual Studio IDE. Using this technique, it is possible to step into your stored procedures, one statement at a time, from within Visual Studio. It is also possible to set breakpoints within your stored procedures’ statements and have these breakpoints hit when debugging your application…

  Read more…

• This article walks through the basics of debugging stored procedures using Visual Studio 2005. It covers breakpoints, watches and stepping through code. It was written by Chris Rock and includes his very unique sense of humor.

  One of my favorites activities while working is debugging. A little weird yes, but I’ve always had an affinity towards taking things apart and putting them back together. I haven’t always had success. As a kid I destroyed many toys and pieces of furniture (office chairs) just to find out how they work. I am a curious person by nature and like to figure out how something works OR why something isn’t working. Alright, enough about my lame personality…

  Read more…

Functional difference between “NOT IN” vs “NOT EXISTS” clauses

“NOT IN” and “NOT EXISTS” clauses are not the same functionally or performance wise and, therefore, should be used appropriately. This blog post outlines how these commands are executed and discusses when it is appropriate to use them.

CREATE TABLE EMP_MASTER
(
EMP_NBR NUMBER(10) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR2(20 CHAR),
MGR_NBR NUMBER(10) NULL
)

INSERT INTO EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO EMP_MASTER VALUES (2, ‘HARI’, 5);
INSERT INTO EMP_MASTER VALUES (3, ‘RAMESH’, 5);
INSERT INTO EMP_MASTER VALUES (4, ‘JOE’, 5);
INSERT INTO EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO EMP_MASTER VALUES (7, ‘JESSIE’, 5);
INSERT INTO EMP_MASTER VALUES (8, ‘KEN’, 5);
INSERT INTO EMP_MASTER VALUES (9, ‘AMBER’, 5);
INSERT INTO EMP_MASTER VALUES (10, ‘JIM’, 5);

Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.

NOT IN

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );
COUNT(*)
———-
0

NOT EXISTS

SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );
COUNT(*)
———-
9

Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back. (in MS SQL Server, depending upon the ANSI NULLS setting, the behavior can be altered but this post only talks about the behavior that is same in Oracle, DB2 LUW and MS SQL Server).

Performance implications:

When using “NOT IN”, the query performs nested full table scans, whereas for “NOT EXISTS”, query can use an index within the sub-query.

Export Gridview to Excel

The focus of the article is the Export to Excel functionality – the Gridview and it’s data binding are only for demonstrating the Export functionality.The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Default.aspx :

           <asp:SqlDataSource ID="sqldsCustomers" runat="server" SelectCommand="select * from UserDetails"
            SelectCommandType="Text" ConnectionString="server=server;database=LegalWindow;Trusted_Connection=yes;" />
        <asp:GridView ID="gvCustomers" runat="server" AllowPaging="true" AllowSorting="true"
            PageSize="10″" DataSourceID="sqldsCustomers" GridLines="Both" />
        <asp:Button ID="btnExportGrid" runat="server" Text="Export to Excel" OnClick="BtnExportGrid_Click" />

Default.aspx.cs :

protected void BtnExportGrid_Click(object sender, EventArgs args)
{
//  pass the grid that for exporting …
GridViewExportUtil.Export(“Customers.xls”, this.gvCustomers);
}

GridViewExportUtil.cs

 public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }

For the case where I actually wanted all rows exported, I turned off paging and rebound the grid before sending the control to the export utility.  For exporting just the first 100 rows, I set the PageSize property to 100 and then rebound.  You should probably use care when exporting the complete GridView just in case your grid has a few more rows that you are expecting.  Here is the code for the export button click handler

protected void BtnExportGrid_Click(object sender, EventArgs args)
    {
        if (this.rdoBtnListExportOptions.SelectedIndex == 1)
        {
            //  the user wants all rows exported, turn off paging
            //  and rebing the grid before sending it to the export
            //  utility
            this.gvCustomers.AllowPaging = false;
            this.gvCustomers.DataBind();
        }
        else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
        {
            //  the user wants just the first 100,
            //  adjust the PageSize and rebind
            this.gvCustomers.PageSize = 100;
            this.gvCustomers.DataBind();
        }

        //  pass the grid that for exporting …
        GridViewExportUtil.Export("Customers.xls", this.gvCustomers);
    }

For More Extensive Study of GridView Export to Excel………………read more

AsyncPostBackTrigger vs PostBackTrigger

In the <triggers> template in an update panel, there are the options of an AsyncPostBackTrigger or a PostBackTrigger.

By default, controls outside of an update panel will trigger a normal synchronous post back.  The AsyncPostBackTrigger “wires” up these controls to trigger an asynchronous post back.  Conversely, controls declared inside an update panel will trigger an asynchronous call by default.  The PostBackTrigger short circuits this, and forces the control to do a synchronous post back.

Utilizing a simple “time” example:

 <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
        <div>
            Page Generated @
            <asp:Label runat="server" ID="uiPageTime" />
            <p />
            <asp:UpdatePanel runat="server" ID="update" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:Label runat="server" ID="uiTime" />
                    <asp:Button runat="server" ID="uiInternalButton" Text="Click" />
                </ContentTemplate>
                <Triggers>
                    <asp:AsyncPostBackTrigger ControlID="uiAsynch" EventName="click" />
                    <asp:PostBackTrigger ControlID="uiInternalButton" />
                </Triggers>
            </asp:UpdatePanel>
            <asp:Button runat="server" ID="uiPostback" Text="Click" />
            <asp:Button runat="server" ID="uiAsynch" Text="Asynch" />
        </div>
    </form>

And the code behind file.

public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Init(object sender, EventArgs e)
        {
            uiAsynch.Click += uiAsynch_Click;
            uiPostback.Click += uiPostback_Click;
            uiInternalButton.Click += uiInternalButton_Click;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            uiPageTime.Text = DateTime.Now.ToLongTimeString();

            if (!IsPostBack)
            {
                uiTime.Text = DateTime.Now.ToLongTimeString();
            }
        }

        private void uiInternalButton_Click(object sender, EventArgs e)
        {
            uiTime.Text = "Internal @ " + DateTime.Now.ToLongTimeString();
        }

        private void uiPostback_Click(object sender, EventArgs e)
        {
            uiTime.Text = "Postback click @ " + DateTime.Now.ToLongTimeString();
            update.Update();
        }

        private void uiAsynch_Click(object sender, EventArgs e)
        {
            uiTime.Text = "Asych click @ " + DateTime.Now.ToLongTimeString();
            update.Update();
        }
    }

JavaScript Access to Page Controls after Ajax Update (via Update Panel)

Several times, we are in a situation where we need to do some work the moment the update panel finishes its update (ASP.NET 2.0 AJAX). There are situations like showing an error if any during the update, showing a success message after a proper update, setting up scroll bars, changing some UI component, updating some other part of UI, etc. The list can get longer as per the developer’s requirement. I had tried to show how we can get control to the page once the update is finished. We can tweak things via JavaScript the way we need to out there in the handler.

Click Here for more Details

How to refresh an UpdatePanel from javascript

I thought that updating an UpdatePanel from javascript was one of the most frequently asked question about ASP.NET Ajax, but even looking around a lot I didn’t find a clean and polished solution for what should be a common task.

after few minutes of Googl-ing i found a useful link. View it for more details

Click Here for more details…

How to hide Gridview column programmatically?

Question :  How to hide Gridview column programmatically?

Solution :

The Columns collection only stores the explicitly declared columns, so if you’re using autogenerated columns, the count will be zero.
If you’re using autogenerated column, after databind you could loop through the rows collection and make the appropriate cells invisible, like:

        GridView1.DataBind();
        if (GridView1.Columns.Count > 0)
            GridView1.Columns[0].Visible = false;
        else
        {
            GridView1.HeaderRow.Cells[0].Visible = false;
            foreach (GridViewRow gvr in GridView1.Rows)
            {
                gvr.Cells[0].Visible = false;
            }
        }

Hope this will help you !!!

Happy Programming