有什么办法遍历我的SQL结果，并在C＃中其他存储某些名称/值对？ [英] Is there any way to loop through my sql results and store certain name/value pairs elsewhere in C#?

问题描述

我有一个大的结果集，从非常复杂的SQL查询来。其中的值是一个字符串它代表一个位置（稍后将帮助我确定该值的来源页面的位置），一个 INT 这是另一个字符串其中包含一个值后，我一定要记住显示的基础上，从该行其它值的每一行计算的优先级编号，和

的问题是，SQL查询是如此复杂（有工会，连接和使用别名复杂计算），我不能在逻辑上适合别的进去不搞乱它的工作方式。

我只想说，虽然，查询完成并进行了计算，我需要的东西，也许聚合函数可能会解决后，但是不是一种选择，因为所有的列不来自其他聚合函数。

我一直令人头大我好几天的大脑现在，我怎么可以遍历这些结果，存储在一个列表中的一对值（或两个单独的列表绑在一起以某种方式），其中一个值是所有的每个位置和其他值的优先级值的总和是一个独特的位置值（即，作为结果通过环，它不会产生与已使用过的同一位置值的另一个列表项，但是，它仍然需要从相同位置的所有其他优先级值的总和）。 。另外，其结果需要由优先级降序排列（因此使用两个列表的问题）进行排序

示例：

编辑：我忘了，保留的价值应该从与SQL查询的优先级最高的行中的值

如果我有以下结果：

 位置的优先级值
 
 -------------------------------------------------- ------------------------------ 
第1页1一些文字！ 
第2页3更多的文字！ 
第2页4甚至更多的文字！ 
第3页3文本再次
第3页1文本
第3页1还是更多的文字！ 
第4页6文本
  

如果我能够做什么，我想我能来实现迭代后这样的事情（在这个顺序）：

 位置的优先级值
 
  - -------------------------------------------------- ---------------------------- 
第2页7甚至更多的文字！ 
第4页6文本
第3页5个文本再次
第1页1一些文字！ 
  

我已经做了研究后，研究研究，但绝对没有经过真正的甚至会接近解决这一难题。

是我要问，即使是强大的C＃语言太强硬？

事情我必须考虑：

的通过SQL结果循环和检查重复每个位置，将所有优先值加在一起，因为我去和存储这两个加在两个或三个单独的列表值。的

为什么我还需要帮助

我不能使用foreach因为逻辑没有成功，因为我无法访问一个IEnumerable我不能使用为循环（或者是任何类型什么用指数从 Database.Open.Query（）返回的商店。（这是有道理的，当然）。另外，我需要的排序优先级，但不能得到一个列表不与他人同步。

---

的使用LINQ来选择和存储什么，我需要的

为什么我还需要帮助

我不知道LINQ（在所有！），主要是因为我不明白lambda表达式（不管多少我读了这件事）。

---

的使用一个实例化类存储名称/值对的

为什么我仍然需要帮助

我不仅期待在排序这样的事情是不可能的，虽然我现在做的如何使用的.cs 在WebMatrix的环境下我的C＃.NET的网页文件，我主要是只使用过的静态类，而且还需要在构造一个小进修课程以及如何适当地设置起来。

---

的不知怎的，装修这个功能集成到已有相当规模和复杂的SQL查询的

为什么我还需要帮助

虽然这可能是在那里我将理想像这样的功能是，我再次，这是压力不是一个选项。我一直在使用聚合函数尝试过，但只得到一个错误，说所有的其他列怎么不是来自聚合函数。

---

的使基于价值从第一个查询结果的另一个查询集的

为什么我还需要帮助

我不能仅根据一列中选择（即位置）单独不同的结果。

---

的假设我能得到循环逻辑正确，存储在一个3维数组中的值的

为什么我仍需要帮助

我不能声明数组，因为我不知道它的所有方面之前，我需要使用它。

解决方案

你的讯息已让我吃惊的许多方面就像说要'大多采用静态类和期待实例化一个类/对象是不可能'..真的很奇怪的事你说。我只能从查尔斯·巴贝奇报价作出回应：

我不能够正确地逮捕的那种思想的混乱，可能引发这样的一个问题。

不管怎么说。至于你说你找到lambda表达式辛苦，让我们跟踪问题中的经典手动方式。

让我们假设你有一个包含位置和优先事项行的列表。

 列表与LT; DataRow的>行= ....; //数据表，SqlDataReader的，无论
  

您说您需要：

• 的独特位置


• 一个配对与总结priorites

<位置列表列表/ UL>



让我们先从第一个目标。





要收集独特的价值的列表，HashSet的就是完美

 的HashSet<串GT;位置=新的HashSet<串GT;（）; 
的foreach（行VAR行）
 locations.Add（（字符串）行[所在地]）; 
  





好，仅此而已。在此之后，地点 HashSet的只会记住所有的独特位置。 添加不导致重复的元素。该HashSet的检查和uniquifies被放在它里面的所有值。小棘手的事情是HashSet的不具有 [指数] 运营商。你必须枚举HashSet的得到的值：

 的foreach（在位置字符串LOC）
 $ { b $ b Console.WriteLine（LOC）; 
} 
  

或转换/改写到一个列表：

 列表<串GT; locList =新的List<串GT;（位置）; 
 Console.WriteLine（locList [2]）; //当然，假设有至少三.. 
  





让我们的第二个目标。





要收集相关的一些东西表现得像一个逻辑键值的列表，词典<关键的Val> 可能是有用的。它允许你存储/价值与一些重点，联系起来，即：

 词典<字符串，双>字典=新词典<字符串，双>（）; 
字典[妈妈] = 123.45; 
双D =字典[妈妈]; //Ð== 123.45 
  





字典[妈妈] + = 101; //可能！
双E =字典[妈妈]; //Ð== 224.45





然而，它有一个愉快的行为的投掷例外，当你尝试的阅读从未知键

 词典<字符串，双>字典=新词典<字符串，双>（）; 
字典[妈妈] = 123.45; 
双D =字典[爸爸]; //抛出KeyNotBlarghException 
  





字典[爸爸] + = 101; //会抛出呢！尝试读取旧/电流值！





所以，人们必须非常小心将其与钥匙，它还不知道。 Fortunatelly，你可以随时要求字典，如果它已经知道了关键：

 词典<字符串，双>字典=新词典<字符串，双>（）; 
字典[妈妈] = 123.45; 
布尔knowIt = dict.ContainsKey（爸爸）; // ==虚假
  





所以，你可以很容易地检查和初始化，当未知的：

 词典<字符串，双>字典=新词典<字符串，双>（）; 
布尔knowIt = dict.ContainsKey（爸爸）; // ==虚假
如果（knowIt！）
字典[爸爸] = 5; 
 
字典[爸爸] + = 101; //现在106 
  





所以..让我们尝试总结优先位置明智的：

 词典<字符串，双> prioSums =新词典<字符串，双>（）; 
的foreach（行VAR行）
 {
线位置=（字符串）行[所在地]; 
双优先=（双）行[优先]; 
 
如果
 //确保字典知道位置
 prioSums [位置] = 0.0（prioSums.ContainsKey（位置）！）; 
 
 prioSums [位置] + =优先; 
} 
  

和，真的，这就是全部。现在 prioSums 会知道所有位置和优先级的所有款项：

  VAR SSS = prioSums [纽约]; // 9123，假设纽约是一些位置
  





然而，这会是相当无用的有硬编码所有位置。因此，你也可以问什么键它有curently知道





 的foreach（字符串键的字典在prioSums.Keys）
 Console.WriteLine（键）; 
  

和可以immediatelly使用它：

 的foreach（字符串prioSums.Keys键）
 {
 Console.WriteLine（键）; 
 Console.WriteLine（prioSums [关键]）; 
} 
  

这应该打印出他们的所有款项的所有位置。





您可能已经注意到了一件有趣的事情：字典可以告诉你哪些键有它记住了。因此，您不需要的从第一目标HashSet的。简单地总结字典里的优先级，你会得到通过自由位置的uniquized名单：只要求其键的dict





编辑：





我注意到你有几个请求（如排序或降序发现，最高PRIO值），但我想我会离开他们现在。如果你了解我是如何使用字典收集优先级，那么你会很容易地建立一个类似的词典<字符串，字符串> 来收集位置排名最高的价值。如果只有你把值超出字典，并将它们分类为即列表中的降序是很容易做的..所以我会跳过现在...本文得到了远TL;博士已经我想:)




I have a large result set coming from a pretty complex SQL query. Among the values are a string which represents a location (that will later help me determine the page location that the value came from), an int which is a priority number calculated for each row based on other values from the row, and another string which contains a value I must remember for display later.

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.

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.

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).

EXAMPLE:

EDIT: I forgot, the preserved value should be the value from the row with the highest priority from the sql query.

If I had the following results:

  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

If I was able to do what I wanted I would be able to achieve something like this after iteration (and in this order):

  location                      priority                               value  

--------------------------------------------------------------------------------
   page2                           7                               even more text!
   page4                           6                                    text
   page3                           5                                  text again
   page1                           1                                  some text!

I have done research after research after research but absolutely nothing really even gets close to solving this dilemma.

Is what I'm asking too tough for even the powerful C# language?

THINGS I HAVE CONSIDERED:

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.

Why I still need help

I can't use a foreach because the logic didn't pan out, and I can't use a for loop because I can't access an IEnumerable (or whatever type it is that stores what's returned from Database.Open.Query() 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.

---

Using LINQ to select and store what I need

Why I still need help

I don't know LINQ (at all!) mainly because I don't understand lambda expressions (no matter HOW MUCH I read up about it).

---

Using an instantiated class to store the name/value pairs

Why I still need help

Not only do I expect sorting on this sort of thing to be impossible, and while I do now how to use .cs 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.

---

Somehow fitting this functionality into the already sizeable and complex SQL query

Why I still need help

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.

---

Making another query based on values from the first query's result set

Why I still need help

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

---

Assuming I could get the loop logic correct, storing the values in a 3 dimensional array

Why I still need help

I can't declare the array, because I do not know all of its dimensions before I need to use it.

解决方案

Your post has amazed me in a number of ways like saying to 'mostly using static classes' and 'expecting instantiate a class/object to be impossible'.. really strange things you say. I can only respond in a quote from Charles Babbage:

I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

Anyways.. As you say you find lambdas hard, let's trace the problem in the classic 'manual' way.

Let's assume you have a list of ROWS that contains LOCATIONS and PRIORITIES.

List<DataRow> rows = .... ; // datatable, sqldatareader, whatever

You say you need:

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

Let's start with the first objective.

To gather a list of unique 'values', a HashSet is just perfect:

HashSet<string> locations = new HashSet<string>();
foreach(var row in rows)
    locations.Add( (string)rows["LOCATION"] );

well, and that's all. After that, the locations hashset will only remember all the unique locations. The "Add" does not result in duplicate elements. The HashSet checks and "uniquifies" all values that are put inside it. Small tricky thing is the hashset does not have the [index] operator. You'll have to enumerate the hashset to get the values:

foreach(string loc in locations)
{
    Console.WriteLine(loc);
}

or convert/rewrite it to a list:

List<string> locList = new List<string>(locations);
Console.WriteLine(locList[2]); // of course, assuming there were at least three..

Let's get to the second objective.

To gather a list of values related to some thing behaving like a "logical key", a Dictionary<Key,Val> may be useful. It allows you to store/associate a "value" with some "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, it has a behavior of happily throwing exceptions when you try to read from an unknown key:

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

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

So, one have to be very careful with it with "keys" that it does not yet know. Fortunatelly, you can always ask the dictionary if it already knows a key:

Dictionary<string, double> dict = new Dictionary<string, double>();
dict["mamma"] = 123.45;
bool knowIt = dict.ContainsKey("daddy"); // == false

So you can easily 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; // now 106

So.. let's try summing up the 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 that dictionary knows the location
        prioSums[location] = 0.0;

    prioSums[location] += priority;
}

And, really, that's all. Now the prioSums will know all locations and all sums of priorities:

var sss = prioSums["NewYork"];  // 9123, assuming NewYork was some location

However, that'd be quite useless to have to hardcode all locations. Hence, you also can ask the dictionary about what keys does it curently know

foreach(string key in prioSums.Keys)
    Console.WriteLine(key);

and you can immediatelly use it:

foreach(string key in prioSums.Keys)
{
    Console.WriteLine(key);
    Console.WriteLine(prioSums[key]);
}

that should print all locations with all their sums.

You might already noticed an interesting thing: the dictionary can tell you what keys has it remembered. Hence, you do not need the HashSet from the first objective. Simply by summing up the priorities inside the Dictionary, you get the uniquized list of location by free: just ask the dict for its keys.

EDIT:

I noticed you've had a few more requests (like sort-descending or find-highest-prio-value), but I think I'll leave them for now. If you understand how I used a dictionary to collect the priorities, then you will easily build a similar Dictionary<string,string> to collect the highest-ranking value for a location. And the 'descending order' is done very easily if only you take the values out of dictionary and sort them as a i.e. List.. So I'll skip that for now.. This text got far tl;dr already I think :)

这篇关于有什么办法遍历我的SQL结果，并在C＃中其他存储某些名称/值对？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！