Martin Olivares MS Web Stack experiences

Kendo Grid State Manager Plugin

noreply@blogger.com (Martín Olivares) — Sun, 21 Jun 2015 00:03:00 +0000

I've developed a Kendo plugin to manage kendo grid states (filtering/sorting/grouping) that allows create, re-use and share grid "views".

This is my first GitHub project and could be found at: https://github.com/martinolivares/kendo-gridstatemanager-plugin

Below a preview:

Hope you find it useful ;)

Realtime data editing using an Excel-like grid and KnockoutJs

noreply@blogger.com (Martín Olivares) — Fri, 22 Jun 2012 22:17:00 +0000

Sometimes you need to edit a batch data requiring an agile UI that allow:

Send changes without need confirmation
Quickly Navigate to any field that you want edit
Provide a visual indicator of the updated fields

An example: Realtime Weather Forecast Editor

On this example, the main requirement is allow quickly edit the cities temperatures at different day times. Basically for each city could edit the morning, afternoon or midnight temperature. For simplicity, the cities list are stored in a session variable.

This is a list of the implementation techniques for the app main features:

Updates are sent back to the server when textboxes lost focus: using a KnockoutJs extender the temperature fields changes are captured and sent to server using ajax calls.
Updated values are highlighted: a css binding changes the textbox background color after a field is updated successfully on server
Key based navigation: a jQuery handler allow navigate between textboxes using the arrows keys

App Screenshot

Using a KnockoutJs extender to send the realtime updates

A new extender named "realtime" is added to the Knockout extenders. At the initialization, it stores in two private fields (cityId and dayTime) the values required to identify the time slot to be updated on the ajax call after the observable property value changes. Also, a sub-observable (hasChanged) is used to notify the highlighter databinder.

ko.extenders.realtime = function(target, options) {
    // store private vars to use as parameters on updates
    // cityId: the key of entity to be updated
    // daytime: the field of entity to be updated (Morning/Afternoon/Midnight)
    target.cityId = options[0];
    target.daytime = options[1];
    
    //add a sub-observable to mantain a changed flag
    target.hasChanged = ko.observable(false);

    //define a function to send updates
    function update(newValue) {
             $.ajax({
                 url: "/home/updateforecast", 
                data: { cityId: target.cityId, daytime: target.daytime, value: newValue },
                type: "post", 
                success: function(result) 
                {
                    if(result) 
                        target.hasChanged(true);
                }
            });
    }

    //update whenever the value changes
    target.subscribe(update);

    //return the original value
    return target;
};

Applying the extender

The extender is applied on the ForecastModel temperature fields

var model = @Html.Raw(new JavaScriptSerializer().Serialize(Model));
var viewModel = { 
    forecasts :  ko.observableArray(ko.utils.arrayMap(model, function(forecast) {
        return new ForecastModel(forecast);
    }))
   
}; 
   
function ForecastModel(forecast) {
    this.City = ko.observable(forecast.City);
    // extend the observable fields that will be updated in realtime
    this.Morning = ko.observable(forecast.Morning).extend({realtime:[forecast.Id, "Morning"]});
    this.Afternoon = ko.observable(forecast.Afternoon).extend({realtime:[forecast.Id, "Afternoon"]});
    this.Midnight = ko.observable(forecast.Midnight).extend({realtime:[forecast.Id, "Midnight"]});
}

ko.applyBindings(viewModel);

Configuring the View

A table (tblForecasts) is used to display the cities names and edit the different day times temperatures. On each temperature textbox the "value binding" is applied to each temperature field. Also, a "css binding" to the hasChanged sub-observable is applied in order to highlight the textbox background color when a change callback returns successfully from server.

<h1>
    Realtime Weather Forecast Editor</h1>
<table id="tblForecasts">
    <thead>
        <tr>
            <th>
                City
            </th>
            <th>
                Morning
            </th>
            <th>
                Afternoon
            </th>
            <th>
                Midnight
            </th>
        </tr>
    </thead>
    <tbody data-bind="template: { name: 'forecastRowTemplate', foreach: forecasts }">
    </tbody>
</table>
<script type="text/html" id="forecastRowTemplate"> 
<tr> 
    <td><span data-bind="text: City"/></td> 
    <td><input data-bind="value: Morning, css: { hasChanged: Morning.hasChanged }"/></td> 
    <td><input data-bind="value: Afternoon, css: { hasChanged: Afternoon.hasChanged }"/></td> 
    <td><input data-bind="value: Midnight, css: { hasChanged: Midnight.hasChanged }"/></td> 
</tr>
</script>

Adding keyboard navigation to table

A simple handler is attached to the textboxes keydown event to detect the arrows keys pressing and move focus

$("#tblForecasts").on("keydown", "input:text", function(e) {
        // detect arrows pressing
        if (e.keyCode < 37 || e.keyCode > 40)
            return;

        e.preventDefault();
  
        var target;
        var cellAndRow = $(this).parents('td,tr');
        var cellIndex = cellAndRow[0].cellIndex;
        var rowIndex = cellAndRow[1].rowIndex;

        switch (e.keyCode) {
            // left arrow                 
            case 37:
                cellIndex = cellIndex - 1;                        
                break;
            // right arrow                 
            case 39:
               cellIndex = cellIndex + 1;                        
                break;
            // up arrow                 
            case 40:
                rowIndex = rowIndex + 1;
                break;
            // down arrow                 
            case 38:
                rowIndex = rowIndex - 1;
                break;
        }
        target = $('table tr').eq(rowIndex).find('td').eq(cellIndex).find("input:text");
        if (target != undefined) {
            target.focus();
            target.select();
        }
});

Server side code

The Forecast Model

public class Forecast
{
    public int Id { get; set; }
    public string City { get; set; }
    public int Morning { get; set; }
    public int Afternoon { get; set; }
    public int Midnight { get; set; }
}

The Controller

Just contains the code to initialize the cities list on session and a method to update the temperatures

private const string C_ForecastModels = "ForecastModels";

// Expose the stored Forecasts from Session
public IEnumerable<Forecast> ForecastModels
{
    get
    {
        if (Session[C_ForecastModels] == null)
        {
            Session[C_ForecastModels] = new[] {
                new Forecast { Id=1, City="Barcelona", Morning=90, Afternoon=45, Midnight=40},                new Forecast { Id=2, City="Berlin", Morning=78, Afternoon=34, Midnight=0 },
                new Forecast { Id=3, City="New York", Morning=45, Afternoon=35, Midnight=20 },
                new Forecast { Id=4, City="Sydney", Morning=65, Afternoon=45, Midnight=40 },
                new Forecast { Id=5, City="Tokio", Morning=74, Afternoon=70, Midnight=60 }
            };
        }
        return Session[C_ForecastModels] as IEnumerable<Forecast>;
    }
    set { Session[C_ForecastModels] = value; }
}

 public ActionResult Index()
 {
     // Return the Session stored Forecasts
     return View(ForecastModels);
 }

 //
 // Update Forecast value for selected daytime (Morning/Afternoon/Midnight)
 [HttpPost]
 public JsonResult Updateforecast(int cityId, string daytime, int value)
 {
     var cityForecast = ForecastModels.SingleOrDefault(forecastModel => forecastModel.Id == cityId);
     if (cityForecast == null)
         return Json(false);
     switch (daytime)
     {
         case "Morning":
             cityForecast.Morning = value;
             break;
         case "Afternoon":
             cityForecast.Afternoon = value;
             break;
         case "Midnight":
             cityForecast.Midnight = value;
             break;
     }
     return Json(true);
 }

Conclusion

This is a basic example to demonstrate the KnockoutJs extenders usage. The realtime edition on the grid and the keyboard navigation resembles the old DOS applications, but still is a powerful technique for some specific scenarios (I've worked in some projects on that this type of UI was explicitly required by the client or app end users).
Enjoy!

Project files

Telerik + Nettiers series

noreply@blogger.com (Martín Olivares) — Tue, 15 Sep 2009 14:19:00 +0000

Radgrid paging, sorting, grouping and sorting with Nettiers objectdatasource

This post starts a new serie of post to demostrate the combination of two cools asp.net tools: Telerik Suite & Nettiers
This example shows the use of the more common features of Radgrid as paging, sorting, filtering and grouping with a Nettiers object datasource.
Some helpfull notes:
1) The objectdatasource method used to retrieve the data is GetPaged.
2) The code in OnSelecting event of the object datasource set correct the parameters for the objectdatasource.
3) The EnableLinqExpressions of radgrid is set to false to avoid by pass the objectdatasource results.
4) The CanRetrieveAllData of mastertableview is set to false in order to get a correct sorting.

The aspx page


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

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <telerik:RadScriptManager ID="RadScriptManager1" runat="server">
    </telerik:RadScriptManager>
    <div>
        <telerik:RadGrid ID="RadGrid1" ShowGroupPanel="True" GridLines="None" DataSourceID="EmployeeDataSource1"
            AllowFilteringByColumn="True" AllowAutomaticDeletes="True" AllowAutomaticUpdates="True"
            AllowAutomaticInserts="True" OnPreRender="RadGrid1_PreRender" AllowPaging="True"
            AllowSorting="True" Skin="WebBlue" PageSize="10" EnableLinqExpressions="false"
            AllowCustomPaging="true" runat="server">
            <MasterTableView GroupLoadMode="client" AllowMultiColumnSorting="true" CommandItemDisplay="TopAndBottom"
                DataSourceID="EmployeeDataSource1" DataKeyNames="EmployeeId" CanRetrieveAllData="false"
                AllowCustomSorting="true">
                <PagerStyle AlwaysVisible="true" />
            </MasterTableView>
            <ClientSettings AllowDragToGroup="True" AllowColumnsReorder="True" ReorderColumnsOnClient="True">
                <Selecting AllowRowSelect="True" />
            </ClientSettings>
        </telerik:RadGrid>
        <data:EmployeeDataSource ID="EmployeeDataSource1" runat="server" EnablePaging="True"
            EnableSorting="True" SelectMethod="GetPaged" EnableCaching="false" OnSelecting="EmployeeDataSource1_OnSelecting">
        </data:EmployeeDataSource>
    </div>
    </form>
</body>
</html>

The code behind


using System;
using System.Web.UI.WebControls;

public partial class Radgrid : System.Web.UI.Page
{
    string masterTableFilter = string.Empty;

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void EmployeeDataSource1_OnSelecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        // Set the pagesize in each selecting to avoid the Int32.MaxValue (2147483647) passed by some Radgrid versions when filtering
        e.Arguments.MaximumRows = RadGrid1.PageSize;

        // Clear the master table filterExpression 
        if (!string.IsNullOrEmpty(RadGrid1.MasterTableView.FilterExpression))
        {
            masterTableFilter = RadGrid1.MasterTableView.FilterExpression;
            RadGrid1.MasterTableView.FilterExpression = string.Empty;
        }

        // Set the Nettiers Datasource parameters 
        e.InputParameters["WhereClause"] = masterTableFilter;
        e.InputParameters["OrderBy"] = RadGrid1.MasterTableView.SortExpressions.GetSortString();
    }

    // Restore the master table filterExpression 
    protected void RadGrid1_PreRender(object sender, EventArgs e)
    {
        RadGrid1.MasterTableView.FilterExpression = masterTableFilter;
    }
}

Efficient Nettiers Deepload - Filling object graphs in just one database call

noreply@blogger.com (Martín Olivares) — Thu, 23 Jul 2009 14:32:00 +0000

After using several ORM's I found that when need a complete object graph or some related entities (INNER/LEFT JOIN "like") they haven't an efficient approach built-in.
Almost all ORM can lazy load related properties (collections or instances) that means additional db calls and specifically in Nettiers filling an object with n-childs implies (n+1) database calls with the related performance penalty.
On this post, I want expose one alternative to populate an entire object graph in just one database call.
The concept it's very simple and consists on deserialize a string returned from the database that contains an xml with the representation of the entire object graph.

Example
"In one application that uses the AdventureWorks database you need to get the orders with their order details and the product referenced in the order detail for a selected customer."

1) First develop a simple method to take a look to the string that the deserializer is expecting.

Usage:

string sampleData = GetSampleData(676);

Method:

protected string GetSampleData(int customerId)
{
SalesOrderHeaderService salesOrderHeaderService = new SalesOrderHeaderService();
Type[] childTypes = new Type[] { typeof(TList<SalesOrderDetail>), typeof(Product) };
TList<SalesOrderHeader> salesOrders = salesOrderHeaderService.DeepLoadByCustomerID(customerId, true, DeepLoadType.IncludeChildren, childTypes);
return EntityHelper.SerializeXml<SalesOrderHeader>(salesOrders);
}

2) Create an stored procedure that returns an xml with the representation of the entire object graph

CREATE PROCEDURE [dbo].[SalesOrdersGetWithDetailsAndProductsByCustomerId]
@customerId int,
@result ntext OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SET @result=
'<?xml version="1.0" encoding="utf-16"?><ArrayOfSalesOrderHeader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'
+
(SELECT SalesOrderHeader.*,
   '<SalesOrderDetailCollection>' +
   (SELECT SalesOrderDetail.*,
       (SELECT Product.*
           FROM Production.Product Product
               WHERE Product.ProductID = SalesOrderDetail.ProductID
                   FOR XML RAW ('ProductIDSource'), ELEMENTS)
       FROM Sales.SalesOrderDetail SalesOrderDetail
           WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
               FOR XML AUTO, ELEMENTS)
   +  '</SalesOrderDetailCollection>'
FROM
   Sales.SalesOrderHeader SalesOrderHeader
WHERE SalesOrderHeader.CustomerID = @customerId
   FOR XML AUTO, ELEMENTS)
+
'</ArrayOfSalesOrderHeader>'

SELECT @result

END;

3) Call the SP and deserialize the result

Usage:
TList<SalesOrderHeader> customerSalesOrders = SalesOrdersGetWithDetailsAndProductsByCustomerId(676);

Method:
protected TList<SalesOrderHeader> SalesOrdersGetWithDetailsAndProductsByCustomerId(int customerId)
{
SqlCommand command = new SqlCommand("SalesOrdersGetWithDetailsAndProductsByCustomerId");
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@result", SqlDbType.VarChar);
param.Direction = ParameterDirection.Output;
param.Size = 10000;
command.Parameters.Add(param);
command.Parameters.Add(new SqlParameter("@customerId", customerId));
DataRepository.Provider.ExecuteNonQuery(command);
string resultParameter = command.Parameters[0].Value.ToString().Replace("&lt;", "<").Replace("&gt;", ">").Replace("&amp;lt;", "<").Replace("&amp;gt;", ">");
return EntityHelper.DeserializeListXml<SalesOrderHeader>(resultParameter);
}

Notes:
- Remove XmlIgnore Attribute for the related entities that you want populate. You can identify them by the "Source" sufix (ProductIDSource in the example).
- Attributes serialization is case sensitive, if you has applied some style on the UsePascalCasing codesmith template property, your class fields names casing could be different that the db field definition. In this case use field aliasing in the stored procedure select clause.
- To deepload a collection put the subquery on the select fields and enclose with the collections delimiter ('SalesOrderDetailCollection' in the example).

- To deepload a one to one relation put the subquery on the select fields and use for "RAW" in FOR XML clause to overwrite the row element name generated automatically ('ProductIDSource' in the example).

Codesmith Method Picker Property

noreply@blogger.com (Martín Olivares) — Wed, 29 Oct 2008 14:20:00 +0000

I just published my first project on Codeplex. The project is a Codesmith add-in, below is the project Url, description and a screenshot. Hope that the Codesmith users find this helpful.

Project Description
This project is a custom property for Codesmith that allows select methods from assemblies.
You can choose a set of methods and use this list to any purpose as generate a webservice to expose them, make method tests or a javascript method call wrapper.

Project Url
Codesmith Method Picker Property

jQuery Carousel Example

noreply@blogger.com (Martín Olivares) — Sun, 29 Jun 2008 14:04:00 +0000

A cool way to show a items gallery is using a scrollable div as Amazon displays related books for the current selected book. This technique is known as "carousel". I made an example (Download source (85Kb)) that shows a list of motherboards using a jQuery Carousel Component populated dinamically via Ajax queries (useful when haves a large items quantity to show)

Server side pagination method
The request/response to the method that returns a "page" of the carousel are maded using JSON.
For simplicity the pagination is done against a generic collection, but easily can be a database query.


    [WebMethod]

    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]

    public static List<Motherboard> GetItems(int pageIndex, int pageSize)

    {

        List<Motherboard> motherboards = new List<Motherboard>

        {

           new Motherboard("Asus P5Q Deluxe", "Asus P5Q Deluxe Motherboard", "Intel Socket T LGA775, P45 Express, ATX, 16GB DDR2, 1600MHz FSB"),

           new Motherboard("GigaByte GA-P35-DS3L", "GigaByte GA-P35-DS3L Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR, 1333MHz FSB "),

           new Motherboard("Intel D5400XS", "Intel D5400XS Motherboard", "Intel Socket J LGA771, P35 Express, Extended ATX, 8GB DDR2, 1600MHz FSB "),

           new Motherboard("Asus M3N-HT Deluxe Mempipe", "Asus M3N-HT Deluxe Mempipe Motherboard", "Socket AM2+, nVIDIA nForce 780a, ATX, 8GB DDR2, 2600MHz FSB"),

           new Motherboard("ABIT IP35 PRO XE", "ABIT IP35 PRO XE Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR2, 1600MHz FSB"),

           new Motherboard("Asus Striker Extreme", "Asus Striker Extreme Motherboard", "Quad-Core/Core 2 Duo Extreme/Core 2 Duo/Pentium D/Celeron D, Socket T"),

           new Motherboard("XFX MB-N780-ISH9", "XFX MB-N780-ISH9 Motherboard", "Intel Socket T LGA775, nForce 780i SLI, ATX, 8GB DDR2, 1333MHz FSB "),

           new Motherboard("HP Asus P5BW-LA", "HP Asus P5BW-LA Motherboard", "Intel Socket T LGA775, P965 Express, Micro ATX, 8GB DDR2, 1066MHz FSB"),

           new Motherboard("Biostar P4M900-M4", "Biostar P4M900-M4 Motherboard", "Core 2 Quad/Pentium D/Pentium 4/Celeron D, Socket T SLI, ATX, 8GB DDR2, 1333MHz Bus"),

           new Motherboard("MSI P6N Diamond", "MSI P6N Diamond Motherboard", "Intel Socket T LGA775, nForce 680i SLI, ATX, 8GB DDR2, 1333MHz FS"),        

           new Motherboard("Intel D915GAG", "Intel D915GAG Motherboard", "Pentium 4/Celeron D, Socket T, 915G, mATX, 4GB DDR, 800MHz FSB"),

           new Motherboard("ABIT IP35-E", "ABIT IP35-E Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR2, 1333MHz FSB "),        

        };

        return motherboards.GetRange((pageIndex - 1) * pageSize, pageSize);

    }

Client side pagination method


<script>



function mycarousel_makeRequest(carousel, first, last, per_page, page)

{

    // Lock carousel until request has been made

    carousel.lock();



    // Call the server side method with pageIndex and pageSize parameters

    $.ajax({

    type: "POST",

    url: "Default.aspx/GetItems",

    data: "{'pageIndex':'" + page + "', 'pageSize':'" + per_page + "'}",

    contentType: "application/json; charset=utf-8",

    dataType: "json",

    success: function(data) {

            mycarousel_itemAddCallback(carousel, first, last, data.d, page);

        }

        });

}



function mycarousel_itemAddCallback(carousel, first, last, data, page)

{

    // Unlock 

    carousel.unlock();



     // Set size

     carousel.size(12);



     var per_page = carousel.last - carousel.first + 1;

  

     for (var i = first; i <= last; i++)

     {

         var pos = i - 1;

         var idx = Math.round(((pos / per_page) - Math.floor(pos / per_page)) * per_page);



         carousel.add(i, mycarousel_getItemHTML(data[idx]));

     }



};



/**

 * Buil item html 

 */

function mycarousel_getItemHTML(mother)

{

    var item = "<div>";

    item += "<img src='images/" + mother.Image + ".jpg' alt='" + mother.Title + "'>";

    item += "</div>";

    item += "<div class='Title'>";

    item += mother.Title;

    item += "</div>";

    item += "<div class='Description'>";

    item += mother.Description;

    item += "</div>";

    return item;

};



</script>

Download source: jQuery Carousel in asp.net.rar (85Kb)

Generics Examples - Generating instances with two methods or how to simplify the DAL

noreply@blogger.com (Martín Olivares) — Tue, 06 May 2008 00:45:00 +0000

In this delivery two methods to generate instances or collections of our entities, simplifying therefore the code of our DAL (traditionally composed by a great amount of methods and mappings “field to field” from tables to objects). Once collected the data using a DataReader, with these methods is possible to obtain a single entity instance or a strong typed collection of instances.

The Methods


// A single instance
public static void FillEntity<T>(T instance, IDataRecord datarecord)
{
    Type instanceType = instance.GetType();
    for (int i = 0; i < datarecord.FieldCount; i++)
    {
        if (datarecord[i] != DBNull.Value)
        {
            string propName = datarecord.GetName(i);
            PropertyInfo propInfo = instanceType.GetProperty(propName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
            if (propInfo != null)
            {
                propInfo.SetValue(instance, datarecord[propInfo.Name], null);
            }
        }
    }
}

// A instance collection
public static List<T> FillEntities<T>(IDataReader dr)
{
    List<T> entities = new List<T>();
    while (dr.Read())
    {
        T instance = Activator.CreateInstance<T>();
        Utils.FillEntity<T>(instance, dr);
        entities.Add(instance);
    }
    return entities;
}

Using the methods


// A Single Instance
public static UserInfo UserGetById(string userid)
{
    UserInfo userInfo = new UserInfo();
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetStoredProcCommand("usergetbyid");
    db.AddInParameter(dbCommand, "userid", DbType.String, userid);
    IDataReader idr = db.ExecuteReader(dbCommand);
    if (idr.Read())
    {
        Utils.FillEntity<UserInfo>(userInfo, idr);
    }
    return userInfo;
}

// A instance collection
public static List<OrderInfo> GetOrders(int customerid)
{
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetStoredProcCommand("getorders");    
    db.AddInParameter(dbCommand, "customerid", DbType.Int32, customerid);
    return Utils.FillEntities<OrderInfo>(db.ExecuteReader(dbCommand));
}

Generics Examples - Passing paramaters to Stored Procedures

noreply@blogger.com (Martín Olivares) — Sat, 26 Apr 2008 00:39:00 +0000

In this post begins a series of examples of application of Generics that were useful to me throughout several developments. In this delivery a generic method that receives an instance of some entity of our domain and a DbCommand, and based on the SqlParameterAttributes attributes “reflected” from the entity maps the value of the properties with the parameters of stored procedure of the received DbCommand. Using this only method and “decorating” with the SqlParameterAttribute attributes the suitable properties of our classes it is possible to simplify the stored procedures calls.

The Method


public static void MatchStoreProceduresParams<T>(T entity, DbCommand command)
{
    Type type = entity.GetType();
    foreach (PropertyInfo entityProperty in type.GetProperties())
    {
        SqlParameterAttribute[] propSqlAttributeArray = (SqlParameterAttribute[])entityProperty.GetCustomAttributes(typeof(SqlParameterAttribute), false);
        if (propSqlAttributeArray.Length > 0)
        {
            SqlParameterAttribute propSqlAttribute = propSqlAttributeArray[0];
            if (propSqlAttribute != null)
            {
                SqlParameter sqlParam = new SqlParameter();
                sqlParam.ParameterName = propSqlAttribute.Name;
                sqlParam.SqlDbType = propSqlAttribute.SqlDbType;
                sqlParam.Value = entityProperty.GetValue(entity, null);
                if (propSqlAttribute.IsDirectionDefined)
                {
                    sqlParam.Direction = propSqlAttribute.Direction;    
                }
                command.Parameters.Add(sqlParam);
            }
        }

    }
}

Using the method


public static int CustomerSave(CustomerInfo customer)
{
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetStoredProcCommand("CustomerUpdate");
    Utils.MatchStoreProceduresParams(customer, dbCommand);
    return db.ExecuteNonQuery(dbCommand);
}

Decorando la clase


public class CustomerInfo
{
    int _id;
    string _name;
    int _age;
    decimal _balance;

    public CustomerInfo()
    {

    }
    
    [SqlParameter("@CustomerId", System.Data.SqlDbType.Int)]
    public int Id
    {
        get { return _id; }
        set { _id = value; }
    }
    
    [SqlParameter("@FirstName", System.Data.SqlDbType.Char)]
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }

    [SqlParameter("@Age", System.Data.SqlDbType.Int)]
    public int Age
    {
        get { return _age; }
        set { _age = value; }
    }

    [SqlParameter("@Balance", System.Data.SqlDbType.Decimal)]
    public decimal Balance
    {
        get { return _balance; }
        set { _balance = value; }
    }
}

Telerik Radgrid: showing hierarchical data using a Dataset with multiple tables example

noreply@blogger.com (Martín Olivares) — Mon, 21 Apr 2008 00:20:00 +0000

The telerik radgrid is one of the most complete grid control in the market. Have a cool set of features and covers the most commonly scenaries.
A common UI requirement is show Hierarchycal data, this data can be loaded in several ways with or without postbacks changing the property "HierarchyLoadMode". In case that the set of data to show is not great (use your criteria), you can choose load all data in the client obtaining a more responsive UI. To minimize roundtrips to database a good choice is code an stored procedure that returns multiple results and obtain a DataSet with multiple tables.
I make an example based on the AdventureWorks db to show the Departments and their respective employees.

The Stored Procedure:
This SP gets the data from Departments and Employees returning two results that share one column (DepartmentId) to make the parent/child relation.


CREATE PROCEDURE [HumanResources].[Department_Get_DepartmentAndEmployees]
AS

/* Get the departments */
SELECT HRD.DepartmentID, HRD.Name as DepartmentName
FROM HumanResournces.Department HRD
ORDER BY DepartmentName

/* Get the employees */
SELECT PC.FirstName, 
       PC.LastName,
       HRE.Title,
       HRDep.DepartmentId
FROM
HumanResources.Employee HRE
INNER JOIN Person.Contact PC ON PC.ContactId = HRE.ContactId
INNER JOIN HumanResources.EmployeeDepartmentHistory HRDep ON HRDep.EmployeeId = HRE.EmployeeId

The .aspx Code:
Note the ParentTableRelation tag uses the DepartmentId field to make the relation from the Employees table to the Department table.


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

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<!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>Telerik Radgrid: showing hierarchical data using a Dataset with multiple tables</title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="Scriptmanager1" runat="server">
    </asp:ScriptManager>
    <telerik:RadGrid ID="gvEmployeesByDepartment" runat="server" Width="95%" AutoGenerateColumns="False"
        GridLines="None" ShowHeader="True" Skin="Web20">
        <MasterTableView DataMember="Departments" DataKeyNames="DepartmentId" Width="100%"
            Name="Customers" HierarchyLoadMode="Client">
            <DetailTables>
                <telerik:GridTableView DataMember="Employees" DataKeyNames="DepartmentId" Width="100%"
                    runat="server" ShowHeader="true">
                    <ParentTableRelation>
                        <telerik:GridRelationFields DetailKeyField="DepartmentId" MasterKeyField="DepartmentId" />
                    </ParentTableRelation>
                    <RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                        Visible="False">
                        <HeaderStyle Width="20px"></HeaderStyle>
                    </RowIndicatorColumn>
                    <ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                        Visible="False" Resizable="False">
                        <HeaderStyle Width="20px"></HeaderStyle>
                    </ExpandCollapseColumn>
                    <Columns>
                        <telerik:GridBoundColumn DataField="Title" UniqueName="Title" ReadOnly="true" HeaderText="Title">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="FirstName" UniqueName="FirstName" ReadOnly="true"
                            HeaderText="First Name">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="LastName" UniqueName="LastName" ReadOnly="true"
                            HeaderText="Last Name">
                        </telerik:GridBoundColumn>
                    </Columns>
                </telerik:GridTableView>
            </DetailTables>
            <RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                Visible="False">
                <HeaderStyle Width="20px"></HeaderStyle>
            </RowIndicatorColumn>
            <ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                Resizable="False">
                <HeaderStyle Width="20px"></HeaderStyle>
            </ExpandCollapseColumn>
            <Columns>
                <telerik:GridBoundColumn DataField="DepartmentName" UniqueName="DepartmentName" HeaderText="Department">
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>
    </form>
</body>
</html>

The .cs Code:


using System;
using System.Data;
using System.Web.UI;
using System.Data.SqlClient;

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

    private void BindDepartmentsAndEmployees()
    {
        SqlConnection conn = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;User ID=marketpoint;Initial Catalog=AdventureWorks;Data Source=(local)\sqlexpress");
        SqlCommand cmd = new SqlCommand("[HumanResources].[Department_Get_DepartmentAndEmployees]", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet dsDepartmentAndEmployees = new DataSet();
        da.Fill(dsDepartmentAndEmployees);
        if(da != null)
        {
            // Renames Dataset table to get more clear names in grid declarative properties
            dsDepartmentAndEmployees.Tables[0].TableName = "Departments";
            dsDepartmentAndEmployees.Tables[1].TableName = "Employees";
            gvEmployeesByDepartment.DataSource = dsDepartmentAndEmployees;
            gvEmployeesByDepartment.DataBind();
        }
    }
}

The UI Result (Click to Enlarge):

NetTiers: Expose custom stored procedures as componentlayer methods

noreply@blogger.com (Martín Olivares) — Fri, 04 Apr 2008 21:08:00 +0000

Sometimes is needed a custom functionality that goes far that the CRUD functionality generated by NetTiers. To acomplish this task we can use a custom stored procedure to get the data and expose it as a method of the componentlayer (services layer).
Follow the next steps to expose a custom stored procedure:

1) Configure Codesmith properties
CustomProceduresStartsWith (example: {1}{0}_)
ProcedurePrefix (example: cust_sp_ )

2) Add a Stored Procedure
Add a SP with a name that follows the convention defined in CustomProceduresStartsWith where {1} is the placeholder for
ProcedurePrefix and {0} is the placeholder for the TableName, for example cust_sp_Contact_GetBySalesOrderCountBetween (using AdventureWorks db)


CREATE PROCEDURE [dbo].[cust_sp_Contact_GetBySalesOrderCountBetween]

    @min int,
    @max int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM Person.Contact C
    WHERE 
    (SELECT COUNT(*) FROM Sales.SalesOrderHeader SOH
        WHERE SOH.ContactId = C.ContactId) BETWEEN @min and @max
END

3) Run the template

4) Use the method
Now in the componentlayer for the entity we have our custom stored procedure exposed as a method


ContactService contactService = new ContactService();
gvContacts.DataSource = contactService.GetBySalesOrderCountBetween(25, 30);
gvContacts.DataBind();

Note: the return type of the custom stored procedure method can be a TList or the defined in the CustomNonMatchingReturnType property (DataSet/IDataReader).
To determine what type will be return a method compares every column order and type returned from the Sp with every column order and type of the Entity table.
Debugging a bit the template, I have reviewed the method that compares the stored procedure column type with the table column type. This method returns false if some column type returned has no equivalent (File: \TemplateLib\CommonSqlCode.cs Method: IsMatching)


if (!SqlTypesAreEquivalent(command.CommandResults[0].Columns[i].NativeType, table.Columns[i].NativeType))
        return false;

If you have UDT (User-Defined Data Types) fields in your table this method will be return false and CustomNonMatchingReturnType will be setted as Method Return type.

Usually is preferible that have an TList as method return than a Dataset or a IDataReader, because in this way you have all the NetTiers functionality to manipulate the data (deepload the TList returned for example). To force return a TList when have
UDT fields you can edit the file \TemplateLib\CommonSqlCode.cs and change in the method IsMatching the above line for this:


if (!SqlTypesAreEquivalent(command.CommandResults[0].Columns[i].DataType.ToString(), table.Columns[i].DataType.ToString()))

Export Html to Pdf using iTextSharp

noreply@blogger.com (Martín Olivares) — Thu, 06 Mar 2008 19:12:00 +0000

This is an example to export from html to pdf using iTextSharp. In this case I used a html template file with some token fields that are replaced and after exported to pdf.


    private static void GeneratePDF()
    {
        // Set output folder to /pdf
        string outputFolder = HttpContext.Current.Server.MapPath("pdf");
        // Set output file name to a Guid based name
        string pdfFileName = outputFolder + "\\" + Guid.NewGuid().ToString() + ".pdf";

        Document iTextSharpDocument = new Document(new Rectangle(880, 700));
        FileStream fs = new FileStream(pdfFileName, FileMode.CreateNew);
        PdfWriter writer = PdfWriter.GetInstance(iTextSharpDocument, fs);
        iTextSharpDocument.Open();

        // Read the html template and replace values
        string templatePath = HttpContext.Current.Server.MapPath("html/ReportTemplate.html");
        TextReader templateReader = new StreamReader(templatePath);
        StringBuilder sbTemplate = new StringBuilder(templateReader.ReadToEnd());
        sbTemplate.Replace("[@title]", "Sales");
        sbTemplate.Replace("[@name]", "Jhon Smith");
        sbTemplate.Replace("[@date]", DateTime.Now.ToString());

        XmlDocument htmlDoc = new XmlDocument();
        htmlDoc.LoadXml(sbTemplate.ToString());

        HtmlParser.Parse(iTextSharpDocument, htmlDoc);

        // Release resources
        templateReader.Close();

        iTextSharpDocument.Close();
        writer.Close();

        writer = null;
        iTextSharpDocument = null;

        fs.Close();
        fs.Dispose();
    }

NetTiers "SELECT DISTINCT" method

noreply@blogger.com (Martín Olivares) — Mon, 03 Dec 2007 20:57:00 +0000

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 });

Reflect the underlying type of nullable property / field

noreply@blogger.com (Martín Olivares) — Tue, 07 Aug 2007 14:10:00 +0000

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>

Handle multiple asynchronous calls in Asp.Net Ajax complex scenarios

noreply@blogger.com (Martín Olivares) — Mon, 16 Jul 2007 14:54:00 +0000

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();
    }
}

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

noreply@blogger.com (Martín Olivares) — Wed, 06 Jun 2007 23:30:00 +0000

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)

Localization and UICulture="auto" attribute

noreply@blogger.com (Martín Olivares) — Mon, 04 Jun 2007 23:57:00 +0000

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

noreply@blogger.com (Martín Olivares) — Mon, 04 Jun 2007 17:50:00 +0000

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