Domain Model - Stored Procedure - Result Mapping

[Kurt]

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;
        }

[Serge]

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

[Daniel Plomp]

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

[Serge]

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

[Daniel Plomp]

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

[sitefinitysteve]

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;
        }

[sitefinitysteve]

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

[Alexander]

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