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.
With the new release there’ll be two ways of working with stored procedures in XPO:
- Direct calling of existing stored procedures and the ability to handle returned data;
- 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:
- SelectedData 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.
- 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:
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
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:
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.
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:
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.
The static NorthwindSprocHelper class is a helper class that works with stored procedures from the Northwind database. This class provides the following methods:
- ExecCustOrdersOrders – calls the CustOrdersOrders stored procedure via the ExecSproc method and returns a result set.
- ExecCustOrdersOrdersIntoObjects – calls the stored procedure via the GetObjectsFromSproc methods and returns a collection of CustOrdersOrders objects.
- 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.
The following code snipped demonstrates how to use the XPDataView class in your code:
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:
In the property grid for the unitOfWork1 component set the connection string to the Northwind database:
XpoProvider=MSSqlServer;data source=localhost;integrated security=SSPI;initial catalog=Northwind
Then set the Session property of the xpDataView1 to unitOfWork1.
Finally, select xpDataView1 as a data source of the gridControl1 component.
To load the data from the stored procedure write the following code in the form’s Load event handler:
private void Form1_Load(object sender, EventArgs e) {
NorthwindSprocHelper.ExecCustOrdersOrdersIntoDataView(xpDataView1, session, "HUNGC");
}
Then run the application to see the data:
That’s it for this post, in the next one we’ll look at the second method of using stored procedures in XPO 10.2. Until then, happy XPO’ing! :-)