Databases
StackOverflow2013
Postsdbo
POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
    primarykey
    data
    text
    <p>I have a large result set coming from a pretty complex SQL query. Among the values are a <code>string</code> which represents a location (that will later help me determine the page location that the value came from), an <code>int</code> which is a priority number calculated for each row based on other values from the row, and another <code>string</code> which contains a value I must remember for display later.</p> <p>The problem is that the sql query is so complex (it has UNIONS, JOINS, and complex calculations with aliases) that I can't logically fit anything else into it without messing with the way it works.</p> <p>Suffice it to say, though, after the query is done and the calculations performed, I need something that perhaps aggregate functions might solve, but that IS NOT an option, as all the columns do not come from other aggregate functions.</p> <p>I have been wracking my brain for days now as to how I can iterate through the results, store a pair of values in a list (or two separate lists tied together somehow) where one value is the sum of all the priority values for each location and the other value is a distinct location value (i.e., as the results are looped through, it will not create another list item with the same location value that has been used before, HOWEVER, it does still need the sum of all of the other priority values from locations that ARE identical). Also, the results need to be ordered by priority in Descending order (hence the problem with using two lists).</p> <p><strong>EXAMPLE:</strong></p> <p><strong>EDIT:</strong> I forgot, the preserved value should be the value from the row with the highest priority from the sql query.</p> <p>If I had the following results:</p> <pre><code> location priority value -------------------------------------------------------------------------------- page1 1 some text! page2 3 more text! page2 4 even more text! page3 3 text again page3 1 text page3 1 still more text! page4 6 text </code></pre> <p>If I was able to do what I wanted I would be able to achieve something like this after iteration (and in this order):</p> <pre><code> location priority value -------------------------------------------------------------------------------- page2 7 even more text! page4 6 text page3 5 text again page1 1 some text! </code></pre> <p>I have done research after research after research but absolutely nothing really even gets close to solving this dilemma.</p> <p>Is what I'm asking too tough for even the powerful C# language?</p> <p><strong>THINGS I HAVE CONSIDERED:</strong></p> <p><em>Looping through the sql results and checking each location for repeats, adding together all priority values as I go, and storing these two plus value in two or three separate lists.</em></p> <p><strong>Why I still need help</strong></p> <p>I can't use a foreach because the logic didn't pan out, and I can't use a <code>for</code> loop because I can't access an IEnumerable (or whatever type it is that stores what's returned from <code>Database.Open.Query()</code> by index. (this makes sense, of course). Also, I need to sort on priority, but can't get one list out of sync with the others.</p> <hr> <p><em>Using LINQ to select and store what I need</em></p> <p><strong>Why I still need help</strong></p> <p>I don't know LINQ (at all!) mainly because I don't understand lambda expressions (no matter HOW MUCH I read up about it).</p> <hr> <p><em>Using an instantiated class to store the name/value pairs</em></p> <p><strong>Why I still need help</strong></p> <p>Not only do I expect sorting on this sort of thing to be impossible, and while I do now how to use <code>.cs</code> files in my C#.net webpages with WebMatrix environment, I have mainly only ever used static classes and would also need a little refresher course on constructors and how to set this up appropriately.</p> <hr> <p><em>Somehow fitting this functionality into the already sizeable and complex SQL query</em></p> <p><strong>Why I still need help</strong></p> <p>While this is probably where I would ideally like this functionality to be, I stress again that this IS NOT AN OPTION. I have tried using aggregate functions, but only get an error saying how not all the other columns come from aggregate functions.</p> <hr> <p><em>Making another query based on values from the first query's result set</em></p> <p><strong>Why I still need help</strong></p> <p>I can't select distinct results based on only one column (i.e., location) alone.</p> <hr> <p><em>Assuming I could get the loop logic correct, storing the values in a 3 dimensional array</em></p> <p><strong>Why I still need help</strong></p> <p>I can't declare the array, because I do not know all of its dimensions before I need to use it.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGert Arnold
    1. USVoidKing
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
      1. USMagus
      1. VTFavorite
    1. COAre you storing the results of this query in an ADO.NET dataset, Entity Framework entity, or what?
      singulars
      1. POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
      1. USCortright
    2. CO@Cortright it would be like this `var results = Database.Open("databaseName").Query(queryString, argumentArray)` According to MSDN the data type is: `System.Collections.Generic.IEnumerable<Object>`
      singulars
      1. POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
      1. USVoidKing
    3. COI knew I've seen it before. [CabalTherapy](http://vrtstore.com/pic/JUD/Cabal%20Therapy.full.jpg).. Too bad it does not have any flavor text. That reminds me of an [EssenceLeak](http://8e8460c4912582c4e519-11fcbfd88ed5b90cfb46edba899033c9.r65.cf1.rackcdn.com/sales/cardscans/MTG/INV/en/nonfoil/EssenceLeak.jpg) with its inspiring footnote. I think I still have it somewhere in my wallet heh.
      singulars
      1. POIs there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?
      1. USquetzalcoatl
 

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