PersistentObject

PersisistentObject is a light-weight orm toolkit based on .net framework 4.5 (current). In current stage, PO only contains dialects for SqlServer2012 and MySql, which means some features are only supported when running on these databases.

 

Usage

This version is tested by a manually created Student database named Student.trn. It is a database backup that you can easily restore it to your SqlServer 2012 server.

Before you start to use the POConnection class which is the main function class to manipulate database, you should get the connection string and initialize a database dialect.

static string connectionString = ConfigurationManager.ConnectionStrings["Students"].ConnectionString;
static SqlServerDialect2012 dialect = new SqlServerDialect2012();
static POConnection connection = new POConnection(connectionString, dialect);

You can find some test functions under the PersistentObject.StudentTest project. Some examples show how to use POConnection to select, save and delete objects persisted to the database.

Example 1, How to load objects without any criteria and order

var r1 = connection.Load<ClassA>();
Assert.AreEqual(r1.Count() > 0, true);
var ra = r1.First();
Assert.AreEqual(ra.BOOL == true, true);
Assert.AreEqual(ra.INT == 10, true);
Assert.AreEqual(ra.STRING == "A", true);
Assert.AreEqual(ra.DATETIME == new DateTime(2013, 1, 1), true);
Assert.AreEqual(ra.DECIMAL == 10.01M, true);

Example 2, How to load objects with BinaryCriteria

BaseCriteria c1 = new BinaryCriteria("FirstName", "Bill");
var r1 = connection.Load<Student>(c1, null);
Assert.AreEqual(r1.First() != null, true);

BaseCriteria c3 = new BinaryCriteria("Tutor.FirstName", "David");
var r3 = connection.Load<Teacher>(c3, null);
Assert.AreEqual(r3.Count() > 0, true);
var s3 = r3.First();
Assert.AreEqual(s3.Tutor.FirstName == "David", true);

BaseCriteria c1 = new BinaryCriteria("Birthday", new DateTime(1991, 1, 1), BinaryComparison.GreaterThan);
var r1 = connection.Load<Student>(c1);
Assert.AreEqual(r1.Count() > 0, true);

Example 3, How to use BetweenCriteria, NullCriteria and InCriteria

BaseCriteria c1 = new BetweenCriteria("SelectDate", new DateTime(2013, 1, 2), new DateTime(2013, 1, 3));
var r1 = connection.Load<CourseSelection>(c1);
Assert.AreEqual(r1.Count() > 0, true);

BaseCriteria c1 = new NullCriteria("Score");
var r1 = connection.Load<CourseSelection>(c1);
Assert.AreEqual(r1.Count() > 0, true);

BaseCriteria c1 = new InCriteria("Id", new int[] { 1, 4, 7 });
var r1 = connection.Load<Student>(c1);
Assert.AreEqual(r1.Count() > 0, true);

BaseCriteria c2 = new InCriteria("FirstName", new string[] { "Mary", "Bill", "Ross" });
var r2 = connection.Load<Student>(c2);
Assert.AreEqual(r2.Count() > 0, true);

Example 4, How to use AndCriteria, OrCriteria and NotCriteria

BaseCriteria c1 = new AndCriteria(
    new BinaryCriteria("LastName", "Geller"),
    new BinaryCriteria("FirstName", "Ross"),
    new BinaryCriteria("StudentId", "300123009")
);
var r1 = connection.Load<Student>(c1);
Assert.AreEqual(r1.Count() > 0, true);

BaseCriteria c1 = new OrCriteria(
    new BinaryCriteria("LastName", "Geller"),
    new BinaryCriteria("FirstName", "Ross"),
    new BinaryCriteria("StudentId", "300123009")
);
var r1 = connection.Load<Student>(c1);
Assert.AreEqual(r1.Count() > 0, true);

BaseCriteria c1 = new NotCriteria(
    new BetweenCriteria("SelectDate", new DateTime(2013, 1, 2), new DateTime(2013, 1, 3))
);
var r1 = connection.Load<CourseSelection>(c1);
Assert.AreEqual(r1.Count() > 0, true);

Example 5, How to save objects.

Student r1 = new Student() { FirstName = "Test", LastName = "Test", Picture = Image.FromFile(IMAGE_PATH) };
connection.Save<Student>(r1);
Assert.AreEqual(r1.Id > 0, true);

var r1 = connection.Load<Student>();
foreach (var item in r1)
{
    item.Birthday = item.Birthday.AddDays(1);
    connection.Save<Student>(item);
}

Example 6, How to delete objects.

BaseCriteria c1 = new BinaryCriteria("FirstName", "Test");
var r2 = connection.Load<Student>(c1);
connection.Delete<Student>(r2);

BaseCriteria c1 = new BinaryCriteria("FirstName", "Test");
connection.Delete<Student>(c1);
var r1 = connection.Load<Student>(c1);
Assert.AreEqual(r1.Count() == 0, true);

Example 7, Transaction

connection.BeginTransaction();
Student r1 = new Student() { FirstName = "Test", LastName = "Test" };
connection.Save<Student>(r1);
Assert.AreEqual(r1.Id > 0, true);

Student r2 = new Student() { FirstName = "Test", LastName = "Test" };
connection.Save<Student>(r2);
Assert.AreEqual(r2.Id > 0, true);

BaseCriteria c3 = new BinaryCriteria("FirstName", "Test");
connection.Delete<Student>(c3);
connection.CommitTransaction();

var r3 = connection.Load<Student>(c3);
Assert.AreEqual(r3.Count() == 0, true);

 

Project Plan

The new dialects for MySql and Oracle are coming soon.

 

History

2014-02-19: The first version that can manipulate database by given sql script or use the criteria was released.