Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ideally, you want to retrieve all the relevant records at once into an in-memory collection, and then read from that memory collection during your recursion. The paradigm of memoization/caching of records will make this simple, as well as separating your data-access logic from business logic.</p> <p>First, create a <code>static</code> method for retrieving data that grabs data from the database the first time, but makes use of its in-memory collection on subsequent calls. I'm assuming that since you are passing <code>fTableName</code>, this method may or may not be used with multiple tables, so the cache is capable of storing multiple tables at once (using a <code>Dictionary</code> keyed on the table name), and treats requests to different tables separately. (WARNING: untested code, but should give you the idea):</p> <pre><code>private static Dictionary&lt;string, DataTable&gt; _menuCache = null; public static DataRow[] GetMenuLayer(string fTableName, string fID, string fClause) { if (_menuCache == null) _menuCache = new Dictionary&lt;string, DataTable&gt;(); if (!_menuCache.ContainsKey(fTableName)) { // retrieve all records from the database the first time SQLCommand = "SELECT * FROM " + fTableName; ... _menuCache[fTableName] = result; } // query appropriate records from the cache var dt = _menuCache[fTableName]; return dt.Select("MenuParent = " + fID + " AND Visible=1 AND " + fClause); } </code></pre> <p>Since this method is <code>static</code>, its data is saved during the entire request/response cycle, but <em>not</em> between responses. So this will reduce generating the menu to a single database call, while each time the page is loaded, the data is still loaded new. If your menu data is relatively static, you could go to the next level using the .NET cache with a timeout that will store the database results, for say 30 minutes at a time before refreshing. Then the page could be loaded many times with only a single database call.</p> <p>The code in your <code>GenerateNestedMenus</code> that retrieves data from the database would instead make a call to <code>GetMenuLayer</code> with the appropriate parameters. This method then is responsible for retrieving the data in whatever method it can (the former method doesn't need to care how it gets there). Behind the scenes, the first time a table <code>fTableName</code> is requested, the <em>entire</em> table is downloaded into a local memory cache. Then that memory cache is queried in subsequent calls according to the parameters to return the result rows that can be iterated (this assumes that your dynamic filter logic <code>fClause</code> is not too complicated, because <code>dt.Select(</code> only understands a very small subset of basic <code>T-SQL</code> logic):</p> <pre><code>public string GenerateNestedMenus(string fTableName, string fID, string fClause) { DataRow[] dt = GetMenuLayer(fTableName, fID, fClause); int i = 0; string temp = null; for (i = 0; i &lt;= dt.Length - 1; i++) { if (Convert.ToInt32(ChildCounter(fTableName, dt[i]["id"])) &gt; 0) { temp = "&lt;li&gt;" + Constants.vbCrLf + "&lt;a href='#'&gt;" + Strings.Trim(dt[i]["MenuName"]) + "&lt;/a&gt;" + Constants.vbCrLf + Constants.vbTab + "&lt;ul&gt; "; _temp += temp; GenerateNestedMenus2("menus", dt[i]["id"], fClause); _temp += Constants.vbTab + "&lt;/ul&gt;" + Constants.vbCrLf + Constants.vbTab + "&lt;/li&gt;" + Constants.vbCrLf; } else { //For rows they have not child temp = Constants.vbTab + "&lt;li&gt;&lt;a href='#'&gt;" + Strings.Trim(dt[i]["MenuName"]) + "&lt;/a&gt;" + "&lt;/li&gt;" + Constants.vbCrLf; _temp += temp; GenerateNestedMenus2("menus", dt[i]["id"], fClause); } } return _temp; } </code></pre> <p>This is a rough idea of the approach to a solution, which may require some tweaking and experimenting to get everything working.</p>
    singulars
    1. This table or related slice is empty.
    1. POOptimized solution to retrieve data from DB instead of recursive function
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMaysam
    1. USmellamokb
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOptimized solution to retrieve data from DB instead of recursive function
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COwould you please explain `var dr in dt.Rows`? what is `var`?
      singulars
      1. PO
      1. USMaysam
    2. CO`var` is [**implicit variable declaration**](http://msdn.microsoft.com/en-us/library/bb384061.aspx). If the type is known at compile-time, you can use `var` instead of explicitly declaring the type. The type is actually `DataRow`, so it's the same as saying `foreach (DataRow dr in dt.Rows) {`.
      singulars
      1. PO
      1. USmellamokb
    3. COHow can I access table columns? `dr("ID")` returns `'Rows' is not a member of 'System.Array'.`
      singulars
      1. PO
      1. USMaysam
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload