Manual CRUD Operation In MVC Using ADO.NET

I am explaining about manual CRUD operations;  i.e., I am not using entity framework here. I am using  ADO.NET. So we will see step by step.

Step 1

First, we have to create a database. Here I took an example of database ‘MVCDB’ and created a table for this; I took the example table name ‘Registration’.

For this open the SQL Server management studio to create these.



Step 2

Now we have to create a project, so for this open visual studio and create a project and give a name for example, CRUDOperation,



Next we click OK button and choose mvc template and again click OK.



After creating the project we have to create a model class so for this, I added a model class and gave a name, for example, User.cs. In this go in solution explorer and go to the model folder and select Add option and select a class,



Write the code and define all properties of user class

User.cs

using System.Web;

namespace mvcRegistration.Models

{
    public class User

    {
        public int userId
      {
            get;
            set;
        }

        public string firstName
        {
            get;
            set
        }

        public string middileName
        {
            get;
            set;
        }

        public string lastName
        {
            get;
            set;
        }

        public string dob
        {
            get;
            set;
        }

        public string address
        {
            get;
            set;
        }

        public string phoneNo
        {
            get;
            set;
        }

        public string emailId
        {
            get;
            set;
        }

        public string password
        {
            get;
            set;
        }

        public string pincode
        {
            get;
            set;
        }

    }

}

Again add another class(same process) and give the a name(Ex: Details.cs).Here I created a class and wrote business logic to display the user information.

Add the namespace Data and Data.SqlClient for database operation.

Details.cs

using System;

using System.Data;

using System.Data.SqlClient;

namespace mvcRegistration.Models

{
    public class Details

    {
        string constr = @ "Server=mithilesh;Database=MVCDB;User Id=sa;Password=password";

        public void AddUser(User obj)

        {

            string cmdText = string.Format("Insert into Registration vaules{0},{1},{2},{3},{4},{5},{6},{7},{8},{9})", obj.userId, obj.firstName, obj.middileName, obj.lastName, obj.dob, obj.address, obj.phoneNo, obj.emailId, obj.password, obj.pincode);

            SqlConnection con = new SqlConnection(constr);

            SqlCommand cmd = new SqlCommand(cmdText, con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }
        public void DeleteUser(int i)

        {
            string cmdText = string.Format("Delete from Registration where UserId={0}", i);

            SqlConnection con = new SqlConnection(constr);

            SqlCommand cmd = new SqlCommand(cmdText, con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }
        public void EditUser(User obj)

        {
            string cmdText = string.Format("Update Registration Set FirstName='{0}',MidleName='{1}',LastName='{2}',DOB='{3}',Addres='{4}',FoneNo='{5}',EmailId='{6}',Password='{7}',PinCode='{8}' where UserId={9}",
                obj.firstName, obj.middileName, obj.lastName, obj.dob, obj.address, obj.phoneNo, obj.emailId, obj.password, obj.pincode, obj.userId);


            SqlConnection con = new SqlConnection(constr);

            SqlCommand cmd = new SqlCommand(cmdText, con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }

    }

}

Again we add a class (with same process)

And give a name(Ex: DataContext.cs).

See the below

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

namespace mvcRegistration.Models

{
    public class DataContext

    {
        string constr = @ "Server=mithilesh;Database=MVCDB;User Id=sa;Password=password";

        public List < User > GetUsers()

        {
            string cmdText = "Select * from Registration";

            SqlDataAdapter da = new SqlDataAdapter(cmdText, constr);

            DataSet ds = new DataSet();

            da.Fill(ds);
            List < User > UserList = new List < User > ();

            foreach(DataRow item in ds.Tables[0].Rows)

            {
                User objUser = new User();

                objUser.userId = int.Parse(item["UserId"].ToString());

                objUser.firstName = item["FirstName"].ToString();

                objUser.middileName = item["MidleName"].ToString();

                objUser.lastName = item["LastName"].ToString();

                objUser.dob = item["DOB"].ToString();

                objUser.address = item["Addres"].ToString();

                objUser.phoneNo = item["FoneNo"].ToString();

                objUser.emailId = item["EmailId"].ToString();

                objUser.password = item["Password"].ToString();

                objUser.pincode = item["PinCode"].ToString();

                UserList.Add(objUser);

            }
            return UserList;

        }
        public User GetUser(int i)

        {
            string cmdText = "Select * from Registration Where UserId=" + i;

            SqlConnection con = new SqlConnection(constr);

            SqlCommand cmd = new SqlCommand(cmdText, con);

            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            User objUser = new User();

            if (dr.Read() == true)

            {
                objUser.userId = int.Parse(dr["UserIs"].ToString());

                objUser.firstName = dr["FirstName"].ToString();

                objUser.middileName = dr["MidleName"].ToString();

                objUser.lastName = dr["LastName"].ToString();

                objUser.dob = dr["DOB"].ToString();

                objUser.address = dr["Addres"].ToString();

                objUser.phoneNo = dr["FoneNo"].ToString();

                objUser.emailId = dr["EmailId"].ToString();

                objUser.password = dr["Password"].ToString();

                objUser.pincode = dr["PinCode"].ToString();

            }
            con.Close();
            return objUser;

        }

    }

}

Step 3

Now we have to create a controller class so for this, right click on solution explorer and go to Controllers folder and right click on the controller and add a controller class and give a name, for example RegController.

I selected empty Controller and clicked add option,



Note: Here we have to add a namespace.

(According my project)

using mvcRegistration.Models;

And write the code as shown below,

using System;
using System.Web;
using System.Web.Mvc;
using mvcRegistration.Models;

namespace mvcRegistration.Controllers

{
    public class RegController: Controller

    { //

        // GET: /Reg/

        Details ds = new Details();

        DataContext DC = new DataContext();

        public ActionResult Index()

        {
            List < User > UserList = DC.GetUsers();

            return View(UserList);

        }
        public ActionResult Details(int id)

        {
            User objUser = DC.GetUser(id);

            return View(objUser);

        }
        public ActionResult Create()

        {
            return View();

        }
        [HttpPost]

        public ActionResult Create(User obj)

        {
            ds.AddUser(obj);
            return RedirectToAction("Index");

        }
        public ActionResult Delete(int id)

        {
            User obj = DC.GetUser(id);

            return View(obj);

        }
        [HttpPost]

        public ActionResult Delete(string id)

        {
            int i = int.Parse(id);

            ds.DeleteUser(i);
            return RedirectToAction("Index");

        }
        public ActionResult Edit(int id)

        {
            User obj = DC.GetUser(id);

            return View(obj);

        }
        public ActionResult Edit(User obj)

        {
            ds.EditUser(obj);
            return RedirectToAction("Index");


        }

    }

}

Step 4

Now we have to create, view pages for displaying the user information so for this, Right click on index() method in RegController.

Select AddView, here it automaticaly displays the index name so leave that and click ok, then one view page is created, "Index.cshtml".

And then write the code,

NOTE-Add namespace   

@using mvcRegistration.Models
@model IEnumerable
<User>

index.cshtml
@{
ViewBag.Title = "Index";

}

@using mvcRegistration.Models

@model IEnumerable
    <User>
        <div align="center">
            <h1 align="center" style="background-color: aqua; color: maroon;border-radius: 25px; box-shadow: 10px 10px 10px 10px yellow; width:800px;">Index</h1>
        </div>
        <hr />
        <div align="center">
            <a href="Home/Create">
                <b style="font-size: 25px"align="center">Create New</b>
            </a>
        </div>
        <br />
        <br />
        <table align="center" width="80%" style="background-image:url(Images/Baroque-485x728.jpg); background-color: darkgreen;border: double solid maroon; font-weight: bold; color: white; padding:5px 8px 4px 10px; border-color: white; font-size: 20px; border-radius:25px; box-shadow: 10px 10px 10px 10px maroon" border="3">
            <tr>
                <th width="10%">User Id</th>
                <th>First Name</th>
                <th>Middle Name</th>
                <th>Last Name</th>
                <th width="10%">DOB</th>
                <th>Address</th>
                <th>Phone Number</th>
                <th>Emaild Id</th>
                <th>Password</th>
                <th>PinCode</th>
            </tr>

@{
foreach (User item in Model)

{
int n = item.userId;


            <tr>
                <td>@item.userId</td>
                <td>@item.firstName</td>
                <td>@item.middileName</td>
                <td>@item.lastName</td>
                <td>@item.dob</td>
                <td>@item.address</td>
                <td>@item.phoneNo</td>
                <td>@item.emailId</td>
                <td>@item.password</td>
                <td>@item.pincode</td>
            </tr>

}
}

        </table>

See the output



Similarly, we have to add a class to display information for a particular user.

So for this, right click on detail method in the controller and add a view page and give a name for exam details.cshtml and write the code.

details.cshtml

@{
Layout = null;

}

@using mvcRegistration.Models

@model User


<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>Details</title>
    </head>
    <body>
        <div>
            <table width="40%" border="2" align="center" style="background-image:url(~/Images/colorful_ride-wide.jpg); background-color:#3D0B0B; border: double solid maroon; font-weight: bold; color:white; padding: 5px 8px 4px 10px; border-color: white; font-size:20px; border-radius: 25px; box-shadow: 10px 10px 10px 10pxmaroon; font-weight: bold">
                <tr>
                    <td>User Id</td>
                    <td>@Model.userId</td>
                </tr>
                <tr>
                    <td>First Name</td>
                    <td>@Model.firstName</td>
                </tr>
                <tr>
                    <td>Middle Name</td>
                    <td>@Model.middileName</td>
                </tr>
                <tr>
                    <td>Last Name</td>
                    <td>@Model.lastName</td>
                </tr>
                <tr>
                    <td>Date of Birthday</td>
                    <td>@Model.dob</td>
                </tr>
                <tr>
                    <td>Addrss</td>
                    <td>@Model.address</td>
                </tr>
                <tr>
                    <td>Phone Number</td>
                    <td>@Model.phoneNo</td>
                </tr>
                <tr>
                    <td>Email Id</td>
                    <td>@Model.emailId</td>
                </tr>
                <tr>
                    <td>Password</td>
                    <td>@Model.password</td>
                </tr>
                <tr>
                    <td>PinCode</td>
                    <td>@Model.pincode</td>
                </tr>
                <tr>
                    <td colspan="2">
                        <a href="/">Back to Index</a>
                    </td>
                </tr>
            </table>
        </div>
    </body>
</html>

See the output



Similarly, we have to add a class to Add information for a particular user.

So for this, right click on create method in the controller and add a view page and give a name, for example, create.cshtml and write the code.

Create.cshtml

@{
Layout = null;

}


<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>Create</title>
    </head>
    <body>
        <div align="center">
            <h1 align="center" style="background-color:aqua; color: maroon; border-radius: 25px; box-shadow: 10px 10px10px 10px yellow; width: 800px;">Create New Employee Details</h1>
        </div>
        <div>

@using(Html.BeginForm())


{

            <table width="40%" border="2" align="center" style="background-image:url(~/Images/colorful_ride-wide.jpg); background-color:#061CC7; border: double solid maroon; font-weight: bold; color:white; padding: 5px 8px 4px 10px; border-color: white; font-size:20px; border-radius: 25px; box-shadow: 10px 10px 10px 10pxmaroon; font-weight: bold">
                <tr>
                    <td>@Html.Label("First Name")</td>
                    <td>@Html.TextBox("FirstName")</td>
                </tr>
                <tr>
                    <td>@Html.Label("MiddleName")</td>
                    <td>@Html.TextBox("MiddleName")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Last Name")</td>
                    <td>@Html.TextBox("LastName")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Date Of Birth")</td>
                    <td>@Html.TextBox("DOB")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Address")</td>
                    <td>@Html.TextBox("Addres")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Phone Number")</td>
                    <td>@Html.TextBox("FoneNo")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Email Id")</td>
                    <td>@Html.TextBox("EmailId")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Password")</td>
                    <td>@Html.TextBox("Password")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Pine Code")</td>
                    <td>@Html.TextBox("PinCode")</td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <input type="submit"name="sb1" value="Create" />
                    </td>
                </tr>
            </table>

}

            <br />
            <a href="/" style="font-size:25px;align-content:center">
                <balignbalign="center">Back to Index
                </b>
            </a>
        </div>
    </body>
</html>

See the output


Similarly, we have to add a class to delete information for a particular user.

So for this, right click on delete method in the controller and add a view page and give a name for exam delete.cshtml and write the code.

delete.cshtml

@{
Layout = null;

}

@using mvcRegistration.Models;

@model User


<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>Delete</title>
    </head>
    <body>
        <div>
            <div align="center">
                <h1 align="center" style="background-color: aqua; color: maroon;border-radius: 15px; box-shadow: 7px 7px 7px 7px darkgreen; width:800px;">Delete Employee Detail</h1>
            </div>
            <br />

@using (Html.BeginForm())




{

            <table width="40%" border="2" align="center" style="background-image: url(~/Images/colorful_ride-wide.jpg); background-color:#3D0B0B; border: double solid maroon; font-weight: bold; color:white; padding: 5px 8px 4px 10px; border-color: white; font-size:20px; border-radius: 25px; box-shadow: 10px 10px 10px 10pxdarkgreen; font-weight: bold">
                <tr>
                    <td>@Html.Label("User Id")</td>
                    <td>@Html.TextBox("UserId")</td>
                </tr>
                <tr>
                    <td>@Html.Label("First Name")</td>
                    <td>@Html.TextBox("FirstName")</td>
                </tr>
                <tr>
                    <td>@Html.Label("MiddleName")</td>
                    <td>@Html.TextBox("MidleName")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Last Name")</td>
                    <td>@Html.TextBox("LastName")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Date Of birth")</td>
                    <td>@Html.TextBox("DOB")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Address")</td>
                    <td>@Html.TextBox("Addres")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Phone Number")</td>
                    <td>@Html.TextBox("FoneNo")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Email Id")</td>
                    <td>@Html.TextBox("EmailId")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Password")</td>
                    <td>@Html.TextBox("Password")</td>
                </tr>
                <tr>
                    <td>@Html.Label("Pine Code")</td>
                    <td>@Html.TextBox("PinCode")</td>
                </tr>
                <tr>
                    <td colspan="2">
                        <b align="center" style="color: red; font-size:30px">Do want delete?</b>


                        <input type="submit" style="font-size: 25px; font-weight: bold"name="sb1" value="Delete" />
                    </td>
                </tr>
            </table>




}

            <hr />
            <br />
            <br />
            <div align="center">
                <a href="/" style="font-size: 25px; font-weight: bold">Back to Index</a>
            </div>
        </div>
    </body>
</html>

output


Similarly, we have to add a class to edit information for a particular user.

So for this, right click on Edit method in the controller and add a view page and give a name for example Edit.cshtml and write the code.

Edit.cshtml

@{
Layout = null;

}

@using mvcRegistration.Models

@model User


<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>Edit</title>
    </head>
    <body>
        <div align="center">
            <h1 align="center" style="background-color:aqua; color: maroon; border-radius: 15px; box-shadow: 7px 7px 7px7px darkgreen; width: 800px;">Edit Employee Details</h1>
        </div>
        <hr />
        <div>

@using(Html.BeginForm())

{

            <table width="40%" border="2" align="center" style=" background-color: #4B2A2F; border: double solid maroon; font-weight: bold; color:white; padding: 5px 8px 4px 10px; border-color: white; font-size:20px; border-radius: 25px; box-shadow: 10px 10px 10px 10pxdarkgreen; font-weight: bold">
                <tr>
                    <td>@Html.Label("User Id")</td>
                    <td>@Html.TextBox("UserId",Model.userId)</td>
                </tr>
                <tr>
                    <td>@Html.Label("First Name")</td>
                    <td>@Html.TextBox("FirstName",Model.firstName)</td>
                </tr>
                <tr>
                    <td>@Html.Label("Middle Name")</td>
                    <td>@Html.TextBox("MidleName",Model.middileName)</td>
                </tr>
                <tr>
                    <td>@Html.Label("Last Name")</td>
                    <td>@Html.TextBox("LastName",Model.lastName)</td>
                </tr>
                <tr>
                    <td>@Html.Label("Address")</td>
                    <td>@Html.TextBox("Addres",Model.address)</td>
                </tr>
                <tr>
                    <td>@Html.Label("Phone Number")</td>
                    <td>@Html.TextBox("FoneNo",Model.phoneNo)</td>
                </tr>
                <tr>
                    <td>@Html.Label("Email Id")</td>
                    <td>@Html.TextBox("EmailId",Model.EmailId)</td>
                </tr>
                <tr>
                    <td>@Html.Label("Password")</td>
                    <td>@Html.TextBox("Password",Model.password)</td>
                </tr>
                <tr>
                    <td>@Html.Label("Pine Code")</td>
                    <td>@Html.TextBox("PinCode",Model.pincode)</td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <input type="button"style="font-size:25px;align-content:center" value="Edit" />
                    </td>
                </tr>
            </table>

}
            <br />
            <br />
            <div align="center">
                <a href ="/" align="center" style="font-size:25px;align-content:center">Back to Index</a>
            </div>
        </div>
    </body>
</html>