C#: connection database ใน .NET Core

Byphunsanit

.NET, Connection, connection strings, DbContext, DSN, Models, MVC

หลังจากได้ Connection Strings มาจากวิธี C#: Connection string ก็สามารถนำมาใช้ในโปรแกรมที่เขียนให้ติดต่อกับ database โดยใช้ .NET Core, MVC และ Entity Framework Core ( .NET Core EF ) Data Source Name ( DSN )

เก็บข้อมูล Connection Strings สามารถเก็บได้ที่ ไฟล์ appsettings.json โดยเพิ่ม “ConnectionStrings” ใน
appsettings.json
เช่น

...
  "ConnectionStrings": {
    "{ DSN name }": "{ Connection Strings }"
  },
...

...
  "ConnectionStrings": {
    "DSNDevContext": "Encrypt=False;Initial Catalog=Pine;Password=%mpLST38c2q8K2;Persist Security Info=True;Trust Server Certificate=True;User ID=T8NSFTE8FyRV;"
  },
...

จากนั้นจะสามารถดึงช้อมูลมาใช้ได้โดยเพิ่ม
/Program.cs

...
//This section below is for connection string
var connectionString = builder.Configuration.GetConnectionString("DSNDevContext");
builder.Services.AddDbContext<DSNDevDBContext>(options => options.UseSqlServer(connectionString));
...

จากนั้นสร้างส่วนเชื่อมโยง model ด้วยกัน เช่น
Models/DSNDevDBContext.cs

namespace Pine.Models;

using Microsoft.EntityFrameworkCore;

public class DSNDevDBContext(DbContextOptions<DSNDevDBContext> options) : DbContext(options)
{
    protected readonly IConfiguration Configuration;

    //model table
    public virtual DbSet<line_logins> line_Logins { get; set; }

    //end model table
}

สร้าง model แทนตัว database table เช่น
/Model/line_logins.cs

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics.CodeAnalysis;

namespace Pine.Models
{
    public class line_logins
    {
        [Key]
        [Column("client_id", Order = 0, TypeName = "VARCHAR(50)")]
        [Comment("The URL of the blog")]
        [MaxLength(50)]
        public string client_id { get; set; }

        [Required]
        [Column("client_secret", Order = 1, TypeName = "VARCHAR(50)")]
        [Comment("The URL of the blog")]
        [MaxLength(50)]
        public string client_secret { get; set; }

        [Required]
        [Column("code", Order = 2, TypeName = "VARCHAR(50)")]
        [Comment("The URL of the blog")]
        [MaxLength(50)]
        public string code { get; set; }

        [AllowNull]
        [Column("code_verifier", Order = 3, TypeName = "VARCHAR(50)")]
        [Comment("The URL of the blog")]
        [MaxLength(50)]
        public string? code_verifier { get; set; }

        [Required]
        [Column("grant_type", Order = 4, TypeName = "VARCHAR(50)")]
        [Comment("The URL of the blog")]
        [MaxLength(50)]
        public string grant_type { get; set; }

        [Required]
        [Column("redirect_uri", Order = 5, TypeName = "VARCHAR(2048)")]
        [Comment("The URL of the blog")]
        [MaxLength(2048)]
        public string redirect_uri { get; set; }
    }
}

ตัวอย่างการเรียกใช้
\Controllers\LineLoginsController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Pine.Models;

namespace Pine.Controllers
{
    public class LineLoginsController : Controller
    {
        private readonly DSNDevDBContext _context;

        public LineLoginsController(DSNDevDBContext context)
        {
            _context = context;
        }

        // GET: LineLogins/Create
        public IActionResult Create()
        {
            return View();
        }

        // POST: LineLogins/Create To protect from overposting attacks, enable the specific
        // properties you want to bind to. For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("client_id,client_secret,code,code_verifier,grant_type,redirect_uri")] line_logins line_logins)
        {
            if (ModelState.IsValid)
            {
                _context.Add(line_logins);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            return View(line_logins);
        }

        // GET: LineLogins/Delete/5
        public async Task<IActionResult> Delete(string id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var line_logins = await _context.line_Logins
                .FirstOrDefaultAsync(m => m.client_id == id);
            if (line_logins == null)
            {
                return NotFound();
            }

            return View(line_logins);
        }

        // POST: LineLogins/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(string id)
        {
            var line_logins = await _context.line_Logins.FindAsync(id);
            if (line_logins != null)
            {
                _context.line_Logins.Remove(line_logins);
            }

            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

        // GET: LineLogins/Details/5
        public async Task<IActionResult> Details(string id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var line_logins = await _context.line_Logins
                .FirstOrDefaultAsync(m => m.client_id == id);
            if (line_logins == null)
            {
                return NotFound();
            }

            return View(line_logins);
        }

        // GET: LineLogins/Edit/5
        public async Task<IActionResult> Edit(string id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var line_logins = await _context.line_Logins.FindAsync(id);
            if (line_logins == null)
            {
                return NotFound();
            }
            return View(line_logins);
        }

        // POST: LineLogins/Edit/5 To protect from overposting attacks, enable the specific
        // properties you want to bind to. For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(string id, [Bind("client_id,client_secret,code,code_verifier,grant_type,redirect_uri")] line_logins line_logins)
        {
            if (id != line_logins.client_id)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                try
                {
                    _context.Update(line_logins);
                    await _context.SaveChangesAsync();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!line_loginsExists(line_logins.client_id))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
                return RedirectToAction(nameof(Index));
            }
            return View(line_logins);
        }

        // GET: LineLogins
        public async Task<IActionResult> Index()
        {
            return View(await _context.line_Logins.ToListAsync());
        }

        private bool line_loginsExists(string id)
        {
            return _context.line_Logins.Any(e => e.client_id == id);
        }
    }
}

อ่านเพิ่มเติม

C#: connection database ใน .NET Core