CRUD Operations In ASP.NET MVC Using ADO.NET

using System.Data;
using System.Data.SqlClient;

namespace MVCWithADO.Models
{
    public class CRUDModel
    {
        /// <summary>
        /// Get all records from the DB
        /// </summary>
        /// <returns>Datatable</returns>
        public DataTable GetAllStudents()
        {
            DataTable dt = new DataTable();
            string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(strConString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("Select * from tblStudent", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// Get student detail by Student id
        /// </summary>
        /// <param name="intStudentID"></param>
        /// <returns></returns>
        public DataTable GetStudentByID(int intStudentID)
        {
            DataTable dt = new DataTable();
            string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(strConString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("Select * from tblStudent where student_id=" + intStudentID, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// Update the student details
        /// </summary>
        /// <param name="intStudentID"></param>
        /// <param name="strStudentName"></param>
        /// <param name="strGender"></param>
        /// <param name="intAge"></param>
        /// <returns></returns>
        public int UpdateStudent(int intStudentID, string strStudentName, string strGender, int intAge)
        {
            string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(strConString))
            {
                con.Open();
                string query = "Update tblStudent SET student_name=@studname, student_age=@studage, student_gender=@gender where student_id=@studid";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@studname", strStudentName);
                cmd.Parameters.AddWithValue("@studage", intAge);
                cmd.Parameters.AddWithValue("@gender", strGender);
                cmd.Parameters.AddWithValue("@studid", intStudentID);
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Insert Student record into DB
        /// </summary>
        /// <param name="strStudentName"></param>
        /// <param name="strGender"></param>
        /// <param name="intAge"></param>
        /// <returns></returns>
        public int InsertStudent(string strStudentName, string strGender, int intAge)
        {
            string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(strConString))
            {
                con.Open();
                string query = "Insert into tblStudent (student_name, student_age, student_gender) values(@studname, @studage, @gender)";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@studname", strStudentName);
                cmd.Parameters.AddWithValue("@studage", intAge);
                cmd.Parameters.AddWithValue("@gender", strGender);
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Delete student based on ID
        /// </summary>
        /// <param name="intStudentID"></param>
        /// <returns></returns>
        public int DeleteStudent(int intStudentID)
        {
            string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(strConString))
            {
                con.Open();
                string query = "Delete from tblStudent where student_id=@studid";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@studid", intStudentID);
                return cmd.ExecuteNonQuery();
            }
        }
    }
}

CRUDController.cs

In an MVC application, the controller is the entry point. The following code contains all the action methods for the complete CRUD operation.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using MVCWithADO.Models;

namespace MVCWithADO.Controllers
{
    public class CRUDController : Controller
    {
        /// <summary>
        /// First Action method called when page loads
        /// Fetch all the rows from DB and display it
        /// </summary>
        /// <returns>Home View</returns>
        public ActionResult Index()
        {
            CRUDModel model = new CRUDModel();
            DataTable dt = model.GetAllStudents();
            return View("Home", dt);
        }

        /// <summary>
        /// Action method, called when the "Add New Record" link clicked
        /// </summary>
        /// <returns>Create View</returns>
        public ActionResult Insert()
        {
            return View("Create");
        }

        /// <summary>
        /// Action method, called when the user hit "Submit" button
        /// </summary>
        /// <param name="frm">Form Collection  Object</param>
        /// <param name="action">Used to differentiate between "submit" and "cancel"</param>
        /// <returns></returns>
        public ActionResult InsertRecord(FormCollection frm, string action)
        {
            if (action == "Submit")
            {
                CRUDModel model = new CRUDModel();
                string name = frm["txtName"];
                int age = Convert.ToInt32(frm["txtAge"]);
                string gender = frm["gender"];
                int status = model.InsertStudent(name, gender, age);
                return RedirectToAction("Index");
            }
            else
            {
                return RedirectToAction("Index");
            }
        }

        /// <summary>
        /// Action method called when the user click "Edit" Link
        /// </summary>
        /// <param name="StudentID">Student ID</param>
        /// <returns>Edit View</returns>
        public ActionResult Edit(int StudentID)
        {
            CRUDModel model = new CRUDModel();
            DataTable dt = model.GetStudentByID(StudentID);
            return View("Edit", dt);
        }

        /// <summary>
        /// Action method, called when users update the record or cancel the update.
        /// </summary>
        /// <param name="frm">Form Collection</param>
        /// <param name="action">Denotes the action</param>
        /// <returns>Home view</returns>
        public ActionResult UpdateRecord(FormCollection frm, string action)
        {
            if (action == "Submit")
            {
                CRUDModel model = new CRUDModel();
                string name = frm["txtName"];
                int age = Convert.ToInt32(frm["txtAge"]);
                string gender = frm["gender"];
                int id = Convert.ToInt32(frm["hdnID"]);
                int status = model.UpdateStudent(id, name, gender, age);
                return RedirectToAction("Index");
            }
            else
            {
                return RedirectToAction("Index");
            }
        }

        /// <summary>
        /// Action method called when the "Delete" link clicked
        /// </summary>
        /// <param name="StudentID">Student ID to edit</param>
        /// <returns>Home view</returns>
        public ActionResult Delete(int StudentID)
        {
            CRUDModel model = new CRUDModel();
            model.DeleteStudent(StudentID);
            return RedirectToAction("Index");
        }
    }
}

Views

Views are a combination of markup as well as server-side code. As you noticed the views "Home" and "Edit" take the ADO.NET object Datatable as a model. Also, for simplicity, I don't use "Layout".

Home. cshtml

@using System.Data
@using System.Data.SqlClient
@model System.Data.DataTable
@{
    Layout = null;
}
<!DOCTYPE html>
<html>

<head>
    <meta name="viewport" content="width=device-width" />
    <title>Home</title>
</head>

<body>
    <form method="post" name="Display">
        <h2>Home</h2>
        @Html.ActionLink("Add New Record", "Insert")
        <br />
        @{
            if (Model.Rows.Count > 0)
            {
                <table border="1">
                    <thead>
                        <tr>
                            <td>Student ID</td>
                            <td>Name</td>
                            <td>Age</td>
                            <td>Gender</td>
                        </tr>
                    </thead>
                    @foreach (DataRow dr in Model.Rows)
                    {
                        <tr>
                            <td>@dr["student_id"].ToString()</td>
                            <td>@dr["student_name"].ToString()</td>
                            <td>@dr["student_age"].ToString()</td>
                            <td>@dr["student_gender"].ToString()</td>
                            <td>@Html.ActionLink("Edit", "Edit", new { StudentID = dr["student_id"].ToString() })</td>
                            <td>@Html.ActionLink("| Delete", "Delete", new { StudentID = dr["student_id"].ToString() })</td>
                        </tr>
                    }
                </table>
                <br />
            }
            else
            {
                <span>No records found!!</span>
            }
        }
    </form>
</body>

</html>

Create. cshtml

<!DOCTYPE html>
<html>

<head>
    <meta name="viewport" content="width=device-width" />
    <title>Insert</title>
</head>

<body>
    <form id="frmDetail" method="post" action="@Url.Action("InsertRecord")">
        Enter Name: <input name="txtName" /><br />
        Enter Age: <input name="txtAge" /><br />
        Select Gender:
        <input type="radio" name="gender" value="male" checked> Male
        <input type="radio" name="gender" value="female"> Female
        <br />
        <input type="submit" value="Submit" name="action" />
        <input type="submit" value="Cancel" name="action" />
    </form>
</body>

</html>

Edit. cshtml

@using System.Data
@using System.Data.SqlClient
@model System.Data.DataTable
@{
    ViewBag.Title = "EditView";
}
<html>
<head>
    <script type="text/javascript">
    </script>
</head>
<body>
    <form id="frmDetail" method="post" action="@Url.Action("UpdateRecord")">
        Enter Name: <input name="txtName" value="@Model.Rows[0]["student_name"]" /><br />
        Enter Age: <input name="txtAge" value="@Model.Rows[0]["student_age"]" /><br />
        Select Gender:
        @if (Model.Rows[0]["student_gender"].ToString().ToLower() == "male")
        {
            <input type="radio" name="gender" value="male" checked /> @Html.Raw("Male")
            <input type="radio" name="gender" value="female" /> @Html.Raw("Female")
        }
        else
        {
            <input type="radio" name="gender" value="male"> @Html.Raw("Male")
            <input type="radio" name="gender" value="female" checked /> @Html.Raw("Female")
        }
        <input type="hidden" name="hdnID" value="@Model.Rows[0]["student_id"]" /><br />
        <input type="submit" value="Submit" name="action" />
        <input type="submit" value="Cancel" name="action" />
    </form>
</body>
</html>

Readers, I hope you like this article. Let me know your thoughts as comments.