Smart.Data.SqlServer 4.0.2

Smart.Data.SqlServer

English | 中文

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

English

Smart.Data.SqlServer is a lightweight SQL Server database operation library based on Microsoft.Data.SqlClient, supporting .NET 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.SqlServer

Quick Start

1. Initialization

You can instantiate SmartMSSqlService directly or use Dependency Injection.

Direct Instantiation:

using Smart.Data.SqlServer;

var connectionString = "Server=.;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
var db = new SmartMSSqlService(connectionString);

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

Returns the number of rows affected.

using Microsoft.Data.SqlClient;

string sql = "INSERT INTO Employees (FirstName, Age) VALUES (@name, @age)";
var parameters = new SqlParameter[]
{
    new SqlParameter("@name", "Alice"),
    new SqlParameter("@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 Employees";
var count = await db.ExecuteScalarAsync(sql);

5. Execute Reader

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

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

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

6. Query to DataTable

Fetches data and populates a DataTable.

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

Dependency Injection

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

using Smart.Data.SqlServer;

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

Then inject SmartMSSqlService into your classes:

public class MyService
{
    private readonly SmartMSSqlService _db;

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

Important Notes

1. Identifier Quoting: If your column or table names are SQL Server keywords (e.g., Order, User), you must wrap them in square brackets (e.g., [Order]).
2. Transaction Row Count: The integer returned by ExecuteNonQuerysAsync is an aggregate of affected rows.
3. Stored Procedures:
   • ExecuteNonQuerysAsync (batch transaction) does not support stored procedures.
   • Other methods support stored procedures.
4. SQL Injection: Always use SqlParameter for user inputs to prevent SQL injection attacks.
5. Target Frameworks: This library primarily supports .NET 8 and .NET 9 (due to dependencies on newer Microsoft.Data.SqlClient versions).

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

中文

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

功能特性

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

安装

通过 NuGet 安装：

dotnet add package Smart.Data.SqlServer

快速入门

1. 初始化

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

直接实例化：

using Smart.Data.SqlServer;

var connectionString = "Server=.;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
var db = new SmartMSSqlService(connectionString);

2. 执行增删改 (ExecuteNonQuery)

返回受影响的行数。

using Microsoft.Data.SqlClient;

string sql = "INSERT INTO Employees (FirstName, Age) VALUES (@name, @age)";
var parameters = new SqlParameter[]
{
    new SqlParameter("@name", "Alice"),
    new SqlParameter("@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 Employees";
var count = await db.ExecuteScalarAsync(sql);

5. 执行读取器 (ExecuteReader)

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

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

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

6. 查询并返回 DataTable

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

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

依赖注入

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

using Smart.Data.SqlServer;

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

然后在你的类中注入 SmartMSSqlService：

public class MyService
{
    private readonly SmartMSSqlService _db;

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

注意事项

1. 标识符引用：如果列名或表名是 SQL Server 的关键字（如 Order、User），必须使用方括号将其包裹（例如 [Order]）。
2. 事务行数：ExecuteNonQuerysAsync 返回的整数是受影响行数的总和。
3. 存储过程：
   • ExecuteNonQuerysAsync（批量事务）不支持存储过程。
   • 其他方法支持存储过程。
4. SQL 注入：始终使用 SqlParameter 处理用户输入，以防止 SQL 注入攻击。
5. 目标框架：本项目主要支持 .NET 8 和 .NET 9（依赖于较新版本的 Microsoft.Data.SqlClient）。

Developed by zenglei