Welcome to weblogs.com.pk Sign in | Join | Help

Classic ASP to ASP.NET: Master Detail (Part II)

This post is about the ASP.NET version of the page I mentioned earlier.

 

The problem in that approach was un-necessary database hit, consider if there are 10 dealers in particular area and each dealer has on average two phones and one email, then there were 1 + 10 + 10 = 21 database hits.

 

Now we can have multiple tables in single dataset, we can use this to implement the same problem with much fancier method. We can retrieve all the data in a single hit using a query something like

 

create procedure GetDealers ( /*Procedure parameters for area*/ )

as

select dealers-data from dealers where required-criteria;

select phone-data from phones where dealerid in (select dealerid from dealers where required-criteria);

select email-data from emails where dealerid in (select dealerid from dealers where required-criteria);

 

Now at middle-tier, we can have something like

 

class Dealers

{

      System.Data.DataSet dataSet=new DataSet();

void GetData(string city, string area)

{

      this.dataSet=

      Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(

            Constants.ConnectionString, System.Data.CommandType.StoredProcedure,

            "GetDealers",

            new System.Data.SqlClient.SqlParameter[] {

                  new System.Data.SqlClient.SqlParameter("@City", city),

                  new System.Data.SqlClient.SqlParameter("@Area", area)

            }

      );

      this.dataSet.Tables[0].TableName="Dealers";

      this.dataSet.Tables[1].TableName="Emails";

      this.dataSet.Tables[2].TableName="Phones";

}

}

 

See, we have everything in a single hit; we have saved 20 extra hits, which will definitely give us better performance J

 

Now lets use server controls to prepare the output in a more elegant way, rather tedious response.write statements.

 

Here is the datagrid tag we used in our ASPX/ASCX

 

<asp:DataGrid id="DataGridDealers" runat="server" AutoGenerateColumns="False" CellPadding="2"

      CellSpacing="0" Font-Names="Verdana, Arial, sans-serif">

      <ItemStyle Font-Size="x-small" />

      <Columns>

            <asp:TemplateColumn ItemStyle-VerticalAlign="Top" HeaderText="Dealer">

                  <ItemTemplate>

                        <b><%# DataBinder.Eval(Container.DataItem, "Company") %></b><br>

                        <%# DataBinder.Eval(Container.DataItem, "Address" ) %><br>

                        <asp:Repeater Runat="server" ID="RepeaterPhones" DataSource='<%# getPhones( (int)DataBinder.Eval(Container.DataItem, "dealerid") ) %>'>

                              <ItemTemplate>

                                    <b>Phone: </b>

                                    <%# DataBinder.Eval(Container.DataItem, "phone") %>

                                    <br>

                              </ItemTemplate>

                        </asp:Repeater>

                        <asp:Repeater Runat="server" ID="RepeaterEmails" DataSource='<%# getEmails( (int)DataBinder.Eval(Container.DataItem, "dealerid") ) %>'>

                              <ItemTemplate>

                                    <b>Email: </b>

                                    <%# DataBinder.Eval(Container.DataItem, "email") %>

                                    <br>

                              </ItemTemplate>

                        </asp:Repeater>

                  </ItemTemplate>

            </asp:TemplateColumn>

      </Columns>

</asp:DataGrid>

 

See, our code is much cleaner and much friendly for our web-designer. The interesting point is the use of inline call to method calls for the data-sources of the Repeater controls. The two methods, getPhones and getEmails are defined in the code-behind class. Here they are:

 

protected DataView getPhones(int dealerID)

{

      DataView dvPhone = this.dataSet.Tables["Phones"].DefaultView;

      dvPhone.RowFilter="dealerid="+dealerID.ToString();

      return dvPhone;

}

protected DataView getEmails(int userIndex)

{

      DataView dvEmail = this.dataSet.Tables["Emails"].DefaultView;

      dvEmail.RowFilter="dealerid="+dealerID.ToString();

      return dvEmail;

}

 

Don’t forget these methods should be protected or public.

 

What are the end-results this all effort?

 

  • The code is more maintainable
  • The code is more elegant and understandable
  • The interface layer is separated cleanly, and can be updated easily with much less effort
  • We saved extra data-base hits
Published Monday, October 11, 2004 4:23 PM by khurram
Filed under:

Comments

# re: Classic ASP to ASP.NET: Master Detail (Part II)

Tuesday, October 12, 2004 3:16 AM by Sohaib AtharT
Khurram,

The way I handle this heirarchical representation is by setting the foreign key relationships in the DS tables, and then filtering for the current parent row in the child table by using the child relation filter in the parent's item binding event, and binding the filtered table.

That way, the function getPhones will be redundant in the nested repeater, and will be moved to the itembound event in its parent repeater. I THINK it will be more efficient.

Another thing that I've seen people use (my brother included) is to get the dataset as xml and transform it using some xsl, which has the added advantage of being modifyable and reusable on multiple forms.

# re: Classic ASP to ASP.NET: Master Detail (Part II)

Tuesday, October 12, 2004 8:50 AM by khurram
Establishing relationship in the dataset is a good idea; I was lazy that I didn’t do it ?

Another option that Sohaib has mentioned is to populate the grid column with dynamic controls in OnDataBound event. In this approach, the web-designer will not be involved and the UI layout code will have to be written into the code-behind class. For this reason, I preferred to use the template column and inline method call to the code-behind class, which web-designer don’t mind much. The getPhones() and getEmails() are called equal time in both approach, and one gets equal performance.

Xml approaches are no doubt “in”, but my overall experience is that they always are slow. Secondly, my priority was to move all the UI code out from middle tier. Xsl can be in an external file, but it will be tedious; we need to document it properly that whenever we need a change in UI which Xsl file is required to change.
New Comments to this post are disabled