Is there any way to loop through my sql results and store certain name/value pairs elsewhere in C#? -

i have large result set coming pretty complex sql query. among values string represents location (that later me determine page location value came from), int priority number calculated each row based on other values row, , string contains value must remember display later.

the problem sql query complex (it has unions, joins, , complex calculations aliases) can't logically fit else without messing way works.

suffice say, though, after query done , calculations performed, need perhaps aggregate functions might solve, not option, columns not come other aggregate functions.

i have been wracking brain days how can iterate through results, store pair of values in list (or 2 separate lists tied somehow) 1 value sum of priority values each location , other value distinct location value (i.e., results looped through, not create list item same location value has been used before, however, still need sum of of other priority values locations identical). also, results need ordered priority in descending order (hence problem using 2 lists).

example:

edit: forgot, preserved value should value row highest priority sql query.

if had following results:

  location                      priority                               value    --------------------------------------------------------------------------------    page1                           1                                  text!    page2                           3                                  more text!    page2                           4                               more text!    page3                           3                                  text again    page3                           1                                     text    page3                           1                              still more text!    page4                           6                                     text 

if able wanted able achieve after iteration (and in order):

  location                      priority                               value    --------------------------------------------------------------------------------    page2                           7                               more text!    page4                           6                                    text    page3                           5                                  text again    page1                           1                                  text! 

i have done research after research after research absolutely nothing gets close solving dilemma.

is i'm asking tough powerful c# language?

things have considered:

looping through sql results , checking each location repeats, adding priority values go, , storing these 2 plus value in 2 or 3 separate lists.

why still need help

i can't use foreach because logic didn't pan out, , can't use for loop because can't access ienumerable (or whatever type stores what's returned database.open.query() index. (this makes sense, of course). also, need sort on priority, can't 1 list out of sync others.

---

using linq select , store need

why still need help

i don't know linq (at all!) because don't understand lambda expressions (no matter how read it).

---

using instantiated class store name/value pairs

why still need help

not expect sorting on sort of thing impossible, , while how use .cs files in c#.net webpages webmatrix environment, have ever used static classes , need little refresher course on constructors , how set appropriately.

---

somehow fitting functionality sizeable , complex sql query

why still need help

while ideally functionality be, stress again not option. have tried using aggregate functions, error saying how not other columns come aggregate functions.

---

making query based on values first query's result set

why still need help

i can't select distinct results based on 1 column (i.e., location) alone.

---

assuming loop logic correct, storing values in 3 dimensional array

why still need help

i can't declare array, because not know of dimensions before need use it.

your post has amazed me in number of ways saying 'mostly using static classes' , 'expecting instantiate class/object impossible'.. strange things say. can respond in quote charles babbage:

i not able rightly apprehend kind of confusion of ideas provoke such question.

anyways.. find lambdas hard, let's trace problem in classic 'manual' way.

let's assume have list of rows contains locations , priorities.

list<datarow> rows = .... ; // datatable, sqldatareader, whatever 

you need:

• list of unique locations
• a "list" of locations paired summed priorites

let's start first objective.

to gather list of unique 'values', hashset perfect:

hashset<string> locations = new hashset<string>(); foreach(var row in rows)     locations.add( (string)rows["location"] ); 

well, , that's all. after that, locations hashset remember unique locations. "add" not result in duplicate elements. hashset checks , "uniquifies" values put inside it. small tricky thing hashset not have [index] operator. you'll have enumerate hashset values:

foreach(string loc in locations) {     console.writeline(loc); } 

or convert/rewrite list:

list<string> loclist = new list<string>(locations); console.writeline(loclist[2]); // of course, assuming there @ least three.. 

let's second objective.

to gather list of values related thing behaving "logical key", dictionary<key,val> may useful. allows store/associate "value" "key", ie:

dictionary<string, double> dict = new dictionary<string, double>(); dict["mamma"] = 123.45; double d = dict["mamma"]; // d == 123.45 

    dict["mamma"] += 101; // possible! double e = dict["mamma"]; // d == 224.45

however, has behavior of happily throwing exceptions when try read unknown key:

dictionary<string, double> dict = new dictionary<string, double>(); dict["mamma"] = 123.45; double d = dict["daddy"]; // throws keynotblarghexception 

    dict["daddy"] += 101; // throw too! tries read old/current value!

so, 1 have careful with "keys" not yet know. fortunatelly, can ask dictionary if knows key:

dictionary<string, double> dict = new dictionary<string, double>(); dict["mamma"] = 123.45; bool knowit = dict.containskey("daddy"); // == false 

so can check-and-initialize-when-unknown:

dictionary<string, double> dict = new dictionary<string, double>(); bool knowit = dict.containskey("daddy"); // == false if( !knowit )     dict["daddy"] = 5;  dict["daddy"] += 101; // 106 

so.. let's try summing priorities location-wise:

dictionary<string, double> priosums = new dictionary<string, double>(); foreach(var row in rows) {     string location = (string)rows["location"];     double priority  = (double)rows["priority"];      if( ! priosums.containskey(location) )         // make sure dictionary knows location         priosums[location] = 0.0;      priosums[location] += priority; } 

and, really, that's all. priosums know locations , sums of priorities:

var sss = priosums["newyork"];  // 9123, assuming newyork location 

however, that'd quite useless have hardcode locations. hence, can ask dictionary keys curently know

foreach(string key in priosums.keys)     console.writeline(key); 

and can immediatelly use it:

foreach(string key in priosums.keys) {     console.writeline(key);     console.writeline(priosums[key]); } 

that should print locations sums.

you might noticed interesting thing: dictionary can tell keys has remembered. hence, do not need hashset first objective. summing priorities inside dictionary, uniquized list of location free: ask dict keys.

edit:

i noticed you've had few more requests (like sort-descending or find-highest-prio-value), think i'll leave them now. if understand how used dictionary collect priorities, build similar dictionary<string,string> collect highest-ranking value location. , 'descending order' done if take values out of dictionary , sort them i.e. list.. i'll skip now.. text got far tl;dr think :)