How to: Map a Persistent Class to a Database View Which Has No Key Field

With XAF, you can build new applications from scratch or maintain existing databases. The How to: Generate XPO Business Classes for Existing Data Tables topic describes how to use the design-time wizard that generates business classes for one or more data tables in the specified database at the same time. Additionally, the existing database can contain views (stored queries), which need to be accessed in an XAF application's List Views, Analysis, and Reports. If the database view has a key column, you can map a persistent class to it using the same approach as mapping to a regular table.

This topic describes how to implement a persistent class mapped to a database view without a key. We used the "Northwind Traders" demo database, shipped with DXperience Suite and installed in %PUBLIC%\Documents\DevExpress Demos 18.1 \Components\Data\nwind.mdb by default, in this topic. Besides tables filled with demo data, this database includes several database views.

Note

This approach uses composite keys, which are not supported in the Mobile applications, and works only for WinForms and ASP.NET applications.

  1. Create a new XAF solution and follow the steps from the How to: Generate XPO Business Classes for Existing Data Tables topic. Do not forget to modify the connection string that connects your application to the nwind.mbd database.
  2. Open the nwind.mbd database to see what views (queries) it contains. You can use Microsoft Office Access or any other MDB viewer application. In this example, the CustomerReports view is mapped to the CustomerReports persistent class:

    MapDatabaseView_1

    Note

    This view contains the ProductName, CompanyName, OrderDate and ProductAmount fields. These names are used when implementing the CustomerReports class.

  3. Create a new CustomerReports persistent class (you can use the DevExpress 18.1 ORM Persistent Object template). Replace the automatically generated class declaration with the following code:

    using DevExpress.ExpressApp.Xpo.Utils;
    using DevExpress.Persistent.Base;
    using DevExpress.Xpo;
    // ...
    [DefaultClassOptions]
    public class CustomerReports : XPLiteObject {
        public CustomerReports(Session session) : base(session) { }
        [Key, Persistent, Browsable(false)]
        public CustomerReportsViewKey Key;
        public string ProductName { get { return Key.ProductName; } }
        public string CompanyName { get { return Key.CompanyName; } }
        public DateTime OrderDate { get { return Key.OrderDate; } }
        public string ProductAmount { get { return Key.ProductAmount; } }
    }
    [TypeConverter(typeof(StructTypeConverter<CustomerReportsViewKey>))]
    public struct CustomerReportsViewKey {
        [Persistent("ProductName"), Browsable(false)]
        public string ProductName;
        [Persistent("CompanyName"), Browsable(false)]
        public string CompanyName;
        [Persistent("OrderDate"), Browsable(false)]
        public DateTime OrderDate;
        [Persistent("ProductAmount"), Browsable(false)]
        public string ProductAmount;
    }
    

    Each persistent class requires a primary key. The CustomerReports class is an XPLiteObject class descendant, which has no auto-generated key property. So, the Key property representing a composite key (a key formed by combining at least two or more columns) was implemented.

    The CustomerReportsViewKey struct defines columns which form a composite key. The struct requires the TypeConverter attribute, which enables ASPxGridView to recognize the key value and to process objects.

    Additionally, the CustomerReports class exposes properties corresponding to all the view's columns. You can omit any property, but the composite key should still include all columns.

    Note
    • A database view can already have a key column. In this case, you do not need the composite key, and you should decorate the key property with the KeyAttribute.
    • The number of columns included in the composite key is limited. For instance, Microsoft SQL Server allows a maximum of 16 columns.

    If you do not want your class to have the same name as the database view, you can use a custom name, and decorate the class with the PersistentAttribute:

    [DefaultClassOptions, Persistent("CustomerReports")]
    public class MyCustomerReports : XPLiteObject {
        // ...
    }
    
  4. Run the application. The "Customer Reports" object is available.

    MapDatabaseView_2

    You can use the CustomerReports class as the Data Type in Reports and Analysis:

    MapDatabaseView_3

See Also