Open Visual Studio 2022
Create New Project
Search and Select Blank Solution > Click Next
Name the solution MyProject > Choose the Location of your project > click Create button
Click File > Add > New Project
Search for API > Select ASP.NET Code Web API > Click Next
Enter Project name as MyProject.Api > Click Next
Select Framework > uncheck Configure for HTTPS > Click Create
Under Properties folder > Edit launchSettings.json > change Port number to 5010
Open appsettings.json > add the ConnectionString
Sample Connection String value (Update the values based on your Server/Database)
"ConnectionStrings": {
"SampleDatabase":
"Server=MyServerName\\SQLEXPRESS;Database=Sample;Trusted_Connection=True;TrustServerCertificate=true;"
}
Go to Tools > Nuget Package Manager > Manager Nuget Packager for Solution...
Search for entityframeworkcore > then click Install
On Preview Changes popup > Click Apply
On License Acceptance popup > Click “I Accept”
Search for entityframeworkcore.sqlserver > then click Install
On Preview Changes popup > Click Apply
On License Acceptance popup > Click “I Accept”
Right-click on Solution name MyProject > click Add > click New Folder
Create new folder named as Data
Create new Class file inside Data folder
Right-click on Data folder > Add > Class
Edit AppDbContext.cs
using Microsoft.EntityFrameworkCore;
using MyProject.Api.Data.Entity;
namespace MyProject.Api.Data
{
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
{
public DbSet<Student> Students { get; set; }
}
}
Add new folder Entity under Data folder
Right-click on Data folder > click Add > click New Folder
Create new Class file inside Entity folder
Name the class file as Student.cs > click Add
Edit Student.cs
namespace MyProject.Api.Data.Entity
{
public class Student
{
public long Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
public int Age { get; set; }
}
}
On your Database Server
Create database named Sample
Create database table named Students
CREATE TABLE [dbo].[Students](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL,
[Age] [int] NULL
) ON [PRIMARY]
Create Stored Procedures
Create procedure [dbo].[sp_AddStudent]
@Name nvarchar(50),
@Email nvarchar(50),
@Phone nvarchar(50),
@Age int
as
insert into dbo.Students (
Name,
Email,
Phone,
Age)
Values (
@Name,
@Email,
@Phone,
@Age
)
GO
Create procedure [dbo].[sp_DeleteStudent]
@Id int
as
delete from dbo.Students where id = @Id
GO
Create procedure [dbo].[sp_EditStudent]
@Id int,
@Name nvarchar(50),
@Email nvarchar(50),
@Phone nvarchar(50),
@Age int
as
update dbo.Students
set Name = @Name,
Email = @Email,
Phone = @Phone,
Age = @Age
where Id = @Id
GO
create procedure [dbo].[sp_getStudents]
as
select * from dbo.Students
GO
create procedure [dbo].[sp_getStudentsById]
@Id int
as
select * from dbo.Students
where id = @Id
GO
Update Program.cs file
Add below code on Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
{
options.UseSqlServer(builder.Configuration.GetConnectionString("SampleDatabase"));
});
Create New Scaffolded Item
Select “API Controller with actions, using Entity Framework” > click Add
Select or Enter the following then click Add
Model class: Student
DBContext Class: AppDbContext
Controllner Name: StudentController
Wait until scaffolding has successfully finished
Controller should be created after successful scaffolding
By this time, you can Run and Test the API application. Press F5
Expand GET /api/Students > Click Try Out button
Click Execute button
If Student table contain records, result would show on Response Body section (see below)
You can also try the other API endpoints:
ENDPOINT PURPOSE
GET /api/Students Get all records
POST /api/Students Add record
GET /api/Students/{Id} Get record by specific Id
PUT /api/Students/{Id} Edit record
DELETE /api/Students/{Id} Delete record
You can also try the above APIs on POSTMAN (see below for sample postman requests):
Next Steps is to Convert the pre-generated controller methods to connect or use
Stored Procedures
Before doing the succeeding steps, your should have already created the Stored Procedures
below on your database:
[dbo].[sp_AddStudent]
[dbo].[sp_DeleteStudent]
[dbo].[sp_EditStudent]
[dbo].[sp_getStudents]
[dbo].[sp_getStudentsById]
Open StudensController.cs
Update codes below for GET api/Students:
FROM:
// GET: api/Students
[HttpGet]
public async Task<ActionResult<IEnumerable<Student>>> GetStudents()
{
return await _context.Students.ToListAsync();
}
TO:
// GET: api/Students
[HttpGet]
public async Task<ActionResult<IEnumerable<Student>>> GetStudents()
{
string sqlQuery = "exec dbo.sp_getStudents";
var data = await _context.Students.FromSql(FormattableStringFactory.Create(sqlQuery)).ToListAsync();
if (data == null)
{
return NotFound();
}
return Ok(data);
}
Update codes below for GET api/Students/{id}:
FROM:
// GET: api/Students/5
[HttpGet("{id}")]
public async Task<ActionResult<Student>> GetStudent(long id)
{
var student = await _context.Students.FindAsync(id);
if (student == null)
{
return NotFound();
}
return student;
}
TO:
// GET: api/Students/5
[HttpGet("{id}")]
public async Task<ActionResult<Student>> GetStudent(long id)
{
string sqlQuery = "exec dbo.sp_getStudentsById @Id";
SqlParameter parameter = new SqlParameter("@Id", id);
var data = await _context.Students.FromSqlRaw(sqlQuery, parameter).ToListAsync();
if (data == null)
{
return NotFound();
}
return Ok(data[0]); //return the first item (index 0)
}
Update codes below for PUT api/Students/{id}:
FROM:
// PUT: api/Students/5
// To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
[HttpPut("{id}")]
public async Task<IActionResult> PutStudent(long id, Student student)
{
if (id != student.Id)
{
return BadRequest();
}
_context.Entry(student).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!StudentExists(id))
{
return NotFound();
}
else
{
throw;
}
}
return NoContent();
}
TO:
// PUT: api/Students/5
// To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
[HttpPut("{id}")]
public async Task<IActionResult> PutStudent(long id, Student student)
{
if (id != student.Id)
{
return BadRequest();
}
string sqlQuery = "dbo.sp_EditStudent @Id, @Name, @Email, @Phone, @Age";
SqlParameter[] parameter = {
new SqlParameter("@Id", student.Id),
new SqlParameter("@Name", student.Name),
new SqlParameter("@Email", student.Email),
new SqlParameter("@Phone", student.Phone),
new SqlParameter("@Age", student.Age)
};
var data = await _context.Database.ExecuteSqlRawAsync(sqlQuery, parameter);
return Ok(data);
}
Update codes below for POST api/Students:
FROM:
// POST: api/Students
// To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
[HttpPost]
public async Task<ActionResult<Student>> PostStudent(Student student)
{
_context.Students.Add(student);
await _context.SaveChangesAsync();
return CreatedAtAction("GetStudent", new { id = student.Id }, student);
}
TO:
// POST: api/Students
// To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
[HttpPost]
public async Task<ActionResult<Student>> PostStudent(Student student)
{
string sqlQuery = "dbo.sp_AddStudent @Name, @Email, @Phone, @Age";
SqlParameter[] parameter = {
new SqlParameter("@Name", student.Name),
new SqlParameter("@Email", student.Email),
new SqlParameter("@Phone", student.Phone),
new SqlParameter("@Age", student.Age)
};
var data = await _context.Database.ExecuteSqlRawAsync(sqlQuery, parameter);
return Ok(data);
}
Update codes below for DELETE api/Students/{id}:
FROM:
// DELETE: api/Students/5
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteStudent(long id)
{
var student = await _context.Students.FindAsync(id);
if (student == null)
{
return NotFound();
}
_context.Students.Remove(student);
await _context.SaveChangesAsync();
return NoContent();
}
TO:
// DELETE: api/Students/5
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteStudent(long id)
{
string sqlQuery = "dbo.sp_DeleteStudent @Id";
SqlParameter parameter = new SqlParameter("@Id", id);
var data = await _context.Database.ExecuteSqlRawAsync(sqlQuery, parameter);
return Ok(data);
}
Run and Re-Test the API Project by pressing F5
