快捷導(dǎo)航

C# 使用Dapper與金倉數(shù)據(jù)庫交互的實(shí)現(xiàn)步驟

更新時間：2025年12月16日 09:57:45 作者：caifox菜狐貍

本文主要介紹了C# 使用 Dapper 與金倉數(shù)據(jù)庫交互,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1. 安裝必要的 NuGet 包

Install-Package Dapper
Install-Package Npgsql  # 金倉數(shù)據(jù)庫兼容 PostgreSQL 協(xié)議

2. 基本配置和連接

using System.Data;
using Dapper;
using Npgsql;

// 連接字符串配置
string connectionString = "Host=localhost;Port=54321;Database=mykingbase;User Id=myuser;Password=mypassword;";

// 創(chuàng)建連接
using (IDbConnection connection = new NpgsqlConnection(connectionString))
{
    connection.Open();
    
    // 執(zhí)行查詢
    var result = connection.Query<string>("SELECT version()");
    Console.WriteLine(result.First());
}

3. 完整的 CRUD 操作示例

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class UserRepository
{
    private readonly string _connectionString;

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    // 查詢
    public async Task<User> GetUserByIdAsync(int id)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        return await connection.QueryFirstOrDefaultAsync<User>(
            "SELECT * FROM users WHERE id = @Id", new { Id = id });
    }

    // 插入
    public async Task<int> InsertUserAsync(User user)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        var sql = @"INSERT INTO users (name, email, created_at) 
                   VALUES (@Name, @Email, @CreatedAt) RETURNING id";
        return await connection.ExecuteScalarAsync<int>(sql, user);
    }

    // 更新
    public async Task<int> UpdateUserAsync(User user)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        var sql = @"UPDATE users SET name = @Name, email = @Email 
                   WHERE id = @Id";
        return await connection.ExecuteAsync(sql, user);
    }

    // 刪除
    public async Task<int> DeleteUserAsync(int id)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        return await connection.ExecuteAsync(
            "DELETE FROM users WHERE id = @Id", new { Id = id });
    }
}

4. 事務(wù)處理

public async Task<bool> TransferBalanceAsync(int fromUserId, int toUserId, decimal amount)
{
    using var connection = new NpgsqlConnection(_connectionString);
    connection.Open();
    
    using var transaction = connection.BeginTransaction();
    try
    {
        // 扣款
        await connection.ExecuteAsync(
            "UPDATE accounts SET balance = balance - @Amount WHERE user_id = @UserId",
            new { Amount = amount, UserId = fromUserId }, transaction);

        // 存款
        await connection.ExecuteAsync(
            "UPDATE accounts SET balance = balance + @Amount WHERE user_id = @UserId",
            new { Amount = amount, UserId = toUserId }, transaction);

        transaction.Commit();
        return true;
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

5. 連接字符串配置建議

在 appsettings.json 中配置：

{
  "ConnectionStrings": {
    "KingbaseConnection": "Host=localhost;Port=54321;Database=mydb;User Id=myuser;Password=mypassword;Pooling=true;Minimum Pool Size=5;Maximum Pool Size=100;"
  }
}

注意事項(xiàng)：

驅(qū)動選擇：金倉數(shù)據(jù)庫通常兼容 PostgreSQL 協(xié)議，因此使用 Npgsql 驅(qū)動
數(shù)據(jù)類型映射：注意金倉數(shù)據(jù)庫特有的數(shù)據(jù)類型可能需要特殊處理
連接池：建議啟用連接池以提高性能
錯誤處理：妥善處理數(shù)據(jù)庫連接異常和超時

這樣配置后，你就可以像使用其他數(shù)據(jù)庫一樣，使用 Dapper 與金倉數(shù)據(jù)庫進(jìn)行高效的數(shù)據(jù)交互了。

到此這篇關(guān)于C# 使用Dapper與金倉數(shù)據(jù)庫交互的實(shí)現(xiàn)步驟的文章就介紹到這了,更多相關(guān)C# Dapper與金倉數(shù)據(jù)庫交互內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: