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
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 }
);
}
}
}
| 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. |
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
///
///
///
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");
}
}
}
<!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>
