Get multiple result set from stored proc using Entity Framework


Hello folks,

I have some strange requirement.I want multiple result set from stored procedure.It does not seems a tough task but it’s taken few minutes to search out on google ,’cause i need this one in entity framework.So here i’m sharing the code snippet for the same.

For this we need to create wrapper class .Wrapper class is nothing but a simple class which wraps other types of class in single object.Seems quite confusing.Let’s go through example.

For example you have class named MyClass1,MyClass2,MyClass3 ,and you have stored proc which is returns these three types of result set.

So wrapper class would be,


public class WrapperClass
{
 public MyClass1 class1 { get; set; }
 
 public List<MyClass2> class2 { get; set; }
 
 public MyClass2 class2 { get; set; }
}

Our wrapper class ready now.

So here is a method to get result set from stored procedure.

public static WrapperClass GetMultipleResultSet(int Id)
 {
 WrapperClass wrapper = new WrapperClass();

 using (var context = new myDBContext())
 {
 context.Database.Initialize(force: false);
 var cmd = context.Database.Connection.CreateCommand();
 cmd.CommandText = "[dbo].[YourStoredProcedureName] @Id";
 
 var param = cmd.CreateParameter();
 param.ParameterName = "@Id";
 param.Value = Id;

 cmd.Parameters.Add(param);

 context.Database.Connection.Open(); 

 // Run the sproc 
 var reader = cmd.ExecuteReader();

 // Read first result set 
 wrapper.class1 = ((IObjectContextAdapter)context)
 .ObjectContext
 .Translate<MyClass1>(reader).FirstOrDefault();

 reader.NextResult();

 wrapper.class2 = ((IObjectContextAdapter)context)
 .ObjectContext
 .Translate<MyClass2>(reader).ToList();
 
 reader.NextResult();
 
 wrapper.class3 = ((IObjectContextAdapter)context)
 .ObjectContext
 .Translate<MyClass3>(reader).FirstOrDefault();
 
 return wrapper;
 }
 }

Happy Coding šŸ™‚

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: