我已将所有SQL Connections包裹在using语句中。
using (DbConnection dbConnection = GetConnection())
{
using (DbCommand dbCommand = dbConnection.CreateCommand(cmdInsert))
{
//some work
}
}
对于UnitTests，我应该使用:memory: database，但是database在关闭连接后会自动删除。
https://www.sqlite.org/inmemorydb.html
当数据库的最后一个连接关闭时，数据库将自动删除并回收内存。
是否有解决方案如何使用:memory: database并使用using？我不想在没有using的情况下两次编写完全相同的代码。
完整的示例
数据库
public abstract class SqliteBase
{
public string ConnectionString;
protected SqliteBase()
{
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
{
DataSource = ":memory:",
ForeignKeys = true,
DefaultTimeout = 3,
DateTimeKind = DateTimeKind.Utc,
Pooling = false
};
ConnectionString = builder.ConnectionString + ";mode=memory;cache=shared";
}
private DbConnection _MemoryConnection;
protected DbConnection GetConnection()
{
try
{
if (_MemoryConnection == null)
{
_MemoryConnection = new SQLiteConnection(ConnectionString);
_MemoryConnection.Open();
}
DbConnection dbConnection = new SQLiteConnection(ConnectionString);
dbConnection.Open();
return dbConnection;
}
catch (Exception ex)
{
throw new Exception("Error opening database connection.", ex);
}
}
/// <summary>
/// Creates a table in the SQL database if it does not exist
/// </summary>
/// <param name="tableName">The name of the table</param>
/// <param name="columns">Comma separated column names</param>
protected void CreateTable(string tableName, string columns)
{
using (DbConnection dbConnection = GetConnection())
{
using (DbCommand dbCommand = dbConnection.CreateCommand($"create table if not exists {tableName} ({columns})"))
{
dbCommand.ExecuteNonQuery();
}
}
}
}
public class FooDatabase : SqliteBase
{
public FooDatabase()
{
CreateTable("FooTable", "Foo TEXT");
}
public void DoFoo()
{
using (DbConnection dbConnection = GetConnection())
{
using (DbCommand dbCommand = dbConnection.CreateCommand("Select * from FooTable"))
{
dbCommand.ExecuteNonQuery();
}
}
}
}
单元测试
public static class SQLiteTestSetup
{
public static FooDatabase ClassInit()
{
return new FooDatabase();
}
public static void Cleanup()
{
}
}
public abstract class SQLiteTestBase
{
public static FooDatabase Database { get; set; }
[TestMethod]
public void DoSomeFooTest()
{
Database.DoFoo();
}
}
[TestClass]
public class SQLiteTest : SQLiteTestBase
{
[ClassInitialize]
public static void ClassInit(TestContext context)
{
Database = SQLiteTestSetup.ClassInit();
}
[ClassCleanup]
public static void ClassCleanup() => SQLiteTestSetup.Cleanup();
}
异常
Die Testmethode "....SQLiteTest.DoSomeFooTest" hat eine Ausnahme ausgelöst:
System.Data.SQLite.SQLiteException: SQL logic error
no such table: FooTable
bei System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
bei System.Data.SQLite.SQLiteCommand.BuildNextCommand()
bei System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
bei System.Data.SQLite.SQLiteDataReader.NextResult()
bei System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
bei System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
bei ....FooDatabase.DoFoo() in ...SqliteDatabaseBase.cs:Zeile 83.
bei ....SQLiteTestBase.DoSomeFooTest() in ...SQLiteTest.cs:Zeile 30.
工作解决方案方法
我添加了ConnectionContext类，可以在其中设置标志来决定是否要处理我的DbConnection。
数据库类
public class ConnectionContext : IDisposable
{
private readonly bool _ContextOwnsConnection;
public readonly DbConnection Connection;
public ConnectionContext(DbConnection connection, bool contextOwnsConnection)
{
Connection = connection;
_ContextOwnsConnection = contextOwnsConnection;
}
public void Dispose()
{
if(_ContextOwnsConnection)
Connection.Dispose();
}
}
public abstract class SqliteBase
{
public Func<ConnectionContext> GetContext;
private ConnectionContext _GetConnectionContext()
{
return new ConnectionContext(GetConnection(), true);
}
private string _ConnectionString;
private readonly string _Dbfile;
protected SqliteBase()
{
GetContext = _GetConnectionContext;
_Dbfile = ":memory:";
_InitConnectionString();
}
private void _InitConnectionString()
{
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
{
DataSource = _Dbfile,
ForeignKeys = true,
DefaultTimeout = 3,
DateTimeKind = DateTimeKind.Utc,
Pooling = true
};
_ConnectionString = builder.ConnectionString;
}
public DbConnection GetConnection()
{
try
{
DbConnection dbConnection = SQLiteFactory.Instance.CreateConnection();
dbConnection.ConnectionString = _ConnectionString;
dbConnection.Open();
return dbConnection;
}
catch (Exception ex)
{
throw new Exception("Error opening database connection.", ex);
}
}
/// <summary>
/// Creates a table in the SQL database if it does not exist
/// </summary>
/// <param name="tableName">The name of the table</param>
/// <param name="columns">Comma separated column names</param>
protected void CreateTable(string tableName, string columns)
{
using (ConnectionContext context = GetContext())
{
using (DbCommand dbCommand = context.Connection.CreateCommand($"create table if not exists {tableName} ({columns})"))
{
dbCommand.ExecuteNonQuery();
}
}
}
}
public class FooDatabase : SqliteBase
{
public FooDatabase()
{
Initialize();
}
public void Initialize()
{
CreateTable("FooTable", "Foo TEXT");
}
public void DoFoo()
{
using (ConnectionContext context = GetContext())
{
using (DbCommand dbCommand = context.Connection.CreateCommand("Select * from FooTable"))
{
dbCommand.ExecuteNonQuery();
}
}
}
}
单元测试
public abstract class SQLiteTestBase
{
public static ConnectionContext Connection { get; set; }
public static FooDatabase Database { get; set; }
[TestMethod]
public void DoSomeFooTest()
{
Database.DoFoo();
}
}
[TestClass]
public class SQLiteTest : SQLiteTestBase
{
[ClassInitialize]
public static void ClassInit(TestContext context)
{
Database = new FooDatabase();
Database.GetContext = () => Connection;
Connection = new ConnectionContext(Database.GetConnection(), false);
}
[TestInitialize]
public void TestInit()
{
Connection = new ConnectionContext(Database.GetConnection(), false);
Database.Initialize();
}
[TestCleanup]
public void TestCleanup()
{
Connection.Dispose();
Connection = null;
}
}
我想你停止阅读到早期。您发布的链接的第二段是"内存数据库和共享缓存"，描述了您在情况下可以做和需要做的事情：
关闭后，数据库将被删除，如果您使用发布的链接中所述的方法，则最后一个连接。
。您需要一个文件URI和mode=memory，用于内存和 cache=shared才能具有多个连接到同一名称的数据库。
因此，如果您的单元测试从命名连接的使用语句开始(例如使用与当前测试相同的名称(，则使用该语句，所有使用相同连接字符串的连接都应连接到相同内存数据库。
我在内存SQLITE数据库中也有同样的问题。我的解决方案是将连接字符串设置为" fuluri = file :: memory：？cache =共共享"
尝试为数据源添加一个名称而不是：emore：
data source=testName;foreign keys=True;default timeout=3;datetimekind=Utc;pooling=False;mode=memory;cache=shared