Gary's Blog

In the comments to my previous blog post about executing stored procedures from XPO, you asked how you could execute a SQL query from within a persistent class, so in this post I thought I’d show you how it is done.

Taking an example from the previous post where Form1 contains a UnitOfWork, a XPDataView and a GridControl, and where XPDataView is a data source for a GridControl.

Firstly, you can simply execute the query, for example, the update discounts for orders:

public void UpdateDiscount(out int affectedRows) 
{
    affectedRows = unitOfWork1.ExecuteNonQuery("UPDATE [Northwind]" +
        ".[dbo].[Order Details] SET [Discount] = 0.15 WHERE "+
        "[UnitPrice] > 50");
}

Secondly, you can request and obtain a scalar value, for example, if you need to know the number of orders from a specific employee:

public int GetEmployeeOrdersCount(int employeeId)
{
    return (int)unitOfWork1.ExecuteScalar(string.Format(
        "SELECT COUNT(*) FROM [Northwind].[dbo].[Orders] " +
        "WHERE [EmployeeID] = {0}", employeeId));        
}

Thirdly, you can run a query that returns a result set:

using DevExpress.Xpo.DB;

//…

public SelectedData GetEmployeesSimpleData() 
{
    return unitOfWork1.ExecuteQuery("SELECT EmployeeID, (FirstName + ' ' " +
        "+ LastName) as Name, City, Country FROM " +
        "[Northwind].[dbo].[Employees]");
}

You will have to describe the class so that the data can be loaded into objects or a XPDataView:

[NonPersistent]
public class EmployeeSimple : XPLiteObject 
{
    [Key]
    public int EmployeeID;
    public string Name;
    public string City;
    public string Country;
    public EmployeeSimple(Session session) : base(session) { }
}

And since the order of fields in the class is the same as the order in the query, then the objects can be loaded as follows:

public ICollection<EmployeeSimple> GetEmployeesSimple()
{
    return unitOfWork1.GetObjectsFromQuery<EmployeeSimple>("SELECT " +
        "EmployeeID, (FirstName + ' ' + LastName) as Name, City, Country " +
        "FROM [Northwind].[dbo].[Employees]");
}

To load data into the XPDataView simply amend the  Form1_Load event as follows:

private void Form1_Load(object sender, EventArgs e)
{
    //Fill information about the properties of a class EmployeeSimple
    xpDataView1.FillProperties(unitOfWork1.GetClassInfo<EmployeeSimple>());

    //Load the data
    xpDataView1.LoadData(GetEmployeesSimpleData());
}

Executing that code will give you the following result:

If you don’t want to show all the fields, or if their order differs from the request, then you can provide additional information to obtain the correct fields in the correct order:

public SelectedData GetEmployeesDataForOrderExample()
{
    //Fields are mixed, and the field 'City' removed from the query
    return unitOfWork1.ExecuteQuery("SELECT (FirstName + ' ' + LastName) " +
    "as Name, Country, EmployeeID FROM [Northwind].[dbo].[Employees]");
}

//Array to specify the order of the fields in the query
static LoadDataMemberOrderItem[] employeesLoadOrder = new LoadDataMemberOrderItem[] 
{
    new LoadDataMemberOrderItem(2, "EmployeeID"),
    new LoadDataMemberOrderItem(0, "Name"),
    new LoadDataMemberOrderItem(1, "Country")

};

And once again modify the Form1_Load event:

private void Form1_Load(object sender, EventArgs e)
{
    XPClassInfo employeesClassInfo = unitOfWork1.GetClassInfo<EmployeeSimple>();

    //Using an array employeesLoadOrder
    xpDataView1.FillPropertiesOrdered(employeesClassInfo, employeesLoadOrder);
    xpDataView1.LoadOrderedData(employeesLoadOrder, GetEmployeesDataForOrderExample());
}

To get the following result:

I hope that answers some of your questions regarding SQL queries with 2010.2.

That’s all for this post, so until next time, happy XPOing!