CRUD Operations in MVC using ADO.NET

CRUD stands for Create the data into database, Read the the data from database ,Update the the data into the database and Delete the data from the database.Here we are using the ADO.NET with MVC to perform the CRUD operations.
Problem Statement :- As you shown in the below image data is oragnized into a grid and we used three buttons one for Edit the record , second for Details of record and third for Delete the record. We select the data from Database and presented here into a grid/table. You can see a Create New button also on the left top ,when we click on this button we redirected to new user creation page from there we can add the user.So in this article you will learn How to Create Data,How to select Data and How to Update the data based on selected user and How to Delete the data in details and step by step with complete code.

When we click on Create New then you can redirect on below page and we can add a user from this page.

When we click on Edit then you can redirect on edit page and we can add edit a user from this page.

When we click on Deatils then you can redirect on details page and you can see the details of a user in this page.

When we click on Delete then you can redirect on delete page and you can delete a user from the database.

Details Implementation on above problem statement

For implementing CRUD we need to follow below steps.

Create a MVC Project in Visual Studio.
Add a User Class in Models folder and add the properties into it.
Add a empty Controller and named it as User .
Add a folder named withDataAccessLayer and add a Class named Crudoperations into the folder ,used for ADO.Net code.
Create a table in database named with tblUsers for saving the user records here.
Create Stored Procedure for Select Users,Insert User,Update the User ,Delete the User.
Add ConnectionString into the Crudoperations class to connect with Database.
Install required packages System.Data and System.Data.SqlClient from Nuget Package Manager.
Add methods in Crudoperations class for Insert,Update,Edit and Delete data using the ADO.Net Code.
Add Action methods into the Controller for Insert,Update,Edit and Delete with HttpGet and HttpPost.
Add Views for Action Methods and decorate with HTML and CSS.

Create a MVC Project and Added folders and Classes and then structure of Project folder looks like below.

Write the below code in User model class and use the namespace using System.ComponentModel for DisplayName attribute.


                         public class User

                    {

                    [DisplayName("User Number")]

                            public int UserId { get; set; }

                            [DisplayName("First Name")]

                            public string Name { get; set; }

                    [DisplayName("Last Name")]

                            public string LastName { get; set; }

                            [DisplayName("User Name")]

                            public string UserName { get; set; }

                    public string Password { get; set; }

                            [DisplayName("Mobile Number")]

                    public string Mobile { get; set; }

                    }

Write the below code in Crudoperation class for inserting,selecting,updating and deleting the data from database using ADO.Net.

                
        public class UserCrudOperation
        {
        public string connectionString="";
        connectionString="Data Source=DESKTOP-1CO7VJ1\\SQLEXPRESS;Initial Catalog=TestDB;
        Integrated Security=True";
        public List GetUserList()
        {
            List list = new List();

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SP_Select_Users";
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                User user = new User();
                user.UserId = Convert.ToInt32(reader["UserId"]);
                user.Name = Convert.ToString(reader["Name"]);
                user.LastName = Convert.ToString(reader["LastName"]);
                user.UserName = Convert.ToString(reader["UserName"]);
                user.Password = Convert.ToString(reader["Password"]);
                user.Mobile = Convert.ToString(reader["Mobile"]);
                list.Add(user);
            }
            return list;
        }
        public void CreateUser(User user)
        {

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SP_Insert_User";
            conn.Open();
            cmd.Parameters.AddWithValue("@Name", SqlDbType.NVarChar).Value = user.Name;
            cmd.Parameters.AddWithValue("@LastName", SqlDbType.NVarChar).Value = user.LastName;
            cmd.Parameters.AddWithValue("@UserName", SqlDbType.NVarChar).Value = user.UserName;
            cmd.Parameters.AddWithValue("@Password", SqlDbType.NVarChar).Value = user.Password;
            cmd.Parameters.AddWithValue("@Mobile", SqlDbType.NVarChar).Value = user.Mobile;
            cmd.ExecuteNonQuery();
        }

        public User GetUserByUserID(int userId)
        {
            User user = new User();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SP_Select_User_by_UserID";
            cmd.Parameters.AddWithValue("@UserId", SqlDbType.Int).Value = userId;
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {

                user.UserId = Convert.ToInt32(reader["UserId"]);
                user.Name = Convert.ToString(reader["Name"]);
                user.LastName = Convert.ToString(reader["LastName"]);
                user.UserName = Convert.ToString(reader["UserName"]);
                user.Password = Convert.ToString(reader["Password"]);
                user.Mobile = Convert.ToString(reader["Mobile"]);

            }
            return user;
        }
        public void UpdateUser(User user)
        {

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SP_Update_User";
            conn.Open();
            cmd.Parameters.AddWithValue("@UserId", SqlDbType.Int).Value = user.UserId;
            cmd.Parameters.AddWithValue("@Name", SqlDbType.NVarChar).Value = user.Name;
            cmd.Parameters.AddWithValue("@LastName", SqlDbType.NVarChar).Value = user.LastName;
            cmd.Parameters.AddWithValue("@UserName", SqlDbType.NVarChar).Value = user.UserName;
            cmd.Parameters.AddWithValue("@Password", SqlDbType.NVarChar).Value = user.Password;
            cmd.Parameters.AddWithValue("@Mobile", SqlDbType.NVarChar).Value = user.Mobile;
            cmd.ExecuteNonQuery();
        }

        public void DeleteUser(int UserId)
        {

            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "SP_Delete_User";
            conn.Open();
            cmd.Parameters.AddWithValue("@UserId", SqlDbType.Int).Value = UserId;

            cmd.ExecuteNonQuery();
        }
    }

Write the below code in User Controller for Actions methods used for Select User List,Edit User,and Delete the user.

                
    public class UserController : Controller
    {
        [HttpGet]
        public IActionResult UserList()
        {
            List users = new List();
            DataAccessLayer.UserCrudOperation operation = new DataAccessLayer.UserCrudOperation();
            users = operation.GetUserList();
            return View(users);
        }
        [HttpGet]
        public IActionResult CreateUser()
        {
            User user = new User();
            return View(user);
        }
        [HttpPost]
        public IActionResult CreateUser(User user)
        {
            DataAccessLayer.UserCrudOperation userCrudOperation = new DataAccessLayer.UserCrudOperation();
            userCrudOperation.CreateUser(user);
            return RedirectToAction("UserList");
        }
        [HttpGet]
        public IActionResult EditUser(int id)
        {
            DataAccessLayer.UserCrudOperation operation = new DataAccessLayer.UserCrudOperation();
            User user = operation.GetUserList().FirstOrDefault(x=>x.UserId==id); 
            return View(user);
        }
        [HttpPost]
        public IActionResult EditUser(User user)
        {
            User userObj = new User();
            userObj.UserId =Convert.ToInt32(user.UserId);
            userObj.Name = Convert.ToString(user.Name);
            userObj.LastName = Convert.ToString(user.LastName);
            userObj.UserName = Convert.ToString(user.UserName);
            userObj.Password = Convert.ToString(user.Password);
            userObj.Mobile = Convert.ToString(user.Mobile);
            DataAccessLayer.UserCrudOperation operation = new DataAccessLayer.UserCrudOperation();
            operation.UpdateUser(userObj);
            return RedirectToAction("UserList");
        }
        [HttpGet]
        public IActionResult UserDetails(int id)
        {
            DataAccessLayer.UserCrudOperation operation = new DataAccessLayer.UserCrudOperation();
            User user = operation.GetUserList().FirstOrDefault(x => x.UserId == id);
            return View(user);
        }
        [HttpGet]
        public IActionResult DeleteUser(int id)
        {
            DataAccessLayer.UserCrudOperation operation = new DataAccessLayer.UserCrudOperation();
            User user = operation.GetUserList().FirstOrDefault(x => x.UserId == id);
            return View(user);
        }
        [HttpGet]
        public IActionResult DeleteUserbyId(int id)
        {
               DataAccessLayer.UserCrudOperation operation = new DataAccessLayer.UserCrudOperation();
               operation.DeleteUser(id);
            return RedirectToAction("UserList");
        }
        [HttpPost]
        public IActionResult DeleteUser(int id,User user)
        {
            DataAccessLayer.UserCrudOperation operation = new DataAccessLayer.UserCrudOperation();
            operation.DeleteUser(id);
            return RedirectToAction("UserList");
        }
    }

View for Create Action Method to display data into the view just copy and paste below code.

View for GetList for Users will display all the users data into the page.

View for Delete to delete a user just copy and paste below code.

View for user Details to display details of any user into the view just copy and paste below code.

View for Edit User based on userid we can edit any user into the view ,just copy and paste below code.

Below is the SQL Stored Procedures used in CRUD operation.