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";
        orderSearchOptions.Classes.Add("PurchaseOrder");
        var whereClause = TextBox1.Text.Trim();
        var whereMetaClause = TextBox2.Text.Trim();
        if (!string.IsNullOrEmpty(whereClause))
        {
            orderSearchParams.SqlWhereClause =  whereClause;
        }
        if (!string.IsNullOrEmpty(whereMetaClause))
        {
            orderSearchParams.SqlMetaWhereClause = whereMetaClause;
        }
        try
        {
            var orders = Mediachase.Commerce.Orders.OrderContext.Current.FindPurchaseOrders(orderSearchParams, orderSearchOptions);
            Label3.Text = "";
            GridView1.DataSource = orders;
            GridView1.DataBind();
        }
        catch(Exception ex)
        {
            Label3.Text = "Error:" + ex.InnerException.Message;
        }
    }
</script>

<!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>
    </head>
    <body>
        <form id="aspnetForm" runat="server">
            <div class="epi-contentContainer epi-padding">
                <div class="epi-contentArea">
                    <h1 class="EP-prefix">Order search</h1>
                    <div>
                        <asp:Label ID="Label1" runat="server" Text="SqlWhereClause:"></asp:Label>
                        <asp:TextBox ID="TextBox1" runat="server" Width="500px"></asp:TextBox>
                        <br />
                    </div>
                </div>
            </div>
            <asp:Label ID="Label2" runat="server" Text="SqlMetaWhere clause:"></asp:Label>
            <asp:TextBox ID="TextBox2" runat="server" Width="500px"></asp:TextBox>
            <p>
                <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search" />
            </p>
            <p>
                <asp:Label ID="Label3" runat="server"></asp:Label>
            </p>
            <asp:GridView ID="GridView1" runat="server">
            </asp:GridView>
        </form>
    </body>
</html>

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!