StoredProcedures with multiple result sets and RIA Services

Here is one way we could work with StoredProcedures that return multiple results when using WCF RIA Services. I grabbed the code for EFExtensions and  was working with a model from MusicStoreSample

I created a simple procedure, which returns the artist details for the given Id and all the Albums for the Artist

create procedure [dbo].[GetArtistDetails](@artistId int) as

select * from Artist where ArtistId=@artistId                    
select * from album where ArtistId=@artistId                    
I extended the generated Entities class in the web project to add a method (followed the Sample in the  EFExtensions sample project).

public partial class MusicStoreEntities : ObjectContext
    {
        private static readonly Materializer<Artist> s_artistMaterializer = new Materializer<Artist>();
        private static readonly Materializer<Album> s_albumMaterializer = new Materializer<Album>();
        public Artist GetArtistAndRelatedAlbums(int artistId)
        {
            DbCommand command = this.CreateStoreCommand(“GetArtistDetails”, CommandType.StoredProcedure, new SqlParameter(“artistId”, artistId));
            Artist artist;

            using (command.Connection.CreateConnectionScope())
            using (DbDataReader reader = command.ExecuteReader())
            {
                // first result set includes the category
                artist = s_artistMaterializer
                    .Materialize(reader)
                    .Bind(this.Artists)
                    .SingleOrDefault();

                // second result set includes the related products
                if (null != artist && reader.NextResult())
                {
                    artist.Albums.Attach(s_albumMaterializer
                        .Materialize(reader)
                        .Bind(this.Albums));
                }
            }

            return artist;
        }
    }

In the DomainService class I created based on the MusicStoreEntities, I added the following method which calls the method we created above

  public Artist GetArtistAndRelatedAlbums(int artistId)
        {
            return this.ObjectContext.GetArtistAndRelatedAlbums(artistId);
        }

In the Generated MetaData class, Added [Include] attribute on Albums property

 public partial class Artist
    {
      
        internal sealed class ArtistMetadata
        {

            // Metadata classes are not meant to be instantiated.
            private ArtistMetadata()
            {
            }

            [Include]
            public EntityCollection<Album> Albums { get; set; }

            public int ArtistId { get; set; }

            public string Name { get; set; }
        }
    }

In the client project, we could write small bit of code to test and see, we get Artist Details along with all the Albums for the artist

Artist artist = null;
MusicStoreDomainContext _ctx = new MusicStoreDomainContext();           
            _ctx.Load<Artist>(_ctx.GetArtistAndRelatedAlbumsQuery(22), (lo) => {
                artist = lo.Entities.FirstOrDefault();
                }, null);                                    
            }, false);

Advertisements

3 thoughts on “StoredProcedures with multiple result sets and RIA Services

  1. Lee,

    How to work with Silverlight App in Offline Mode?

    my requirement is like if my internet disconnected the application should run and i need to store the data and i want to update the data ones i connected to the internet.

    I wanted to Implement Microsoft sync Framework.

    I never used sysnc in any my Apps so far

    I need some help regarding this.

    Thanks

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