Monday, December 3, 2007

NetTiers "SELECT DISTINCT" method

Using Nettiers CRUD methods and custom methods we get a very complete DAL that covers a wide range of the queries needed by a application. A frequently question in forums is about how obtain a "Select Distinct" clause equivalent in Nettiers. I've developed a method to obtain the distinct entities based on the values of one or more properties.

public static TList<T> SelectDistinctFromList<T>(TList<T> originalList, System.Enum[] distinctProperties) where T : IEntity, new()
{
Type instanceType = originalList.GetType().GetGenericArguments()[0].BaseType;

PropertyInfo[] propertyColumns = new PropertyInfo[distinctProperties.Length];
for(int i = 0; i < distinctProperties.Length; i++)
{
propertyColumns[i] = instanceType.GetProperty(distinctProperties[i].ToString());
}

TList<T> resultList = new TList<T>();
System.Collections.Specialized.StringCollection keys = new System.Collections.Specialized.StringCollection();
foreach (T instance in originalList)
{
string key = string.Empty;
foreach (PropertyInfo pi in propertyColumns)
{
key += pi.GetValue(instance, null) + "|";
}
if (!keys.Contains(key))
{
keys.Add(key);
resultList.Add(instance);
}
}
return resultList;
}

As you can see there are two steps to get the "Select Distinct", in the first step is retrieve the base collection (TList) and in the second step apply the SelectDistinctFromList method.
This approach is adequate when the first step data is cached, have not a big collection or want to do many "Select Distinct" over the collection. For example, using a Nettiers project generated from
the AdventureWorks DB first obtains the orders for a SalesPerson and after get a list of orders with the distinct Territories and other list with the distinct Customers and Status.

SalesOrderHeaderService service = new SalesOrderHeaderService();
TList<SalesOrderHeader> orders = service.GetBySalesPersonId(279);

TList<SalesOrderHeader> distinctTerritoriesFromOrders = SelectDistinctFromList<SalesOrderHeader>(orders, new Enum[] { SalesOrderHeaderColumn.TerritoryId });

TList<SalesOrderHeader> distinctCustomerAndStatusFromOrders = SelectDistinctFromList<SalesOrderHeader>(orders, new Enum[] { SalesOrderHeaderColumn.CustomerId, SalesOrderHeaderColumn.Status });

Tuesday, August 7, 2007

Reflect the underlying type of nullable property / field

Sometimes it's necessary list the properties with their types of an entity instanciated at run-time. For example when develop a CRUD form at runtime for a selected entity or when build a designer form like a report designer or expression designer that implies entities and her properties. This task is not problematic using Reflection, but if we use Nullable properties (and we would have to use it whenever a value can be null) the underlying type of a nullable property can't be obtained in the traditional way. A nullable property is a "constructed type" of the Nullable<T> generic class, this means that the type of the property is the parameter for the constructor of Nullable<T> (For example a nullable int property is an instance of Nullable<int>). I wrote a method that list the properties with their types iterating over the reflected properties adding his name and type to a dictionary, and when it finds a generic property obtains the underlying type of the property using the argument that was supplied to construct the nullable type.
Below a sample of using the method to list the properties and types of a run-time instantiated entity.


using System;
using System.Web;
using System.Collections.Generic;
using System.Reflection;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

public static SortedDictionary<string, string> GetPropertiesAndDataTypes(string entityName)
{
SortedDictionary<string, string> dict = new SortedDictionary<string, string>();
string propertyType = string.Empty;
if (!String.IsNullOrEmpty(entityName))
{
Type type = Type.GetType(entityName);
if (type != null)
{
PropertyInfo[] properties = type.GetProperties();
if (properties != null && properties.Length > 0)
{
foreach (PropertyInfo propertyInfo in properties)
{
if (propertyInfo.PropertyType.IsGenericType)
{
Type nullableProperty = Type.GetType(propertyInfo.PropertyType.FullName);
// Obtains the Name of the type used as parameter of Nullable<T>
propertyType = nullableProperty.GetGenericArguments()[0].FullName;
}
else
{
propertyType = propertyInfo.PropertyType.FullName;
}
dict.Add(propertyInfo.Name, string.Format("{0} ({1})", propertyInfo.Name, propertyType));
}
}
}
}
return dict;
}

protected void ddlEntities_SelectedIndexChanged(object sender, EventArgs e)
{
lbEntityProperties.Items.Clear();
if (ddlEntities.SelectedIndex > 0)
{
lbEntityProperties.DataSource = GetPropertiesAndDataTypes(ddlEntities.SelectedValue);
lbEntityProperties.DataTextField = "Value";
lbEntityProperties.DataValueField = "Key";
lbEntityProperties.DataBind();
}
}
}

public class Product
{
private int _productId;
private string _name;
private bool? _makeFlag;
private DateTime _selStartDate;
private DateTime? _selEndDate;
private int? _daysToManufacture;

public int ProductId
{
get { return _productId; }
set { _productId = value; }
}

public string Name
{
get { return _name; }
set { _name = value; }
}

public bool? MakeFlag
{
get { return _makeFlag; }
set { _makeFlag = value; }
}

public DateTime SelStartDate
{
get { return _selStartDate; }
set { _selStartDate = value; }
}

public DateTime? SelEndDate
{
get { return _selEndDate; }
set { _selEndDate = value; }
}

public int? DaysToManufacture
{
get { return _daysToManufacture; }
set { _daysToManufacture = value; }
}

public Product()
{

}

}

public class Contact
{
private string _name;
private int? _score;
private bool? _isMember;

public Contact()
{ }

public int? Score
{
get { return _score; }
set { _score = value; }
}

public string Name
{
get { return _name; }
set { _name = value; }
}

public bool? IsMember
{
get { return _isMember; }
set { _isMember = value; }
}

}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>Reflect type of nullable properties</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlEntities" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlEntities_SelectedIndexChanged">
<asp:ListItem>-- Select Entity --</asp:ListItem>
<asp:ListItem>Contact</asp:ListItem>
<asp:ListItem>Product</asp:ListItem>
</asp:DropDownList><br /><br />
<asp:ListBox ID="lbEntityProperties" runat="server" Height="258px" Width="265px"></asp:ListBox>
</div>
</form>
</body>
</html>

Monday, July 16, 2007

Handle multiple asynchronous calls in Asp.Net Ajax complex scenarios

I have been developing an Ajaxed Asp.net application that has in some pages a heavy load of Ajax components implemented with multiple update panels containing controls that fires heavy load process in the server side. To obtain a responsive UI the challenge was that the user can initiates an Ajax request and while it is processed, can initiate another requests.
Browsing the ASP.NET AJAX Client Life-Cycle Events I found the next explanation of Event Order for Multiple Asynchronous Postbacks:
"The default behavior of asynchronous postbacks is that the most recent asynchronous postback takes precedence. If two asynchronous postbacks occur in sequence, and if the first postback is still being processed in the browser, the first postback is canceled. If the first postback has been sent to the server, the server processes the second request when it arrives and does not return the first request."

This behavour "last request wins" prevents the implementation of a full process of multiple request. Googling I found this article thats use a queue and works fine with few Ajax components but freeze the browser in the heavy Ajax request scenario. With a little tweak in the code (commented below) now it works smoothly and can handle a set of continuous requests.
WARNING: have precaution in scenarios with controls that depend of the result of the execution of another control.


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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 id="Head1" runat="server">
<title>Untitled Page</title>

</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

</div>

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

<asp:UpdatePanel ID="UpdatePanel2" runat="server">
<ContentTemplate>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
<asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Button" />
</ContentTemplate>
</asp:UpdatePanel>

<asp:UpdatePanel ID="UpdatePanel3" runat="server">
<ContentTemplate>
<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
<asp:Button ID="Button3" runat="server" OnClick="Button3_Click" Text="Button" />
</ContentTemplate>
</asp:UpdatePanel>
<asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1"
DisplayAfter="0">
<ProgressTemplate>
Updating panel1
</ProgressTemplate>
</asp:UpdateProgress>
<asp:UpdateProgress ID="UpdateProgress3" runat="server" AssociatedUpdatePanelID="UpdatePanel3"
DisplayAfter="0">
<ProgressTemplate>
Updating panel3
</ProgressTemplate>
</asp:UpdateProgress>
<asp:UpdateProgress ID="UpdateProgress2" runat="server" AssociatedUpdatePanelID="UpdatePanel2"
DisplayAfter="0">
<ProgressTemplate>
Updating panel2
</ProgressTemplate>
</asp:UpdateProgress>
<div id="AlertDiv">
<span id="AlertMessage"></span>
</div>

<script type="text/javascript">
Sys.Application.add_load(ApplicationLoadHandler)

function ApplicationLoadHandler(sender, args)
{
var prm = Sys.WebForms.PageRequestManager.getInstance();
if (!prm.get_isInAsyncPostBack())
{
prm.add_initializeRequest(InitializeRequest);
prm.add_endRequest(CompleteRequest);
}
}
// initialize a queue
var myQueue = new Array();

function CompleteRequest(sender, args)
{
if(myQueue.length > 0)
{ // Fire correspond event again of the item cached
// Original Code:
// $get(myQueue[0].id).click();
// Changed to:
// var control = $get(myQueue[0]);
// setTimeout('__doPostBack(\'' + control.name + '\',\'\')', 0);
var control = $get(myQueue[0]);
setTimeout('__doPostBack(\'' + control.name + '\',\'\')', 0);
Array.removeAt(myQueue, 0);
}
}
function InitializeRequest(sender, args)
{
var prm = Sys.WebForms.PageRequestManager.getInstance();
if (prm.get_isInAsyncPostBack())
{ // If it's working on another request, cache the current item that cause the request
args.set_cancel(true);
// Original Code:
// Array.add(myQueue, args.get_postBackElement());
// Changed to:
// Array.add(myQueue, args.get_postBackElement().name);
Array.add(myQueue, args.get_postBackElement().name);
}
}

if(typeof(Sys) !== "undefined") Sys.Application.notifyScriptLoaded();

</script>

</form>
</body>
</html>


using System;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

protected void Button1_Click(object sender, EventArgs e)
{
System.Threading.Thread.Sleep(2000);
Label1.Text = DateTime.Now.ToLongTimeString();
}
protected void Button2_Click(object sender, EventArgs e)
{
System.Threading.Thread.Sleep(2000);
Label2.Text = DateTime.Now.ToLongTimeString();
}
protected void Button3_Click(object sender, EventArgs e)
{
System.Threading.Thread.Sleep(2000);
Label3.Text = DateTime.Now.ToLongTimeString();
}
}

Wednesday, June 6, 2007

Optimized paging in queries over two or more tables using Sql Server 2005 functions


The default paging made by the ASP.Net controls that support pagination is based on the following steps:
1) Receive the DataSource with the result of the query
2) Based on the page size and the page selected by the user, it selects the records of the "selected page" to show, discarding the rest of the data. This pagination is highly expensive in terms of resources (greater traffic and data processing), when the volume of records on which the query is made is of a considerable size.
For example, a user has 200 Orders in the database and a form with gridview that shows 10 records for each page. If he browse 10 grid pages, the amount of records transferred to gridview so that this pagine will be of 2000 (200 records by each selected page) whereas the shown amount indeed is of 100 records. That is that 1900 records are transferred only to be discarded, by all means that this waste exponentially increases with the amount of records that the query gives back. The example of custom paging which we can easily find in the Web, makes use of function ROW_NUMBER() of SQL Server 2005, and only is useful when the query is made on an single table. At the time of making queries on related tables function ROW_NUMBER() does not apply, since to a registry “father” (as the head of an order) would assign so many RowNumbers as “children” (order details) records have. Therefore, looking for the new functions of SQL Server 2005 to solve this problem, I found the DENSE_RANK() function that allows to obtain only one RowNumber by each registry “father” in a query over multiple tables. Thus, I developed an stored procedure for the AdventureWorks database that gives back a certain page of result of a query over Orders and its detail in optimized form, this can be useful when need populate paged hierarchical data like Orders & Order Details and want make to do a single call to retrieve all data of a single page.


Stored procedure PagedOrders

CREATE PROCEDURE [dbo].[PagedOrders]
@pagesize int,
@pageindex int
AS

SET NOCOUNT ON;

SELECT * FROM
(
SELECT DENSE_RANK() OVER (ORDER BY Sales.SalesOrderHeader.SalesOrderID DESC) AS Row,
Sales.SalesOrderHeader.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.DueDate,
Sales.SalesOrderHeader.PurchaseOrderNumber,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.ProductID,
Sales.SalesOrderDetail.UnitPriceDiscount,
Sales.SalesOrderDetail.LineTotal,
Production.Product.Name
FROM Sales.SalesOrderDetail
INNER JOIN
Sales.SalesOrderHeader
ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN
Production.Product
ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
)

AS Result

WHERE Row between ((@pageindex - 1) * @pagesize + 1) and (@pageindex * @pagesize)

Monday, June 4, 2007

Localization and UICulture="auto" attribute

While develop an application that requires location in all its pages I follow these 3 steps:
1) Develop the pages and generate the resources files (in VS2005 Tools Option --> Local Generate Resource)
2) Create a page where the user selects the language of the application, which is stored in a Session variable or Profile field or Database field.
3) Apply the language selected to all requests in the Application_PreAcquireRequestState.

culture = HttpContext.Current.Session["culture"].ToString();
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(culture);
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture(culture);


When test the application I found that the changes of language were not reflected in the different pages. This is because the UICulture="auto" attribute of the @Page directive in each page, added automatically in step 1, does that the initialization of the localization succeeds at page level overriding the language selected by the user that is applied in the Application_PreAcquireRequestState. The solve this problem follow the next steps:
· Apply the default culture to all pages in web.config (using globalization tag)
· Delete UICulture="auto" attibute tags added when generating the resources files.

To have a better notion of the execution order of events in a request you can read this article or explore this graphic.

Resources
· Globalization:Das Blonde Blog
· Profiles : MSDN2

Changing the Default Profile Provider

I confess that it had not research so much over Memberships, Roles and Profiles provider until arrived the hour to use them in a project. While installing the SQL Server database to give support to providers with the Aspnet_regsql.exe tool, it thought how the fields corresponding to each property would keep from a profile, “wishing” that it was not an only field that contains names and values for all properties. When finalizing the installation I verified that my desires did not become reality and I find a table with 3 fields, one that describes the properties (PropertyNames) and in the other two the values are stored (PropertyValuesString and PropertyValuesBinary)

This way to store the profiles reduces the flexibility when needs make reports, changes in the fields or store profile fields in different tables. The essence of providers is offer a point of variation in certain parts of an application, protecting the application of the implementation of the selected provider (that is the code of the application is not sensible when selects any provider). Native Providers of framework 2.0 does not try to include an exhaustive functionality, but rather they solve the common topics that it can be need in an application and serve as reference for the development of new providers. Luckyly they exist providers for SQL Server that solves the mentioned problems.
In Sandbox are available 2 Sql Server providers:
· SqlTableProfileProvider: it allows map each property of profile with a field of an unique table.
· SqlStoredProcedureProfileProvider: it allows map each property of profile with a parameter of a Stored Procedure and within this, we can map each parameter to any table. The configuration is quite simple, only requires to add the class of the profile provider in directory APP_CODE and modify the web.config so that utilize this.
Resources