Showing posts with label NetTiers. Show all posts
Showing posts with label NetTiers. Show all posts

Tuesday, September 15, 2009

Telerik + Nettiers series

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;
    }
}
Posted by at No comments:
Labels: , ,

Thursday, July 23, 2009

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

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).


Posted by at 6 comments:
Labels: , , , ,

Friday, April 4, 2008

NetTiers: Expose custom stored procedures as componentlayer methods

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()))
Posted by at 6 comments:
Labels:

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 });
Posted by at 2 comments:
Labels: ,
Subscribe to: Posts (Atom)