Building a REST API with .NET ASP.NET Core and SQL Server

SQL Server Connection

This guide will walk you through creating a REST API using ASP.NET Core and connecting it to a SQL Server database. The API will have two endpoints:

• GET /api/getUserData: Retrieves user data from the users table based on the provided user_id parameter.
• POST /api/updateUser: Updates user data in the users table based on the provided user_id, fullname, and email parameters.

Prerequisites:

• Visual Studio or your preferred IDE
• .NET 6.0 or higher
• SQL Server database set up
• Basic understanding of C# programming
• Familiarity with REST API concepts

Steps:

1. Create a new ASP.NET Core Web API project: Open Visual Studio and create a new ASP.NET Core Web API project. Select the “API” project template and name it appropriately, such as “MyRestApi”.
2. Sql server connect : Install SQL Server Entity Framework Core: Install the Microsoft.EntityFrameworkCore.SqlServer NuGet package to enable Entity Framework Core for SQL Server. This package will help you interact with the database.
3. Postgre sql connect: Install the Npgsql.EntityFrameworkCore NuGet package to enable Entity Framework Core for PostgreSQL. This package will help you interact with the database.
4. Mysql connect: Install the MySql.Data.EntityFrameworkCore NuGet package to enable Entity Framework Core for MySQL. This package will help you interact with the database.
5. Define data models: Create a models folder and define the User class to represent the data structure for users:

   public class User
   {
       public int UserId { get; set; }
       public string Fullname { get; set; }
       public string Email { get; set; }
   }

4. Configure database connection: In the appsettings.json file, add the connection string to your SQL Server database:

   {
       "ConnectionStrings": {
           "DefaultConnection": "Server=localhost;Database=myDatabase;User Id=sa;Password=myPassword;"
       }
   }

5. Create a database context: Create a DbContext class to manage interactions with the database:

   public class ApplicationDbContext : DbContext
   {
       public DbSet<User> Users { get; set; }

       public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
           : base(options)
       {
       }
   }

6. Implement data access services: Create a services folder and implement a repository class to handle data access operations:

   public class UserRepository
   {
       private readonly ApplicationDbContext _context;

       public UserRepository(ApplicationDbContext context)
       {
           _context = context;
       }

       public async Task<User> GetUserById(int userId)
       {
           return await _context.Users.FindAsync(userId);
       }

       public async Task UpdateUser(User user)
       {
           _context.Users.Update(user);
           await _context.SaveChangesAsync();
       }
   }

7. Create API controllers: Create a controllers folder and define the API controllers for the GET and POST endpoints:

   [Route("api/[controller]")]
   [ApiController]
   public class UsersController : ControllerBase
   {
       private readonly UserRepository _userRepository;

       public UsersController(UserRepository userRepository)
       {
           _userRepository = userRepository;
       }

       [HttpGet("{userId}")]
       public async Task<ActionResult<User>> GetUserData(int userId)
       {
           var user = await _userRepository.GetUserById(userId);
           if (user == null)
           {
               return NotFound();
           }

           return user;
       }

       [HttpPost]
       public async Task<ActionResult<User>> UpdateUser([FromBody] User user)
       {
           if (!ModelState.IsValid)
           {
               return BadRequest(ModelState);
           }

           await _userRepository.UpdateUser(user);

           return Ok(user);
       }
   }

8. Register services and configure middleware: In the Startup.cs file, register the ApplicationDbContext and UserRepository as services and configure middleware for error handling and JSON serialization: “`c#
   public void ConfigureServices(IServiceCollection services)
   {
   services.AddDbContext(options =>
   {
   options.UseSqlServer(Configuration.GetConnectionString(“DefaultConnection”));
   }); services.AddScoped(); services.AddControllers().AddNewtonsoftJson();
   } public void Configure(IApplicationBuilder app, IWebHostEnvironment env)