program.cs :  » Persistence-Frameworks » ODX » SQL » C# / CSharp Open Source

using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Reflection;
using NUnit.Framework;
using ODX.Core;
using ODX.LazyUnitTester;

namespace SQL{
    public enum Gender
    {
        Unknown,
        Male,
        Female
    }

    [Table("Human", Versioned = true)]
    [HierarchyRoot]
    [TypeDef("H")]
    public abstract class Human : Entity
    {
        public abstract string Name { get; set; }
        public abstract Gender Sex { get; set; }
        public abstract DateTime DateOfBirth { get; set; }
        public int Age
        {
            get { return (int)((DateTime.Now - DateOfBirth).Days / 365.25); }
        }

        public abstract float Weight { get; set; }
        public abstract short Salary { get; set; }

        [Column("MasterID")]
        public abstract IEntityList<Pet> Pets { get; }
    }

    [TypeDef("M")]
    public abstract class Man : Human
    {
        protected override void OnCreated()
        {
            Sex = Gender.Male;
            Weight = 123.1f;
            Salary = 121;
            base.OnCreated();
        }
    }

    [Table("Pet")]
    public abstract class Pet : Entity
    {
        public abstract string Name { get; set;}
        public abstract Human Master { get; set; }
    }

    [TestFixture]
    public class C08_SQL
    {
        [TestBody]
        static void Main()
        {
            Session oledbSession = new Session(new DbDataProvider(
                    OleDbFactory.Instance,
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sql.mdb;"));

            oledbSession.RegisterAssembly(Assembly.GetExecutingAssembly());
            oledbSession.Prepare();

            Session odbcSession = new Session(new DbDataProvider(
                    OdbcFactory.Instance,
                    "Driver={Microsoft Access Driver (*.mdb)};Dbq=sql.mdb;"));

            odbcSession.RegisterAssembly(Assembly.GetExecutingAssembly());
            odbcSession.Prepare();

            ClearDatabase();

            Man oledbJohn = oledbSession.Create<Man>();
            oledbJohn.Name = "John";
            oledbJohn.DateOfBirth = new DateTime(1977, 04, 06);
            oledbSession.Save();
            LUT.WriteLine("John is successfully saved through OLE DB");

            Man odbcSmith = odbcSession.Create<Man>();
            odbcSmith.Name = "Smith";
            odbcSmith.DateOfBirth = new DateTime(1953, 11, 18);
            odbcSession.Save();
            LUT.WriteLine("Smith is successfully saved through ODBC");

            Man odbcJohn = odbcSession.SelectOne<Man>("Name = ?", "DateOfBirth DESC", "John");

            // Concurrently change john record through different sessions
            odbcJohn.DateOfBirth = new DateTime(1974, 02,09);
            oledbJohn.DateOfBirth = new DateTime(1969, 08,23);

            Pet odbcAlice = odbcSession.Create<Pet>();
            odbcAlice.Name = "Alice";
            odbcAlice.Master = odbcJohn;

            Pet oledbMolly = oledbSession.Create<Pet>();
            oledbMolly.Name = "Molly";
            oledbMolly.Master = oledbJohn;

            LUT.Write("Saving John through ODBC...");
            odbcSession.Save();
            LUT.WriteLine("Success!");

            try
            {
                LUT.Write("Save John through OLE DB...");
                oledbSession.Save(SaveMode.Concurrent);
            }
            catch ( DBConcurrencyException e )
            {
                LUT.WriteLine("Concurrent access violation");
                LUT.WriteLine("Conflicted table: " + e.Row.Table.TableName);
                foreach (DataColumn dc in e.Row.Table.Columns)
                    if (dc.ColumnName.ToUpper() != "ID" && dc.ColumnName.ToUpper() != "ROWVERSION") // Unit testing will fail due to rendom ID nature.
                        LUT.WriteLine("\t{0}\t{1}", dc.ColumnName, e.Row[dc.ColumnName]);

                // reject the violated changes to be able to save other work
                Entity violatedEntity = oledbSession.FindEntity(e.Row["ID"].ToString());
                violatedEntity.RejectChanges();
                LUT.WriteLine("Conflicted changes rejected.");
            }

            LUT.Write("Save John through OLE DB...");
            oledbSession.Save(SaveMode.Concurrent);
            LUT.WriteLine("Success!");

            LUT.WriteLine("\nCurrently Johns are\n");
            LUT.WriteLine("OLEDB");
            DescribeHuman(oledbJohn);
            LUT.WriteLine("ODBC");
            DescribeHuman(odbcJohn);

        }

        static void DescribeHuman(Human john)
        {
            LUT.WriteLine("Now {0} is {1}. He masters", john.Name, john.Age);
            foreach (Pet pet in john.Pets)
                LUT.WriteLine("\t{0}", pet.Name);
            LUT.WriteLine();
            LUT.WriteLine("------------------------");
        }

        static void ClearDatabase()
        {
            DbDataProvider provider = new DbDataProvider(
                OleDbFactory.Instance,
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sql.mdb;");
            
            Session session = new Session(provider, Assembly.GetExecutingAssembly());

            provider.CreateDatabase(session.Schema);

            foreach (Human human in session.All<Human>()) human.Delete();
            foreach (Pet pet in session.All<Pet>()) pet.Delete();
            session.Save();
        }

        [Test]
        public void Test()
        {
            LUT.Execute(Main);
        }

    }
}