XPO – (Gary's post)Stored Procedure Support Coming in V2010 Vol 2 Part1

原文：http://community.devexpress.com/blogs/garyshort/archive/2010/09/22/xpo-stored-procedure-support-coming-in-v2010-vol-2.aspx

XPO – Stored Procedure Support Coming in V2010 Vol 2 --Part 1

Unless you work in a very small software shop, or are a ‘one man band’, the chances are you don’t ‘own’ the data that you are trying to connect to. Between you and it there will normally be a DBA. You know the type, a big burly bruiser of a guy, telling you ‘if your name ain’t on the list you ain’t getting in’. You have to get passed this ‘gatekeeper’ if you want to access any of ‘his’ data. Because of this, any good ORM tool has to have ‘out of the box’ support for stored procedures, and I’m delighted to say that, as of V10.2, XPO will have this support.
除非你在一家很小的软件商店工作，或者在一个个体中，很多情况下你不连接到自己的数据。你和它之间通常会有一个数据库管理员。你知道的类型，一个人高马大的家伙，告诉你，如果你的名字不在他的名单上你就不能进入。如果你要访问他的任何数据，你就不得不通过他的认证。正因如此，任何好的ORM工具不得不支持存储过程，我非常高兴的说，在V10.2版中，XPO将支持存储过程。

With the new release there’ll be two ways of working with stored procedures in XPO:
随着新版发布，在XPO中，将有两种方法支持存储过程：

1. Direct calling of existing stored procedures and the ability to handle returned data;
   直接调用已存在的存储过程，能处理返回数据；
2. Mapping of persistent classes on views in the database with help of INSTEAD-OF triggers and stored procedures.
   用数据库中视图持映射久类代替触发器和存储过程。

I guess most people will be using the first variant, so let’s go ahead and check out how that’ll work. Firstly, two methods have been introduced into the Session class:
我猜，大多数人将用第一种方式，因此，让我们继续，看看，那将如何工作。首先，在会话类中介绍两个方法

1. Selected Data ExecuteSproc(string sprocName, params OperandValue[] parameters), where sprocName is the name of the stored procedure you wish to execute and parameters are the parameters you wish to pass to this stored procedure. This method returns the result in a SelectedData instance.
   选择数据ExecuteSproc(string sprocName, params OperandValue[] parameters)，参数sprocName：你要执行的存储过程名称，parameters:你要执行的存储过程的参数。这个方法返回的结果在SelectedData实例中。
2. ICollection<T> GetObjectsFromSproc<T>(string sprocName, params OperandValue[] parameters).
   This method enables you to load data returned by the stored procedure into non-persistent classes.
   这个方法能使你给非持久类加载存储过程返回的数据。

The Persistent Class Wizard has been extended to help you out when working with stored procedures. So let’s take a look at that now:
已扩展这个持久类向导，帮助你在工作中使用存储过程。因此，现在让我们看看。

To use the Wizard choose the Add New Item command:
要使用向导，选择Add New Item命令：

Then select the Persistent Classes 10.2 item template, change the file name as required and press Add, specify the connection information and hit next
然后，选择Persistent Classes 10.2项目模板，更改为你需求的文件名称并按Add,指定连接信息并点next。

In the next step, the wizard displays a list of tables and their columns that can be mapped to persistent objects. We will select the EmployeeSplit table (just for demo purposes) and then press Next:
在接下了一步，向导显示一个表的列表和他们的列，能被映射为持久对象。我们将选择EmployeeSplit表，然后按Next:

Now select the stored procedures we interested in, and select the required columns that will be included in the result set returned by stored procedure then press Finish.
现在，选择我们感兴趣的存储过程，并选择所需的列，将包括在存储过程返回的结果集，然后按Finish。

In this example we will choose the CustOrdersOrders stored procecdure, which will return the OrderID, OrderDate, RequireDate, ShippedDate columns. Just for this demo we won’t include the ShippedDate column. After we press Finish, the wizard will generate the following code:

在这个例子中，我们将选择CustOrdersOrders存储过程，将返回OrderID,OrderDate,RequireDate,ShippedDate列。正由于这个演示，我们不包括ShippedDate列。我们按Finsish后，向导生成下面代码：

 

using System;

using DevExpress.Xpo;

namespace Northwind {

    [Persistent("EmployeeSplit_xpoView")]

    public class EmployeeSplit : XPLiteObject {

        int fID;

        [Key]

        public int ID {

            get { return fID; }

            set { SetPropertyValue<int>("ID", ref fID, value); }

        }

        string fExtension;

        [Size(4)]

        public string Extension {

            get { return fExtension; }

            set { SetPropertyValue<string>("Extension", ref fExtension, value); }

        }

        string fPhotoPath;

        [Size(255)]

        public string PhotoPath {

            get { return fPhotoPath; }

            set { SetPropertyValue<string>("PhotoPath", ref fPhotoPath, value); }

        }

        public EmployeeSplit(Session session) : base(session) { }

        public EmployeeSplit() : base(Session.DefaultSession) { }

        public override void AfterConstruction() { base.AfterConstruction(); }

    }

 

    [NonPersistent]

    public class CustOrdersOrders : PersistentBase {

        int fOrderID;

        public int OrderID {

            get { return fOrderID; }

            set { SetPropertyValue<int>("OrderID", ref fOrderID, value); }

        }

        DateTime fOrderDate;

        public DateTime OrderDate {

            get { return fOrderDate; }

            set { SetPropertyValue<DateTime>("OrderDate", ref fOrderDate, value); }

        }

        DateTime fRequiredDate;

        public DateTime RequiredDate {

            get { return fRequiredDate; }

            set { SetPropertyValue<DateTime>("RequiredDate", ref fRequiredDate, value); }

        }

        public CustOrdersOrders(Session session) : base(session) { }

        public CustOrdersOrders() : base(Session.DefaultSession) { }

        public override void AfterConstruction() { base.AfterConstruction(); }

    }

    public static class NorthwindSprocHelper {

 

        public static DevExpress.Xpo.DB.SelectedData ExecCustOrdersOrders(Session session, string CustomerID){

            return session.ExecuteSproc("CustOrdersOrders", CustomerID);

        }

 

        static LoadDataMemberOrderItem[] CustOrdersOrdersOrderArray = {new LoadDataMemberOrderItem(0, "OrderID"),
          new LoadDataMemberOrderItem(1, "OrderDate"), new LoadDataMemberOrderItem(2, "RequiredDate")};

 

        public static System.Collections.Generic.ICollection<CustOrdersOrders> ExecCustOrdersOrdersIntoObjects(
           Session session, string CustomerID){

            return session.GetObjectsFromSproc<CustOrdersOrders>(CustOrdersOrdersOrderArray, "CustOrdersOrders",
               CustomerID);

        }

 

        public static XPDataView ExecCustOrdersOrdersIntoDataView(Session session, string CustomerID){

            DevExpress.Xpo.DB.SelectedData sprocData = session.ExecuteSproc("CustOrdersOrders", CustomerID);

            return new XPDataView(session.Dictionary, session.GetClassInfo(typeof(CustOrdersOrders)),
               CustOrdersOrdersOrderArray, sprocData);

        }

        public static void ExecCustOrdersOrdersIntoDataView(XPDataView dataView, Session session,
           string CustomerID){

            DevExpress.Xpo.DB.SelectedData sprocData = session.ExecuteSproc("CustOrdersOrders", CustomerID);

            dataView.PopulatePropertiesOrdered(session.GetClassInfo(typeof(CustOrdersOrders)),
               CustOrdersOrdersOrderArray);

            dataView.LoadOrderedData(CustOrdersOrdersOrderArray, sprocData);

        }

    }

}

In this example we’re really only interested in the CustOrdersOrders class, which was generated for our stored procedure. Notice that this class is non-persistent (it’s decorated with the [NonPersistent] attribute. The properties of this class correspond to columns of the result set we configured earlier.
在这个例子中，我真正仅对CustOrdersOrders类感兴趣，这是我们的存储过程生成。注意这个类是非持久的(他用[NonPersistent]特性修饰)。这个类的属性对应我们先前配置的结果集的列。

The static NorthwindSprocHelper class is a helper class that works with stored procedures from the Northwind database. This class provides the following methods:
这个静态的NorthwindSprocHelper类是一个帮助类，使用在Northwind数据库中存储过程工作。这个类提供下列方法：

• ExecCustOrdersOrders – calls the CustOrdersOrders stored procedure via the ExecSproc method and returns a result set.
  ExecCustOrdersOrders – 通过ExecSproc方法调用CustOrdersOrders存储过程并返回一个结果集。
• ExecCustOrdersOrdersIntoObjects – calls the stored procedure via the GetObjectsFromSproc methods and returns a collection of CustOrdersOrders objects.
  ExecCustOrdersOrdersIntoObjects – 通过GetObjectsFromSproc方法调用这个存储过程并返回一个结果集。
• ExecCustOrdersOrdersIntoDataView – calls the stored procedure via the ExecSproc method and returns a XPDataView class instance containing the results from the stored procedure execution. This method also has an overload that also calls the stored procedure but fills the XPDataView object passed to it as a parameter.
  ExecCustOrdersOrdersIntoDataView  - 通过ExecSproc方法调用这个存储过程并返回存储过程执行的结果集，一个XPDataView类实例。这个方法也有一个覆写，也调用这个存储过程，但通过它作为参数填充XPDataView对象。

The following code snipped demonstrates how to use the XPDataView class in your code:
下面代码片段演示在你的代码中如何使用XPDataView类

public static bool CheckRequiredDate(Session session) {

    XPDataView view = new XPDataView(session);

    NorthwindSprocHelper.ExecCustOrdersOrdersIntoDataView(view, session, "HUNGC");

    foreach(DataViewRecord record in view) {

        if(record["RequiredDate"] != null && ((DateTime)record["RequiredDate"]) > DateTime.Now) {

            return true;

        }

    }

    return false;

}

It’s worth mentioning that XPDataView can be used as a data source. To demonstrate this usage, let’s create a new Windows Forms application and add a new Form into it. Then drop the GridControl(gridControl1), UnitOfWork(unitOfWork1)and XPDataView(xpDataView1)components onto this form:
值得说的是，XPDataView可以被用作数据源。要演示这个作用，让我们建立一个新的Windows窗体应用程序。然后拖拽GridControl(gridControl1),UnitOfWork(unitOfWork1)和XPDataView(xpDataView1)组件到这个窗体上：

In the property grid for the unitOfWork1 component set the connection string to the Northwind database:
在unitOfWork1组件属性格子中设置数据库Northwind的连接串：

XpoProvider=MSSqlServer;data source=localhost;integrated security=SSPI;initial catalog=Northwind

Then set the Session property of the xpDataView1 to unitOfWork1.
然后，设置xpDataView1的Session属性为unitOfWork1。

Finally, select xpDataView1 as a data source of the gridControl1 component.
最后，设置gridControl组件的数据源为xpDataView1。

To load the data from the stored procedure write the following code in the form’s Load event handler:
要从存储过程加载数据，需在窗体的Load时间处理中写以下代码：

private void Form1_Load(object sender, EventArgs e) {

    NorthwindSprocHelper.ExecCustOrdersOrdersIntoDataView(xpDataView1, session, "HUNGC");

}

Then run the application to see the data:
然后运行应用程序查看数据：

 

欢迎转载，转载请注明出处：http://www.cnblogs.com/Tonyyang/