Configuration:
For this particular application, I have used the below configuration:
1. Visual Studio 2015
2. SQL Server 2008
In order to keep my hand clean, I have used a simple table to do the CRUD operation.
Step 1:
Execute the below script on your DB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblStudent](
[student_id] [int] IDENTITY(1,1) NOT NULL,
[student_name] [varchar](50) NOT NULL,
[stduent_age] [int] NOT NULL,
[student_gender] [varchar](6) NOT NULL,
CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED
(
[student_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Step 2:
Create an “Empty” ASP.Net.MVC 4 application in Visual Studio 2015.
Step 3:
Add an “Empty” Controller by right clicking on the “Controller” folder select “Add” then select “Controller..”. In the popup select “MVC 5 Controller” then click “Add”. In the next popup you should give the name of the as “CRUDController”.
After add the controller, you may noticed as per the “convention” in ASP.Net MVC under the “Views” folder, a new folder named “CRUD” also created.
Step 4:
Our DB access code are going to be placed inside the “Models” folder. Model is just a “Class” file. So we are going to create a Model class for our purpose as below:
1. Right click on the “Model” folder. In the context menu select “Add” then choose “New item..”.
2. In the popup, select “Code” then choose “Class” and name the class file as “CRUDModel” then click “Add”. That’s all!
Step 5:
Till now we created classes for “Controller” and “Model”. We didn’t create any views till now. In this step we are going to create a view, which is going to act as a “home” page for our application. In order to create the view:
1. Right click on the “CRUD” folder under the “Views” folder in the context menu select “Add” then choose “View..”.
2. In the popup, give “View Name” and uncheck the “Use a layout page” checkbox. Finally click “Add” button.
Step 6:
We don’t have a controller named “Default”, which is specified in the “RouteConfig.cs” file. We need to change the controller’s name as “CRUD”, in the default route values.
Route.config:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
namespace MVCWithADO
{
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "CRUD", action = "Index", id = UrlParameter.Optional }
);
}
}
}
After the above change, just press F5 in visual studio, to verify that our application works fine without any error.
Step 7:
In order to achieve the complete CRUD operation, I’m going to add number of views as described in the Step 5. Here is the complete list of views and it’s purpose.
| View | Purpose |
| Home.cshtml | This is the default view. Loaded when the application launched. Will display all the records in the table |
| Create.cshtml | Displays control’s to insert the record. Will be rendered when the “Add New Record” button clicked on the “Home.cshtml” view. |
| Edit.cshtml | Displays control’s to edit the record. Will be rendered when the “Edit” button clicked on the “Home.cshtml” view. |
Step 8:
The model class, contains the all the “Data Access” logic. In other words, it will interact with Database and give it back to “View” thru “Controller”.
using System.Data;
using System.Data.SqlClient;
namespace MVCWithADO.Models
{
public class CRUDModel
{
///
/// Get all records from the DB
///
/// Datatable
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;
}
///
/// Get student detail by Student id
///
///
///
/// Update the student details
///
///
///
///
///
///
/// Insert Student record into DB
///
///
///
///
///
/// Delete student based on ID
///
///
///
Controller:
Controller
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
{
///
/// First Action method called when page loads
/// Fetch all the rows from DB and display it
///
/// Home View
public ActionResult Index()
{
CRUDModel model = new CRUDModel();
DataTable dt = model.GetAllStudents();
return View("Home",dt);
}
///
/// Action method, called when the "Add New Record" link clicked
///
/// Create View
public ActionResult Insert()
{
return View("Create");
}
///
/// Action method, called when the user hit "Submit" button
///
/// Form Collection Object
/// Used to differentiate between "submit" and "cancel"
///
/// Action method called when the user click "Edit" Link
///
/// Student ID
/// Edit View
public ActionResult Edit(int StudentID)
{
CRUDModel model = new CRUDModel();
DataTable dt = model.GetStudentByID(StudentID);
return View("Edit", dt);
}
///
/// Actin method, called when user update the record or cancel the update.
///
/// Form Collection
/// Denotes the action
/// Home view
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");
}
}
///
/// Action method called when the "Delete" link clicked
///
/// Stutend ID to edit
/// Home view
public ActionResult Delete(int StudentID)
{
CRUDModel model = new CRUDModel();
model.DeleteStudent(StudentID);
return RedirectToAction("Index");
}
}
}
Views:
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>
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>
<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>
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>
