You’ll recall in my previous post on this topic I told you that there would be two ways of working with stored procedures in the forthcoming 2010.2 release, those were:
- 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 then went on to demonstrate how to use the first version. Well guess what? I’m back, and this time we’re going to take a look at that second variant, which works by mapping persistent classes to views in the database, which in turn query data from related tables in the database. Clearly, it’s not possible to add, edit or delete records from a database view and so to solve this problem, we’ll use INSTEAD-OF triggers to redirect the required operations to the stored procedures. To do this we’ll have to perform the following set of tasks for each database table:
- Create a view.
- Map the persistent class to the corresponding View;
- Create INSTEAD-OF triggers for INSERT, UPDATE and DELETE functionality.
- Create stored procedure for INSERT, UPDATE and DELETE functionality.
In the first blog post I demonstrated how to use the Persistent Classes Wizard to get started with stored procedures. Here we will use the same technique, with one difference. In the Generating Persistent Classes for an existing Database dialog, we will check the Generate views and stored procedures for tables access button.
The remaining steps are the same as in the previous post. As a result, the wizard will generate the code shown in the first post as well as the DDL code to generate views, triggers and stored procedures in the database.
For example, this code generates an EmployeeSplit_xpoView view:
CREATE VIEW [EmployeeSplit_xpoView] AS
SELECT
[ID],
[Extension],
[PhotoPath]
FROM [EmployeeSplit]
GO
CREATE PROCEDURE [sp_EmployeeSplit_xpoView_insert]
@ID int,
@Extension nvarchar(4),
@PhotoPath nvarchar(255)
AS
BEGIN
BEGIN TRY
INSERT INTO [EmployeeSplit](
[ID],
[Extension],
[PhotoPath]
)
VALUES(
@ID,
@Extension,
@PhotoPath
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END CATCH
END
GO
CREATE PROCEDURE [sp_EmployeeSplit_xpoView_update]
@ID int,
@old_Extension nvarchar(4),
@Extension nvarchar(4),
@old_PhotoPath nvarchar(255),
@PhotoPath nvarchar(255)
AS
UPDATE [EmployeeSplit] SET
[Extension]=@Extension,
[PhotoPath]=@PhotoPath
WHERE
[ID] = @ID
GO
CREATE PROCEDURE [sp_EmployeeSplit_xpoView_delete]
@ID int,
@old_Extension nvarchar(4),
@old_PhotoPath nvarchar(255)
AS
DELETE FROM [EmployeeSplit] WHERE
[ID] = @ID
GO
And this code generates the respective triggers.
CREATE TRIGGER [t_EmployeeSplit_xpoView_insert]
ON [EmployeeSplit_xpoView]
INSTEAD OF INSERT AS
BEGIN
DECLARE @cur CURSOR
SET @cur = CURSOR FOR
SELECT
[ID],
[Extension],
[PhotoPath]
FROM inserted
OPEN @cur
DECLARE @ID int
DECLARE @Extension nvarchar(4)
DECLARE @PhotoPath nvarchar(255)
FETCH NEXT FROM @cur INTO
@ID,
@Extension,
@PhotoPath
WHILE(@@fetch_status <> -1)
BEGIN
EXEC [sp_EmployeeSplit_xpoView_insert]
@ID,
@Extension,
@PhotoPath
FETCH NEXT FROM @cur INTO
@ID,
@Extension,
@PhotoPath
END
CLOSE @cur
DEALLOCATE @cur
END
GO
CREATE TRIGGER [t_EmployeeSplit_xpoView_update]
ON [EmployeeSplit_xpoView]
INSTEAD OF UPDATE AS
BEGIN
DECLARE @cur CURSOR
SET @cur = CURSOR FOR
SELECT
i.[ID],
d.[Extension] as [old_Extension],
i.[Extension],
d.[PhotoPath] as [old_PhotoPath],
i.[PhotoPath]
FROM
inserted i
INNER JOIN
deleted d
ON
i.[ID] = d.[ID]
OPEN @cur
DECLARE @ID int
DECLARE @old_Extension nvarchar(4)
DECLARE @Extension nvarchar(4)
DECLARE @old_PhotoPath nvarchar(255)
DECLARE @PhotoPath nvarchar(255)
FETCH NEXT FROM @cur INTO
@ID,
@old_Extension,
@Extension,
@old_PhotoPath,
@PhotoPath
WHILE(@@fetch_status <> -1)
BEGIN
EXEC [sp_EmployeeSplit_xpoView_update]
@ID,
@old_Extension,
@Extension,
@old_PhotoPath,
@PhotoPath
FETCH NEXT FROM @cur INTO
@ID,
@old_Extension,
@Extension,
@old_PhotoPath,
@PhotoPath
END
CLOSE @cur
DEALLOCATE @cur
END
GO
CREATE TRIGGER [t_EmployeeSplit_xpoView_delete]
ON [EmployeeSplit_xpoView]
INSTEAD OF DELETE AS
BEGIN
DECLARE @cur CURSOR
SET @cur = CURSOR FOR
SELECT
[ID],
[Extension],
[PhotoPath]
FROM deleted
OPEN @cur
DECLARE @ID int
DECLARE @Extension nvarchar(4)
DECLARE @PhotoPath nvarchar(255)
FETCH NEXT FROM @cur INTO
@ID,
@Extension,
@PhotoPath
WHILE(@@fetch_status <> -1)
BEGIN
EXEC [sp_EmployeeSplit_xpoView_delete]
@ID,
@Extension,
@PhotoPath
FETCH NEXT FROM @cur INTO
@ID,
@Extension,
@PhotoPath
END
CLOSE @cur
DEALLOCATE @cur
END
GO
DROP TRIGGER [t_EmployeeSplit_xpoView_delete]
GO
DROP TRIGGER [t_EmployeeSplit_xpoView_update]
GO
DROP TRIGGER [t_EmployeeSplit_xpoView_insert]
GO
DROP PROCEDURE [sp_EmployeeSplit_xpoView_delete]
GO
DROP PROCEDURE [sp_EmployeeSplit_xpoView_update]
GO
DROP PROCEDURE [sp_EmployeeSplit_xpoView_insert]
GO
DROP VIEW [EmployeeSplit_xpoView]
GO
In addition to the above generated code, the corresponding persistent classes will be decorated with the PersistentAttribute, mapping them to the corresponding views in the database:
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);
}
}
}
Well that’s it for this post, until next time, happy XPO’ing 