Yearly Archive 2023-10-21

Byphunsanit

C#: connection database ใน .NET Core

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

  1. เก็บข้อมูล 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;"
      },
    ...
    
  2. จากนั้นจะสามารถดึงช้อมูลมาใช้ได้โดยเพิ่ม
    /Program.cs
    ...
    //This section below is for connection string
    var connectionString = builder.Configuration.GetConnectionString("DSNDevContext");
    builder.Services.AddDbContext<DSNDevDBContext>(options => options.UseSqlServer(connectionString));
    ...
    
  3. จากนั้นสร้างส่วนเชื่อมโยง 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
    }
    
  4. สร้าง 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; }
        }
    }
    
  5. ตัวอย่างการเรียกใช้
    \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);
            }
        }
    }
    

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