给我一个.NET 6.0中的 sqllite帮助类
时间: 2024-05-29 12:12:13 浏览: 121
以下是一个.NET 6.0中的SQLite帮助类示例:
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
namespace SQLiteDemo
{
public class SQLiteHelper
{
private readonly string connectionString;
public SQLiteHelper(string dbFilePath)
{
connectionString = $"Data Source={dbFilePath};";
}
public async Task<int> ExecuteNonQueryAsync(string commandText, params SqliteParameter[] parameters)
{
using (var connection = new SqliteConnection(connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = commandText;
command.Parameters.AddRange(parameters);
return await command.ExecuteNonQueryAsync();
}
}
}
public async Task<object> ExecuteScalarAsync(string commandText, params SqliteParameter[] parameters)
{
using (var connection = new SqliteConnection(connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = commandText;
command.Parameters.AddRange(parameters);
return await command.ExecuteScalarAsync();
}
}
}
public async Task<IEnumerable<T>> ExecuteReaderAsync<T>(string commandText, Func<IDataRecord, T> mapper, params SqliteParameter[] parameters)
{
var results = new List<T>();
using (var connection = new SqliteConnection(connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = commandText;
command.Parameters.AddRange(parameters);
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
results.Add(mapper(reader));
}
}
}
}
return results;
}
}
}
使用示例:
var dbFilePath = "path/to/database/file.db";
var helper = new SQLiteHelper(dbFilePath);
// Execute a non-query command
var insertCommandText = "INSERT INTO MyTable (Name, Age) VALUES (@name, @age)";
var parameters = new[]
{
new SqliteParameter("@name", "John"),
new SqliteParameter("@age", 30),
};
var rowsAffected = await helper.ExecuteNonQueryAsync(insertCommandText, parameters);
// Execute a scalar command
var countCommandText = "SELECT COUNT(*) FROM MyTable";
var count = (long)await helper.ExecuteScalarAsync(countCommandText);
// Execute a reader command
var selectCommandText = "SELECT * FROM MyTable";
var records = await helper.ExecuteReaderAsync(selectCommandText, r => new { Name = r.GetString(0), Age = r.GetInt32(1) });