如何使用SqlDataReader获取存储过程返回的分组数据?

I have a sproc that is defined by

我有一个sproc

CREATE PROCEDURE GetQuestionsGroupedBySection
    @survey_id INT
AS
    SELECT S.title, Q.qtext
    FROM Sections AS S INNER JOIN Questions AS Q 
         ON S.id = Q.section_id
         WHERE S.survey_id = @survey_id
         GROUP BY S.title;

where the relevant tables are defined by

相关的表由何处定义

CREATE TABLE Questions (
    id INT IDENTITY(1,1),
    section_id INT,
    qtext NVARCHAR(300) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (section_id) REFERENCES Sections(id) ON DELETE CASCADE
);

and

和

CREATE TABLE Sections (
    id INT IDENTITY(1,1),
    title NVARCHAR(200) NOT NULL,
    survey_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES Surveys(id) ON DELETE CASCADE
);

Now I'm trying to call that from my server-side code to get the groupings in a List of elements of type

现在我试着从我的服务器端代码中调用它来获取类型元素列表中的分组

public class QuestionsBySection
{
    public string SectionTitle { get; set; }
    public List<string> SecionQuestions;
}

(or is there a better data structure to use?). What I have is

(还是有更好的数据结构可以使用?)我有是什么

    public List<QuestionsBySection> GetQuestionsAndSection (int survid)
    {
        // survid: survey id
        List<QuestionsBySection> AllQuestions = new List<QuestionsBySection>();
        using (SqlCommand cmd = new SqlCommand("GetQuestionsGroupedBySection", this._Conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@survey_id", survid);
            this._Conn.Open();
            using ( SqlDataReader dataReader = cmd.ExecuteReader() )
            {
                while ( dataReader.Read() )
                {
                    // ... 
                }
            }
            this._Conn.Close();
        }
        return AllQuestions;
    }

but I'm not sure how to fill in the // .... Any hints?

但我不知道如何填写/ / ....有提示吗?

2 个解决方案

#1

Sql group by is wrong

Sql group by是错误的

You have 2 columns in the results, but only 1 in the group. You can either add the second column as a group by field or use an agregate (eg min() or max()).

结果中有2列，但组中只有1列。可以通过字段添加第二列，也可以使用agregate(如min()或max())。

SELECT S.title, qtext=max(Q.qtext)
    FROM .....

DataReader

DataReader

Just use the field position as an index parameter to GetString:

只需将字段位置作为GetString的索引参数:

var sTitle = dataReader.GetString(0);
var sQuestion = dataReader.GetString(1);

#2

The SQL query is invalid with SQL Server; when using a group by clause, all fields selected must either be included in the group by clause or be an aggregate. Based on your expected results, there are a couple of ways of returning a collection of QuestionsBySection.

SQL查询在SQL Server中无效;当使用group by子句时，选中的所有字段要么包含在group by子句中，要么是聚合。根据预期的结果，有几种方法可以返回一组QuestionsBySection。

One option is to return the result set flattened out and then aggregate on the app tier.

一种选择是返回平铺的结果集，然后在应用程序层上聚合。

The SProc should remove the group by clause:

SProc应按以下条款删除集团:

SELECT S.title, Q.qtext
FROM Sections AS S INNER JOIN Questions AS Q 
    ON S.id = Q.section_id
WHERE S.survey_id = @survey_id

When retrieved, each row needs to be evaluated to see if the Title has already been used:

检索时，需要对每一行进行评估，以查看标题是否已被使用:

var questions = new List<QuestionsBySection>();
// create sql connection
// execute command

while (dataReader.Read())
{
    string title = dataReader.GetString("title");
    string question = dataReader.GetString("qtext");

    QuestionsBySection qbs = questions.FirstOrDefault(x => x.SectionTitle.Equals(title , StringComparison.OrdinalIgnoreCase))
    if (qbs != null)
    {
        qbs.SecionQuestions.Add(question);
        continue;
    }

    qbs = new QuestionsBySection
    {
        SectionTitle = title,
        SecionQuestions = new List<string>{ question }
    }
    questions.Add(qbs);
}

There are several other ways of achieving the result, but this should give you a better understanding of how to aggregate the results.

还有其他几种实现结果的方法，但这应该能让您更好地理解如何聚合结果。

#1