users api

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;
using WebApplication1.Models;

namespace WebApplication1.Controllers
{
    //[Route("api/[controller]")]
    //[ApiController]
    public class UsersController : ControllerBase
    {

        private readonly IConfiguration _configuration;
        public UsersController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        [HttpGet]
        [Route("users")]
        public JsonResult GetUsers()
        {
            string query = @"select * from  dbo.users";
            string sqlDataSource = _configuration.GetConnectionString("AzureSQL");
            DataTable table = new DataTable();
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }
            return new JsonResult(table);
        }


        [HttpGet]
        [Route("user/{id}")]
        public JsonResult GetUser(int id)
        {
            string query = @"select * from  dbo.users where ID=@userID";
            string sqlDataSource = _configuration.GetConnectionString("AzureSQL");
            DataTable table = new DataTable();
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myCommand.Parameters.AddWithValue("@userID", id);
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult(table);
        }

        [HttpPost]
        [Route("user")]
        public JsonResult Post([FromBody] User user)
        {
            string query = @"INSERT INTO dbo.users (LastName, FirstName, Address, City, ZipCode, Country, Email, AvatarURL)
                        VALUES(
                        @userLastName,
                        @userFirstName,
                        @userAddress,
                        @userCity,
                        @userZipCode,
                        @userCountry,
                        @userEmail,
                        @userAvatarURL)
                        ";

            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("AzureSQL");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myCommand.Parameters.AddWithValue("@userLastName", user.LastName);
                    myCommand.Parameters.AddWithValue("@userFirstName", user.FirstName);
                    myCommand.Parameters.AddWithValue("@userAddress", user.Address);
                    myCommand.Parameters.AddWithValue("@userCity", user.City);
                    myCommand.Parameters.AddWithValue("@userZipCode", user.ZipCode);
                    myCommand.Parameters.AddWithValue("@userCountry", user.Country);
                    myCommand.Parameters.AddWithValue("@userEmail", user.Email);
                    myCommand.Parameters.AddWithValue("@userAvatarURL", user.AvatarURL);

                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }
            return new JsonResult("Added Successfully");
        }



        [HttpPut]
        [Route("user")]
        public JsonResult Put([FromBody] User user)
        {
            string query = @"UPDATE dbo.users SET LastName=@userLastName,
                            FirstName = @userFirstName,
                            Address = @userAddress,
                            City = @userCity,
                            ZipCode = @userZipCode,
                            Country = @userCountry,
                            Email = @userEmail,
                            AvatarURL = @userAvatarURL
                            WHERE ID = @userID";

            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("AzureSQL");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myCommand.Parameters.AddWithValue("@userLastName", user.LastName);
                    myCommand.Parameters.AddWithValue("@userFirstName", user.FirstName);
                    myCommand.Parameters.AddWithValue("@userAddress", user.Address);
                    myCommand.Parameters.AddWithValue("@userCity", user.City);
                    myCommand.Parameters.AddWithValue("@userZipCode", user.ZipCode);
                    myCommand.Parameters.AddWithValue("@userCountry", user.Country);
                    myCommand.Parameters.AddWithValue("@userID", user.ID);
                    myCommand.Parameters.AddWithValue("@userEmail", user.Email);
                    myCommand.Parameters.AddWithValue("@userAvatarURL", user.AvatarURL);

                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }
            return new JsonResult("Updated Successfully");
        }


        [HttpDelete]
        [Route("user/{id}")]
        public JsonResult DeleteUser(int id)
        {
            string query = @"DELETE FROM dbo.users where ID=@userID";
            string sqlDataSource = _configuration.GetConnectionString("AzureSQL");
            DataTable table = new DataTable();
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myCommand.Parameters.AddWithValue("@userID", id);
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult("Deleted Successfully");
        }


    }
}