Creating objects from the result of a stored procedure

Question

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

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

Note. I use only SqlDataReader ; 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()) { } } 

LITERATURE

• LINQ: fill objects on the left.
• Integrated GROUP BY by DataTable

Answer 1

Well I wouldn’t do that

I would 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 will 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 would 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 want, you can do it all in one stored procedure and return multiple result sets.

Answer 2

This should do the job:

 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() }); } 

Answer 3

I think the important 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 each time, the answer may depend on the context in which you need a solution. As I could understand, you do not want to use ORM, so a clean and basic solution could be:

• Using SqlDataReaderToObject
• Using Extension Methods for IDbComannd and IDataReader

each of them is “helpers” on the base objects of ADO.NET. As I said, the use of such similar objects requires some restrictions, for example, object fields or property names, etc. Hope this helps.

Answer 4

You really should consider using dapper . It supports stored procedures , and as the page shows, the performance difference between the extremely simple dapper call and the reconfigured solution with manual mapping is not significant:

SELECT mapping performance over 500 iterations - POCO Serialization

• Manual encoding (using SqlDataReader) 47 ms
• Dapper ExecuteMapperQuery 49ms

Answer 5

Using the ADO.Net Entity structure and a method such as SPTE (stored by Proc To Enity) can be used to create objects.

I have a project that dynamically creates objects and relationships based on an Entity Framework definition. It is more dynamic.

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