请使用 Microsoft.Data.Sqlite 来编写针对Sqlite数据访问帮助类,至少拥有对DataTable、DataSet格式的支持
时间: 2023-11-22 13:54:26 浏览: 41
好的,下面是一个简单的 Sqlite 数据访问帮助类,它使用 Microsoft.Data.Sqlite 库来实现对 Sqlite 数据库的基本操作,并支持将查询结果转换为 DataTable 和 DataSet 格式:
```csharp
using Microsoft.Data.Sqlite;
using System.Data;
public class SqliteHelper
{
private string connectionString;
public SqliteHelper(string connectionString)
{
this.connectionString = connectionString;
}
public DataTable ExecuteQuery(string sql, params SqliteParameter[] parameters)
{
using (var connection = new SqliteConnection(connectionString))
{
connection.Open();
using (var command = new SqliteCommand(sql, connection))
{
command.Parameters.AddRange(parameters);
using (var reader = command.ExecuteReader())
{
var dataTable = new DataTable();
dataTable.Load(reader);
return dataTable;
}
}
}
}
public DataSet ExecuteQueryDataSet(string sql, params SqliteParameter[] parameters)
{
using (var connection = new SqliteConnection(connectionString))
{
connection.Open();
using (var command = new SqliteCommand(sql, connection))
{
command.Parameters.AddRange(parameters);
using (var adapter = new SqliteDataAdapter(command))
{
var dataSet = new DataSet();
adapter.Fill(dataSet);
return dataSet;
}
}
}
}
public int ExecuteNonQuery(string sql, params SqliteParameter[] parameters)
{
using (var connection = new SqliteConnection(connectionString))
{
connection.Open();
using (var command = new SqliteCommand(sql, connection))
{
command.Parameters.AddRange(parameters);
return command.ExecuteNonQuery();
}
}
}
}
```
使用示例:
```csharp
// 创建 SqliteHelper 实例
var helper = new SqliteHelper("Data Source=mydb.db");
// 执行查询并转换为 DataTable
var dataTable = helper.ExecuteQuery("SELECT * FROM mytable WHERE id = @id", new SqliteParameter("@id", 1));
// 执行查询并转换为 DataSet
var dataSet = helper.ExecuteQueryDataSet("SELECT * FROM mytable");
// 执行非查询语句
var rowsAffected = helper.ExecuteNonQuery("UPDATE mytable SET name = @name WHERE id = @id",
new SqliteParameter("@name", "new name"), new SqliteParameter("@id", 1));
```
注意需要在项目中安装 Microsoft.Data.Sqlite 库,可以使用 NuGet 进行安装。