RadDomainDataSource using stored procedure

[Chris Thierry]

Hi, I need to use a TEXTBOX with a radGridView in my silverlight RIA project, when user right something in the textbox, I need to go to the db and seach for this entry and reload the radGridView, my source is not a table, is an stored procedure can I do this using RadDomainDataSource ? is there an sample of doing this ?
Thank you.

[Rossen Hristov]

Hello Chris Thierry,

We will release SP1 tomorrow. There will be an example that does exactly what you want.

Basically, there is no difference between using a table and a stored procedure on the server. After all, all you write on the client is the name of a method that resided on the server. What happens inside this method is of no concern for the client.

Imagine that you have the following code on the server. It uses the GetSalesByCategory stored procedure:

namespace Examples.Web.Services
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Data;
    using System.Linq;
    using System.ServiceModel.DomainServices.EntityFramework;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using Examples.Web.Models;


    // Implements application logic using the salesEntities context.
    // TODO: Add your application logic to these methods or in additional methods.
    // TODO: Wire up authentication (Windows/ASP.NET Forms) and uncomment the following to disable anonymous access
    // Also consider adding roles to restrict access as appropriate.
    // [RequiresAuthentication]
    [EnableClientAccess()]
    public class SalesService : LinqToEntitiesDomainService<salesEntities>
    {

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Orders' query.
        public IQueryable<Order> GetOrders()
        {
            return this.ObjectContext.Orders;
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'ProductCategories' query.
        public IQueryable<ProductCategory> GetProductCategories()
        {
            return this.ObjectContext.ProductCategories;
        }

        public IQueryable<SalesByCategory_Result> GetSalesByCategory(string categoryName, string ordYear)
        {
            return this.ObjectContext.SalesByCategory(categoryName, ordYear).AsQueryable<SalesByCategory_Result>();
        }

        public IList<Year> GetDistinctOrderYears()
        {
            return this.ObjectContext.Orders
                .Where(order => order.OrderDate.HasValue)
                .Select(order => order.OrderDate)
                .Cast<DateTime>()
                .Select(orderDate => orderDate.Year)
                .Distinct()
                .Select(id => new Year() { ID = id })
                .ToList();
        }
    }

    public class Year
    {
        [Key]
        public int ID { get; set; }
    }
}

On the client you can do the following:

<Examples:DomainDataSourceExample x:Class="Telerik.Windows.Examples.DomainDataSource.QueryParameters.Example"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:Examples="clr-namespace:Telerik.Windows.Examples"
    xmlns:telerik="http://schemas.telerik.com/2008/xaml/presentation"
    xmlns:telerikQuickStart="clr-namespace:Telerik.Windows.Controls.QuickStart;assembly=Telerik.Windows.Controls"
    xmlns:services="clr-namespace:Examples.Web.Services"
    xmlns:e="clr-namespace:Examples.Web">
    <Grid>

        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
            <RowDefinition Height="Auto"/>
        </Grid.RowDefinitions>

        <telerik:RadDomainDataSource x:Name="SalesByCategoryDataSource"
                                     QueryName="GetSalesByCategory"
                                     LoadingData="OnSalesByCategoryDataSourceLoadingData"
                                     LoadedData="OnSalesByCategoryDataSourceLoadedData">
            <telerik:RadDomainDataSource.DomainContext>
                <services:SalesContext />
            </telerik:RadDomainDataSource.DomainContext>
            <telerik:RadDomainDataSource.QueryParameters>
                <telerik:QueryParameter ParameterName="categoryName"
                                        Value="{Binding SelectedValue, ElementName=categoryNameComboBox}" />
                <telerik:QueryParameter ParameterName="ordYear"
                                        Value="{Binding SelectedValue, ElementName=ordYearComboBox}" />
            </telerik:RadDomainDataSource.QueryParameters>
        </telerik:RadDomainDataSource>

        <telerik:RadDomainDataSource x:Name="CategoriesDataSource"
                                     AutoLoad="True"
                                     QueryName="GetProductCategories"
                                     LoadedData="OnCategoriesDataSourceLoadedData">
            <telerik:RadDomainDataSource.DomainContext>
                <services:SalesContext />
            </telerik:RadDomainDataSource.DomainContext>
        </telerik:RadDomainDataSource>

        <StackPanel Grid.Row="0" HorizontalAlignment="Left">
            <Grid>
                <Grid.RowDefinitions>
                    <RowDefinition/>
                    <RowDefinition/>
                </Grid.RowDefinitions>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="Auto"/>
                    <ColumnDefinition Width="Auto"/>
                </Grid.ColumnDefinitions>
                <TextBlock Text="Category" Margin="2" VerticalAlignment="Center"/>
                <telerik:RadComboBox x:Name="categoryNameComboBox" Grid.Column="1" Margin="2" VerticalAlignment="Center"
                                     ItemsSource="{Binding DataView, ElementName=CategoriesDataSource}"
                                     SelectedIndex="0"
                                     Width="100"
                                     DisplayMemberPath="CategoryName"
                                     SelectedValuePath="CategoryName"/>
                <TextBlock Text="Year" Grid.Row="1" Margin="2" VerticalAlignment="Center"/>
                <telerik:RadComboBox x:Name="ordYearComboBox"  Margin="2" VerticalAlignment="Center"
                                     Grid.Row="1" Grid.Column="1"
                                     Width="100">
                </telerik:RadComboBox>
            </Grid>
        </StackPanel>

        <telerik:RadGridView x:Name="radGridView" Margin="1" Grid.Row="1"
                             IsReadOnly="True"
                             IsBusy="{Binding IsBusy, ElementName=SalesByCategoryDataSource}"
                             CanUserFreezeColumns="False"
                             ShowGroupPanel="False"
                             RowIndicatorVisibility="Collapsed"
                             AutoGenerateColumns="False"
                             ItemsSource="{Binding DataView, ElementName=SalesByCategoryDataSource}">
            <telerik:RadGridView.Columns>
                <telerik:GridViewDataColumn Header="Product"
                                            DataMemberBinding="{Binding ProductName}"
                                            ShowDistinctFilters="False"/>
                <telerik:GridViewDataColumn Header="Total Purchase"
                                            DataMemberBinding="{Binding TotalPurchase, StringFormat='{}{0:c}'}"
                                            ShowDistinctFilters="False"/>
            </telerik:RadGridView.Columns>
        </telerik:RadGridView>

        <TextBox x:Name="debugTextBox"
                     Grid.Row="2"
                     IsReadOnly="True"
                     VerticalAlignment="Stretch"
                     Margin="0,10,0,0"
                     FontFamily="Courier New"
                     Height="65"
                     VerticalContentAlignment="Top"
                     telerik:StyleManager.Theme="Office_Black"
                     HorizontalScrollBarVisibility="Auto"/>

    </Grid>
</Examples:DomainDataSourceExample>

In other words, RadDomainDataSource does not care whether the thing on the server is a table or stored procedure. The name of the method and its parameters are the ones that matter.

There are many tutorials on the net that show how to add a stored procedure to your Entity Framework model.

I hope this helps.

All the best,
Ross
the Telerik team

[Chris Thierry]

Thank you !!

[Deborah]

Hi,
what happens if I have a RadDomainDataSource, a GridView and a RadDataPager?
If I call the stored procedure (as in sample code posted by Ross) all the resulting rows are passed to the client which provides to do the paging, or the server passes to the client only the rows needed for the actual page?
Thanks,

Deborah

[Rossen Hristov]

Hello,

RadDomainDataSource simply steps on top of WCF RIA Services and delegates all of the work to WCF RIA Services -- it does not communicate directly with the server. It is a direct replacement of the MS DomainDataSource. The only feature it adds on top of the standard MS DomainDataSource is the seamless integration with the other Telerik controls, i.e. when you filter a RadGridView bound to a RadDomainDataSource, the RadDomainDataSource will automatically pick up those filters and prepared a filtered query to send to the server and so on. Same applies for sorting and paging.

Since this is really a WCF RIA Services related question, i.e. you could easily ask exactly the same question by replacing RadDomainDataSource with the MS DomainDataSource, RadGridView with the MS DataGrid and RadDataPager with the MS DataPager, I suggest you take a look at this interesting discussion.

I hope this helps.

Regards,
Ross
the Telerik team

[Dempsey]

Wow, it would be so incredibly nice to have this all wrapped up in a small, working sample project to study, with all the code-behind, etc. This might not be needed by your more experienced users, but it is by me. Would you please consider adding such a sample project?
Thanks

[Rossen Hristov]

Hello,

RadDomainDataSource has absolutely nothing to do with stored procedures. This is WCF RIA Services + SQL Server territory which is beyond the scope of Telerik support. The WCF RIA Services might be the place for such a project.

If you have a sample project with the stock Microsoft WCF RIA Services stuff -- you can simply replace their controls with ours and everything will be the same. That is because controls are client-side things and play no role in stored procedures and WCF RIA Services. They have absolutely nothing to do with stored procedures and databases.

I hope this makes sense.

All the best,
Rossen Hristov
the Telerik team

[Dempsey]

Where does SalesContext get declared, defined, or generated? It isn't at all clear from the text or the code.

[Rossen Hristov]

Hello,

It is the WCF RIA Services project template that reads what is written in the domain service on the server project and generates client-side proxies in the Silverlight project's hidden folder.

For more information, please visit the official WCF RIA Services home page.

Greetings,
Rossen Hristov
the Telerik team