Creating objects from stored procedure result

Question

We have an existing SQL Server stored procedure with following query. We need to create a collection of Student objects in the following class design, from the result of the query.

What is the best way to create the objects from the SqlDataReader using LINQ?

Note: I am using SqlDataReader only; no ORM

Query

SELECT 
    S.StudentID, S.StudentName, E.ExamID, E.ExamName, SE.Mark 
FROM 
    StudentExam SE
INNER JOIN 
    Student S ON S.StudentID = SE.StudentID
INNER JOIN 
    Exam E ON E.ExamID = SE.ExamID

Class

public class ExamMark
{
    public int ExamID { get; set; }
    public string ExamName { get; set; }
    public int Mark { get; set; }
}

public class Student
{
    public int StudentID { get; set; }
    public string StudentName { get; set; }
    public List<ExamMark> examResults { get; set; }
}

SqlDataReader

   SqlDataReader reader = command.ExecuteReader();
   if (reader.HasRows)
   {
      while (reader.Read())
      {

      }
   }

REFERENCES

What's wrong with instantiating new object and assigning values from database to properties? — Leri, Jul 15 '13 at 08:06
@Lijo How does iterating over returned resultset increase database calls? — Leri, Jul 15 '13 at 08:08
@Lijo just to let you know linq is using loop internally. see http://stackoverflow.com/questions/3156059/linq-statement-faster-than-foreach-loop — Zaki, Jul 15 '13 at 08:13

Jodrell · Answer 1 · 2013-07-15T09:13:58.857

Well, I wouldn't do it like that,

I'd have two statements

-- Student Statement
SELECT
             S.StudentID,
             S.StudentName
    FROM
             Student S
    WHERE
             EXISTS (
              SELECT * FROM StudentExam SE WHERE SE.StudentID = S.Student.ID);

-- Exam Statement
SELECT
            SE.StudentID,
            E.ExamID,
            E.ExamName,
            SE.Mark 
    FROM
            StudentExam SE
        JOIN
            Exam E
                ON E.ExamID = SE.ExamID;

Then, I'd have a function that does this,

private IEnumerable<Tuple<int, ExamMark>> GetMarks()
{
    ... setup the exam command here
    var reader = examCommand.ExecuteReader();
    while (reader.Read())
    {
        yield return Tuple.Create(
            reader.GetInt32(0),
            new ExamMark
                {
                    reader.GetInt32(1),
                    reader.GetString(2),
                    reader.GetInt32(3)
                });
    }
}

Then I'd have this function to call,

private IEnumerable<Student> GetStudents()
{
    var resultLookup = GetMarks().ToLookup(t => t.Item1, t => t.Item2);

    ... setup the student command here
    var reader = studentCommand.ExecuteReader();
    while (reader.Read())
    {
        var studentId = reader.GetInt32(0);
        yield return new Student
                {
                    studentId,
                    reader.GetString(1),
                    resultLookup[studentId].ToList()
                });
    }
}

If you wanted, you could do it all in one stored procedure and return multiple result sets.

@Lijo, what are your concerns with this answer? – Jodrell Aug 06 '13 at 08:08 — Jodrell, Aug 06 '13 at 08:08

Adrian Trifan · Answer 2 · 2013-07-30T06:52:43.610

This should do the work:

using (SqlDataReader reader = command.ExecuteReader())
        {
            var records = (from record in reader.Cast<DbDataRecord>()
                           select new
                           {
                               StudentID = record.GetInt32(0),
                               StudentName = record.GetString(1),
                               ExamID = record.GetInt32(2),
                               ExamName = record.GetString(3),
                               Mark = record.GetInt32(4)
                           })
                           .GroupBy(r => new { StudentID = r.StudentID, StudentName = r.StudentName })
                           .Select(
                                     r => new Student
                                     {
                                         StudentID = r.Key.StudentID,
                                         StudentName = r.Key.StudentName,
                                         examResults = r.Select(e => new ExamMark
                                         {
                                             ExamID = e.ExamID,
                                             ExamName = e.ExamName,
                                             Mark = e.Mark
                                         }).ToList()
                                     });
        }

score 2 · Answer 3 · edited May 23 '17 at 10:31

I think the important of your question is about creating objects from a DataReader (in this case a SqldataReader).

Here you can find one of my answers about a similar argument. As I say everytime, the answer can depend upon the context on which you need a solution. As I could understand, You don't want to use ORMs, so a clean and basic solution could be:

Using SqlDataReaderToObject
Using Extension methods for IDbComannd and IDataReader

each of those are "helpers" over the base ADO.NET objects. As I said before, using soluitons like that requires some restrictions, for example object fields or property names, and so on. Hope this helps.

score 1 · Answer 4 · edited May 23 '17 at 12:28

1

You should really consider using dapper. It supports stored procedures and as the page indicates, the performance difference between an extremely simple dapper call and an overengineered hand-mapped solution is negligible:

Performance of SELECT mapping over 500 iterations - POCO serialization

Hand coded (using a SqlDataReader) 47ms

Dapper ExecuteMapperQuery 49ms

edited May 23 '17 at 12:28

Community

1
1

answered Jul 30 '13 at 17:42

Kevin Stricker

17,178
5
45
71

score 0 · Answer 5 · answered Aug 05 '13 at 09:25

Using ADO.Net Entity framework and a Method like SPTE (Stored Proc To Enity) can be used to build the objects.

I have a Project which dynamically builds the objects and relations based on Entity Framework definition. This is more dynamic.

I can share the code on code.google.com.

Creating objects from stored procedure result

5 Answers5

Performance of SELECT mapping over 500 iterations - POCO serialization