Call stored procedure from entity framework and handle multiple result sets.

How to get multiple result sets in Entity Framework


This article for intermediate level programmers, so I hope you have a basic idea about the Entity Framework terms like model, .edmx file, calling the stored procedures etc.

Sometimes we need multiple result sets by calling stored procedures. We don't need to call multiple time to the database. Prior to EF5, Entity Framework would allow the stored procedure to be called but would only return the first result set to the calling code.




This blog is basically to introduce the way how we can get multiple result sets by calling the stored procedures using the Entity Framework. In this case we have to divide process in 2 steps :
  1. Create stored procedures that will return multiple result sets.
  2. Write a method to call the stored procedure and get all the result sets.
1) For the first step i am going to demonstrate a query to get multiple result set that can be used in the stored procedure.

Stored procedure query and result sets
2) For the next step we have to write a method to call the stored procedure and read multiple result set.

public EventDetails GetTournamentDetails(int eventId)
        {
            try
            {
                EventDetails EventSetupDetails = new EventDetails();
                using (var context = new TournamentManagementEntities())
                {
                    DbConnection EntityConnection = context.Database.Connection;
                    DbCommand Command = EntityConnection.CreateCommand();
                    Command.CommandText = "GetEventDetails";
                    Command.CommandType = CommandType.StoredProcedure;
                    Command.Parameters.Add(new SqlParameter("@eventId", eventId));

                    List<Event> Event = new List<Event>();
                    try
                    {
                        EntityConnection.Open();
                        Command.CommandTimeout = 700;
                        //--Read the data from diff result set and store those collection in diff lists
                        using (var reader = Command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                EventSetupDetails.Event = reader.Materialize<Event>().ToList();
                            }
                            reader.NextResult();
                            while (reader.Read())
                            {
                                EventSetupDetails.EventPlayer = reader.Materialize<EventPlayer>().ToList();
                            }
                        }
                        EntityConnection.Close();
                        EntityConnection.Dispose();
                        // return EventSetupDetails;
                        return EventSetupDetails;
                    }
                    catch (Exception ex)
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }

Now we can see above we are holding two result set into a class object named EventSetupDetails, It has two list properties Event and EventPlayer.

That is defined as below :
public class EventDetails
    {
        public List<Event> Event { get; set; }
        public List<EventPlayer> EventPlayer { get; set; }
    }

By calling GetTournamentDetails() method from Controller we can get all the result Ajax call.




Happy coding...

No comments:

Post a Comment