implicit operator

At my previous job, I did a lot of Pervasive SQL and .NET integration work. Before that, I had been using ORM frameworks like NHibernate to interact with MSSQL and SQLite, so I naturally wanted to find an ORM framework that worked with Pervasive SQL. NHibernate didn't have PSQL support, so I tried adding it, but that turned out to be a major headache. Instead, I found the Object Persistence Framework which included PSQL support out of the box.

OPF3 ORM Framework

OPF3 uses the Pervasive ADO.NET Provider, so you'll need to follow the installation instructions in Part 2 before proceeding. Once you have the ADO.NET Provider, you need to download OPF3 from CodePlex. I recommend downloading the latest revision from source control, although the release package contains the source code, as well.

In the release package, only the core OPF3 library and the LINQ to OPF3 library are included as binaries. This means you will need to compile the Pervasive SQL Storage library.

Navigate to the OPF3 source directory and open the Chili.Opf3 solution. Visual Studio may prompt you to convert it to a newer format. Find the Chili.Opf3.Storages.PervasiveSql project and expand its references. You'll notice that the Pervasive.Data.SqlClient reference is missing. You will need to re-add it from the PSQL SDK. Now, right-click on the PSQL Storage project and click Build.

To use the storage provider in your poject, add a reference to Chili.Opf3, Chili.Opf3.Storages.PervasiveSql, and Pervasive.Data.SqlClient. Now, it's time to set up a persistent object. For this example, I will be using the Person table in the DEMODATA database.

[Persistent]
public class Person {
    [Field(Identifier = true, AutoNumber = true)] public int ID { get; set; }
    [Field("First_Name")] public string FirstName { get; set; }
    [Field("Last_Name")] public string LastName { get; set; }
    [Field("Date_Of_Birth")] public DateTime DateOfBirth { get; set; }
}

OPF3 uses attributes to map persistent objects, rather than XML configuration files. First, we mark the class as Persistent. In this example, the table name matches the class name, so no further parameters are required. If your table name were different, you would need to supply it to the first argument of the PersistentAttribute. The columns (fields) in the table are mapped to properties of the object. They are marked with the FieldAttribute. The ID column is an identifier and will auto-increment, so we have to let OPF3 know this. Since the name of the property matches the name of the field, no further configuration is needed. The names of the remaining properties differ, however, so we have to supply the actual field name to the FieldAttribute.

Next up is the actual connection.

string connString = "ServerDSN=DEMODATA";
PervasiveSqlStorage storage = new PervasiveSqlStorage(connString);
using (ObjectContext context = new ObjectContext(storage)) {
    // query code goes here
}

The connection string is identical to the string in the previous part. Anything that works for the ADO.NET Provider will work for the OPF3 Storage Provider. We have to create a Pervasive-specific storage object that gets supplied to the ObjectContext so it knows how to interact with the database. Note that the connection string is supplied to the storage provider and not the object context.

Retrieving Person objects is simple:

var people = context.GetObjectSet<Person>();
foreach (Person person in people) {
    Console.WriteLine(string.Format(
        "ID: {0}, FirstName: {1}, LastName: {2}, DOB: {3}",
        person.ID, person.FirstName, person.LastName, person.DateOfBirth));
}

When run, the program should print every row in the Person table using the format above. That's all there is to it!