Direct SQL Queries
- 6 minutes to read
In XPO, you can execute direct SQL queries against a database and obtain query results as scalar values, result sets or object collections.
Executing SQL Statements
To execute a SQL statement that does not produce a result set, call the Session.ExecuteNonQuery method as shown below.
unitOfWork.ExecuteNonQuery("UPDATE [Northwind].[dbo].[Order Details]" +
"SET [Discount] = 0.15 WHERE [UnitPrice] > 100");
To execute a SQL query and obtain a scalar value, call the Session.ExecuteScalar method.
public int GetEmployeeOrdersCount(int employeeId) {
return (int)unitOfWork.ExecuteScalar(string.Format(
"SELECT COUNT(*) FROM [Northwind].[dbo].[Orders] " +
"WHERE [EmployeeID] = {0}", employeeId));
}
To execute a SQL query and obtain a result set, call the Session.ExecuteQuery method.
using DevExpress.Xpo.DB;
// ...
static string queryString = "SELECT EmployeeID, (FirstName + ' ' " +
"+ LastName) as Name, City, Country FROM " +
"[Northwind].[dbo].[Employees]";
public SelectedData GetEmployeesSimpleData() {
return unitOfWork.ExecuteQuery(queryString);
}
Visualizing Query Results
To visualize query results, you can:
- Load a result set into an XPDataView via the XPDataView.LoadData or XPDataView.LoadOrderedData method.
- Retrieve query results as a collection of objects, via the Session.GetObjectsFromQuery or Session.GetObjectsFromQuery<T> method.
To accomplish this, you need to provide a non-persistent class whose members specify a result set’s column structure. This class will be used to map result set columns to XPDataView columns or object properties.
In the following code example, the EmployeeSimple class corresponds to a result set returned via the GetEmployeesSimpleData function shown above. This class is used to populate an XPDataView with columns, and to obtain a collection of objects from a result set.
[NonPersistent]
public class EmployeeSimple : XPLiteObject {
[Key]
public int EmployeeID {
get { return fEmployeeID; }
set { SetPropertyValue(nameof(EmployeeID), ref fEmployeeID, value); }
}
int fEmployeeID;
public string Name {
get { return fName; }
set { SetPropertyValue(nameof(Name), ref fName, value); }
}
string fName;
public string City {
get { return fCity; }
set { SetPropertyValue(nameof(City), ref fCity, value); }
}
string fCity;
public string Country {
get { return fCountry; }
set { SetPropertyValue(nameof(Country), ref fCountry, value); }
}
string fCountry;
public EmployeeSimple(Session session) : base(session) { }
}
// ...
// Populate an XPDataView with columns based on the auxiliary class.
xpDataView1.PopulateProperties(unitOfWork.GetClassInfo<EmployeeSimple>());
// Load data from a query's result set to an XPDataView.
xpDataView1.LoadData(GetEmployeesSimpleData());
// Retrieve data from a query into a collection of objects.
ICollection<EmployeeSimple> collection =
unitOfWork.GetObjectsFromQuery<EmployeeSimple>(queryString);
If you do not want to show all class members in an XPDataView or if their order differs from the result set, then you can provide additional mapping information to obtain the proper columns in the correct order, as shown below.
static string queryOrderedString = "SELECT (FirstName + ' ' + LastName) " +
"as Name, Country, EmployeeID FROM [Northwind].[dbo].[Employees]";
public SelectedData GetEmployeesSimpleDataOrdered()
{
// Columns are mixed and the 'City' column is removed from the query.
return unitOfWork1.ExecuteQuery(queryOrderedString);
}
// Define a mapping array that specifies the order of columns in a result set.
static LoadDataMemberOrderItem[] employeesLoadOrder = new LoadDataMemberOrderItem[]
{
new LoadDataMemberOrderItem(2, "EmployeeID"),
new LoadDataMemberOrderItem(0, "Name"),
new LoadDataMemberOrderItem(1, "Country")
};
// Populate an XPDataView with columns and load data using the specified mapping array.
xpDataView1.PopulatePropertiesOrdered(unitOfWork.GetClassInfo<EmployeeSimple>(), employeesLoadOrder);
xpDataView1.LoadOrderedData(employeesLoadOrder, GetEmployeesSimpleDataOrdered());
// Retrieve data from a result set into a collection of objects using the specified mapping array.
ICollection<EmployeeSimple> collection =
unitOfWork.GetObjectsFromQuery<EmployeeSimple>(employeesLoadOrder, queryOrderedString);
The examples above demonstrate a strongly-typed approach, where XPDataView automatically retrieves properties from a non-persistent classe. XPDataView also can work in untyped mode. To use XPDataView without non-persistent classes, populate the XPDataView.Properties collection:
Session session = new Session();
SelectedData data = session.ExecuteSproc("sprocName", parameters);
IList<string> propertyNames = new List<string>() { "ProductName", "Total" };
IList<Type> propertyTypes = new List<Type>() { typeof(string), typeof(int) };
XPDataView dataView = new XPDataView(session.Dictionary, propertyNames, propertyTypes);
dataView.LoadData(data);