[.Net] 用API存取DB资料

写个存取DB小功能

在Program.cs要先写

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
//todo 解密
//此动作会在Controller建构子注入SqlConnection
builder.Services.AddScoped<IDbConnection>(p => new SqlConnection(connectionString));

在Controller加上

using Microsoft.AspNetCore.Mvc;
using System.Data;

namespace xxxxx.Controllers;

[ApiController]
[Route("[controller]")]
public class DBController : ControllerBase
{
    private readonly IDbConnection Conn;

    public DBController(IDbConnection conn)
    {
        Conn = conn;
    }

    [HttpGet]
    public IActionResult GetData(string script)
    {
        try
        {
            var result = new List<Dictionary<string, object>>();
            Conn.Open();
            using IDbTransaction tran = Conn.BeginTransaction(IsolationLevel.ReadUncommitted);
            using IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandText = script;
            cmd.Transaction = tran;
            using (IDataReader reader = cmd.ExecuteReader())
                while (reader.Read())
                {
                    var rowDict = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        rowDict[reader.GetName(i)] = reader.GetValue(i);
                    }
                    result.Add(rowDict);
                }
            tran.Rollback();
            return Ok(result);
        }
        catch (Exception ex)
        {
            return StatusCode(500, $"错了: {ex.ToString()}");
        }
    }

    [HttpPost]
    public IActionResult Update(string script, bool commit = false)
    {
        try
        {
            Conn.Open();
            using IDbCommand cmd = Conn.CreateCommand();
            using IDbTransaction tran = Conn.BeginTransaction();
            cmd.CommandText = script;
            cmd.Transaction = tran;
            int count = cmd.ExecuteNonQuery();
            if (commit) tran.Commit();
            else tran.Rollback();
            return Ok($"影响 {count} 笔");
        }
        catch (Exception ex)
        {
            return StatusCode(500, $"错了: {ex.ToString()}");
        }
    }
}

Taiwan is a country. 台湾是我的国家