Stored Procedure returning fields from multiple table

2 posts, 0 answers
  1. Prasanna
    Prasanna avatar
    2 posts
    Member since:
    Nov 2010

    Posted 27 Dec 2010 Link to this post

    Hi,

    I am fairly new to ORM and I need answer for the following questions:

    1. Is it possible to populate objects when a stored procedure is returning list of fields from 2 different table. For example, if I have a Stored Procedure as follows:

     

    <P><FONT color=#0000ff size=2><FONT color=#0000ff size=2> </P>
    <P>ALTER</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT 
    color=#0000ff size=2>PROCEDURE</FONT></FONT><FONT size=2> [dbo]</FONT><FONT 
    color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT 
    size=2>[usp_GetAllJobs]</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>AS</P>
    <P>BEGIN</P></FONT></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff 
    size=2>SELECT</FONT></FONT><FONT size=2> J</FONT><FONT color=#808080 
    size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>Name</FONT><FONT 
    color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2
    J</FONT><FONT color=#808080 size=2><FONT color=#808080 
    size=2>.</FONT></FONT><FONT size=2>JobId</FONT><FONT color=#808080 size=2><FONT 
    color=#808080 size=2>,</FONT></FONT><FONT size=2> JTA</FONT><FONT color=#808080 
    size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>Type</FONT><FONT 
    color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2
    JTA</FONT><FONT color=#808080 size=2><FONT color=#808080 
    size=2>.</FONT></FONT><FONT size=2>AudioQuality </FONT><FONT color=#0000ff 
    size=2><FONT color=#0000ff size=2>from</FONT></FONT><FONT size=2> Jobs J</P>
    <P></FONT><FONT color=#808080 size=2><FONT color=#808080 
    size=2>Left</FONT></FONT><FONT size=2> </FONT><FONT color=#808080 size=2><FONT 
    color=#808080 size=2>Join</FONT></FONT><FONT size=2
    Jobs_TranscriptionAttributes JTA </FONT><FONT color=#0000ff size=2><FONT 
    color=#0000ff size=2>on</FONT></FONT><FONT size=2> J</FONT><FONT color=#808080 
    size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>Jobid</FONT><FONT 
    color=#808080 size=2><FONT color=#808080 size=2>=</FONT></FONT><FONT 
    size=2>JTA</FONT><FONT color=#808080 size=2><FONT color=#808080 
    size=2>.</FONT></FONT><FONT size=2>Jobid</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff 
    size=2>END</FONT></FONT></P>

     

     

    Now when I execute the SP using the code given below only the properties of Object "Job" gets populated.

     

    <FONT face=Consolas color=#2b91af size=2><FONT face=Consolas color=#2b91af 
    size=2><FONT face=Consolas color=#2b91af size=2>Job</FONT></FONT></FONT><FONT 
    face=Consolas size=2><FONT face=Consolas size=2>[] allJobs = 
    ED.ExecuteStoredProcedure<</FONT></FONT><FONT face=Consolas color=#2b91af 
    size=2><FONT face=Consolas color=#2b91af size=2><FONT face=Consolas 
    color=#2b91af size=2>Job</FONT></FONT></FONT><FONT face=Consolas size=2><FONT 
    face=Consolas size=2>>(</FONT></FONT><FONT face=Consolas color=#a31515 
    size=2><FONT face=Consolas color=#a31515 size=2><FONT face=Consolas 
    color=#a31515 size=2>"usp_GetAllJobs"</FONT></FONT></FONT><FONT face=Consolas 
    size=2><FONT face=Consolas size=2>, </FONT></FONT><FONT face=Consolas 
    color=#0000ff size=2><FONT face=Consolas color=#0000ff size=2><FONT 
    face=Consolas color=#0000ff size=2>null</FONT></FONT></FONT><FONT face=Consolas 
    size=2><FONT face=Consolas size=2>, </FONT></FONT><FONT face=Consolas 
    color=#0000ff size=2><FONT face=Consolas color=#0000ff size=2><FONT 
    face=Consolas color=#0000ff size=2>null</FONT></FONT></FONT><FONT face=Consolas 
    size=2><FONT face=Consolas size=2>);<BR></FONT></FONT><FONT face=Consolas 
    color=#2b91af size=2><FONT face=Consolas color=#2b91af size=2><FONT 
    face=Consolas color=#2b91af size=2>Console</FONT></FONT></FONT><FONT 
    face=Consolas size=2><FONT face=Consolas 
    size=2>.WriteLine(allJobs[0].Jobs_BillingInformations[0].OnlineTransReference 
    );</FONT></FONT>


    I need a way to automatically populate "Jobs_BillingInformations" Object too.

    2. Is it possible to execute a SP which returns more than 1 datatable? I could not find a working sample which can accomplish this.

    Thanks.
  2. Dimitar Kapitanov
    Admin
    Dimitar Kapitanov avatar
    632 posts

    Posted 30 Dec 2010 Link to this post

    Hi Prasanna,
    Currently you can use stored procedures to materialize entities, not projections. Entities however are mapped to tables of views. In order to combine fields from multiple tables you will have to prepare a view, then map it to an entity. And in the case of views don't forget to set the right fields/properties to be identities (views do not return identity columns automatically, one has to set it explicitly).

    Greetings,
    Dimitar Kapitanov
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
Back to Top