locked
SPROC not reflected to client RRS feed

  • Question

  • I've been judicious I believe in trying this multiple times.  I've followed SLAdapters step-by-step as well as referenced any number of blog / forum posts but I can't get the SPROC up to the client.  Everything compiles fine.

    I have followed the following (thank you SLAdapter)

    1) Add a ADO Entity Data Model to your Web project;  Select generate from database option; Select your Database instance to connect to.
    2) Choose your DB object to import to the Model. You can expand Table node to select any table you want to import to the Model. Expand Stored Procedure node to select your Stored Precedure as well.  Click Finish to finish the import.
    3) Right click the DB model designer to select Add/Function Import. Give the function a name (same name as your SP would be fine) and select the Stored Procedure you want to map. If your SP returns only one field, you can map the return result to a collection of scalars. If your SP returns more than one field, you could either map the return result to a collection or Entity (if all the field are from a single table) or a collection of Complex types. 
    If you want to use Complex type, you can click Get Column button to get all the columns for your SP. Then click Create new Complex type button to create this Complex type. 
    4) Add a Domain Service class to the Web project. Select the DataModel you just created as the DataContext of this Service. Select all the entitis you want expose to the client. The service functions should be generated for those entities.  
    
    5) You may not see the Complex type in the Entity list. You have to manully add a query function for your SP in your Service:
    Say your SP is called SP1, the Complex type you generated is called SP1_Result. 
    
    Add the following code in your Domain Service class:
    
    public IQueryable<SP1_Result> SP1()
            {
                return this.ObjectContext.SP1().AsQueryable();            
            }
    Now you can compile your project. You might get an error like this: "SP1_Result does not have a Key" (if you not on RIA service SP1 beta).  If you do, you need to do the following in the service metadata file:
    
    Added a SP1_Result metadata class and tagged the Key field:
    [MetadataTypeAttribute(typeof(SP1_Result.SP1_ResultMetadata))]
        public partial class SP1_Result
        {
            internal sealed class SP1_ResultMetadata
            {
                [Key]
                public int MyId;  // Change MyId to the ID field of your SP_Result
            }
        } 
    6) Compile your solution. Now you have SP1_Result exposed to the client. Check the generated file, you should see SP1_Result is generated as an Entity class. Now you can access DomainContext.SP1Query and DomainContext.SP1_Results in your Silverlight code.  You can treat it as you do with any other Entity(the entity mapped to a table) class.
    Hope this is clear enough.
    
    ------------------

     

    Specifically for me:

    //SPROC
            public IQueryable<sp_InventoryNoParam_Result> GetSPInventories()
            {
                return this.ObjectContext.sp_InventoryNoParam().AsQueryable();
            }


     

    //SPROC
        [MetadataTypeAttribute(typeof(sp_InventoryNoParam_Result.sp_InventoryNoParam_ResultMetadata))]
        public partial class sp_InventoryNoParam_Result
        {
    
            internal sealed class sp_InventoryNoParam_ResultMetadata
            {
    
                // Metadata classes are not meant to be instantiated.
                private sp_InventoryNoParam_ResultMetadata()
                {
                }
    
                public int POLineID { get; set; }
                public string chopMark { get; set; }
                public Nullable<decimal> convPounds { get; set; }
                public Nullable<decimal> poActLbs { get; set; }
                public Nullable<decimal> soActLbs { get; set; }
                public string DensityShort { get; set; }
                public string gradeName { get; set; }
                public bool isCert { get; set; }
                public bool isDecaf { get; set; }
                public bool isEuroPrep { get; set; }
                public bool isFairTrade { get; set; }
                public bool isOrganic { get; set; }
                public bool isRainForest { get; set; }
                public string lotNumber { get; set; }
                public string LengthName { get; set; }
                public string origin { get; set; }
                public string packDesc { get; set; }
                public Nullable<decimal> quantity { get; set; }
                public string ScreenName { get; set; }
                public string speciesID { get; set; }
                public string varietal { get; set; }
                public decimal basePrice { get; set; }
                public Nullable<decimal> diffPrice { get; set; }
                public Nullable<decimal> effPrice { get; set; }
                public string whseNumber { get; set; }
                public int POLedgerID { get; set; }
                public string cropYr { get; set; }
            }
        }


     

    The SPROC returns 50 rows and is working fine.  During the function import I created the ComplexType sp_InventoryNoParam_Result and it shows in the Model Browser.  The Function Imports shows the SPROC (sp_InventoryNoParam) and the Store shows the SP as sp_InventoryNoParam so all looks good there.

    Any thoughts on how to resolve would be greatly appreciated.  As mentioned it compiles fine it just isn't available within my context.

     

    Monday, June 6, 2011 9:54 AM

Answers

  • It depends. If you mapped the stored procedure to an entity (which means it has a primary key in the model) then it should be loaded with the Query. If it isn't an entity then it is a complex type and must be passed thorugh an Invoke (and will have no change tracking, SubmitChanges ability, etc.)

    Monday, June 6, 2011 2:22 PM

All replies

  • I needed to mark the method a [Query].

    Just for information, shouldn't this be [Invoke] as it returns its own generated complextype?

    Thanks,

    Mark

     

     

    Monday, June 6, 2011 10:49 AM
  • It depends. If you mapped the stored procedure to an entity (which means it has a primary key in the model) then it should be loaded with the Query. If it isn't an entity then it is a complex type and must be passed thorugh an Invoke (and will have no change tracking, SubmitChanges ability, etc.)

    Monday, June 6, 2011 2:22 PM
  • Hi Colin,

    This is exactly what I thought.  These SPROC's are just for report generation.  It has its own ComplexType from adding the function to the model but I was unable to get it to work with Invoke.

    Monday, June 6, 2011 2:32 PM
  • Also, make sure you are on SP1.

    Monday, June 6, 2011 2:40 PM
  • I'm on the 2 preview ATM ... perhaps that's the difficulty

    Monday, June 6, 2011 2:42 PM
  • It shouldn't be, I should have said at least SP1.

    Monday, June 6, 2011 2:46 PM