Manually CURD operation Of User details in MVC using ADO .Net and SQL

Introduction 

In this article, we will do CRUD operation using normal ADO .Net with an inline query in MVC. In this article, I am not using the entity framework. So this article better for beginners on how to create step by step to perform CRUD operation. So now let us see step by step.

Step 1:- Create database and tables in SQL

->Open Sql Server
->Create Database any name(Ex:MVCDB)
->Create a table any name(Ex: Registration)
see below



Step 2:-Create a project using visual studio

->Open the visual studio,
->Go to file,
->Go to New and select the New Project,
->Select the MVC template,
->Click Ok

Step 3:- Create a model class

->Go to Model,
->Right Click of the model,
->Select Add option,
->And Add the class and give the name(Ex: User.cs)

Note: Add the namespace given all classes

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


And Write the following code

using System;

using System.Collections.Generic;

using System.Linq;

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: Detais.cs)

and write code given below

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

namespace mvcRegistration.Models

{
public class Details

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

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 add a class(with same process)
->And write a name(Ex: DataContext.cs)

See the below

using System;

using System.Collections.Generic;

using System.Linq;

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=12345";

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 4:- Create a controller 

->Go to Controller,
->Right click of the controller option
->Select Add option and again Select Controller Option,
->And Give the Any name(Ex: RegController)
-> Select Empty controller,
->Click Ok

and add the namespace(According to my project)

using mvcRegistration.Models;

and now write below code

using System;

using System.Collections.Generic;

using System.Linq;

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 5:- Create a view page

->Right click of index() method in RegController,
->select AddView,
->and automatic display the index,
->and click ok
then one view page created "Index.cshtml" and then after writing the code see below

Note:- Add below namspace

@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:


Step 6:- Create a Details view page

@{
  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 10px maroon; 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:


Step 7:- Create an Add user view page

->Go to create() method in controller and create cs.htmlpage and wite below code

@{
  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 10px 10px 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 10px maroon; 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"><b align="center">Back to Index</b></a>
</div>
</body>
</html>


see the output:



Step 8:- Create a Delete user view page

->same process of the previous page and write the code as below

@{
  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 10px darkgreen; 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> &nbsp;&nbsp;&nbsp;

<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:

Step 9:- Create an Edit user view page

 write the below code

@{
  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 7px 7px 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 10px darkgreen; 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>