Adding stored procedures to In-Memory DB using SqLite

Question

I am using In-Memory database (using ServiceStack.OrmLite.Sqlite.Windows) for unit testing in servicestack based web api. I want to test the service endpoints which depends on stored Procedures through In-Memory database for which i have gone through the link Servicestack Ormlite SqlServerProviderTests, the unit test class that i am using for the test is as follows,

        using System;
        using System.Collections.Generic;
        using System.Data;
        using System.Linq;
        using NUnit.Framework;
        using ServiceStack.Text;
        using ServiceStack.Configuration;
        using ServiceStack.Data;

        namespace ServiceStack.OrmLite.Tests
        {
            public class DummyTable
            {
                public int Id { get; set; }
                public string Name { get; set; }
            }

            [TestFixture]
            public class SqlServerProviderTests
            {
                private IDbConnection db;
                protected readonly ServiceStackHost appHost;

                public SqlServerProviderTests()
                {
                    appHost = TestHelper.SetUp(appHost).Init();
                    db = appHost.Container.Resolve<IDbConnectionFactory>().OpenDbConnection("inventoryDb");

                    if (bool.Parse(System.Configuration.ConfigurationManager.AppSettings["IsMock"]))
                        TestHelper.CreateInMemoryDB(appHost);
                }

                [TestFixtureTearDown]
                public void TearDown()
                {
                    db.Dispose();
                }       

                [Test]
                public void Can_SqlColumn_StoredProc_returning_Column()
                {
                    var sql = @"CREATE PROCEDURE dbo.DummyColumn
                                @Times integer
                                AS
                                BEGIN
                                SET NOCOUNT ON;

                                CREATE TABLE #Temp
                                (
                                Id   integer NOT NULL,
                                );

                            declare @i int
                            set @i=1
                            WHILE @i < @Times
                            BEGIN
                            INSERT INTO #Temp (Id) VALUES (@i)
                            SET @i = @i + 1
                            END
                            SELECT * FROM #Temp;

                            DROP TABLE #Temp;
                            END;";
                    db.ExecuteSql("IF OBJECT_ID('DummyColumn') IS NOT NULL DROP PROC DummyColumn");
                    db.ExecuteSql(sql);

                    var expected = 0;
                    10.Times(i => expected += i);

                    var results = db.SqlColumn<int>("EXEC DummyColumn @Times", new { Times = 10 });
                    results.PrintDump();
                    Assert.That(results.Sum(), Is.EqualTo(expected));

                    results = db.SqlColumn<int>("EXEC DummyColumn 10");
                    Assert.That(results.Sum(), Is.EqualTo(expected));

                    results = db.SqlColumn<int>("EXEC DummyColumn @Times", new Dictionary<string, object> { { "Times", 10 } });
                    Assert.That(results.Sum(), Is.EqualTo(expected));
                }
            }
        }

when i tried to execute this through Live-DB, it was working fine. but when i tried for In-Memory DB was getting Exceptions as follows,

        System.Data.SQLite.SQLiteException : SQL logic error or missing database near "IF": syntax error

near the code line,

        db.ExecuteSql("IF OBJECT_ID('DummyColumn') IS NOT NULL DROP PROC DummyColumn");

i commented the above line and executed the test case but still i am getting exception as follows,

        System.Data.SQLite.SQLiteException : SQL logic error or missing database near "IF": syntax error

for the code line,

        db.ExecuteSql(sql);

the In-Memory DB Created is as follows, and its working fine for remaining cases.

        public static void CreateInMemoryDB(ServiceStackHost appHost)
                {
                    using (var db = appHost.Container.Resolve<IDbConnectionFactory>().OpenDbConnection("ConnectionString"))
                    {                              
                        db.DropAndCreateTable<DummyData>();
                        TestDataReader<TableList>("Reservation.json", "InMemoryInput").Reservation.ForEach(x => db.Insert(x));

                        db.DropAndCreateTable<DummyTable>();

                    }            
                }

why we are facing this exception is there any other way to add and run stored Procedure in In-Memory DB with Sqlite??

Answer 1

The error is because you're trying to run SQL Server-specific queries with TSQL against an in memory version of Sqlite - i.e. a completely different, embeddable database. As the name suggests SqlServerProviderTests only works on SQL Server, I'm confused why you would try to run this against Sqlite?

SQLite doesn't support Stored Procedures, TSQL, etc so trying to execute SQL Server TSQL statements will always result in an error. The only thing you can do is fake it with a custom Exec Filter, where you can catch the exception and return whatever custom result you like, e.g:

public class MockStoredProcExecFilter : OrmLiteExecFilter
{
    public override T Exec<T>(IDbConnection dbConn, Func<IDbCommand, T> filter)
    {
        try
        {
            return base.Exec(dbConn, filter);
        }
        catch (Exception ex)
        {
            if (dbConn.GetLastSql() == "exec sp_name @firstName, @age")
                return (T)(object)new Person { FirstName = "Mocked" };
            throw;
        }
    }
}

OrmLiteConfig.ExecFilter = new MockStoredProcExecFilter();