打开/关闭一个从流数据库 [英] Open/Close a database from a stream

问题描述

我想从一个给定的流（的FileStream / MemoryStream的）访问SQLite数据库？

I want to access a SQLite Database from a given Stream (FileStream/Memorystream)?

这是到目前为止我的代码（它读取文件到MemoryStream和然后打开它在SQLite的）：

This is my code so far (it reads the File into a Memorystream and then opens it in SQLite):

if (File.Exists(path_DB))
{
    byte[] file = File.ReadAllBytes(path_DB);
    MemoryStream mem = new MemoryStream(file);

    using (SQLiteConnection destination = new SQLiteConnection("Data Source=" + mem + ";Version=3;"))
    {
        destination.Open();

        using (SQLiteCommand command = new SQLiteCommand())
        {
            command.Connection = destination;
            command.CommandText = "SELECT Name FROM MyTable;";

            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    MessageBox.Show(reader["Name"].ToString());
                }
            }
        }
    }
}

这代码触发了我

SQL逻辑错误或丢失的数据库，没有这样的表：MyTable的

SQL Logic error or missing Database, no such table: MyTable

在 Command.ExecuteReader却（）。提到的文件， path_DB 确实有表。

at command.ExecuteReader(). The file referred to with path_DB does have the table.

我到哪里去了？

//编辑：更改

byte[] file = File.ReadAllBytes(path_DB);
MemoryStream mem = new MemoryStream();

到

to

byte[] file = File.ReadAllBytes(path_DB);
MemoryStream mem = new MemoryStream(file);

和进一步的我可以将现有的数据库加载到内存中的一个，并再次节省了内存的DB像磁盘这样的：

and further I can load an existing Database into the memory one and save the memory DB again to disk like this:

if (File.Exists(path_DB))
{
    using (SQLiteConnection destination = new SQLiteConnection("Data Source=:memory:;Version=3;"))
    {
        SQLiteConnection source = new SQLiteConnection("Data Source=" + path_DB + ";Version=3;"); //;Password=" + textBox2.Text + "
        source.Open();

        destination.Open();

        source.BackupDatabase(destination, "main", "main", -1, null, 0);
        source.Close();

        using (SQLiteCommand command = new SQLiteCommand())
        {
            command.Connection = destination;
            command.CommandText = "SELECT Name FROM MyTable;";

            using (System.Data.SQLite.SQLiteDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    MessageBox.Show(reader["Name"].ToString());
                }
            }
        }

        source = new SQLiteConnection("Data Source=" + path_DB + ";Version=3;"); //;Password=" + textBox2.Text + "
        source.Open();

        // save memory db to file
        destination.BackupDatabase(source, "main", "main", -1, null, 0);
        source.Close();
    }
}

所以我想，必须有同样的方式来这样做有一个直接的（解密）流！？

So I figured, there must be also a way to do this with a direct (decrypted) stream!?

推荐答案

有你的代码和你要什么多个问题做的。

There are multiple issues with your code and what you're trying to do.

要开始，你分配给文件但不能用它做任何事情。

To start, you're assigning to file but not doing anything with it.

接下来，你不能只是一个的MemoryStream 实例追加到您的连接。如图中文档：

Next, you can't just append a MemoryStream instance to your connection. As shown in the documentation:

强制SQLite数据库在内存中存在纯粹最常用的方法是使用特殊的文件名，打开数据库：记忆：。换句话说，而不是通过一个实际的磁盘文件的名字进sqlite3_open之一（），sqlite3_open16（），或sqlite3_open_v2（）函数，通过在字符串中，：存储器：

The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:". In other words, instead of passing the name of a real disk file into one of the sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() functions, pass in the string ":memory:".

这样在连接字符串中意味着，你需要：内存：作为数据源。这也显示在 https://www.connectionstrings.com/sqlite/

So that means in the connection string, you need :memory: as your data source. This is also shown on https://www.connectionstrings.com/sqlite/

Data Source=:memory:;Version=3;New=True;

最后，从文件的另一个片段我前面提到的：

Finally, another snippet from the documentation I earlier referred to:

当这样做时，没有盘的文件被打开。相反，在存储器纯粹创建一个新数据库。该数据库不再作为数据库连接被关闭尽快存在。每个：内存：数据库是从每一个不同等。所以，每一个文件名打开两个数据库连接：记忆：将创建两个独立的内存数据库

When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

这意味着你不能使用现有的数据库。您创建一个数据库，使用它，那么它的再次被破坏。 。有一个关于文件级别没有持久性。

This means you cannot use an existing database. You create a database, use it and then it's destroyed again. There's no persistence on file level.

如果这一切没有意义的你，你只是想使用一个数据库，这是你的驱动器上，使用连接字符串是这样的：

If all this makes no sense to you and you just want to use a database that's on your drive, use a connection string like this:

Data Source=c:\mydb.db;Version=3;

SQLite的供应商将负责打开文件，从中读取和写入吧。

The SQLite provider will take care of opening the file, reading from it and writing to it.

有关您的加密的担忧，的您可以为数据库使用密码。这将需要加密数据库

For your encryption concerns, you can use a password for the database. This will take care of encrypting the database

Data Source=c:\mydb.db;Version=3;Password=myPassword;

这篇关于打开/关闭一个从流数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！