How do you connect an existing database to a new ASP Core MVC project?
in Visual Studio
Step 1: Connect the Database Server in Visual Studio
View Explorer in Menu >views server > server
then data connections->-addconnection->servername: .\localdb
Connect to Database-> Select Database
Step 2: Extract the
data connection for the database in SQL Server Explorer
Under Database >Properties >Connection >Connectionstring
Connection String in startup.cs
Insert the connection string into
Asp Core MVC
Add to file Startup.cs ->Configure
Services
|
services.AddDbContext<ApplicationDbContext>(options
=>options.UseSqlServer("Data
Source=.\\localdb;Initial Catalog=Demo;Integrated Security=True"));
|
tables extracting
structure from SQL Server
Then open the table in SQL Server for Design View and display the table
fields with field type
|
CREATE TABLE [dbo].[tblProdDates] (
[IDProduct] INT NOT NULL,
[Date_Product] DATE NOT NULL,
CONSTRAINT
[PK_tblProdDates] PRIMARY KEY CLUSTERED ([IDProduct] ASC,
[Date_Product] ASC)
);
|
Creating a Model Class
in Asp Core Project
The Data Model class is created under:
Startup.cs
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using
Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using MyApp.Models.DBContext;
using Microsoft.EntityFrameworkCore;
namespace MyApp
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration {
get; }
// This
method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection
services)
{
// Add framework services.
services
.AddControllersWithViews()
.AddJsonOptions(options =>
options.JsonSerializerOptions.PropertyNamingPolicy = null);
services.AddDbContext<ApplicationDbContext>(options
=>options.UseSqlServer("Data
Source=.\\localdb;Initial Catalog=Demo;Integrated Security=True"));
}
// This
method gets called by the runtime. Use this method to configure the HTTP
request pipeline.
public void Configure(IApplicationBuilder app,
IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Home/Error");
}
app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllerRoute(
name: "default",
pattern: "{controller=Demo}/{action=Index}");
});
}
}
}
|
Model Class
Data-Definition of Table
|
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace MyApp.Models
{
public class ProductDateModel
{
[Key]
public long IDProduct { get; set; }
public DateTime Date_Product { get; set; }
}
}
|
Extension of the
Model class with PrimaryKey
Cs0246 The
type or namespace name "KeyAttribute" could not be found (are you
missing directive or an assembly reference)?
|
using
System.ComponentModel.DataAnnotations;
|
AppplicationDbContext.cs
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
//< Using>
using Microsoft.EntityFrameworkCore;
//</ Using>
namespace MyApp.Models.DBContext
{
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext>
options): base(options){ }
#region EF: Datase-Tabels to Models
//------------<
region: Datase-Tables to Models >------------
public
DbSet<Models.ProductDateModel> tbl_ProductDates{ get; set; }
protected override void OnModelCreating(ModelBuilder
modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Models.ProductDateModel>().ToTable("tbl_ProductDates");
}
//------------</
region : Datase-Tables to Models >------------
#endregion /EF: Datase-Tabels to Models
}
}
|
Für EF
EntityFramework, SQL Server Visual Studio