AddOrderByAlias

All your dOOdad needs ...

AddOrderByAlias

Postby sbc on Wed Apr 21, 2004 5:53 pm

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)
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Wed Apr 21, 2004 6:07 pm

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 ...
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby sbc on Wed Apr 21, 2004 6:24 pm

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.
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Wed Apr 21, 2004 6:28 pm

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 ...
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby sbc on Wed Apr 21, 2004 6:32 pm

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)
.
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Wed Apr 21, 2004 6:35 pm

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.
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby sbc on Wed Apr 21, 2004 6:38 pm

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?
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Wed Apr 21, 2004 6:55 pm

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.
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby mike.griffin on Thu Apr 22, 2004 2:14 pm

BTW, did those templates I sent you work?
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby sbc on Thu Apr 22, 2004 2:55 pm

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 ?
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Thu Apr 22, 2004 3:09 pm

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?
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby sbc on Thu Apr 22, 2004 3:17 pm

The generated class is
Code: Select all
Public MustInherit Class _MyTableAlias

Can \"Me\" be used? i.e. ht(ColumnIdentity) = Me.PropertyNames.ColumnIdentity
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Thu Apr 22, 2004 3:39 pm

:oops: Check your mail, when you say the work I'll put them in the next build.
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby sbc on Thu Apr 22, 2004 4:34 pm

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?.
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Thu Apr 22, 2004 4:42 pm

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
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby sbc on Thu Apr 22, 2004 4:45 pm

Can't use them till Tuesday anyway, so no rush
User avatar
sbc
Expert
 
Posts: 446
Joined: Sat Apr 03, 2004 6:10 am
Location: North East Lincolnshire, England

Postby mike.griffin on Thu Apr 22, 2004 4:45 pm

You seemed to have switched topics, I didn't touch the paramters logic?
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN


Return to dOOdads - MyGeneration's .NET Architecture

Who is online

Users browsing this forum: No registered users and 1 guest

cron