Quan Mai
Oct 6, 2014
(4 votes)

Order search–made easy

No, I’ll not write a long, detailed, in-depth blog post as Shannon did it in http://world.episerver.com/Blogs/Shannon-Gray/Dates/2012/12/EPiServer-Commerce-Order-Search-Made-Easy/

When I read that blog – I understand how complex our order search APIs is. It’s powerful, yes, but it is also painful to write correctly. Life will be much easier if we can use some strongly typed query, instead of writing complex, error-prone SQL query. But that’ll not happen in near future, and we must live with it.

I decided to to something, while cannot solve entire problem, can still help you to ease the process of searching orders.

And this is it:

<%@ Page Language="C#" AutoEventWireup="true" %>

<script runat="server">  
    /// <summary>
    /// Handles the Load event of the Page control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)

    protected void Button1_Click(object sender, EventArgs e)
        var orderSearchOptions = new Mediachase.Commerce.Orders.Search.OrderSearchOptions();
        var orderSearchParams = new Mediachase.Commerce.Orders.Search.OrderSearchParameters();
        orderSearchOptions.Namespace = "Mediachase.Commerce.Orders";
        var whereClause = TextBox1.Text.Trim();
        var whereMetaClause = TextBox2.Text.Trim();
        if (!string.IsNullOrEmpty(whereClause))
            orderSearchParams.SqlWhereClause =  whereClause;
        if (!string.IsNullOrEmpty(whereMetaClause))
            orderSearchParams.SqlMetaWhereClause = whereMetaClause;
            var orders = Mediachase.Commerce.Orders.OrderContext.Current.FindPurchaseOrders(orderSearchParams, orderSearchOptions);
            Label3.Text = "";
            GridView1.DataSource = orders;
        catch(Exception ex)
            Label3.Text = "Error:" + ex.InnerException.Message;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Order search</title>
        <form id="aspnetForm" runat="server">
            <div class="epi-contentContainer epi-padding">
                <div class="epi-contentArea">
                    <h1 class="EP-prefix">Order search</h1>
                        <asp:Label ID="Label1" runat="server" Text="SqlWhereClause:"></asp:Label>
                        <asp:TextBox ID="TextBox1" runat="server" Width="500px"></asp:TextBox>
                        <br />
            <asp:Label ID="Label2" runat="server" Text="SqlMetaWhere clause:"></asp:Label>
            <asp:TextBox ID="TextBox2" runat="server" Width="500px"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search" />
                <asp:Label ID="Label3" runat="server"></asp:Label>
            <asp:GridView ID="GridView1" runat="server">

Don’t blame me for the boring, unattractive, dull … (insert any negative words here) UI interface – this has not gone through our UI-review process. The idea is simple, you can save this as an .aspx file, and put it in root folder of your site. And now you can write and test your SQL query/Sql Meta query without rebuild and run your website!

And this is how it looks like:

As a rule of thumb, when you search for Purchase orders:

- Any columns in dbo.OrderGroup can appear in SqlWhereClause

- Any columns in dbo.OrderGroup_PurchaseOrder and OrderGroup_PurchaseOrder_Localization can appear in SqlMetaWhere clause.

For more complex query – you might want to take a look at database schemas, but you can always write your query here to test the result.

Hope this helps until we provide a better way to do it!

Oct 06, 2014


Please login to comment.
Latest blogs
Optimizely Search & Navigation - Get autocomplete suggestions in right language

When you are using Optimizely Search & Navigation (Find) to help you generate autocomplete suggestions server side in a multi-language scenario it...

Allan Thraen | Jun 5, 2023 | Syndicated blog

Easy property inheritance in Optimizely CMS

Ever needed a way to inherit property values down through the page hierarchy in Optimizely CMS 12? Now there is an easy way to enable that.

Allan Thraen | Jun 5, 2023 | Syndicated blog

Content Bulk Edit

Bulk Edit is a plugin that allows to make changes to more than one Content at the same time. This can save time by simultaneously updating multiple...

Grzegorz Wiecheć | Jun 5, 2023 | Syndicated blog

Caching & Rendering of metadata from the DAM

For the EPiServer.Cms.WelcomeIntegration.UI 1.2.0 release, we have facilitated the ability to cache and render metadata from assets within the DAM....

Matthew Slim | Jun 2, 2023