Domain Model with Stored Proc

[Steven]

Hi,

With the domain model, I can do just fine with tables and views and get to show them in datagrid (silverlight), but on the other hand not with stored proc. All I get is a count of the columns with object[] return value. Is there any samples, docs? I use Telerik OpenAccess ORM   2010.2.714.1 with mssql 08.

Thanks

[Petko_I]

Hello Steven,

You have come across a rather delicate issue. Since you are dealing with Silverlight, I assume you are using some kind of a WCF service to load your data. Sending an array of objects with your service methods is something that WCF services do not support innately. A DataContract is required in order to get the right data transported without losses which suggests strong typing. Having an array of anonymous objects as a return type for the service methods leads to incorrect serialization in most cases and that is why it is not recommended to try to send such objects. An alternative approach to stored procedures for handling more complex result sets as a binding source for a grid is to use LINQ to retrieve the data you need, expose a service method which returns a strongly typed collection of objects and use the asynchronous invocation of the method to populate your grid. If you must use stored procedures then I suggest casting the retrieved objects to something that can be serialized and sent safely with the service. If you know exactly what type of objects is retrieved with the stored procedures you can do the following:

Create a partial class that extends the definition of the Entity Diagrams context. Expose the stored procedures as methods which return a strongly typed array. Creating the partial class in a separate file guarantees that the code will not be lost when changes are introduced to the domain model as code is generated again. Assuming you have mapped the stored procedures and you can see them in the Model Schema Explorer, you can just cut the stored procedures related code from the EntityDiagrams file where the definition of the context resides and paste it to the new .cs file.  

public partial class NorthwindOAEntityDiagrams
{
    public Order[] GetOrdersByEmployeeLastName(string employeeLastName)
    {
        SqlParameter parameterEmployeeLastName =
            new SqlParameter("employeeLastName", OpenAccessType.Varchar);

        List<SqlParameter> sqlParameters = new List<SqlParameter>()
        {
            parameterEmployeeLastName
        };

        Order[] queryResult =
            this.ExecuteStoredProcedure<Order>(
                "'GetOrdersByEmployeeLastName' ?",
                sqlParameters,
                employeeLastName);
        return queryResult;
    }

}

Even if you manage to load an array of anonymous objects through the service methods, the array cannot be bound correctly to a grid as you have already found out.

We have automated the process of creating WCF services to a great extent. If you haven’t already looked at our help resources, I propose you spend some time to get acquainted with what we offer:

Using WCF RIA Services

Using WCF Data Services

Do not hesitate to contact us if you need to continue the discussion on this or other topics. I would just ask you to provide some more information on the type of services you have chosen.

Kind regards,
Petko_I
the Telerik team

[Steven]

Thanks for the reply. Actually I was for the Add Function Import dialog like the one from Microsoft for edmx...

[Petko_I]

Hi Steven,

We do not have a dedicated UI with which to customize the stored procedure mappings – and more specifically a tool to select the return type. However, you can modify the return type of the method mapped to a stored procedure as I suggested in the previous reply. I would like to mention that we will introduce a new wizard to expand the functionality of forward mapping stored procedures. This exciting feature is planned to be available for Q3.

Regards,
Petko_I
the Telerik team