使用SqLite将存储过程添加到内存数据库中

问题描述：

我正在使用内存数据库（使用ServiceStack.OrmLite.Sqlite.Windows）在基于servicestack的web api中进行单元测试。我想测试依赖于通过内存数据库的存储过程，我已经通过链接Servicestack Ormlite SqlServerProviderTests，我使用的测试是如下的单元测试类了服务端点，使用SqLite将存储过程添加到内存数据库中

 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)); 
       } 
      } 
     }

时我试图通过Live-DB执行此操作，它工作正常。但是当我试图对与内部存储器DB渐渐例外如下，

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

附近的代码行，

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

我评论上面的行和执行的测试用例，但仍然我得到异常如下，

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

的代码行，

 db.ExecuteSql(sql);

In-Memory DB Created如下所示，其余的情况下工作正常。

 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>(); 

        }    
       }

为什么我们都面临这个例外是有任何其他方式在内部存储器DB的SQLite添加和运行存储过程？

答

错误是因为您尝试使用TSQL针对内存版本Sqlite运行SQL Server特定的查询 - 即完全不同的可嵌入数据库。顾名思义，SqlServerProviderTests只适用于SQL Server，我很困惑为什么你会试图对Sqlite运行这个？

SQLite不支持存储过程，TSQL等，所以试图执行SQL Server TSQL语句将始终导致错误。您唯一能做的就是用custom Exec Filter假冒它，在那里您可以捕捉到异常并返回您喜欢的任何自定义结果，例如：

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();

使用SqLite将存储过程添加到内存数据库中

相关推荐