In this article, we will learn how to perform the crud operations in the asp.net core web API using the entity framework database first approach.
In this article, I will explain to you a step-by-step tutorial for performing CRUD operations in ASP.NET Core Web API using the entity framework database first approach.
Whenever we are creating a web application we need a database for saving the data. Here we need to perform insert, update, delete operations in the database table with the help of the entity framework. So need a database.
we can divide the complete article into below step
- Creating Our the Database and Tables
- Creating .NET Core 5.0 API Project in VS 2019
- Install Nuget Packages in our project for Entity Framework
- Setup database connection string in appsettings.json and Dependency Injection
- Creating a Controller and Create Crud API
- Finally, we will perform the Testing on the API End Points in Swagger
Database-First Approach
Database first approach provides us an alternative to code first and models the first approach and it creates model classes and code from the database.
In a database-first development approach, you generate references and entities for existing databases using by executing EF commands.
In this chapter, let’s learn about create an Asp .Net Core API using the Entity framework database first approach.The Database First approach builds Entity Framework from an existing database. We use all other functionalities, such as model/database sync and code generation.
If you want to use the code first approach read below article
Create SQL table for performing the CRUD Operation in API
In this step, we are going to create the database table, I have created a table called TblDoctors for that.
which is got, the Id, DoctorName, Gender, and Salary columns.
CREATE TABLE [dbo].[TblDoctors]( [Id] [int] IDENTITY(1,1) NOT NULL, [DoctorName] [nvarchar](max) NOT NULL, [Gender] [nvarchar](50) NULL, [Salary] [decimal](18, 0) NULL, CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[TblDoctors] ON GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (1, N'Carter', N'Male', CAST(20000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (3, N'Gianna', N'Female', CAST(10000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (4, N'New Name', N'male', CAST(3000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (6, N'Julian', N'Male', CAST(45000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (7, N'Kayla', N'Female', CAST(25000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (9, N'Autumn', N'Female', CAST(13000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (10, N'Sebastian', N'Male', CAST(35000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (11, N'Blake', N'Male', CAST(39000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (12, N'Dr. Jacob', N'Male', CAST(41000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (13, N'Dr. Henry', N'Male', CAST(28000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (14, N'Dr. Tanmay', N'Male', CAST(36000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (15, N'John', N'Male', CAST(20000 AS Decimal(18, 0))) GO INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (16, N'John', N'Male', CAST(20000 AS Decimal(18, 0))) GO SET IDENTITY_INSERT [dbo].[TblDoctors] OFF GO
Create ASP.NET Core 5.0 Web API Project
- Open Visual Studio 2019
- Click to >Create a new Project>Asp .Net Core Web Application
Follow the Below Step
Click on next button it will create the Project for Us
The our Web Api solution will look like the below image, by default it create “WeatherForecaseController” controller.
Open NuGet package Install Nuget Packages for Entity Framework
As we are going to use the Entity Framework Core as ORM for this in our project. So To use Entity Framework Core, we need to install NuGet packages that will provide classes to work with Entity Framework Core.
you can install packages using Package Manager Console by the command or by searching on NuGet Package Manager.
1. Install Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.SqlServer is a database provider that allows Entity Framework Core to be used with Microsoft SQL Server
2. Insatll Microsoft.EntityFrameworkCore.SqlServer.Design
The second NuGet package is Microsoft.EntityFrameworkCore.Design will provide all design-time logic for Entity Framework Core.
3.Insatll Microsoft.EntityFrameworkCore.Tools
The third and last one is Microsoft.EntityFrameworkCore.Tools help with design-time development tasks with database for example manage Migrations, script migration, scaffold a DbContext, update the database, etc.
Creating DbContext and Model Classes
Now we have installed all the needed NuGet packages that are required to work with the Entity Framework Core database first approach.
Now let’s start, creating Entity Framework DbContext and Models Classes from the SQL database.
Open NuGet Package Manager Console
For Opening it, In Visual Studio go to Tools > NuGet Package Manager > Package Manager Console and paste the below command and execute it.
Copy Code the below code
Scaffold-DbContext "Data Source=ADEQUATE-ASHOK\SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=adk;Password=adk@1234" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DatabaseEntity -Force
In the above command, we have passed the database connection string. and we also provided an Output directory where DbContext and Models classes should be created.
The above Nuget command creates one class inside the “DatabaseEntity” folder as CrudCoreDbContext which is nothing but DbContext class for our application and Model classes is related to the database tables.
Now open CrudCoreDbContext class file and comment on the OnConfiguring() function of CrudCoreDbContext class because we will configure a database connection in “appsettings.json” and will set our Dependency Injection in the Startup. cs class just like we put the database connection string in Web. config file.
CrudCoreDbContext.cs
using System; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata; #nullable disable namespace crudcoredatabasefirst.DatabaseEntity { public partial class CrudCoreDbContext : DbContext { public CrudCoreDbContext() { } public CrudCoreDbContext(DbContextOptions<CrudCoreDbContext> options) : base(options) { } public virtual DbSet<TblDoctor> TblDoctors { get; set; } // protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) // { // if (!optionsBuilder.IsConfigured) // { //#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263. // optionsBuilder.UseSqlServer("Data Source=SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=adk;Password=adk@1234"); // } // } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS"); modelBuilder.Entity<TblDoctor>(entity => { entity.Property(e => e.DoctorName).IsRequired(); entity.Property(e => e.Gender).HasMaxLength(50); entity.Property(e => e.Salary).HasColumnType("decimal(18, 0)"); }); OnModelCreatingPartial(modelBuilder); } partial void OnModelCreatingPartial(ModelBuilder modelBuilder); } }
This is a TblDoctor Model class which is auto-generated from the database.
public partial class TblDoctor { public int Id { get; set; } public string DoctorName { get; set; } public string Gender { get; set; } public decimal? Salary { get; set; } }
As we have commented OnConfiguring() method of class and now we are going to define our database connection string inside the appsettings.json as follows:
{ "Logging": { "LogLevel": { "Default": "Information", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Information" } }, "ConnectionStrings": { "BlogDBConnection": "Data Source=SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=admin;Password=adk@1234" }, "AllowedHosts": "*" }
Setup Dependency Injection
Open Startup.cs class and add dependency injection for CrudCoreDbContext class.
using crudcoredatabasefirst.DatabaseEntity; using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.HttpsPolicy; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Microsoft.Extensions.Hosting; using Microsoft.Extensions.Logging; using Microsoft.OpenApi.Models; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace crudcoredatabasefirst { 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) { services.AddControllers(); services.AddDbContext<CrudCoreDbContext>(item => item.UseSqlServer(Configuration.GetConnectionString("DBConnection"))); //register connection string services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new OpenApiInfo { Title = "crudcoredatabasefirst", Version = "v1" }); }); } // 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(); app.UseSwagger(); app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "crudcoredatabasefirst v1")); } app.UseHttpsRedirection(); app.UseRouting(); app.UseAuthorization(); app.UseEndpoints(endpoints => { endpoints.MapControllers(); }); } } }
Create a Controller and Create API Calls
Now, we are going to create an API controller to implement crud operations the END-POINT.
So, Let’s add a new API controller name as ‘DoctorController’.
Right-click on controller folder>add>controller> choose Api Controller from left side and select Empty controller
and name according your choice
it will create the empty api controller for you
using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace crudcoredatabasefirst.Controllers { [Route("api/[controller]")] [ApiController] public class DoctorController : ControllerBase { } }
now let’s implement crud operation endpoints ,copy paste the below code
using crudcoredatabasefirst.DatabaseEntity; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace crudcoredatabasefirst.Controllers { [Route("api/[controller]")] [ApiController] public class DoctorController : ControllerBase { private readonly CrudCoreDbContext _context; public DoctorController(CrudCoreDbContext context) { _context = context; } // GET: api/Doctor [HttpGet] public IEnumerable<TblDoctor> GetDoctors() { return _context.TblDoctors; } // GET: api/ Doctor/5 [HttpGet("{id}")] public async Task<IActionResult> GetDoctor([FromRoute] int id) { if (!ModelState.IsValid) { return BadRequest(ModelState); } var TblDoctor = await _context.TblDoctors.FindAsync(id); if (TblDoctor == null) { return NotFound(); } return Ok(TblDoctor); } // PUT: api/Doctor/5 [HttpPut("{id}")] public async Task<IActionResult> PutDoctor([FromRoute] int id, [FromBody] TblDoctor tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } if (id != tblDoctor.Id) { return BadRequest(); } _context.Entry(tblDoctor).State = EntityState.Modified; try { await _context.SaveChangesAsync(); } catch (DbUpdateConcurrencyException) { if (!DoctorExists(id)) { return NotFound(); } else { throw; } } return NoContent(); } // POST: api/Doctor [HttpPost] public async Task<IActionResult> PostDoctor([FromBody] TblDoctor tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } _context.TblDoctors.Add(tblDoctor); await _context.SaveChangesAsync(); return CreatedAtAction("GetDoctorspecilization", new { id = tblDoctor.Id }, tblDoctor); } // DELETE: api/Doctor/5 [HttpDelete("{id}")] public async Task<IActionResult> DeleteDoctor([FromRoute] int id) { if (!ModelState.IsValid) { return BadRequest(ModelState); } var TblDoctor = await _context.TblDoctors.FindAsync(id); if (TblDoctor == null) { return NotFound(); } _context.TblDoctors.Remove(TblDoctor); await _context.SaveChangesAsync(); return Ok(TblDoctor); } private bool DoctorExists(int id) { return _context.TblDoctors.Any(e => e.Id == id); } } }
Code Explanation
Here we have created a constructor of the controller which taking dbcontext as a parameter, to initialize our db context object.
private readonly CrudCoreDbContext _context; public DoctorController(CrudCoreDbContext context) { _context = context; }
and then we have created Get,Put,Post,delete api endpoint for performing the database operation.
Now Lest perform testing
Now let’s run our project, it will open swagger/v1/swagger, where we can perform our testing.
1. Get All Doctors
GET: api/Doctor
The following GetDoctors() action method in DoctorController class returns all the Doctors from the database using Entity Framework.
// GET: api/Doctor [HttpGet] public IEnumerable<TblDoctor> GetDoctors() { return _context.TblDoctors; }
Let’s excute api in the swagger,it will return the list
- Let’s do test on swagger, Click Get /api/Doctor tab,then click on Try it Out Button
- and Then Click on the excute button, we get all records from the Doctors table.
API Response
[ { "id": 1, "doctorName": "Carter", "gender": "Male", "salary": 20000 }, { "id": 3, "doctorName": "Gianna", "gender": "Female", "salary": 10000 }, { "id": 4, "doctorName": "Brandon", "gender": "Male", "salary": 15000 }, { "id": 5, "doctorName": "Julia", "gender": "Female", "salary": 23000 }, { "id": 6, "doctorName": "Julian", "gender": "Male", "salary": 45000 }, { "id": 7, "doctorName": "Kayla", "gender": "Female", "salary": 25000 }, { "id": 9, "doctorName": "Autumn", "gender": "Female", "salary": 13000 }, { "id": 10, "doctorName": "Sebastian", "gender": "Male", "salary": 35000 }, { "id": 11, "doctorName": "Blake", "gender": "Male", "salary": 39000 }, { "id": 12, "doctorName": "Dr. Jacob", "gender": "Male", "salary": 41000 }, { "id": 13, "doctorName": "Dr. Henry", "gender": "Male", "salary": 28000 }, { "id": 14, "doctorName": "Dr. Tanmay", "gender": "Male", "salary": 36000 } ]
2. Get Doctor by Id
GET: api/Doctor/5
It will return all Doctor with id=5 in the database
As you can see in code, GetDoctor() method returns all the Doctor with by Id using EF. If no Doctor exists in the database table then it will return 404 NotFound responses otherwise it will return 200 OK responses with Doctor data.
The NotFound() and Ok() methods defined in the DoctorController returns 404 and 200 response respectively according to our condition.
// GET: api/Doctor/5 [HttpGet("{id}")] public async Task<IActionResult> GetDoctor([FromRoute] int id) { if (!ModelState.IsValid) { return BadRequest(ModelState); } var TblDoctor = await _context.TblDoctors.FindAsync(id); if (TblDoctor == null) { return NotFound(); } return Ok(TblDoctor); }
- Let’s do test on swagger, Click Get /api/Doctor/{id} tab,then click on Try it Out Button
- Enter Doctor Id for which you want record,Click on the excute button
API Response
{ "type": "https://tools.ietf.org/html/rfc7231#section-6.5.4", "title": "Not Found", "status": 404, "traceId": "00-218c98b4939524468763abb787263eff-f79115110ed1b642-00" }
3. Create Doctor
POST–api/Doctor
The PostDoctor action method will handle HTTP POST requests.
// POST: api/Doctor [HttpPost] public async Task<IActionResult> PostDoctor([FromBody] TblDoctor tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } _context.TblDoctors.Add(tblDoctor); await _context.SaveChangesAsync(); return CreatedAtAction("GetDoctor", new { id = tblDoctor.Id }, tblDoctor); }
In the PostDoctor method, we first validating the model using ModelState.IsValid. This will make sure that the doctor object includes all the required information. If this is not true then you get BadRequest response. If doctor model i.e data is model then add doctor using Entity Framework context and return CreatedAtAction status response.
- Let’s do test on swagger, Click PostDoctor tab,then click on Try it Out Button
- Input Body parameter and click on the excute button, it will insert the record in the table.
You need to pass the doctor object in the request body i.e doctorName, gender, and salary. it will make an entry in our database and in response, you will able to get the newly created object detail.
API Request
{ "id": 0, "doctorName": "John", "gender": "Male", "salary": 20000 }
Response body
{ "id": 16, "doctorName": "John", "gender": "Male", "salary": 20000 }
4. Update Doctor table
PUT–/api/Doctor/5
This endpoint is used to update the doctor table object, You just need to pass the doctor object in the request body, and in response, you will able to get an updated doctor detail.
PutDoctor() action method in our DoctorController is used to update an existing doctor record in the database using Entity Framework.
As you can see from the below code, Put action method includes a parameter of the Doctor model and then changes the state to be modified.
// PUT: api/Doctor/5 [HttpPut("{id}")] public async Task<IActionResult> PutDoctor([FromRoute] int id, [FromBody] TblDoctor tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } if (id != tblDoctor.Id) { return BadRequest(); } _context.Entry(tblDoctor).State = EntityState.Modified; try { await _context.SaveChangesAsync(); } catch (DbUpdateConcurrencyException) { if (!DoctorExists(id)) { return NotFound(); } else { throw; } } return NoContent(); }
- Let’s do test on swagger, Click PUT /api/Doctor/{id} tab,then click on Try it Out Button
- Enter Doctor Id for which you want to update the record,and let’s say i want to update record with Id=4
- Input updated value,Click on the excute button,
- It will update Doctor record with Id=4
Request body
{ "id": 4, "doctorName": "New Name", "gender": "male", "salary": 3000 }
5. DELETE Doctor by Id
DELETE:/api/Doctor?id=2
It will delete the Doctor with id=2 in the database
DeleteDoctor() action method in our DoctorController use to delete an existing Doctor record in the database using Entity Framework.
[HttpDelete("{id}")] public async Task<IActionResult> DeleteDoctor([FromRoute] int id) { if (!ModelState.IsValid) { return BadRequest(ModelState); } var TblDoctor = await _context.TblDoctors.FindAsync(id); if (TblDoctor == null) { return NotFound(); } _context.TblDoctors.Remove(TblDoctor); await _context.SaveChangesAsync(); return Ok(TblDoctor); }
- Let’s do test on swagger, Click DELETE /api/Doctor/{id} tab,then click on Try it Out Button
- Enter Doctor Id for deleting record,Click on the excute button
So, in this post, we have seen how to perform creating ASP.NET Core Web API using Entity Framework Core database first approach.
Please share your using comments and if you have any queries or doubts.
The post Create Asp.Net Core Web Api Using Entity Framework Database First appeared first on Software Development | Programming Tutorials.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- Simple Way Find and replace text in Word document using C#
- Implementing “Remember Me” Feature in ASP.NET MVC
- [Solved]-Cookie loses value when page is changed in MVC
- How to post File Upload and other form fields to one action Asp .Net MVC C#
- How To Post File and Data to API using HttpClient C#
- Create ASP.NET Core Web API Without Entity Framework
- .NET Core Web API Using Code First Entity Framework Approach
- Create Asp.Net Core Web Api Using Entity Framework Database First
- Registration form with image upload in MVC using jquery Ajax
- How to make an Inline editable table in MVC using jquery?
- CRUD operation using partial view in MVC with modal popup
- Insert Update Delete Using Jquery Ajax and Modal Popup in Mvc
- Crud Operations in MVC Without Entity Framework
- Create Login,Signout and Registration in Asp .Net Mvc Using Entity
- Export Gridview to Excel and Csv in Asp .Net With Formatting Using c#
- How to Display Binary Image in Gridview from Database in Asp .Net c#
- [Solved]-How to Upload pdf file using jquery MVC?
- [Solved]-Uploading both data and files in FormData using Ajax MVC
- C# -Saving a base64 string as an image into a folder on server in Web Api
- [Solved]-Download pdf file from link and save in local file folder in Asp .Net
- [Solved]-Delete Files older than X Months,Days,Minute in a Directory- C# .NET
- [Solved]-LEFT OUTER JOIN in LINQ With Where clause in C#
- INNER JOIN,RIGHT JOIN,LEFT JOIN USING LINQ IN Multiple Table C#
- [Solved]-Convert Base64 To Image And Save in folder and Display it- C#
- [Solved]-How to Overlay Two Images in .NET-C#
- How to Create Multilingual Website in Asp .Net
- C# – How to add double quotes to a string that is inside a variable
- Update an Image with Upload Button For Each Row in Gridview-Asp .Net
- How to Bind Data in DataList Control in Asp.Net – C#
- Upload and Display an Image in Gridview From Image Path in Database -C#