AddOrderByAlias

[sbc]

Would it be possible to have AddOrderBy alias? At the moment you can do the following (PersonLastName is the alias for the column LastName):

Code: Select all

' Order by LastName
        ' (you can add as many order by columns as you like by repeatedly calling this)
        emps.Query.AddOrderBy(Employees.ColumnNames.PersonLastName, WhereParameter.Dir.ASC)

or

Code: Select all

' Order by LastName
        ' (you can add as many order by columns as you like by repeatedly calling this)
        emps.Query.AddOrderBy(\"LastName\", WhereParameter.Dir.ASC)

What I would like is to be able to sort by using the alias:

Code: Select all

' Order by LastName
        ' (you can add as many order by columns as you like by repeatedly calling this)
        emps.Query.AddOrderByAlias(\"PersonLastName\", WhereParameter.Dir.ASC)

If the alias does not exist, then throw an exception (or ignore the statement)

[mike.griffin]

Well, remember that Employees.ColumnNames.PersonLastName really equals just a string, say \"LastName\". And when you say AddOrderBy you are building a SQL statement, and the alias you've entered into the user defined meta data means nothing to SQL, however, we have options.

There are two sets of strings,

Employees.ColumnNames
Employees.PropertyNames (Alias if provided, othewise physical name)

I'm confused as to why you want to use the Alias in the OrderBy and not just use the Column Name? It must be that you are allowing the user to pick names and you are showing them the Alias because that is what they see on the screen? Am I correct?

We could add a collection to dOOdads called MetaProperties, and you could access it by name like this:

Code: Select all

emps.MetaProperties[Employees.PropertyNames.PersonLastName].ColumnName;
emps.MetaProperties[Employees.PropertyNames.PersonLastName].AllowsNull;
emps.MetaProperties[Employees.PropertyNames.PersonLastName].DataType;
emps.MetaProperties[Employees.PropertyNames.PersonLastName].MaxLength;

or I could simply enhance the template to have a hashtable in it, that would allow you to do this:

Code: Select all

emps.GetColumnName(Employees.PropertyName.PersonLastName);

what are your thoughts ...

[sbc]

What I am doing is producing a select form field via ASP.NET. Currently the output would end something like this:

Code: Select all

<select name=\"ddlSort_Employees\">
   <option value=\"firstname\">Person First Name</option>
   <option value=\"lastname\">Person Surname</option>
</select>


Doing this gives away your field names (Which you may not want). What would be better is:

Code: Select all

<select name=\"ddlSort_Employees\">
   <option value=\"PersonFirstName\">Person First Name</option>
   <option value=\"PersonSurname\">Person Surname</option>
</select>

The code used to generate the first bit of code is:

Code: Select all

Private Sub SetupSort()<%
' Loop through columns in table
For Each objColumn in objTable.Columns
' Loop through columns selected
   For intLp = 0 To columnNames.Count - 1
      ' current column name from list box
      columnName = columnNames(intLp)
      If objColumn.Name = columnName Then
         ' column alias from table
         columnAlias = objColumn.Alias
%>
      ddlSort_<%=TrimSpaces(tableAlias)%>.Items.Add(new ListItem(\"<%=columnAlias%>\",\"<%=TrimSpaces(columnName)%>\"))<%
      End If
   Next
Next
%>
End Sub

I then sort the datagrid using:

Code: Select all

Sub Sort_<%=TrimSpaces(tableAlias)%>(sender As Object, e As EventArgs)
      SetupGrid(ddlSort_<%=TrimSpaces(tableAlias)%>.SelectedItem.Value, ddlSort_<%=TrimSpaces(tableAlias)%>_dir.SelectedItem.Value)
End Sub

The bit that does the sorting and binds the datagrid is:

Code: Select all

Private Sub SetupGrid(Optional sortBy As String = \"\", Optional sortDir As String = \"ASC\", Optional recordsToShow As Integer = 0)
Dim SetupGrid_<%=TrimSpaces(tableAlias)%> As New <%=TrimSpaces(tableAlias)%>
      ' sort table
      If sortBy <> \"\" Then
         SetupGrid_<%=TrimSpaces(tableAlias)%>.Query.AddOrderBy(sortBy, IIF(sortDir=\"ASC\",WhereParameter.Dir.ASC,WhereParameter.Dir.DESC))
      End If

The problem occurs because I pass a string on as the parameter for sortBy, because that is all that can be passed on.

[mike.griffin]

Then it would seem to me that this would work for you:

Code: Select all

emps.Query.AddOrderBy(emps.GetColumnName(tableAlias), WhereParameter.Dir.DESC);

I could add this fairly quickly ...

[sbc]

So how would I get my Sub SetupGrid to work (where the sortBy parameter indicates what I wish to sort by - sortBy is the trimmed alias). Something that ended up like:

Code: Select all

emps.Query.AddOrderBy(emps.GetColumnName(sortBy), WhereParameter.Dir.DESC)

.

[mike.griffin]

emps.GetColumnName will simply translate the \"Alias\" into the \"Physical\" name, that's all it does, thus you can allow the user to choose from a list of Alias's and then behind the scenes translate them into ColumnNames.

I'm not sure why you're trimming the alias though, an Alias cannot have blanks in it as you cannot have Property names with spaces in them and we use the Alias for the PropertyName.

[sbc]

sortBy is a string that is passed on, i.e.

Code: Select all

emps.Query.AddOrderBy(emps.GetColumnName(\"PersonFirstName\"), WhereParameter.Dir.DESC)

WOuld that work?

[mike.griffin]

Yes, exactly, look at one of your generated files, at the ColumnName or PropertyName regions, you'll see that their just strings, they keep you from hard coding them all over the place, and making typo's which would yield runtime instead of compile time errors, accordingly if you drop a column you'll get a an error when you compile prompting you to change your code as needed.

[mike.griffin]

BTW, did those templates I sent you work?

[sbc]

I get a compilation error BC30451: Name 'MyTableAlias' is not declared:

Code: Select all

Line 206:            ht = new Hashtable
Line 207:            
Line 208:            ht(ColumnIdentity) = MyTableAlias.PropertyNames.ColumnIdentity

Shouldn't it be ht(\"ColumnIdentity\") as well ?

[mike.griffin]

Hmm, I tested those quite extensively last night, no, the :

ht(ColumnIdentity) = MyTableAlias.PropertyNames.ColumnIdentity

is the correct syntax, the error you are getting Name 'MyTableAlias' is not declared: MyTableAlias is supposed to be the name of the Generated class, isn't it?

[sbc]

The generated class is

Code: Select all

Public MustInherit Class _MyTableAlias

Can \"Me\" be used? i.e. ht(ColumnIdentity) = Me.PropertyNames.ColumnIdentity

[mike.griffin]

Check your mail, when you say the work I'll put them in the next build.

[sbc]

Line 464 of the template seemed to cause an error (BC30456: 'IdentityAlias' is not a member of '_TableAlias.Parameters:

Code: Select all

output.write \"      p = cmd.Parameters(Parameters.\" & objColumn.Name & \".ParameterName)\"  & vbCrLf

output to:

Code: Select all

      p = cmd.Parameters(Parameters.id.ParameterName)

Shouldn't it be

Code: Select all

      p = cmd.Parameters(Parameters.IdentityAlias.ParameterName)

The functions that use this code are GetInsertCommand and GetUpdateCommand. GetDeleteCommand and CreateParameters use the column alias, shouldn't they all do?.

[mike.griffin]

Man, I'm so sorry, let me resend them tonight, I'm in too much of a hurry, I changed the parameters section by mistake, forget those templates

Actually, the template I sent you is correct I think, are you hand merging these into your template or running the ones I sent you

[sbc]

Can't use them till Tuesday anyway, so no rush

[mike.griffin]

You seemed to have switched topics, I didn't touch the paramters logic?