PROWAREtech
ASP.NET Core: Create and Use MS SQL Database
This example uses ASP.NET Core 3.1.
Have SQL Server installed and running on the development machine or on the network and have Visual Studio 2019 open with a new MVC project created. NOTE: this also works with Razor pages or WebAPI project.
Make sure that the NuGet packages Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools are installed.
Modify the projects appsettings.json to include a ConnectionStrings property. The database does not need to exist.
{
"ConnectionStrings": {
"Default": "Server=LOCALHOST\\SQLEXPRESS;Database=ACME;Trusted_Connection=True;MultipleActiveResultSets=True"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
In the Models directory, create a file named User.cs.
// User.cs
using System;
using System.ComponentModel.DataAnnotations;
namespace MvcApplication.Models
{
public class User
{
[Key]
public int Id { get; set; }
[Required]
public string Username { get; set; }
[Required]
public string Password { get; set; }
[Required]
public string Email { get; set; }
public DateTime Created { get; set; }
}
}
Again in the Models folder, create a file named AppDbContext.cs.
// AppDbContext.cs
using Microsoft.EntityFrameworkCore;
namespace MvcApplication.Models
{
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
public DbSet<User> User { get; set; }
}
}
Modify Startup.cs as follows.
// 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 Microsoft.EntityFrameworkCore; // ########## THIS LINE ADDED ##########
using MvcApplication.Models; // ########## THIS LINE ADDED ##########
namespace MvcApplication
{
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)
{
// ########### FOLLOWING FOUR LINES ADDED ###########
services.AddDbContext<AppDbContext>(options =>
{
options.UseSqlServer(Configuration.GetConnectionString("Default"));
});
// ########### END OF ADDED CODE ###########
services.AddControllersWithViews();
}
// 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=Home}/{action=Index}/{id?}");
});
}
}
}
Create/Update the SQL Server Database
Now, enough has been done to create the database. Open the Package Manager Console (Tools > NuGet Package Manager > Package Manager Console) and enter the following two PM commands. NOTE: If the following commands do not work on Windows 7 (with a ScriptHalted error) then the PowerShell version may be too old. Install the latest version from HERE. These two commands will create the database and the table for the users.
PM> add-migration AddUserToDb
PM> update-database
The results should look something like this:
PM> add-migration AddUserToDb Build started... Build succeeded. To undo this action, use Remove-Migration. PM> update-database Build started... Build succeeded. Done.
Modify HomeController.cs as follows.
// HomeController.cs
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using MvcApplication.Models;
namespace MvcApplication.Controllers
{
public class HomeController : Controller
{
private readonly AppDbContext db;
public HomeController(AppDbContext db)
{
this.db = db;
}
// ########## THIS METHOD MODIFIED ##########
public async Task<IActionResult> Index(int delete = 0, int edit = 0)
{
if(delete != 0)
{
var user = await db.User.FindAsync(delete);
if(user != null)
{
db.User.Remove(user);
await db.SaveChangesAsync();
}
}
else if (edit != 0)
{
var user = await db.User.FindAsync(edit);
if (user == null)
return NotFound();
return View(user);
}
return View(await db.User.ToListAsync());
}
// ########## THIS METHOD IS NEWLY ADDED ##########
[HttpPost]
public async Task<IActionResult> Index([FromForm] User user)
{
if (user.Id != 0)
{
var edit = await db.User.FindAsync(user.Id);
if (edit == null)
return NotFound();
edit.Username = user.Username;
edit.Password = user.Password;
edit.Email = user.Email;
await db.SaveChangesAsync();
}
else
{
user.Created = DateTime.Now;
await db.User.AddAsync(user);
await db.SaveChangesAsync();
}
return LocalRedirect("/");
}
public IActionResult Privacy()
{
return View();
}
[ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
public IActionResult Error()
{
return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
}
}
}
Modify the view Index.cshtml in the Home folder as follows.
@{
ViewData["Title"] = "Home Page";
}
<div class="text-center">
<h1 class="display-4">Welcome</h1>
<p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
@if (Model is IEnumerable<User>)
{
<div class="text-left">
@foreach (var user in Model)
{
<ul>
<li>@user.Username</li>
<li>@user.Email</li>
<li>@user.Id</li>
<li>@user.Created</li>
<li><a href="/?delete=@user.Id">(delete)</a></li>
<li><a href="/?edit=@user.Id">(edit)</a></li>
</ul>
}
</div>
<form method="post">
<input type="text" name="Username" placeholder="Username" /><br />
<input type="password" name="Password" placeholder="Password" /><br />
<input type="text" name="Email" placeholder="Email" /><br />
<button>Add User</button>
</form>
}
else if (Model is User)
{
<form method="post">
<input type="hidden" name="Id" value="@Model.Id" />
<input type="text" name="Username" placeholder="Username" value="@Model.Username" /><br />
<input type="password" name="Password" placeholder="Password" value="@Model.Password" /><br />
<input type="text" name="Email" placeholder="Email" value="@Model.Email" /><br />
<button>Edit User</button>
</form>
}
</div>
Run the application to add, edit and delete users from the database just created.
Coding Video
In this video, there are steps to modify the database table after it has already been created.
