我要评论《文件读写到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; // 每块大小为 1mb
byte[] 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}'.");
}
}以上就是c#实现文件读写到sqlite数据库的详细内容，更多关于c#文件读写到数据库的资料请关注代码网其它相关文章！
发表评论