Web API erweitern zum Datenbank Verbindung
1.Schritt: man muss EntityFramework in den Nuget Packages anbinden
Unter Nuget Packages
Installieren: Microsoft.EntityFrameworkCore
Und das Package Microsoft.EntityFrameworkCore.SqlServer
Microsoft SQL Server database provider for Entity Framework Core.
2.Schritt: DbConnection in Program.cs eintragen
Datenbank in program.cs verbinden
3.Schritt: Connectionstring in appsettings.json eintragen
ConnectionString eintragen, = Configuration
Die Verbindung zur Datenbank trägt man in appsettings.json ein
{ "ConnectionStrings": { "DefaultConnection": "Server=.\\sqlexpress;Database=codedocu_de;Trusted_Connection=True;MultipleActiveResultSets=true;Encrypt=false" }, "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "AllowedHosts": "*" } |
4.Schritt: Model der Tabelle anpassen
Die Felder in der Model Klasse müssen namensgleich und Typgleich zur echten Tabelle in der SQL Datenbank sein
using System.ComponentModel.DataAnnotations;
namespace Models { public class ArticleModel { [Key] public int IDArticle { get; set; }
public string Title { get; set; }=string.Empty; } } |
5.Schritt: Tabellen in der Datenbank zuweisen
ApplicationDbContext.cs
Mit der Zeile DbSet wird ein lokales Dataset definiert
public virtual DbSet<ArticleModel> tbl_Articles { get; set; } |
Mit der Zeile .ToTable() wird die Tabelle verbunden
modelBuilder.Entity<Models.ArticleModel>().ToTable("tbl_Articles"); |
using Microsoft.EntityFrameworkCore; using Models;
namespace webapp_codedocu.Data { public class ApplicationDbContext : DbContext { public ApplicationDbContext(DbContextOptions options ): base(options){}
#region Datasets //--< Datasets in this Project >-- public virtual DbSet<ArticleModel> tbl_Articles { get; set; } //--</ Datasets in this Project >--- #endregion /Datasets
protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); //------------< region: Datase-Tables to Models >------------ //*zuweisen der Model-Klasse zur Sql-Server Tabelle
modelBuilder.Entity<Models.ArticleModel>().ToTable("tbl_Articles");
//------------</ region : Datase-Tables to Models >------------ } } } |
6.Schritt: Daten holen im Controller
ArticlesController.cs
Abrufen der Daten im Controller
// GET: api/<ArticlesController> [HttpGet ("GetList")] public async Task<ActionResult<List<ArticleModel>>>GetList() { var query = from t in _dbContext.tbl_Articles orderby t.IDArticle descending select t; var list_Artcles=query.Take(10); return list_Artcles.ToList(); } |
Komplette Program.cs
#region //==< Builder >== //==< Builder >== using Microsoft.EntityFrameworkCore; using webapp_codedocu.Data;
var builder = WebApplication.CreateBuilder(args); // Add services to the container.
//* Connect Database var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found."); builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(connectionString));
builder.Services.AddControllers(); // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle builder.Services.AddEndpointsApiExplorer(); builder.Services.AddSwaggerGen();
//*CORS //< CORS > //*allow calls from AngularUI builder.Services.AddCors(options => options.AddPolicy(name: "FrontendUI", policy => { policy.WithOrigins("http://localhost:4200").AllowAnyMethod().AllowAnyHeader(); } )); //</ CORS >
//==</ Builder >== #endregion //==< Builder >==
#region //==< APP >== //==< APP >== var app = builder.Build();
// Configure the HTTP request pipeline. if (app.Environment.IsDevelopment()) { app.UseSwagger(); app.UseSwaggerUI(); }
//< CORS > app.UseCors("FrontendUI"); //</ CORS >
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run(); //==</ APP >== #endregion //==</ APP >== |
Komplette ArticlesController.cs in diesem Schritt
using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using Models; using webapp_codedocu.Data;
namespace webapi_codedocu.Controllers { [Route("api/[controller]")] [ApiController] public class ArticlesController : ControllerBase { //--< Variables >-- //private readonly ILogger<ArticlesController> _logger; private readonly ApplicationDbContext _dbContext; //--</ Variables >--
public ArticlesController(ApplicationDbContext dbContext, ILogger<ArticlesController> logger) { //----< Init >---- _dbContext = dbContext; //_logger = logger; //----</ Init >---- }
// GET: api/<ArticlesController> [HttpGet ("GetList")] public async Task<ActionResult<List<ArticleModel>>>GetList() { var query = from t in _dbContext.tbl_Articles orderby t.IDArticle descending select t; var list_Artcles=query.Take(10); return list_Artcles.ToList(); }
// GET api/<ArticlesController>/5 [HttpGet("{id}")] public string Get(int id) { return "value"; }
// POST api/<ArticlesController> [HttpPost] public void Post([FromBody] string value) { }
// PUT api/<ArticlesController>/5 [HttpPut("{id}")] public void Put(int id, [FromBody] string value) { }
// DELETE api/<ArticlesController>/5 [HttpDelete("{id}")] public void Delete(int id) { } } } |