Add query data to MenuItem 6

[chilly442]

I have a menu that I define as follows:

<asp:Menu ID="Menu1" runat="server" BackColor="Silver" DynamicHorizontalOffset="2" Font-Names="Verdana" ForeColor="Black" Font-Size="7pt" StaticSubMenuIndent="10px" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px">
<Items>
<asp:MenuItem Text="Main Menu" Value="Main Menu">
<asp:MenuItem Text="Event Entry"/>
<asp:MenuItem Text="Event Reporting"/>
</asp:MenuItem>
</Items>
<StaticSelectedStyle BackColor="White" />
<StaticMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" />
<DynamicMenuStyle BackColor="White" />
<DynamicSelectedStyle BackColor="White" />
<DynamicMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" />
<DynamicHoverStyle BackColor="Black" Font-Bold="False" ForeColor="White" />
<StaticHoverStyle BackColor="Black" Font-Bold="False" ForeColor="White" />
</asp:Menu>
For the values in the menu, I want to query a Access DB and return the values to the MenuItem. Is this even possible?
Any help gets you a star!
Chilly442

 

[jmeckley]

i would build the menu manually using repeaters and lists instead of messing with the menu control. they are very picky about what all you can do with them.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chilly442]

Do you have an example? I have searched around and can't come up with anything that works.

Thanks!

 

[jbenson001]

There are examples on line on how to build a menu usind the db and the menu control

 

[jmeckley]

depends what you want it to look like and how you query the data. the most common examples of creating menus use an unordered list.

once you get the html/js/css in place (jquery has menuing options i think). then you need to get the data, but that can be done independently of the layout.

if the menu is small enough you can get all the menu items from the database and build the hierarchy in memory. then bind the hierarchy to the repeaters.

using recursive web user controls will build the menu out nicely

<repeater>
  <header template><ul></header template>
  <footer template></ul></footer template>
  <item template>
    <li>
      <a href="<%#((MenuItem)DataItem).url%>"><%#((MenuItem)DataItem).text%></a>
      <my:menupart items="<%#((MenuItem)DataItem).sub_menu%>" id="blah" runat="server" />
    </li>
  </item template>
<repeater>

public IEnumerable<MenuItem> items 
{
   get { return repeater.DataSource; }
   set { repeater.DataSource = value; }
}

class MenuItem
{
   public MenuItem(string url, string text, params MenuItem[] sub_menu)
   {
      this.url = url;
      this.text = text;
      this.sub_menu = sub_menu;
   }

   public string url {get; private set; }
   public string text {get; private set; }
   public IEnumerable<MenuItem> sub_menu {get; private set; }

in your master page reference the menu web user control you just created.
on page load set the items property of the menu web user control and call DataBind();

all that's left is mapping the database records to menu items. this is all off the top of my head, and I haven't worked with webforms for a while now, so i'm sure there are quirks with the code, but it's a start.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JimApps7]

Another way that works great is to utilize the code below to read an Access Query, and then ouput to an xml file directly readable by the Menu control. The query is three tables nested within one another (for a 3 level menu). The sql for the query is listed below as well.

Sub XMLOUTPUT_VerticalMenu()
'Apps7
'Output for XML file for Vertical Menu 2009 0318 JS

Dim objConn, strConnect, strSQL, tb, mdbFile, xmlFile, objWrite

Dim objFSO As New FileSystemObject

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset



'=== filename variables
mdbFile = ("c:\latino landscape\App_Data\LLdatabase.mdb")
xmlFile = ("c:\latino landscape\VMenu.xml")

'=== tab character for xml file
tb = Chr(9)


'=== connect to database MENU LOOP
'rs.Open "qryCalendarRss", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
strSQL = "SELECT * FROM qryVerticalMenuRss"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


'=== open/create xml file
If Not objFSO.FileExists(xmlFile) Then objFSO.CreateTextFile (xmlFile)
Set objWrite = objFSO.OpenTextFile(xmlFile, 2)

'=== open the xml file
objWrite.WriteLine ("<?xml version=""1.0"" encoding=""utf-8"" ?>")
objWrite.WriteLine ("<Home>")



'=== loop through results
If rs.RecordCount > 0 Then
rs.MoveFirst

a7count = rs.RecordCount

MenuA7 = "none"
SubMenuA7 = "none"
SubSubMenuA7 = "none"

' First record--------------

objWrite.WriteLine ("<Menu Text =""" & rs("Menu") & """ URL =""" & rs("tblmenu.URL") & """>")
MenuA7 = rs!Menu

If rs!SubMenu <> "" Then
objWrite.WriteLine (tb & "<SubMenu Text =""" & rs("SubMenu") & """ URL =""" & rs("tblSubmenu.URL") & """>")
SubMenuA7 = rs!SubMenu
End If

If rs!SubSubMenu <> "" Then
objWrite.WriteLine (tb & tb & "<SubMenu Text =""" & rs("SubSubMenu") & """ URL =""" & rs("tblSubSubmenu.URL") & """></SubMenu>")
SubSubMenuA7 = rs!SubSubMenu

'For closing submenu - Menu only coming up

rs.MoveNext
If rs!Menu <> MenuA7 And Not rs.EOF Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious


'For closing submenu - SubMenu coming up
rs.MoveNext
If rs!Menu = MenuA7 And rs!SubMenu <> SubMenuA7 Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious


End If


If rs!SubMenu <> "" Or rs!SubSubMenu <> "" Then objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")

'--------

rs.MoveNext

a7flag = 0

Do While Not rs.EOF

a7flag = a7flag + 1

' Subsequent records--------------



' If only one ------------Menu
If rs!Menu = MenuA7 Then

' If ----SubMenu
If rs!SubMenu = SubMenuA7 Then

' But first check for SubSub ------------SubMenu
If rs!SubSubMenu <> "" Then
objWrite.WriteLine (tb & tb & "<SubMenu Text =""" & rs("SubSubMenu") & """ URL =""" & rs("tblSubSubmenu.URL") & """></SubMenu>")

SubSubMenuA7 = rs!SubSubMenu

'For closing submenu - Menu only coming up
If a7flag <= a7count - 2 Then 'can't move if it is eof
rs.MoveNext
If rs!Menu <> MenuA7 And Not rs.EOF Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious
End If

'For closing submenu - SubMenu coming up
If a7flag <= a7count - 2 Then 'can't move if it is eof
rs.MoveNext
If rs!Menu = MenuA7 And rs!SubMenu <> SubMenuA7 Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious
End If


'If sub sub is very last record.
If a7flag = a7count - 1 Then objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")

End If

Else 'New submenu line

'SubMenu
objWrite.WriteLine (tb & "<SubMenu Text =""" & rs("SubMenu") & """ URL =""" & rs("tblSubmenu.URL") & """>")

SubMenuA7 = rs!SubMenu

' Check for SubSub ------------SubMenu
If rs!SubSubMenu <> "" Then
objWrite.WriteLine (tb & tb & "<SubMenu Text =""" & rs("SubSubMenu") & """ URL =""" & rs("tblSubSubmenu.URL") & """></SubMenu>")

SubSubMenuA7 = rs!SubSubMenu

'For closing submenu - Menu only coming up
If a7flag <= a7count - 2 Then 'can't move if it is eof
rs.MoveNext
If rs!Menu <> MenuA7 And Not rs.EOF Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious
End If

'For closing submenu - SubMenu coming up
If a7flag <= a7count - 2 Then 'can't move if it is eof
rs.MoveNext
If rs!Menu = MenuA7 And rs!SubMenu <> SubMenuA7 Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious
End If

End If

'If sub is very last record.
If a7flag = a7count - 1 Then objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")

End If


Else
'Menu

objWrite.WriteLine ("</Menu>")
objWrite.WriteLine ("")
objWrite.WriteLine ("<Menu Text =""" & rs("Menu") & """ URL =""" & rs("tblmenu.URL") & """>")
MenuA7 = rs!Menu

' If Menu and Sub ------------SubMenu -- this is for the record with a Menu and One Sub and possibly One Sub Sub

If rs!SubMenu <> "" Then

'SubMenu -- write the first submenu
objWrite.WriteLine (tb & "<SubMenu Text =""" & rs("SubMenu") & """ URL =""" & rs("tblSubmenu.URL") & """>")

SubMenuA7 = rs!SubMenu




' Check for SubSub ------------SubMenu
If rs!SubSubMenu <> "" Then
objWrite.WriteLine (tb & tb & "<SubMenu Text =""" & rs("SubSubMenu") & """ URL =""" & rs("tblSubSubmenu.URL") & """></SubMenu>")

SubSubMenuA7 = rs!SubSubMenu

'If sub sub is very last record.
If a7flag = a7count - 1 Then objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")

Else
'If sub is very last record.
If a7flag = a7count - 1 Then objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")

End If

'For closing submenu - Menu only coming up
If a7flag <= a7count - 2 Then 'can't move if it is eof
rs.MoveNext
If rs!Menu <> MenuA7 And Not rs.EOF Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious
End If

'For closing submenu - SubMenu coming up
If a7flag <= a7count - 2 Then 'can't move if it is eof
rs.MoveNext
If rs!Menu = MenuA7 And rs!SubMenu <> SubMenuA7 Then
objWrite.WriteLine (tb & tb & tb & tb & tb & tb & "</SubMenu>")
End If
rs.MovePrevious
End If



End If




End If


rs.MoveNext



Loop
End If

objWrite.WriteLine ("</Menu>")

'=== finish xml file
objWrite.WriteLine ("")
objWrite.WriteLine ("</Home>")

'objWrite.Close()
MsgBox "Finished."
End Sub


SELECT tblMenu.Menu, tblMenu.URL, tblSubMenu.SubMenu, tblSubMenu.URL, tblSubSubMenu.SubSubMenu, tblSubSubMenu.URL, tblMenu.MenuOrder, tblSubMenu.Submenuorder, tblSubSubMenu.Subsubmenuorder
FROM (tblMenu LEFT JOIN tblSubMenu ON tblMenu.Menu=tblSubMenu.Menu) LEFT JOIN tblSubSubMenu ON tblSubMenu.SubMenu=tblSubSubMenu.SubMenu
ORDER BY MenuOrder, SubMenuOrder, SubsubMenuOrder;

 

[JimApps7]

Sorry -- to clarify, this DOES output to an xml file -- it is all automatic. You would set your menu datasource to the xml file. Note in particular the " XPath="/Home/Menu" instruction for your datasource. Happy programming.


<asp:Menu id="Menu1" runat="server" z-index="8" BorderStyle="None" DynamicHorizontalOffset="2" Font-Names="Belgium" Font-Size="Small" Font-Underline="False" ForeColor="#284E98" Height="9px" StaticEnableDefaultPopOutImage="False" StaticSubMenuIndent="10px" Width="157px" DataSourceID="XmlDataSource2">
<StaticSelectedStyle BackColor="#507CD1" Height="9px" />
<StaticMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" CssClass="style13" />
<DynamicHoverStyle BackColor="Silver" ForeColor="White" />
<DynamicMenuStyle BackColor="#FF6600" />
<DynamicItemTemplate>
<%# Eval("Text") %>
</DynamicItemTemplate>
<DynamicSelectedStyle BackColor="Silver" Height="9px" ForeColor="White" />
<DynamicMenuItemStyle BackColor="#FF6600" HorizontalPadding="5px" VerticalPadding="2px" Height="9px" BorderStyle="None" CssClass="style13" />
<DataBindings>
<asp:menuitembinding DataMember="Menu" NavigateUrlField="url" TextField="text" />
<asp:menuitembinding DataMember="SubMenu" NavigateUrlField="url" TextField="Text" />
</DataBindings>
<StaticHoverStyle BackColor="#FF9966" Font-Bold="True" ForeColor="White" />

<StaticItemTemplate>
<%# Eval("Text") %>
</StaticItemTemplate>
</asp:Menu>
<asp:XmlDataSource ID="XmlDataSource2" runat="server" DataFile="VMenu.xml" XPath="/Home/Menu">
</asp:XmlDataSource>

 

[jmeckley]

I like the idea of extracting the data from a relational database, transforming to hierarchical data (xml in this case) and consuming the data for display purposes.

however there are some points of concern I will mention
1. it't not using .net managed objects, DAO instead of ADO.Net and FileSysObject instead of System.IO.[anything]
2. it's very procedural, but that can be refactored to a more OOP solution
3. in the end you are still using a datasource control for xml, which is bad juju. you cannot debug markup code.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chilly442]

Thanks for the help. I will give it a try and see what comes of it.

Chilly442

 

[Rhys666]

You can also store Xml as xml in Sql Server these days, and when indexed properly it's pretty simple to update and modify directly with t-sql, within stored procedures obviously being the preferred option

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[jmeckley]

within stored procedures obviously being the preferred option

opinions vary, I have not written a stored proc in years, since discovering the power of ORM frameworks.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ca8msm]

Which ORM framework do you prefer Jason? I've not been personally convinced by any of the ones I've tried (Nhibernate and LLBLGen Pro) when it comes to performance i.e. improving execution plans. I'm also not very keen on the security side of things which involves opening up table access to a user as opposed to locking them down to specific sp usage.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[jmeckley]

I have used both LLBL and NH. I prefer NH for cleaner SoC and easier unit testing. LLBL has the advantage of strongly typed queries, however the inheritance hierarchy of LLBL classes pollutes the domain. And there is still a tight coupling between the database and the enities.

I also prefer how NH handles the concept of a UoW over LLBL.

Performance issues with the generated sql statements are usually caused by 2 issues. Poor sql structure on my part (usually select n+1), or table indexing. sql can be fixed by adjusting the Criteria/HQL of NH, or by applying indexing to the tables in question. I'm sure I could squeeze a few more microseconds out my using a proc, but the maintenance that requires outweighs the benefits. In a worst case scenario i can use NH to execute the sql directly. fortunately I have only needed that is one instance. Because I don't want to refactor a 1000+ line proc into code. I'll save that for another day

By using an ORM I find it's easier to maintain the system as a whole because I can manage schema changes with NH and since NH generates all my sql I only have to update the criteria instead of versioning stored procs and db scripts as well as the code.

I have managed all my authorization though the code. If my end users can access the database directly then I have bigger issues than whether they have table vs. proc access. I have not worked on system in the medical or financial fields, so I'm not familiar with these high levels of security requirements. Most times I assign the user to datareader/datawriter roles and call it a day.

I prefer this approach because I can write an automated unit test to verify a section of code is working, without requiring external resources (web services, database, files). There will come a time when that is needed for integration tests, but those are usually smoke tests to ensure all the components are wired together correctly.

What really attracts me to NH though. I can focus on the core functionality of the system and push off database design until the last reasonable moment. you could say the same without an ORM, but I find it makes this much easier.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ca8msm]

Oh well, I guess that's a completely different viewpoint to mine as I pretty much do things the other way around and what you see as plus points I see as negatives (well, maybe not negative but I don't see them as being as useful as what I currently have).

I start with my database design rather than leave it until last. My database is usually 90-100% usable from SMSS before I've even contemplated starting coding. The structure is defined to meet the functionality needed from the application, then the application is built around that, and then I revisit the db to load test and fine tune it. From doing this, I find that I can be pretty confident that the application will scale well when it is hit by a lot of users. I don't think I'd be as confident if I was relying on dynamically generated sql.

Some of the plus points you listed, I actually find a hindrance, for example:

the maintenance that requires outweighs the benefits

I actually find that the maintenance option of using an ORM means quite the opposite. From a performance point of view, our DBA cannot maintain something that is generated from an application. They can't add performance hints (for example, one they used recently was to fix a query that was using a index scan instead of a seek). A performance boost like that on the amount of data involved is very significant, and I don't see many ways of easily fixing and maintaining something like this had I used an ORM.

By using an ORM I find it's easier to maintain the system as a whole because I can manage schema changes with NH and since NH generates all my sql I only have to update the criteria instead of versioning stored procs and db scripts as well as the code.

I also struggle on this point too. If I had to push a new version of the application out each time a relatively simple change was made, I'd end up with very high version numbers . I find that a simple data (not business) logic change is much easier to manage if it is abstracted out of the application.

I have managed all my authorization though the code
...
Most times I assign the user to datareader/datawriter roles and call it a day

Again, this is something I would have trouble with in our security audits. I would have to make sure that any user that accessed the database, regardless of how they obtained access, could specifically only access data that was theirs. By assigning someone a datareader/datawriter role, all it would require is someone to obtain this username/password combination and that's the data integrity completely gone. They could obtain or modify any data, and that is just too big of a risk to introduce.

At a push, and I may be entirely wrong here as it's just a guess, I'd say that all DBA's who do this for a living would probably agree that they would prefer my approach over using an ORM as it gives them the option to help optimise a database. From a development point of view, I do see the benefits it can offer, especially in terms of time and simplicity, I just think they lack that next level of performance/maintenance options that are needed in most of my development work.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[jmeckley]

Again, this is something I would have trouble with in our security audits. I would have to make sure that any user that accessed the database, regardless of how they obtained access, could specifically only access data that was theirs. By assigning someone a datareader/datawriter role, all it would require is someone to obtain this username/password combination and that's the data integrity completely gone. They could obtain or modify any data, and that is just too big of a risk to introduce.

if your system requires this level of auditing then yes, it's needed. however if someone obtains the user/pwd to a system you have bigger problems with social engineering.

At a push, and I may be entirely wrong here as it's just a guess, I'd say that all DBA's who do this for a living would probably agree that they would prefer my approach over using an ORM as it gives them the option to help optimise a database.

this is certainly true of some DBAs. they see themselves as keepers of the data. nothing goes in or out without their approval. Other DBAs, (and my guts says far fewer) are there to provide developers with an efficient and available database. Instead of being gatekeepers, they are there to support the rest of the team.

One place where procs can be useful is reporting. this is an odd-ball in DDD and ORM's as with reports you want to get in and out of the database immediately and display aggregations. And you want to do this as fast as possible. But I can do the same with HQL/projections and still get acceptable results.

Another area where ORMs are not the tool of choice is ETL. for that I would use Rhino.ETL. I have hacked things together in DTS, but that was a pain and little to no testability.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ca8msm]

Reporting and ETL are a whole different ball game for us. We have a data warehouse that gets populated and all aggregated data is viewed from the data cubes rather than directly from the database. We use a mixture of SSIS, SSAS and SSRR for this. But I've dragged this thread off topic enough to get into discussing those!

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[jmeckley]

and that makes perfect sense My systems are not large enough to justify data warehousing. I've read about this techinque. It works well with command query separation. ok, not more highjacking this thread thanks for the discussion.

Jason Meckley
Programmer
Specialty Bakers, Inc.