从存储过程返回具有填充列表属性的对象 [英] Return objects with populated list properties from stored procedure

问题描述

我是SQL Server存储过程的新手，所以如果我是个白痴，我深表歉意.我想使用存储过程返回对象列表，每个对象都有一个包含相关对象列表的属性.例如

I'm new to SQL Server stored procedures, so apologies if I'm being an idiot. I would like to use a stored procedure to return a list of objects, each of which has a property containing a list of related objects. For example

public class Question
{
    public int QuestionID { get; set; }
    public string Question { get; set; }
    public List<Answer> Answers { get; set; }
}

public class Answer
{
    public int AnswerID { get; set;}
    public int QuestionID { get; set; }
    public string Answer { get; set;}
}

我想编写一个存储过程，该存储过程返回问题列表，每个问题的答案属性都填充有相关的答案对象.

I would like to write a stored procedure that returns a list of Questions, each of which has its Answers property populated with the related Answer objects.

任何帮助都将不胜感激！

Any help greatly appreciated!

谢谢

戴夫

推荐答案

实际上，存储过程传递的是关系结果而不是对象.或者，您可以使用 FOR XML 返回XML并将其反序列化为对象.通常使用O/R映射器将其映射到对象.

Actually a stored procedure delivers a relational result rather than objects. As an alternative, you could return XML using FOR XML and deserialize it into objects. Mapping this to objects is usually done using an O/R mapper.

您可以使用数据集和表适配器将关系数据获取到您的应用程序中.一旦加载到数据集中，您就可以填充您的 Question 和 Answer 对象.

You can use datasets and table adapters to get the relational data into your applications. Once loaded into the dataset, you can populate your Question and Answer objects.

以下是示例玩具代码，用于将存储过程的结果填充到数据集中:

Here is a sample toy code to fill the result of a stored procedure into a data set:

var ds = new DataSet();

using (var cn = new SqlConnection())
using (var cmd = new SqlCommand("myStoredProcedure", cn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    using (var adapter = new SqlDataAdapter(cmd))
    {
        adapter.TableMappings.Add("Table0", "Answers");
        adapter.TableMappings.Add("Table1", "Questions");

        adapter.Fill(ds);
    }
}

对于实际开发，建议您使用 类型化的数据集 和适当的 SqlConnection .但是，正如评论所指出的那样，请尽可能使用EF或其他O/R映射器.

For actual development I'd suggest you to use a Typed Dataset and a proper SqlConnection. However, as comments pointed out too, use EF or another O/R mapper if you can.

这篇关于从存储过程返回具有填充列表属性的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！