Smart.Data.Sqlite 4.0.2

Smart.Data.Sqlite

English | 中文

<a name="english"></a>

English

Smart.Data.Sqlite is a lightweight SQLite database operation library based on Microsoft.Data.Sqlite, supporting .NET 6, 8, 9, and 10. It simplifies asynchronous database interactions, provides dependency injection support, and handles connection management efficiently for both file-based and in-memory databases.

Features

• Asynchronous Operations: Full support for async/await pattern for all database operations.
• Connection Management: Automatically handles opening and disposing of connections.
• Transaction Support: Built-in support for batch execution of SQL statements within a transaction.
• Dependency Injection: Easy integration with .NET Core DI container.
• Security: Promotes the use of parameterized queries to prevent SQL injection.
• DataTable Support: Convenient method to query data directly into a DataTable.
• Zero Configuration: Easy to use with embedded database files or in-memory databases.

Installation

Install the package via NuGet:

dotnet add package Smart.Data.Sqlite

Quick Start

1. Initialization

You can instantiate SmartSqliteService directly or use Dependency Injection. Supports both file paths and in-memory connection strings.

Direct Instantiation:

using Smart.Data.Sqlite;

// File-based database
var connectionString = "Data Source=mydb.sqlite";
// In-memory database
// var connectionString = "Data Source=:memory:";

var db = new SmartSqliteService(connectionString);

2. Execute Non-Query (Insert, Update, Delete)

Returns the number of rows affected.

using Microsoft.Data.Sqlite;

string sql = "INSERT INTO users (Name, Age) VALUES (@name, @age)";
var parameters = new SqliteParameter[]
{
    new SqliteParameter("@name", "Alice"),
    new SqliteParameter("@age", 25)
};

int rowsAffected = await db.ExecuteNonQueryAsync(sql, parameters);

3. Execute Batch Transaction

Executes multiple SQL statements within a single transaction. If any statement fails, the entire transaction is rolled back.

var sqlList = new List<string>
{
    "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
    "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
};

// Returns the total estimated rows affected
int totalRows = await db.ExecuteNonQuerysAsync(sqlList);

4. Execute Scalar

Returns the first column of the first row in the result set.

string sql = "SELECT COUNT(*) FROM users";
var count = await db.ExecuteScalarAsync(sql);

5. Execute Reader

Returns a SqliteDataReader. Note: You must manage the disposal of the reader.

string sql = "SELECT * FROM users WHERE Age > @age";
var parameters = new SqliteParameter[] { new SqliteParameter("@age", 18) };

using var reader = await db.ExecuteReaderAsync(sql, parameters);
while (await reader.ReadAsync())
{
    Console.WriteLine(reader["Name"]);
}

6. Query to DataTable

Fetches data and populates a DataTable.

string sql = "SELECT * FROM users";
DataTable dt = await db.ExecuteQueryToDataTableAsync(sql);

Dependency Injection

Register the service in your Program.cs or Startup.cs:

using Smart.Data.Sqlite;

// ...
builder.Services.AddSmartSqlite("Data Source=app.db");

Then inject SmartSqliteService into your classes:

public class MyService
{
    private readonly SmartSqliteService _db;

    public MyService(SmartSqliteService db)
    {
        _db = db;
    }
}

Important Notes

1. Stored Procedures: SQLite does not support stored procedures. The methods in this library that mention stored procedures in their signatures are primarily for interface consistency or compatibility with other database providers; they will likely fail or treat the input as a raw SQL command if used with SQLite.
2. Reserved Keywords: If your column or table names are SQLite keywords (e.g., Group, Order), you should wrap them in double quotes (e.g., "Group").
3. Transaction Row Count: The integer returned by ExecuteNonQuerysAsync is an aggregate of affected rows.
4. In-Memory Databases: If using "Data Source=:memory:", remember that the database is lost when the connection is closed. Since SmartSqliteService manages connections per call (opening and closing), strictly using it for in-memory DBs might require careful lifecycle management or keeping a connection open elsewhere if you need persistence across calls (though this library is designed for stateless-like access). However, for shared cache mode or specific connection strings, behavior may vary.
5. SQL Injection: Always use SqliteParameter for user inputs.

<a name="chinese"></a>

中文

Smart.Data.Sqlite 是一个基于 Microsoft.Data.Sqlite 封装的轻量级 SQLite 数据库操作库，支持 .NET 6, 8, 9, 10。它简化了异步数据库交互，提供了依赖注入支持，并能高效地管理文件数据库和内存数据库的连接。

功能特性

• 异步操作：全线支持 async/await 异步编程模式。
• 连接管理：自动处理数据库连接的打开和释放。
• 事务支持：内置支持在同一个事务中批量执行多条 SQL 语句。
• 依赖注入：提供扩展方法，轻松集成到 .NET Core DI 容器中。
• 安全防护：推荐使用参数化查询，有效防止 SQL 注入。
• DataTable 支持：提供便捷方法直接将查询结果转换为 DataTable。
• 零配置：轻松支持嵌入式文件数据库和内存数据库。

安装

通过 NuGet 安装：

dotnet add package Smart.Data.Sqlite

快速入门

1. 初始化

你可以直接实例化 SmartSqliteService，也可以使用依赖注入。支持文件路径和内存数据库连接字符串。

直接实例化：

using Smart.Data.Sqlite;

// 文件数据库
var connectionString = "Data Source=mydb.sqlite";
// 内存数据库
// var connectionString = "Data Source=:memory:";

var db = new SmartSqliteService(connectionString);

2. 执行增删改 (ExecuteNonQuery)

返回受影响的行数。

using Microsoft.Data.Sqlite;

string sql = "INSERT INTO users (Name, Age) VALUES (@name, @age)";
var parameters = new SqliteParameter[]
{
    new SqliteParameter("@name", "Alice"),
    new SqliteParameter("@age", 25)
};

int rowsAffected = await db.ExecuteNonQueryAsync(sql, parameters);

3. 批量事务执行 (ExecuteBatch Transaction)

在一个事务中执行多条 SQL 语句。如果任何一条语句失败，整个事务将回滚。

var sqlList = new List<string>
{
    "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
    "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
};

// 返回受影响的总行数（估算值）
int totalRows = await db.ExecuteNonQuerysAsync(sqlList);

4. 执行标量查询 (ExecuteScalar)

返回结果集中第一行第一列的值。

string sql = "SELECT COUNT(*) FROM users";
var count = await db.ExecuteScalarAsync(sql);

5. 执行读取器 (ExecuteReader)

返回 SqliteDataReader。注意：外部调用者需要负责释放 Reader。

string sql = "SELECT * FROM users WHERE Age > @age";
var parameters = new SqliteParameter[] { new SqliteParameter("@age", 18) };

using var reader = await db.ExecuteReaderAsync(sql, parameters);
while (await reader.ReadAsync())
{
    Console.WriteLine(reader["Name"]);
}

6. 查询并返回 DataTable

执行查询并将结果填充到 DataTable 中。

string sql = "SELECT * FROM users";
DataTable dt = await db.ExecuteQueryToDataTableAsync(sql);

依赖注入

在 Program.cs 或 Startup.cs 中注册服务：

using Smart.Data.Sqlite;

// ...
builder.Services.AddSmartSqlite("Data Source=app.db");

然后在你的类中注入 SmartSqliteService：

public class MyService
{
    private readonly SmartSqliteService _db;

    public MyService(SmartSqliteService db)
    {
        _db = db;
    }
}

注意事项

1. 存储过程：SQLite 不支持存储过程。库中涉及存储过程的方法签名主要是为了接口一致性，在 SQLite 中使用时请传入 SQL 语句。
2. 保留关键字：如果列名或表名是 SQLite 的关键字（如 Group、Order），需要使用双引号包裹（例如 "Group"）。
3. 事务行数：ExecuteNonQuerysAsync 返回的整数是受影响行数的总和。
4. 内存数据库：如果使用 "Data Source=:memory:"，请注意数据库内容会在连接关闭时丢失。由于 SmartSqliteService 默认在每次调用时管理连接（打开/关闭），纯内存模式可能需要特殊的生命周期管理或共享缓存配置才能持久化数据。
5. SQL 注入：始终使用 SqliteParameter 处理用户输入，以防止 SQL 注入攻击。

Developed by zenglei