原文：http://community.devexpress.com/blogs/garyshort/archive/
直接执行SQL查询
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.
在以前的博客谈到XPO执行存储过程的评论，你问你如何在一个持久类内执行SQL查询，因此，在这篇，我想我将展示如何做到这点。
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.
以以前的一个例子为例，那里的Form1包含一个UnitOfWork,一个XPDataView和一个GridControl,XPDataView是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:
为了给对象或一个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:
要给XPDataView加载数据只需修改Form1_Load事件如下：
private void Form1_Load(object sender, EventArgs e)
{
//Fill information about the properties of a class EmployeeSimple
//填充类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
//混合字段，移除’City’字段
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:
再次修改Form1_Load事件：
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.
我希望回答了你们的关于与2010.2SQL查询的一些问题
欢迎转载，转载请注明出处：http://www.cnblogs.com/Tonyyang/