Smart.Data.Npgsql 5.0.0-beta.1

Smart.Data.Npgsql

English | 中文

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

English

Smart.Data.Npgsql is a lightweight PostgreSQL database operation library based on Npgsql, supporting .NET 6, 8, 9, and 10. It simplifies asynchronous database interactions, provides dependency injection support, and handles connection management efficiently.

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.

Installation

Install the package via NuGet:

dotnet add package Smart.Data.Npgsql

Quick Start

1. Initialization

You can instantiate SmartNpgsqlService directly or use Dependency Injection.

Direct Instantiation:

using Smart.Data.Npgsql;

var connectionString = "Host=localhost;Database=test;Username=myuser;Password=mypassword;";
var db = new SmartNpgsqlService(connectionString);

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

Returns the number of rows affected.

using Npgsql;

// Note: PostgreSQL is case-sensitive for identifiers if quoted.
// It is recommended to quote identifiers to avoid ambiguity.
string sql = @"INSERT INTO ""users"" (""Name"", ""Age"") VALUES (@name, @age)";
var parameters = new NpgsqlParameter[]
{
    new NpgsqlParameter("@name", "Alice"),
    new NpgsqlParameter("@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 NpgsqlDataReader. Note: You must manage the disposal of the reader.

string sql = @"SELECT * FROM ""users"" WHERE ""Age"" > @age";
var parameters = new NpgsqlParameter[] { new NpgsqlParameter("@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.Npgsql;

// ...
builder.Services.AddSmartNpgsql("your_connection_string");

Then inject SmartNpgsqlService into your classes:

public class MyService
{
    private readonly SmartNpgsqlService _db;

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

Important Notes

1. Identifier Quoting:
   • When column or table names are PostgreSQL keywords (e.g., Desc, User), they MUST be wrapped in double quotes (e.g., "Desc").
   • If identifiers contain uppercase letters or special characters, they MUST be wrapped in double quotes (e.g., "Name").
   • For consistency, it is often good practice to always double-quote identifiers.
2. Transaction Row Count: The integer returned by ExecuteNonQuerysAsync is an aggregate of affected rows and might not be precise for all logic. Use it as an indicator of success (non-negative) rather than for exact accounting.
3. Stored Procedures:
   • ExecuteNonQuerysAsync (batch transaction) does not support stored procedures.
   • Other methods support stored procedures.
4. SQL Injection: Always use NpgsqlParameter for user inputs to prevent SQL injection attacks.

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

中文

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

功能特性

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

安装

通过 NuGet 安装：

dotnet add package Smart.Data.Npgsql

快速入门

1. 初始化

你可以直接实例化 SmartNpgsqlService，也可以使用依赖注入。

直接实例化：

using Smart.Data.Npgsql;

var connectionString = "Host=localhost;Database=test;Username=myuser;Password=mypassword;";
var db = new SmartNpgsqlService(connectionString);

2. 执行增删改 (ExecuteNonQuery)

返回受影响的行数。

using Npgsql;

// 注意：PostgreSQL 对未加引号的标识符默认转换为小写。
// 建议使用双引号包裹标识符以避免歧义，特别是包含大写字母时。
string sql = @"INSERT INTO ""users"" (""Name"", ""Age"") VALUES (@name, @age)";
var parameters = new NpgsqlParameter[]
{
    new NpgsqlParameter("@name", "Alice"),
    new NpgsqlParameter("@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)

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

string sql = @"SELECT * FROM ""users"" WHERE ""Age"" > @age";
var parameters = new NpgsqlParameter[] { new NpgsqlParameter("@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.Npgsql;

// ...
builder.Services.AddSmartNpgsql("your_connection_string");

然后在你的类中注入 SmartNpgsqlService：

public class MyService
{
    private readonly SmartNpgsqlService _db;

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

注意事项

1. 标识符引用：
   • 当列名或表名是 PostgreSQL 的关键字（如 Desc、User）时，必须使用双引号包裹（例如 "Desc"）。
   • 当标识符包含大写字母或特殊字符时，必须使用双引号包裹（例如 "Name"），否则 PostgreSQL 默认会将其转换为小写。
   • 为了保持一致性，建议尽可能对列名和表名使用双引号。
2. 事务行数：ExecuteNonQuerysAsync 返回的整数是受影响行数的总和，可能不完全精确。建议将其视为执行成功（非负数）的标志，而不是用于精确计算。
3. 存储过程：
   • ExecuteNonQuerysAsync（批量事务）不支持存储过程。
   • 其他方法支持存储过程。
4. SQL Injection：始终使用 NpgsqlParameter 处理用户输入，以防止 SQL 注入攻击。

Developed by zenglei