C#实现文件读写到SQLite数据库的方法

        《文件读写到SQLite数据库的方法》博客中，介绍了文件读写到SQLite数据库的方法，例子使用的Python语言，本文是使用 C# 将文件读写到 SQLite 数据库的几种方法的具体示例，如果有些概念模糊可参考作者以前博客。

1. 使用 BLOB 存储文件

示例代码：

using System;
using System.Data.SQLite;
using System.IO;class Program
{static void Main(string[] args){string databasePath = "example.db";string connectionString = $"Data Source={databasePath};Version=3;";// 创建数据库和表using (var connection = new SQLiteConnection(connectionString)){connection.Open();string createTableQuery = @"CREATE TABLE IF NOT EXISTS Files (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,data BLOB NOT NULL);";using (var command = new SQLiteCommand(createTableQuery, connection)){command.ExecuteNonQuery();}}// 插入文件到数据库string filePath = "example.pdf"; // 替换为你的文件路径InsertFile(filePath, connectionString);// 从数据库读取文件ReadFile(1, connectionString); // 假设读取 ID 为 1 的文件}static void InsertFile(string filePath, string connectionString){byte[] fileData = File.ReadAllBytes(filePath);string fileName = Path.GetFileName(filePath);using (var connection = new SQLiteConnection(connectionString)){connection.Open();string insertQuery = "INSERT INTO Files (name, data) VALUES (@name, @data)";using (var command = new SQLiteCommand(insertQuery, connection)){command.Parameters.AddWithValue("@name", fileName);command.Parameters.AddWithValue("@data", fileData);command.ExecuteNonQuery();}}Console.WriteLine($"File '{fileName}' has been saved to the database.");}static void ReadFile(int fileId, string connectionString){using (var connection = new SQLiteConnection(connectionString)){connection.Open();string selectQuery = "SELECT name, data FROM Files WHERE id = @id";using (var command = new SQLiteCommand(selectQuery, connection)){command.Parameters.AddWithValue("@id", fileId);using (var reader = command.ExecuteReader()){if (reader.Read()){string fileName = reader.GetString(0);byte[] fileData = (byte[])reader["data"];File.WriteAllBytes(fileName, fileData);Console.WriteLine($"File '{fileName}' has been restored from the database.");}}}}}
}

2. 存储文件路径

示例代码：

using System;
using System.Data.SQLite;class Program
{static void Main(string[] args){string databasePath = "example.db";string connectionString = $"Data Source={databasePath};Version=3;";// 创建数据库和表using (var connection = new SQLiteConnection(connectionString)){connection.Open();string createTableQuery = @"CREATE TABLE IF NOT EXISTS FilePaths (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,path TEXT NOT NULL);";using (var command = new SQLiteCommand(createTableQuery, connection)){command.ExecuteNonQuery();}}// 插入文件路径string filePath = "example.pdf"; // 替换为你的文件路径InsertFilePath(filePath, connectionString);// 从数据库读取文件路径GetFilePath(1, connectionString); // 假设读取 ID 为 1 的文件路径}static void InsertFilePath(string filePath, string connectionString){string fileName = System.IO.Path.GetFileName(filePath);using (var connection = new SQLiteConnection(connectionString)){connection.Open();string insertQuery = "INSERT INTO FilePaths (name, path) VALUES (@name, @path)";using (var command = new SQLiteCommand(insertQuery, connection)){command.Parameters.AddWithValue("@name", fileName);command.Parameters.AddWithValue("@path", filePath);command.ExecuteNonQuery();}}Console.WriteLine($"File path '{filePath}' has been saved to the database.");}static void GetFilePath(int fileId, string connectionString){using (var connection = new SQLiteConnection(connectionString)){connection.Open();string selectQuery = "SELECT name, path FROM FilePaths WHERE id = @id";using (var command = new SQLiteCommand(selectQuery, connection)){command.Parameters.AddWithValue("@id", fileId);using (var reader = command.ExecuteReader()){if (reader.Read()){string fileName = reader.GetString(0);string filePath = reader.GetString(1);Console.WriteLine($"File '{fileName}' is located at '{filePath}'.");}}}}}
}

3. 分块存储文件

示例代码：

using System;
using System.Data.SQLite;
using System.IO;class Program
{static void Main(string[] args){string databasePath = "example.db";string connectionString = $"Data Source={databasePath};Version=3;";// 创建数据库和表using (var connection = new SQLiteConnection(connectionString)){connection.Open();string createTableQuery = @"CREATE TABLE IF NOT EXISTS FileChunks (id INTEGER PRIMARY KEY AUTOINCREMENT,file_id INTEGER NOT NULL,chunk_index INTEGER NOT NULL,chunk_data BLOB NOT NULL);";using (var command = new SQLiteCommand(createTableQuery, connection)){command.ExecuteNonQuery();}}// 插入文件分块string filePath = "example.pdf"; // 替换为你的文件路径InsertFileChunks(filePath, 1, connectionString); // 假设文件 ID 为 1// 重新组装文件ReassembleFile(1, "output.pdf", connectionString); // 输出为 output.pdf}static void InsertFileChunks(string filePath, int fileId, string connectionString){int chunkSize = 1024 * 1024; // 每块大小为 1MBbyte[] buffer = new byte[chunkSize];int chunkIndex = 0;using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))using (var connection = new SQLiteConnection(connectionString)){connection.Open();string insertQuery = "INSERT INTO FileChunks (file_id, chunk_index, chunk_data) VALUES (@file_id, @chunk_index, @chunk_data)";using (var command = new SQLiteCommand(insertQuery, connection)){int bytesRead;while ((bytesRead = fileStream.Read(buffer, 0, chunkSize)) > 0){byte[] chunkData = new byte[bytesRead];Array.Copy(buffer, chunkData, bytesRead);command.Parameters.Clear();command.Parameters.AddWithValue("@file_id", fileId);command.Parameters.AddWithValue("@chunk_index", chunkIndex++);command.Parameters.AddWithValue("@chunk_data", chunkData);command.ExecuteNonQuery();}}}Console.WriteLine($"File '{filePath}' has been stored in chunks.");}static void ReassembleFile(int fileId, string outputPath, string connectionString){using (var connection = new SQLiteConnection(connectionString)){connection.Open();string selectQuery = "SELECT chunk_data FROM FileChunks WHERE file_id = @file_id ORDER BY chunk_index";using (var command = new SQLiteCommand(selectQuery, connection)){command.Parameters.AddWithValue("@file_id", fileId);using (var reader = command.ExecuteReader())using (var outputFile = new FileStream(outputPath, FileMode.Create, FileAccess.Write)){while (reader.Read()){byte[] chunkData = (byte[])reader["chunk_data"];outputFile.Write(chunkData, 0, chunkData.Length);}}}}Console.WriteLine($"File has been reassembled to '{outputPath}'.");}
}