Sqlite3内存数据库

Sqlite3内存数据库

Sqlite3是一个小型的，嵌入式的数据库。它还具有一个特色的功能：即内存数据库模式（与Redis ,Memcached等不同）。 这篇文章使用 .Net 5.0 平台演示实现Sqlite3的内存数据库模式。

环境及依赖

我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

1. 开发环境**.Net 5.0**
2. VS2019
3. Nuget 包： Microsoft.Data.Sqlite

功能演示-内存模式

using Microsoft.Data.Sqlite;
public class MsSqliteTest
{
	string MemoryConnStr = "Data Source=InMemorySample;Mode=Memory;Cache=Shared";
	string FileConnStr = "Data Source=MsSqliteTest.db;Cache=Shared";
	SqliteConnection Conn;
	public bool CheckTableExists(SqliteConnection Conn, string tableName)
	{
		var comm = Conn.CreateCommand();
		comm.CommandText = $"Select Count(name) FROM sqlite_master WHERE type='table' AND name='{tableName}';";
		int rt = System.Convert.ToInt32(comm.ExecuteScalar());
		return rt > 0;
	}
	
	public bool OpenCreateTable(SqliteConnection Conn)
	{
		Conn.Open();
		if (CheckTableExists(Conn, "Users") == false)
		{
			var comm = Conn.CreateCommand();
			comm.CommandText = "Create Table Users (Id Integer PRIMARY KEY AUTOINCREMENT,  Name CHAR (2, 20) NOT NULL,  Age  INTEGER);";
			int rt = System.Convert.ToInt32(comm.ExecuteNonQuery());
			comm.Dispose();
			return rt > 0;
		}
		return true;
	}
	
	public int InsertTable(SqliteConnection Conn)
	{
		var comm = Conn.CreateCommand();
		comm.CommandText = "insert into Users (Name,Age) Values ('xiaoming1',25)";
		int rt = System.Convert.ToInt32(comm.ExecuteNonQuery());
		comm.Dispose();
		return rt;
	}
	
	public int SelectTableCount(SqliteConnection Conn)
	{
		var comm = Conn.CreateCommand();
		comm.CommandText = "select Count(ID) From Users";
		int rt = System.Convert.ToInt32(comm.ExecuteScalar());
		comm.Dispose();
		return rt;
	}
	
	public void MemTest()
	{
		Conn = new SqliteConnection(MemoryConnStr);
		OpenCreateTable(Conn);
		Console.WriteLine("Mem:Insert=" + System.Convert.ToString(InsertTable(Conn)));
		Console.WriteLine("Mem:Select=" + System.Convert.ToString(SelectTableCount(Conn)));
		Conn.Close();
		Conn.Dispose();
	}


}

功能演示-文件模式

	public void FileTest()
	{
		Conn = new SqliteConnection(FileConnStr);
		OpenCreateTable(Conn);
		Console.WriteLine("File:Insert=" + System.Convert.ToString(InsertTable(Conn)));
		Console.WriteLine("File:Select=" + System.Convert.ToString(SelectTableCount(Conn)));
		Conn.Close();
		Conn.Dispose();
	}

功能演示-文件加载到内存

	public void LoadFileToMem()
	{
		Conn = new SqliteConnection(FileConnStr);
		Conn.Open();
		var MemConn = new SqliteConnection(MemoryConnStr);
		MemConn.Open();
		Conn.BackupDatabase(MemConn);
		
		Console.WriteLine(SelectTableCount(Conn));
		Console.WriteLine(SelectTableCount(MemConn));
		
		Conn.Close();
		Conn.Dispose();
		
		MemConn.Close();
		MemConn.Dispose();
		
	}
	

功能演示-内存保存到文件

		/// <summary>
	/// 子查询第6到10条:共5条
	/// </summary>
	public void SQLiteSubSelectTest(SqliteConnection Conn, int startLine, int endLine)
	{
		// SQL Server
		// select top 10 * from users where id not in(select top 30 id from users order by id asc) order by id asc
		var comm = Conn.CreateCommand();
		var Count = endLine - startLine + 1;
		var NotInCount = startLine - 1;
		// Select ID from users where id not in(select ID from users order by id asc Limit 0,5) order by id asc Limit 0,5
		comm.CommandText = $"Select ID from Users Where ID not in(select ID from Users Limit 0,{NotInCount}) Limit 0,{Count}";
		var reader = comm.ExecuteReader();
		var readedLine = 0;
		while (reader.Read())
		{
			Console.WriteLine($"ID={reader.GetInt32(0)}");
			readedLine++;
		}
		Console.WriteLine($"SQLiteSubSelect Read Line={readedLine}");
		reader.Close();
		reader.DisposeAsync();
		comm.Dispose();
		
	}
	


	public void SaveMemToFileTest()
	{
		Conn = new SqliteConnection(FileConnStr);
		Conn.Open();
		var MemConn = new SqliteConnection(MemoryConnStr);
		MemConn.Open();
		Conn.BackupDatabase(MemConn); //Load File To Mem
		
		Console.WriteLine(SelectTableCount(Conn));
		Console.WriteLine(SelectTableCount(MemConn));
		
		
		Console.WriteLine(InsertTable(MemConn));
		Console.WriteLine(InsertTable(MemConn));
		Console.WriteLine(SelectTableCount(MemConn));
		
		MemConn.BackupDatabase(Conn); //Save Mem To File
		
		SQLiteSubSelectTest(MemConn, 6, 10);
		
		MemConn.Close();
		MemConn.Dispose();
		
		Conn.Close();
		Conn.Dispose();
	}
	
	public void AllTest()
	{
		MemTest();
		FileTest();
		LoadFileToMem();
		SaveMemToFileTest();
	}
	public void Main(string[] argv)
	{
		MsSqliteTest ms = new MsSqliteTest();
		// ms.MemTest()
		// ms.FileTest()
		// ms.LoadFileToMem()
		// ms.SaveMemToFileTest()
		ms.AllTest();
	}

执行结果-第N次执行

Mem:Insert=1
Mem:Select=1
File:Insert=1
File:Select=4
4
4
4
4
1
1
6
ID=6
SQLiteSubSelect Read Line=1

执行结果-第N+1次执行

Mem:Insert=1
Mem:Select=1
File:Insert=1
File:Select=7
7
7
7
7
1
1
9
ID=6
ID=7
ID=8
ID=9
SQLiteSubSelect Read Line=4