The "Methods of Reading and Writing Files to SQLite Database" blog introduces the method of reading and writing files to SQLite databases. The example uses Python language. This article is a specific example of several methods of using C# to read and write files to SQLite databases. If some concepts are vague, please refer to the author's previous blog.
1. Use BLOB to store files
Sample code:
using System;
using ;
using ;
class Program
{
static void Main(string[] args)
{
string databasePath = "";
string connectionString = $"Data Source={databasePath};Version=3;";
// Create databases and tables using (var connection = new SQLiteConnection(connectionString))
{
();
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))
{
();
}
}
// Insert file into database string filePath = ""; // Replace with your file path InsertFile(filePath, connectionString);
// Read files from the database ReadFile(1, connectionString); // Assume that the file with ID 1 is read }
static void InsertFile(string filePath, string connectionString)
{
byte[] fileData = (filePath);
string fileName = (filePath);
using (var connection = new SQLiteConnection(connectionString))
{
();
string insertQuery = "INSERT INTO Files (name, data) VALUES (@name, @data)";
using (var command = new SQLiteCommand(insertQuery, connection))
{
("@name", fileName);
("@data", fileData);
();
}
}
($"File '{fileName}' has been saved to the database.");
}
static void ReadFile(int fileId, string connectionString)
{
using (var connection = new SQLiteConnection(connectionString))
{
();
string selectQuery = "SELECT name, data FROM Files WHERE id = @id";
using (var command = new SQLiteCommand(selectQuery, connection))
{
("@id", fileId);
using (var reader = ())
{
if (())
{
string fileName = (0);
byte[] fileData = (byte[])reader["data"];
(fileName, fileData);
($"File '{fileName}' has been restored from the database.");
}
}
}
}
}
}2. Store file path
Sample code:
using System;
using ;
class Program
{
static void Main(string[] args)
{
string databasePath = "";
string connectionString = $"Data Source={databasePath};Version=3;";
// Create databases and tables using (var connection = new SQLiteConnection(connectionString))
{
();
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))
{
();
}
}
// Insert file path string filePath = ""; // Replace with your file path InsertFilePath(filePath, connectionString);
// Read file path from database GetFilePath(1, connectionString); // Assume that the file path with ID 1 is read }
static void InsertFilePath(string filePath, string connectionString)
{
string fileName = (filePath);
using (var connection = new SQLiteConnection(connectionString))
{
();
string insertQuery = "INSERT INTO FilePaths (name, path) VALUES (@name, @path)";
using (var command = new SQLiteCommand(insertQuery, connection))
{
("@name", fileName);
("@path", filePath);
();
}
}
($"File path '{filePath}' has been saved to the database.");
}
static void GetFilePath(int fileId, string connectionString)
{
using (var connection = new SQLiteConnection(connectionString))
{
();
string selectQuery = "SELECT name, path FROM FilePaths WHERE id = @id";
using (var command = new SQLiteCommand(selectQuery, connection))
{
("@id", fileId);
using (var reader = ())
{
if (())
{
string fileName = (0);
string filePath = (1);
($"File '{fileName}' is located at '{filePath}'.");
}
}
}
}
}
}3. Store files in chunks
Sample code:
using System;
using ;
using ;
class Program
{
static void Main(string[] args)
{
string databasePath = "";
string connectionString = $"Data Source={databasePath};Version=3;";
// Create databases and tables using (var connection = new SQLiteConnection(connectionString))
{
();
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))
{
();
}
}
// Insert file chunking string filePath = ""; // Replace with your file path InsertFileChunks(filePath, 1, connectionString); // Assume that the file ID is 1
// Reassemble the file ReassembleFile(1, "", connectionString); // The output is }
static void InsertFileChunks(string filePath, int fileId, string connectionString)
{
int chunkSize = 1024 * 1024; // Each block is 1MB in size byte[] buffer = new byte[chunkSize];
int chunkIndex = 0;
using (var fileStream = new FileStream(filePath, , ))
using (var connection = new SQLiteConnection(connectionString))
{
();
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 = (buffer, 0, chunkSize)) > 0)
{
byte[] chunkData = new byte[bytesRead];
(buffer, chunkData, bytesRead);
();
("@file_id", fileId);
("@chunk_index", chunkIndex++);
("@chunk_data", chunkData);
();
}
}
}
($"File '{filePath}' has been stored in chunks.");
}
static void ReassembleFile(int fileId, string outputPath, string connectionString)
{
using (var connection = new SQLiteConnection(connectionString))
{
();
string selectQuery = "SELECT chunk_data FROM FileChunks WHERE file_id = @file_id ORDER BY chunk_index";
using (var command = new SQLiteCommand(selectQuery, connection))
{
("@file_id", fileId);
using (var reader = ())
using (var outputFile = new FileStream(outputPath, , ))
{
while (())
{
byte[] chunkData = (byte[])reader["chunk_data"];
(chunkData, 0, );
}
}
}
}
($"File has been reassembled to '{outputPath}'.");
}
}The above is the detailed content of C# implementing file reading and writing to SQLite database. For more information about C# file reading and writing to database, please pay attention to my other related articles!