直接SQL查询
- 2020年11月12日
- 6分钟阅读
在XPO中,您可以对数据库执行直接SQL查询,并以标量值,结果集或对象集合的形式获取查询结果。
#执行SQL语句
要执行不产生结果集的SQL语句,请调用Session.ExecuteNonQuery方法,如下所示。
unitOfWork.ExecuteNonQuery("UPDATE [Northwind].[dbo].[Order Details]" +
"SET [Discount] = 0.15 WHERE [UnitPrice] > 100");
要执行SQL查询并获取标量值,请调用Session.ExecuteScalar方法。
public int GetEmployeeOrdersCount(int employeeId) {
return (int)unitOfWork.ExecuteScalar(string.Format(
"SELECT COUNT(*) FROM [Northwind].[dbo].[Orders] " +
"WHERE [EmployeeID] = {0}", employeeId));
}
要执行SQL查询并获取结果集,请调用Session.ExecuteQuery方法。
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);
}
#可视化查询结果
要显示查询结果,您可以:
- 加载一个结果集成XPDataView经由XPDataView.LoadData或XPDataView.LoadOrderedData方法。
- 通过Session.GetObjectsFromQuery或Session.GetObjectsFromQuery 方法将查询结果作为对象的集合进行检索。
为此,您需要提供一个非持久类,其成员指定结果集的列结构。该类将用于将结果集列映射到XPDataView列或对象属性。
在下面的代码示例中,EmployeeSimple类对应于通过上述GetEmployeesSimpleData函数返回的结果集。此类用于用列填充XPDataView,并从结果集中获取对象的集合。
[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);
如果您不想在XPDataView中显示所有类成员,或者它们的顺序与结果集不同,则可以提供其他映射信息,以正确的顺序获取正确的列,如下所示。
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);
上面的示例演示了一种强类型的方法,其中XPDataView自动从非持久类检索属性。XPDataView也可以在非键入模式下工作。要在没有非持久类的情况下使用XPDataView,请填充XPDataView.Properties集合:
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);