DataBinding with CSLA and telerik radGrid control

mongo posted on Sunday, February 25, 2007

I was looking at some threads both here and at Telerik that didn't quite resolve my scenario for hierarchical binding of Telerik's radGrid and CSLA.  So I thought I'd drop my solution here for people to find it.  I hope this helps - I also blogged about it here.

I have two collections where the second  set is a child collection  to the first set.  The code below is a minimal set of operations to detail how they are wired together.

First, I set up two distinct CslaDataSource controls - one for each collection.  In reality, these are part of the same object hiearchy and I could do this with one control, but I feel it comes out a little cleaner with two.

The key points to follow are this:

1. Grid: Set the MasterTableView and DetailTable DataSourceIds to their respective CSLA data binding control (dbc).
2. Grid: Set the MasterTableView's DateKeyNames property to reflect the unique identifier for the parent row.
3. DBC Parent: Set up the DataSource_SelectObject to return the business object via the e.BusinessObject parameter.
4. Grid: Map the ItemCommandEvent to grab parent key using the code snippet below.
5. DBC Child: Set up the DataSource_SelectObject to return the appropriate child object according to the key obtained in step #4.

Below is the code snippet for the Grid_ItemCommand event mentioned in Step#4:

private Guid _activeParentId = Guid.Empty;  //data type may vary for actual implementation.

protected void ..._ItemCommand(...)
{
   if( e.CommandName == ExpandCollapseCommand )
      _activeParentId = (Guid) e.Item.OwnerTableView.DataKeyValues[e.Item.Index][parentIdName];
}

Skafa replied on Sunday, February 25, 2007

I used a somewhat similar approach, thanks for the contibution.

you managed to implement server side paging using radGrid and the 2.1.x CslaDataSource ? haven't had a look at it yet, but maybe you can share an imlementation ?

mongo replied on Friday, March 02, 2007

Sorry for the late reply - I have.  The implementation differs depending on the nature of your collection(s), but it's fairly straight forward.  I will post in the next few days.

Skafa replied on Friday, March 02, 2007

I made a proof of concept this week to see if it works:

    [Serializable()]
    public class OrderList : OrderListBase<OrderList, OrderListItem>, Csla.Core.IReportTotalRowCount
    {
        private int _totalRowCount;
      
       // ....
      
        public static OrderList GetPagedOrderList(Csla.Web.SelectObjectArgs args)
        {
            if (!CanGetObject())
                throw new System.Security.SecurityException("User not authorized to view a OrderList");
            return DataPortal.Fetch<OrderList>(new PagedCriteria(args));
        }

       // .....

        protected class PagedCriteria : CriteriaBase
        {
            private Csla.Web.SelectObjectArgs _args;
            public Csla.Web.SelectObjectArgs Args
            {
                get
                {
                    return _args;
                }
            }

            public PagedCriteria(Csla.Web.SelectObjectArgs args)
                : base(typeof(OrderList))
            {
                _args = args;
            }
        }

       // .....

        private void DataPortal_Fetch(PagedCriteria criteria)
        {
            RaiseListChangedEvents = false;
            IsReadOnly = false;

            using (SqlConnection cn = new SqlConnection(Database.Connection))
            {
                cn.Open();

                ExecuteFetch(cn, criteria);
            }//using

            IsReadOnly = true;
            RaiseListChangedEvents = true;
        }

        protected void ExecuteFetch(SqlConnection cn, PagedCriteria criteria)
        {
            using (SqlCommand cm = cn.CreateCommand())
            {
                string sortExpression = "date_created ASC";

                cm.CommandType = CommandType.Text;

                if (!string.IsNullOrEmpty(criteria.Args.SortBLOCKED EXPRESSION
                    sortExpression = criteria.Args.SortExpression;

                cm.CommandText = string.Format(@"
                    WITH Orders AS
                    (
                        SELECT *, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber
                        FROM tblOrder
                    )
                    SELECT *
                    FROM Orders
                    WHERE RowNumber BETWEEN @StartRow AND (@StartRow + (@PageSize - 1))
                    ORDER BY RowNumber ASC
                ", sortExpression);
                if (criteria.Args.StartRowIndex == 0)
                    cm.Parameters.AddWithValue("@StartRow", 1);
                else
                    cm.Parameters.AddWithValue("@StartRow", criteria.Args.StartRowIndex);
                cm.Parameters.AddWithValue("@PageSize", criteria.Args.MaximumRows);

                using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                {
                    while (dr.Read())
                        this.Add(OrderListItem.GetOrderListItem(dr));
                }
            }//using

            TotalRowCountCommand cmd;
            cmd = DataPortal.Execute<TotalRowCountCommand>(new TotalRowCountCommand("tblOrder"));
            _totalRowCount = cmd.TotalRowCount;
        }

        #region IReportTotalRowCount Members

        public int TotalRowCount
        {
            get { return _totalRowCount; }
        }

        #endregion

       // ....
    }

    public class TotalRowCountCommand : CommandBase
    {
        private string _tableName;
        private string _whereClause;
        private int _totalRowCount;

        public int TotalRowCount
        {
            get
            {
                return _totalRowCount;
            }
        }

        public TotalRowCountCommand(string tableName)
        {
            _tableName = tableName;
        }

        public TotalRowCountCommand(string tableName, string whereClause)
        {
            _tableName = tableName;
            _whereClause = whereClause;
        }

        protected override void DataPortal_Execute()
        {
            using (SqlConnection cn = new SqlConnection(Database.Connection))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = @"SELECT COUNT(*) FROM " + _tableName;
                    if (!string.IsNullOrEmpty(_whereClause))
                        cm.CommandText += " WHERE " + _whereClause;

                    object result = cm.ExecuteScalar();
                    try
                    {
                        _totalRowCount = Convert.ToInt32(result);
                    }
                    catch
                    {
                        _totalRowCount = 0;
                    }
                }
            }
        }
    }


Like this I can bind to the grid as usual. Paging AND Sorting works.

 The only 'problem' is that the sortexpression contains property names and (obviously) not column names. In the case of this proof of concept, property names are equal to their respective column names.
What you really want is some kind of SortExpression parser which translates property names to column names. Also, when designing bussines objects which are constructed from more complex sql query's it gets harder to do the sorting in the database.

Ofcourse you can leave the sorting out and use a sortedbindinglist in the UI, as most types of sorting is an UI thing anyway.
   

Any comments ?

RobKraft replied on Wednesday, March 07, 2007

I think your example looks pretty good and I got a similar one working.  I don't know why sorting would be more difficult with more complex queries; unless you have multiple tables using the same column names and you continue to use select *.

I'm using a stored procedure and I pass in the start, max, and sortby columns to it.  You can see an example of that stored proc currently at robkraft.spaces.live.com.  The stored proc I use is a little more complicated than your SQL example because it runs on SQL 2000 (as well as SQL 2005).

I prefer to do the sorting in the database to reduce the memory requirements on the web server.

Skafa replied on Thursday, March 08, 2007

How about the mapping between property names and column names?

I think i am going to use some kind of xml mapping file, which maps the propertynames given by SortExpression to column names. Is that a workable aproach?

RobKraft replied on Thursday, March 08, 2007

I think there are a lot of workable approaches.  In my app; we are doing a lot of code generation so we have a table in the database that maps the column name to the propertyname.  In my stored procedure for paging, I simply do a lookup to that table to exchange the propertyname for the column name.

You could also use an XML document, or you could just hard-code the associations in the class.  You could probably also build a arraylist in your class to contain both values as you populate your object from the result set.

Personally, I would lean toward this last approach.

Also, we haven't decided how to handle the number of records changing (if it does).  We are considering doing the Count lookup just once to minimize that query, because 99% of the time the number of items in the result set will not change while our user is viewing it.  We could also requery the count just when they sort by a different column.

However, I suppose the best thing to do is to always query the count.  If the count changes, we may even want to let the user know that it changed.  Have you given any thought to this issue?

 

RobKraft replied on Wednesday, March 14, 2007

Also, I would recommend that you POPULATE SortExpression with the column names (at least that is what we plan to do).  The DataField will be the PropertyName and of course the HeaderText will be whatever we want to display.  Of course you still have to map the properties to fieldnames to populate SortExpression.

mongo replied on Sunday, April 15, 2007

I posted the approach I've used here: http://forums.lhotka.net/forums/thread/13802.aspx

Keep in mind, this is an in-memory approach only.  Rob's implementation discussion is far more useful for large datasets. The in-memory approach works for very small return sets or as a reference implementation.