Crud operations in asp net core without entity framework

 In this We will give you an overview of how to perform CRUD operations in ASP.NET Core Web API without using Entity Framework.

Creating the model: We will create a plain old C# object (POCO) representing the data model you want to work with.

Creating the data access layer: We will create a data access layer that interacts with the database using raw SQL queries or stored procedures. You can use ADO.NET, Dapper, or any other data access technology.

Creating the controller: We will create a controller that will handle the HTTP requests from the client and call the appropriate methods from the data access layer.

Implementing CRUD operations: We will implement the CRUD operations in the controller methods. For example, to create a new record, we call the  “Insert”,”Update”,”Delete” method from the data access layer passing the values provided by the client.

Handling exceptions: You should handle exceptions thrown by the data access layer and return appropriate HTTP status codes to the client.

Model Class

public class UserDTOModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string ContactNo { get; set; }
    }

API Controller

 [Route("api/[controller]")]
    [ApiController]
    public class UserController : ControllerBase
    {
        string constr = "Data Source=SQLEXPRESS01;Initial Catalog=DotNetPeTips;User ID=sa;Password=sa@1234";
        // GET: api/User
        [HttpGet]
        public async Task<ActionResult<IEnumerable<UserDTOModel>>> GetAllUsers()
        {
            List<UserDTOModel> users = new List<UserDTOModel>();
            string query = "SELECT * FROM tblusers";
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            users.Add(new UserDTOModel
                            {
                                Id = Convert.ToInt32(sdr["Id"]),
                                Name = Convert.ToString(sdr["Name"]),
                                Email = Convert.ToString(sdr["Email"]),
                                ContactNo = Convert.ToString(sdr["ContactNo"]),
                            });
                        }
                    }
                    con.Close();
                }
            }

            return users;
        }

        // GET: api/user/5
        [HttpGet("{id}")]
        public async Task<ActionResult<UserDTOModel>> GetUser(long id)
        {

            UserDTOModel userObj = new UserDTOModel();
            string query = "SELECT * FROM tblusers where Id=" + id;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            userObj = new UserDTOModel
                            {
                                Id = Convert.ToInt32(sdr["Id"]),
                                Name = Convert.ToString(sdr["Name"]),
                                Email = Convert.ToString(sdr["Email"]),
                                ContactNo = Convert.ToString(sdr["ContactNo"]),
                            };
                        }
                    }
                    con.Close();
                }
            }
            if (userObj == null)
            {
                return NotFound();
            }
            return teacherObj;
        }
        // PUT: api/user/5
        [HttpPut("{id}")]
        public async Task<IActionResult> PutUser(long id, UserDTOModel userDTOModel)
        {
            if (id != userDTOModel.Id)
            {
                return BadRequest();
            }
            if (ModelState.IsValid)
            {
                string query = "UPDATE tblusers SET Name = @Name, Email = @Email," +
                    "ContactNo=@ContactNo Where Id =@Id";
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Name", userDTOModel.Name);
                        cmd.Parameters.AddWithValue("@Email", userDTOModel.Email);
                        cmd.Parameters.AddWithValue("@ContactNo", userDTOModel.ContactNo);
                        cmd.Parameters.AddWithValue("@Id", userDTOModel.Id);
                        con.Open();
                        int i = cmd.ExecuteNonQuery();
                        if (i > 0)
                        {
                            return NoContent();
                        }
                        con.Close();
                    }
                }

            }
            return BadRequest(ModelState);
        }

        // POST: api/user
        [HttpPost]
        public async Task<ActionResult<TeacherModel>> PostUser(UserDTOModel userDTOModel)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            using (SqlConnection con = new SqlConnection(constr))
            {
                //inserting Patient data into database
                string query = "insert into tblusers values (@Name, @Email, @ContactNo)";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Name", userDTOModel.Name);
                    cmd.Parameters.AddWithValue("@Email", userDTOModel.Email);
                    cmd.Parameters.AddWithValue("@ContactNo", userDTOModel.ContactNo);
                    con.Open();
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        return Ok();
                    }
                    con.Close();
                }
            }
            return BadRequest();

        }

        // DELETE: api/user/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteUser(long id)
        {

            using (SqlConnection con = new SqlConnection(constr))
            {
                string query = "Delete FROM tblusers where Id='" + id + "'";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    con.Open();
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        return NoContent();
                    }
                    con.Close();
                }
            }
            return BadRequest();
        }

    }    

Above is just a an example, but it should give you an idea of how to perform CRUD operations in ASP.NET Core Web API without using Entity Framework in your application.