Create a orderby builder

Hi, I use views quite alot in Nettiers and have found that the orderby clause could be extended to accomodate strongly typed parameters like the the FilterBuilder. For example take the following code:-

AllNamesSortBuilder foo = new AllNamesSortBuilder();
foo.AddColumn(AllNamesColumn.Name, SortDirection.ASC);
foo.AddColumn(AllNamesColumn.Origin, SortDirection.DESC);
Response.Write(foo.ToString());

VList<AllNames> AllNames1 = DataRepository.AllNamesProvider.Get("1=1", foo.ToString());
VList<AllNames> AllNames2 = DataRepository.AllNamesProvider.GetPaged("1=1", foo.ToString(),0,100, ref count);

The above code would generate the following string for the orderby clause:

Name ASC, Origin DESC

Obviously the code below would need to be generared for for each found view.

/// <summary>
/// Database sort direction
/// </summary>
public enum SortDirection
{
    ASC,
    DESC
}

/// <summary>
/// All names column
/// </summary>
public class Column
{

    private AllNamesColumn AllNamesColumn;
    private SortDirection Direction;

    public Column(AllNamesColumn column, SortDirection direction)
    {
        this.AllNamesColumn = column;
        this.Direction = direction;
    }

    public AllNamesColumn GetColumn()
    {
        return this.AllNamesColumn;
    }

    public SortDirection GetDirection()
    {
        return Direction;
    }

}

/// <summary>
/// All names sort builder
/// </summary>
public class AllNamesSortBuilder
{

    private List<Column> Columns = new List<Column>();

    public AllNamesSortBuilder() { }

    public void AddColumn(AllNamesColumn Column, SortDirection Direction)
    {
        Columns.Add(new Column(Column, Direction));
    }

    public override string ToString()
    {
        StringBuilder sb = new StringBuilder();
        Int32 count = 1;
        foreach (Column Column in Columns)
        {
            sb.Append(Column.GetColumn().ToString()).Append(" ").Append(Column.GetDirection().ToString());
            if (count++ < Columns.Count)
            {
                sb.Append(", ");
            }
        }
        return sb.ToString();
    }

}


Is this something that could find its way into NetTiers? Any thoughts would be welcome...
many thanks Rippo

I like the idea of what you are proposing here.  I do have a couple of questions, though.

1.  Wouldn't this be applicable to tables as well as views?

2.  Rather than requiring another column class, couldn't you just use a dictionary class?  So, instead of having an internal List<Column> you would have Dictionary<XXXColumn, SortDirection>?  Otherwise, you'll need to come up with a naming scheme for the Column class since you will need one for each view/table.  Either that, or you could make the Column class internal to the XXXSortBuilder class...

3.  Any chance you could fully integrate this and submit a patch?

Ok got the code working with the dictionary so now all I need are some tips on how to intregrate this into NetTiers for views and tables. Can anyone give me some tips how/where I should implement this code, many thanks

/// <summary>
/// Data base sort direction
/// </summary>
public enum SortDirection
{
    ASC,
    DESC
}

/// <summary>
/// All names sort builder
/// </summary>
public class AllNamesSortBuilder
{

    private Dictionary<AllNamesColumn, SortDirection> Columns = new Dictionary<AllNamesColumn,SortDirection>();

    public AllNamesSortBuilder() { }

    public void AddColumn(AllNamesColumn Column, SortDirection Direction)
    {
        Columns.Add(Column, Direction);
    }

    public override string ToString()
    {
        StringBuilder sb = new StringBuilder();
        Int32 count = 1;
        foreach (KeyValuePair<AllNamesColumn, SortDirection> kvp in Columns)
        {
            sb.Append(kvp.Key).Append(" ").Append(kvp.Value);
            if (count++ < Columns.Count)
            {
                sb.Append(", ");
            }
        }
        return sb.ToString();
    }

}

Guys I am attempting to add this code to the two files cst templates
    EntityViewProviderBaseCore.generated.cst
    EntityProviderBaseCore.generated.cst

All works well.

The only problem I am facing is trying to work out the best place to put this common enum?

/// <summary>
/// Database sort direction
/// </summary>
public enum SortDirection
{
    ASC,
    DESC
}

I have put this code for now in Guru.Data/Query/SQLUtil.cs
I have also tried to create a patch following instructions from http://www.nettiers.com/nightly.aspx but the repo is asking for username/password authentication.

I would really like this functionality.  Can you make a patch? 

NET TIERS CHANGES, so far, just need to confirm where to put the enum SortDirection and which templates I need to change for the tables.

Can anyone help? 

IN EntityProviderBaseCore.generated.cst

    #region <%= className %>SortBuilder
   
    /// <summary>
    /// All names sort builder
    /// </summary>
    [CLSCompliant(true)]
    public class <%= entityClassName %>SortBuilder
    {

        /// <summary>
        /// holds key value pair of <%= entityClassName %> column and sort direction
        /// </summary>
        private Dictionary<<%= entityClassName %>Column, SortDirection> Columns = new Dictionary<<%= entityClassName %>Column, SortDirection>();

        /// <summary>
        /// Constructor
        /// </summary>
        public <%= entityClassName %>SortBuilder() { }

        /// <summary>
        /// Adds a column to the sort builder
        /// </summary>
        /// <param name="Column"></param>
        /// <param name="Direction"></param>
        public void AddColumn(<%= entityClassName %>Column Column, SortDirection Direction)
        {
            Columns.Add(Column, Direction);
        }

        /// <summary>
        /// Provides the actual SQL sort string.
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            Int32 count = 1;
            foreach (KeyValuePair<<%= entityClassName %>Column, SortDirection> kvp in Columns)
            {
                sb.Append(kvp.Key).Append(" ").Append(kvp.Value);
                if (count++ < Columns.Count)
                {
                    sb.Append(", ");
                }
            }
            return sb.ToString();
        }

    }   
    #endregion <%= entityClassName %>SortBuilder


IN EntityViewProviderBaseCore.generated.cst

    #region <%= className %>SortBuilder
   
    /// <summary>
    /// All names sort builder
    /// </summary>
    [CLSCompliant(true)]
    public class <%= className %>SortBuilder
    {

        /// <summary>
        /// holds key value pair of <%= className %> column and sort direction
        /// </summary>
        private Dictionary<<%= className %>Column, SortDirection> Columns = new Dictionary<<%= className %>Column, SortDirection>();

        /// <summary>
        /// Constructor
        /// </summary>
        public <%= className %>SortBuilder() { }

        /// <summary>
        /// Adds a column to the sort builder
        /// </summary>
        /// <param name="Column"></param>
        /// <param name="Direction"></param>
        public void AddColumn(<%= className %>Column Column, SortDirection Direction)
        {
            Columns.Add(Column, Direction);
        }

        /// <summary>
        /// Provides the actual SQL sort string.
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            Int32 count = 1;
            foreach (KeyValuePair<<%= className %>Column, SortDirection> kvp in Columns)
            {
                sb.Append(kvp.Key).Append(" ").Append(kvp.Value);
                if (count++ < Columns.Count)
                {
                    sb.Append(", ");
                }
            }
            return sb.ToString();
        }

    }   
    #endregion <%= className %>SortBuilder

in sqlutil.cs

    /// <summary>
    /// Data base sort direction
    /// </summary>
    public enum SortDirection
    {
        /// <summary>
        /// Database Ascending
        /// </summary>
        ASC,

        /// <summary>
        /// Database Descending
        /// </summary>
        DESC
    }

I've done the changes to the templates, and run a successful codegen. 

Now we need to figure out how the SortBuilder will come in to play with the rest of nettiers.

We will have to overload some methods in the component layer to allow us to pass the SqlSortBuilder to the Find and Get methods....

Have you done any of this yet?

From the looks of it, we'll have to make edits to the ComponentDataAccess.cst template.

Edit: And I'm assuming this won't be the only place where we need to overload our Find and Get methods....

Anyone else care to chime in?

 No, have not done this yet. I too am hoping that somebody else can chirp in who knows where to overload the various methods...

I've made a couple of changes to the code to follow along with the naming of the methods used in the FilterBuilder.  I've also refactored a few things to prevent a DRY problem.

SqlUtil.cst

#region SqlSortDirection Enum
 
 /// <summary>
    /// Enumeration of SQL expression Sort Directions
    /// </summary>
    public enum SqlSortDirection
    {
        /// <summary>
        /// Database Ascending
        /// </summary>
        ASC,

        /// <summary>
        /// Database Descending
        /// </summary>
        DESC
    }
 
 #endregion

SqlStringBuilder.cst

#region SqlOrderByBuilder

    /// <summary>
    /// Allows for building SQL OrderBy expressions using strongly-typed column enumeration values.
    /// </summary>
    /// <typeparam name="EntityColumn">An enumeration of entity column names.</typeparam>
    [CLSCompliant(true)]
    public class SqlSortBuilder<EntityColumn> : SqlStringBuilder
    {
        /// <summary>
        /// holds key value pair of ScheduledFlight column and sort direction
        /// </summary>
        private Dictionary<EntityColumn, SqlSortDirection> _columns = new Dictionary<EntityColumn, SqlSortDirection>();

        #region Constructors

        /// <summary>
        /// Initializes a new instance of the SqlFilterBuilder class.
        /// </summary>
        public SqlSortBuilder() : base() { }

        #endregion

        #region Append

        /// <summary>
        /// Appends the specified column and sorts it by the specified SqlSortDirection
        /// </summary>
        /// <param name="column">The column to sort</param>
        /// <param name="sortDirection">The specified column's sort direction</param>
        /// <returns></returns>
        public virtual void Append(EntityColumn column, SqlSortDirection sortDirection)
        {
            AddColumn(column, sortDirection);
        }

        /// <summary>
        /// Appends the specified column and sorts it Ascending
        /// </summary>
        /// <param name="column">The column to sort</param>
        /// <returns></returns>
        public virtual void AppendASC(EntityColumn column)
        {
            AddColumn(column, SqlSortDirection.ASC);
        }

        /// <summary>
        /// Appends the specified column and sorts it Descending
        /// </summary>
        /// <param name="column">The column to sort</param>
        /// <returns></returns>
        public virtual void AppendDESC(EntityColumn column)
        {
            AddColumn(column, SqlSortDirection.DESC);
        }

        #endregion

        /// <summary>
        /// Adds a column to the sort builder
        /// </summary>
        /// <param name="column">The entity's column to sort by</param>
        /// <param name="sortDirection">The sort direction</param>
        private void AddColumn(EntityColumn column, SqlSortDirection sortDirection)
        {
            _columns.Add(column, sortDirection);
        }

        /// <summary>
        /// Provides the actual SQL sort string.
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            StringBuilder sb = new StringBuilder();
            Int32 count = 1;
            foreach (KeyValuePair<EntityColumn, SqlSortDirection> kvp in _columns)
            {
                sb.Append(kvp.Key).Append(" ").Append(kvp.Value);
                if (count++ < _columns.Count)
                {
                    sb.Append(", ");
                }
            }
            return sb.ToString();
        }

        #region Methods

        /// <summary>
        /// Gets the column name from the specified column enumeration value.
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        protected virtual String GetColumnName(EntityColumn column)
        {
            String name = EntityHelper.GetEnumTextValue(column as Enum);

            if (String.IsNullOrEmpty(name))
            {
                name = column.ToString();
            }

            return name;
        }

        #endregion Methods

    }
    #endregion

EntityViewProviderBaseCore.generated.cst

#region <%= className %>SortBuilder
   
    /// <summary>
    /// A strongly-typed instance of the <see cref="SqlSortBuilder&lt;EntityColumn&gt;"/> class
 /// that is used exclusively with a <see cref="<%= className %>"/> object.
    /// </summary>
    [CLSCompliant(true)]
    public class <%= className %>SortBuilder : SqlSortBuilder<<%= className %>Column>
    {
  #region Constructors

  /// <summary>
  /// Initializes a new instance of the <%= className %>SqlSortBuilder class.
  /// </summary>
  public <%= className %>SortBuilder() : base() { }

  #endregion Constructors

    }   
    #endregion <%= className %>SortBuilder

EntityProviderBaseCore.generated.cst:

#region <%= entityClassName %>SortBuilder
   
    /// <summary>
    /// A strongly-typed instance of the <see cref="SqlSortBuilder&lt;<%= GetClassName(SourceTable, ClassNameFormat.Column) %>&gt;"/> class
 /// that is used exclusively with a <see cref="<%= entityClassName %>"/> object.
    /// </summary>
    [CLSCompliant(true)]
    public class <%= entityClassName %>SortBuilder : SqlSortBuilder<<%= GetClassName(SourceTable, ClassNameFormat.Column) %>>
    {
  #region Constructors

  /// <summary>
  /// Initializes a new instance of the <%= entityClassName %>SqlSortBuilder class.
  /// </summary>
  public <%= entityClassName %>SortBuilder() : base() { }

  #endregion Constructors

    }   
    #endregion <%= entityClassName %>SortBuilder

Next up is actually using it.... to be continued...

Ok... so I'm taking a look at the EntityProviderBaseCore.generates.cs.

For the Find methods, I'm noticing that the Filter parameters are being passed as IFilterParameterCollection.

I'm thinking we should follow this, and build some similar classes for the SortBuilder.

SqlSortColumnCollection : List<SqlSortColumn>, ISortColumnCollection

SqlSortColumn

ISortColumnCollection

Then, we implement it in the SqlSortBuilder, like so:

SqlSortBuilder<EntityColumn> : SqlStringBuilder , ISortColumnCollection

Then, we overload the Find methods with something like this:

public virtual void TList<Entity> Find(IFilterParameterCollection parameters, ISortColumnCollection sortColumns)

What do you think?

(EDIT: A new page is attached further on in the thread) 

Here is a patch with the new SortBuilder.

Let me know if it works for you...

Usage:

AirportCodeParameterBuilder pb = new AirportCodeParameterBuilder();
pb.AppendEquals(AirportCodeColumn.CountryCode, "US");

AirportCodeSortBuilder sb = new AirportCodeSortBuilder();

sb.AppendASC(AirportCodeColumn.City);
sb.AppendDESC(AirportCodeColumn.Code);

// or...
// sb.Append(AirportCodeColumn.Code, SqlSortDirection.DESC);

AirportCodeService acs = new AirportCodeService();

TList<AirportCode> airportCodes = acs.Find(pb, sb);

I NEED SOME OTHER EYES ON THIS!!eleventy1 

Please help me test this, and please give me some comments/suggestions/flames.

~Cheers

Is this patch going to make it into the next version of NetTiers as Citizen has done some great work! 

Rippo,

Have you tested this patch at all?  It worked for me for the table entities, but I have not tried it with views.

 Sorr for delay, been away for a week, I will give it some hammering this weekend and let you know what I find.