Home > Software engineering >  How to retrieve data from SQL Server database into the application
How to retrieve data from SQL Server database into the application

Time:06-11

I am creating a web app using Razor pages in ASP.NET Core. How can I retrieve data from my database? I have connected my database with the application.

1

This is how the models look which is auto-created after running the command scaffold-DbContext.

How can I retrieve data from the DB.

public Employee employees { get; set; }

returns null.

CodePudding user response:

You only create your model. You had already created the database, please skip step 6.

you should do the following step:

1. Create an App

Create an ASP.NET Core app .NET 6.

Install the following NuGet packages:

  • Microsoft.EntityFrameworkCore.SqlServer 6
  • Microsoft.EntityFrameworkCore.Tools 6

2. Create the Models

Create a model for your existing SQL Server database. You can create the model by Scaffold-DbContext command or manually.

To create models from existing SQL Server database use Scaffold-DbContext command:

Scaffold-DbContext "Server=(localdb)\\mssqllocaldb;Database=WebAPI;Trusted_Connection=True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

The model could be created manually as follow:

public class Employee
{
  [Key]
  public Guid EmployeeId { get; set; }
  [Required]
  [StringLength(50)]
  public string Name { get; set; }
  [Required]
  [StringLength(100)]   
  public string Address { get; set; }
  public byte Status { get; set; }
}

You have done this step.

3. Create a Context

You could create db context class manually as follow:

public class WebAPIContext: DbContext
{
   public DbSet<Employee> Employees { get; set; }
}

Remark: If you used Scaffold-DbContext command, the dbContext was created automatically. The name of the db context class is [Your Database Name]Context. In this example is WebAPIContext.

4. Configure database connection

Add the following code to appsettings.json file

  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=WebAPI;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

5. Add AddDbContext service

Add the following code to Program.cs file

builder.Services.AddDbContext(options =>
                   options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"))
                   , optionsLifetime: ServiceLifetime.Scoped);

6. Add-Migration and Update-Database command

Use the Add-Migration init1 command to scaffold a migration to apply these changes to the database.

Run the Update-Database command to apply the new migration to the database.

Remark: If you have not created the database, you should do step 6.

Now you can retrieve data from SQL database and make CRUD operations on your database.

To list employees, replace the following code:

public class IndexModel : PageModel
{
   private readonly WebAPIContext _context;
   public IndexModel(WebAPIContext context)
   {
      _context = context;
   }

   public List<Employee> Employees { get; set; }

   public async Task OnGetAsync()
   {
      Employees = _context.Employees.ToList();
   }
}

CodePudding user response:

You have great tutorial in answer above, first of all check key aspects:

  1. have you made connection to Db? (DB Context)
  2. is Db connection string correct (in appsettings.json)
  3. have you registered DbContext in services in Startup.cs to use in Dependency Injection
  4. are you properly using db context in other classes - you can compare with tutorial approach to db context

cheers

  • Related