[Solved] in-memory sqlite

[MATEUSZ]

Hi.
I've been trying to make openaccess create sqlite in-memory database for easy testing. However, it appears to be more complicated than i thought.

I'm using forward mapping strategy.

Classes used and code sample:

    [Telerik.OpenAccess.Persistent()]
    public class Order
    {
        public string customer;
        public DateTime orderDate;
        public string shipAddress;
        public IList<OrderDetail> details = new List<OrderDetail>();

    }

    [Telerik.OpenAccess.Persistent()]
    public class OrderDetail
    {
        public string product;
        public float price;
        public int quantity;
        public Order order;
    }

            ObjectScopeProvider1.AdjustForDynamicLoad();
            IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();

            scope.Transaction.Begin();

            var order = new Order();
            order.customer = "aaa";
            order.orderDate = DateTime.Now;
            order.shipAddress = "bbb";

            var detail = new OrderDetail();
            detail.price = 423;
            detail.product = "ccc";
            detail.quantity = 3;

            var detail2 = new OrderDetail();
            detail2.price = 42334;
            detail2.product = "ddd";
            detail2.quantity = 33;

            order.details.Add(detail);
            order.details.Add(detail2);

            scope.Add(order);

            scope.Transaction.Commit();

<connection id="db_connection">
        <databasename>:memory:</databasename>
        <servername>lt</servername>
        <integratedSecurity>True</integratedSecurity>
        <backendconfigurationname>sqliteConfiguration</backendconfigurationname>
</connection>

The result is getting an sqlite exception: ordr table not found. It appears, that created database "forgets" its schema. So I tried adding this piece of code to AdjustForDynamicLoad:

ISchemaHandler schemaHandler = db.GetSchemaHandler();
                schemaHandler.CreateDatabase();
                string script = schemaHandler.CreateDDLScript();
                schemaHandler.ExecuteDDLScript(script);

but it didn't help. Can anyone tell my what am I doing wrog or at least if making in-memory db is possible?
Thanks for any advice.

[MATEUSZ]

I had one more unsuccessfull attempt. I switched to fluent api and:

IUnityContainer container = new UnityContainer();

            string connectionString = "Data Source=:memory:;Version=3;New=True";
            string backend = "sqlite";
            //string connectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=dp;Integrated Security=True;Connect Timeout=2;User Instance=False";
            //string backend = "mssql";
            container.RegisterType<IContextFactory, ContextFactory>(new ContainerControlledLifetimeManager(), new InjectionConstructor(connectionString, backend))
                     .RegisterType<IUnitOfWork, UnitOfWork>(new ContainerControlledLifetimeManager())
                     .RegisterType(typeof(IRepository<>), typeof(Repository<>));

            var uow = container.Resolve<IUnitOfWork>();

            var cf = container.Resolve<IContextFactory>();
            ISchemaHandler schemaHandler = cf.Get().GetSchemaHandler();

            schemaHandler.CreateDatabase();
            var script = schemaHandler.CreateDDLScript();
            schemaHandler.ExecuteDDLScript(script);

            var ur = container.Resolve<IRepository<User>>();
            Assert.AreEqual(0, ur.Count());
            ur.Add(new User(){ Email = "f@a.b", IsLockedOut = false, Name="a", Password = "earh"});
            uow.Commit();
            Assert.AreEqual(1, ur.Count());

using mssql everything works fine. however, when using sqlite, it doesn't find Users table - the schema has probably not been propagated to the database. Why does it happen?

[Ady]

Hi Mateusz,

 To create the database using OpenAccess there are 2 approaches you can follow:

1. Create the database from within VisualStudio while developing your application
2. Create the database using the API within the application.

 In the 'Classic'/Older approach an OpenAccess enabled project has an 'Update Database' project property. This property is set to 'true' for a code first project/Forward mapping project. You can check it's value by selecting the project in the solution explorer and pressing F4. When you build your project, OpenAccess will create the database as a post-build step. You can also create the database via code.

If you use the new domain model approach you can create the database using the 'Update Database from Model' wizard or use the same API to create the database via code.

I have created a sample solution where both the approaches are demonstrated.
Do get back in case you need further assistance.

All the best,
Ady
the Telerik team

[MATEUSZ]

That's not the answer, however I managed to solve the problem.

The most important issue is that the database should be created in-memory and NOT DISPOSED until all the test actions are performed. But the database disappeared during the test. It turned out that openaccess disposes OAConnection object, which is encapsulated within OpenAccessContect, and according to sqlite documentation, than the database was dropped.

So I had to retrieve Connection from Context in [SetUp] method, assign it to the TestBase class field, perform the tests and dispose it in [TearDown] method.