Domain Model - Stored Procedure - Result Mapping

8 posts, 0 answers
  1. Kurt
    Kurt avatar
    17 posts
    Member since:
    Aug 2006

    Posted 16 Jul 2010 Link to this post


    I think what I want to do can only be accomplished manually, but thought I would ask anyway as it feels like this should be possible using the designer. 

    When reverse engineering a database model, I had a number of stored procedures that return defined entities. The Open Access Domain Model reverse mapped this fine but  it created very generic function imports. For example

    spProductsGet(@CategioryId INT)  -- turns into -> object[] spProductsGet(int CategoryId)

    What would be helpful is the ability to map the rerun type, and be able to define the method name using the designer. This way I could have it do something like:

    spProductsGet(@CategioryId INT)  -- be mapped to -> IList<Products> GetProducts(int CategoryId)

    And still preserve the ability to make changes and update the model from the db.

    An workaround alternative might be to change the code generator to use generics for the stored procedures.Ex:
    public IList<T> spProductsGet<T>(int? categoryId) where T : class, new()
            {
                SqlParameter parameterCategoryId = new SqlParameter("CategoryId", OpenAccessType.Int32);
     
                List<SqlParameter> sqlParameters = new List<SqlParameter>()
                {
                    parameterCategoryId
                };
     
                IList<T> queryResult = this.ExecuteStoredProcedure<T>("'spProductsGet' ?", sqlParameters, categoryId);
                return queryResult;
            }


  2. Serge
    Admin
    Serge avatar
    375 posts

    Posted 19 Jul 2010 Link to this post

    Hello Kurt,

    We are currently reviewing our goal list for the Q3 release and this particular feature will be part of our discussion. We are considering implementing a way for the user to specify if a certain procedure returns a persistent type. 

    For the time being however I suggest creating a partial class of the Context and writing similar methods as the one you have provided.

    I hope this helps.

    All the best,
    Serge
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 29 Nov 2010 Link to this post

    Hi,

    I've curious if there is a way to set the EntityType for a stored procedure when using the domain model.
    I'm using the latest version of OA.

    Greetings,
    Daniel
  5. Serge
    Admin
    Serge avatar
    375 posts

    Posted 30 Nov 2010 Link to this post

    Hi Daniel,

     At the moment this is not possible however we have planned it and it will be implemented for the next Service Pack or Q1 at the latest. The runtime is fully capable of returning typed result sets though, you can do a workaround at the moment in order to achieve your goal. You should manually write the code executing the stored procedures in methods in a partial class of the context that is generated by the domain model. 

    As you can see by the generated calls to the stored procedures in the context that the ExecuteStoredProcedure API can take a type, and returning a typed result set is doable by just calling the method and providing the desired type as the generic parameter. 

    I hope this is helpful.

    Kind regards,
    Serge
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  6. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 30 Nov 2010 Link to this post

    Hi Serge,

    Thanks for the information.
    I indeed managed to do it through the ExecuteStoredProcedure<> method, so it works.

    Would be nice if this is supported by the designer.

    Regards,
    Daniel
  7. Steve
    Steve avatar
    1851 posts
    Member since:
    Dec 2008

    Posted 05 Dec 2010 Link to this post

    You wouldn't be able to share the code for that would you?

    This compiles and runs, but I don't get results back

    Is this close?

    ** EDIT**
    Yeah, this works fine lol, I was just passing in the params in the wrong order :)

    public List<Dictionary> GetRelatedDictionaryItems(string itemid, string itemType){
     
                List<Telerik.OpenAccess.SqlParameter> parameters = new List<Telerik.OpenAccess.SqlParameter>();
                parameters.Add(new Telerik.OpenAccess.SqlParameter("ItemID", Telerik.OpenAccess.OpenAccessType.Varchar));
                parameters.Add(new Telerik.OpenAccess.SqlParameter("ItemType", Telerik.OpenAccess.OpenAccessType.Varchar));
                 
                List<Dictionary> items = new List<Dictionary>(this.ExecuteStoredProcedure<Dictionary>("GetRelatedDictionaryItems", parameters, new object[] {itemid, itemType}));
     
                return items;
            }
  8. Steve
    Steve avatar
    1851 posts
    Member since:
    Dec 2008

    Posted 05 Dec 2010 Link to this post

    Sorry to hijack this once more ;)

    Is this only able to return types mapped to openaccess?

    Server Error in '/' Application.

    Only persistent types, object or object[] is allowed

  9. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 07 Dec 2010 Link to this post

    Hello Steve,

    Yes, at the moment only persistent classes can be used as return type of stored procedures. A possible workaround is to have a view in the database that encapsulates the columns returned from the stored procedure. Then you can add this view to your domain model and map the persistent class to the stored procedure result.

    Best wishes,
    Alexander
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Back to Top
DevCraft banner