愛比的新手筆記

在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()}");
        }
    }
}

用get method, DB?script = 語法, 來取資料
而post method, DB?script=語法&commit=true, 來更新資料, 試跑但不想更新db, 就不要寫commit

Taiwan is a country. 臺灣是我的國家