Smart.Data.Oracle 4.1.0

Smart.Data.Oracle

English | 中文

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

English

Smart.Data.Oracle is a lightweight Oracle database operation library based on Oracle.ManagedDataAccess.Core, 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.Oracle

Quick Start

1. Initialization

You can instantiate SmartOracleService directly or use Dependency Injection.

Direct Instantiation:

using Smart.Data.Oracle;

var connectionString = "User Id=scott;Password=tiger;Data Source=localhost:1521/orcl;";
var db = new SmartOracleService(connectionString);

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

Returns the number of rows affected.

using Oracle.ManagedDataAccess.Client;

// Note: Oracle uses colon (:) for parameters.
string sql = "INSERT INTO users (Name, Age) VALUES (:name, :age)";
var parameters = new OracleParameter[]
{
    new OracleParameter("name", "Alice"),
    new OracleParameter("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 OracleDataReader. Note: You must manage the disposal of the reader.

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

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

Then inject SmartOracleService into your classes:

public class MyService
{
    private readonly SmartOracleService _db;

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

Important Notes

1. Parameter Syntax: Oracle uses a colon (:) prefix for parameters (e.g., :name) instead of the @ symbol used in SQL Server or MySQL.
2. Reserved Keywords: If your column or table names are Oracle reserved keywords (e.g., NUMBER, DESC), you must wrap them in double quotes (e.g., "DESC").
3. 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.
4. Stored Procedures:
   • ExecuteNonQuerysAsync (batch transaction) does not support stored procedures.
   • Other methods support stored procedures.
5. SQL Injection: Always use OracleParameter for user inputs to prevent SQL injection attacks.

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

中文

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

功能特性

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

安装

通过 NuGet 安装：

dotnet add package Smart.Data.Oracle

快速入门

1. 初始化

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

直接实例化：

using Smart.Data.Oracle;

var connectionString = "User Id=scott;Password=tiger;Data Source=localhost:1521/orcl;";
var db = new SmartOracleService(connectionString);

2. 执行增删改 (ExecuteNonQuery)

返回受影响的行数。

using Oracle.ManagedDataAccess.Client;

// 注意：Oracle 使用冒号 (:) 作为参数前缀。
string sql = "INSERT INTO users (Name, Age) VALUES (:name, :age)";
var parameters = new OracleParameter[]
{
    new OracleParameter("name", "Alice"),
    new OracleParameter("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)

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

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

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

然后在你的类中注入 SmartOracleService：

public class MyService
{
    private readonly SmartOracleService _db;

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

注意事项

1. 参数语法：Oracle 使用冒号 (:) 作为参数前缀（例如 :name），而不是 SQL Server 或 MySQL 中常用的 @ 符号。
2. 保留关键字：如果列名或表名是 Oracle 的保留关键字（如 NUMBER、DESC），必须使用双引号包裹（例如 "DESC"）。
3. 事务行数：ExecuteNonQuerysAsync 返回的整数是受影响行数的总和，可能不完全精确。建议将其视为执行成功（非负数）的标志，而不是用于精确计算。
4. 存储过程：
   • ExecuteNonQuerysAsync（批量事务）不支持存储过程。
   • 其他方法支持存储过程。
5. SQL 注入：始终使用 OracleParameter 处理用户输入，以防止 SQL 注入攻击。

Developed by zenglei