Bulb Flash:-Returning Complex objects from Stored Procedures in Entity Framework!

Sometimes when using the Entity Framework we decide to encapsulate a complicated piece of SQL into a stored procedure and call it directly from your code.

But how to you extract the result of the Stored Proc if it returns a combination of columns from different tables?

Consider the following Stored Proc which returns a list clients recently worked on.

Notice that columns returned span 2 tables (Joined) so you can’t just make the SP return a List of Clients..

select top(@number)c.Client_Name as ClientName,c.ClientID asClientID,Max(p.EndTimeStamp) asLastTimeWorked
fromClients c joinTimeLog p
on
c.ClientID =p.ClientCode
Group Byc.Client_Name,c.ClientID
Order byMAX(p.EndTimeStamp)desc

So now you add the Stored Procedure in your Entity Diagram, but what will be its return type?

• If you double click the StoredProc in the Model.Store to get the following screen

• If your SP returns a combination of columns click on the “Get Column Information” button to get the column names returned by the SP

• Click on “Create new Complex Type” to create a new class for this group of columns

Now you can use the RecentClients_Results(Rename if you like) as a normal entity class in your code!

Hope this helps!

Until next time!

Cennest

Code for Keeps:- Store Retrieve image to/from Database

One of our early blogs was about extracting images from databases, websites and showing them on the screen.

The blog(see it here) dealt with

a) Retrieving images from the www

b) Retrieving images from the database when retrieved as a binary.

We realized there are some more scenarios to take care of here

c) Save Image to the database as a byte[]

public Byte[] BufferFromImage(BitmapImage imageSource)
     {
         MemoryStream memStream = new MemoryStream();
         JpegBitmapEncoder encoder = new JpegBitmapEncoder();
         encoder.Frames.Add(BitmapFrame.Create(imageSource));
         encoder.Save(memStream);
         return memStream.GetBuffer();
         
     }

d) Retrieve Image from byte[]( When you use Entity Framework with the Image database type you would get it back as a byte[])

public BitmapImage ConvertToImage(byte[] rawImageData)
  {
      MemoryStream stream = new MemoryStream(rawImageData);
      stream.Position = 0;
      BitmapImage bi = new BitmapImage();
      bi.BeginInit();
      bi.StreamSource = stream;
      bi.EndInit();
      return bi; 
  }
If you see any more scenarios do add comments and lets complete this list…

Cennest!