Awesome Dynamic SQL generation….

By patrickwellink
March 15, 2005


Played around a littlebit with the dOOdad architecture of MyGeneration. Besides a lot of nice features there is also the the dynamic query feature…. Let me show what I mean…..

‘ See if it is in cache
Mymenu = Cache.Get(“DefaultMenu”)
If Mymenu Is Nothing Then
‘get the default Menu
Dim MyMenuID As New MenuID
MyMenuID.Where.MenuDescription.Value = “DefaultMenu”
‘Now get the MenuEntries for the menu !
Mymenu = New MenuData
Mymenu.Where.MenuID.Value = MyMenuID.ID
Mymenu.Where.MenuID.Operator = MyGeneration.dOOdads.WhereParameter.Operand.Equal
‘ Only those rows that we are Authenticated to see
Mymenu.Where.AuthenticationLevel.Value = Session(“AuthenticationLevel”)

   Mymenu.Where.AuthenticationLevel.Operator = MyGeneration.dOOdads.WhereParameter.Operand.LessThanOrEqual
   Mymenu.Query.AddOrderBy(Mymenu.ColumnNames.MenuOrder, ,myGeneration.dOOdads.WhereParameter.Dir.ASC)
‘Cache this menu.
Cache.Insert(“DefaultMenu”, Mymenu)
End If
‘Bind to the grid
MenuGrid.DataSource = Mymenu.DefaultView

In the blue piece of code we set some where parameters and add a sortorder. Everything resulted in the following query beeing executed :

SELECT * FROM [MenuData] WHERE [MenuID] = @MenuID1 AND [AuthenticationLevel] <= @AuthenticationLevel2 ORDER BY [MenuOrder] ASC

And this is a really nice query, nothing wrong with that !. I just was surprised how easy it was !.




Comments: 1

  1. Mike Griffin says:

    That is probably the feature folks love best about dOOdads. Equal is the default operator so you really don’t even have to set that. To be honest I pull off about 80% – 90% of an average project with just that simple approach.

    However, there are other ways to load dOOdads via custom SQL. Both the LoadFromSql() and the LoadFromRawSql() methods can be invoked if the dynamic query mechanism won’t do what you want.

    More on that topic:

Comments are closed.

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta