This article is all about ASP.NET MVC CRUD operations
using ADO.NET
Here we will learn how to implement crud operations in
asp.net mvc using ado.net
Step
1
Create a Table Dept
Step 2 Create a Asp.net MVC Application Following these Images
Step 3 Create Datacontext class and Entity class in Model folder
Step 4 Create Entity class and give name is Dept.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MVC_Crud_Operation.Models
{
public class Dept
{
public int Deptno { get; set; }
public string Dname { get; set; }
public string Loc { get; set; }
}
}
Step 4 Create DataContext class and give name is DataContext.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MVC_Crud_Operation.Models
{
public class DataContext
{
String constr= ConfigurationManager.ConnectionStrings["MySampleDBConnectionString"].ConnectionString;
public List<Dept> GetDepts()
{
List<Dept> deplist = new List<Dept>();
string cmdtext = "SELECT * FROM DEPT";
SqlDataAdapter da = new SqlDataAdapter(cmdtext, constr);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow item in dt.Rows)
{
Dept obj = new Dept();
obj.Deptno = (int)item["Deptno"];
obj.Dname = (string)item["Dname"];
obj.Loc = (string)item["Loc"];
deplist.Add(obj);
}
return deplist;
}
public Dept GetDept(int id)
{
Dept obj = new Dept();
string cmdtext = "SELECT * FROM DEPT WHERE DEPTNO=" + id;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(cmdtext, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if(dr.HasRows==true)
{
dr.Read();
obj.Deptno = (int)dr["Deptno"];
obj.Dname = (string)dr["Dname"];
obj.Loc = (string)dr["Loc"];
}
dr.Close();
con.Close();
return obj;
}
public void AddDept(Dept obj)
{
string cmdtext ="INSERT INTO DEPT VALUES ('"+ obj.Deptno+"',' "+obj.Dname+" ', ' "+obj.Loc+"')";
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(cmdtext, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public void EditDept(Dept obj)
{
string cmdtext = "UPDATE DEPT SET DNAME ='"+obj.Dname+"',LOC='"+obj.Loc+"' WHERE DEPTNO ='"+ obj.Deptno+"' ";
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(cmdtext,con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public void DeleteDept(int n)
{
string cmdtext = "DELETE FROM DEPT WHERE DEPTNO =" +n;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(cmdtext, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
Step 5 Create Configuration in Web.config file
<connectionStrings>
<add name="MySampleDBConnectionString" connectionString="Data Source=DESKTOP-84DUHUL\SA;Initial Catalog=MySampleDB;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.5"/>
<httpRuntime targetFramework="4.5"/>
</system.web>
Step 6 Create HomeController class in Controller folder and give name
is HomeController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVC_Crud_Operation.Models;
namespace MVC_Crud_Operation.Controllers
{
public class HomeController : Controller
{
DataContext db = new DataContext();
public ActionResult Index()
{
List<Dept> deplist = db.GetDepts();
return View(deplist);
}
public ActionResult create()
{
return View();
}
[HttpPost]
public ActionResult create(Dept obj)
{
db.AddDept(obj);
return RedirectToAction("Index");
}
public ActionResult Details(int id)
{
Dept obj = db.GetDept(id);
return View(obj);
}
public ActionResult Edit(int id)
{
Dept obj = db.GetDept(id);
return View(obj);
}
[HttpPost]
public ActionResult Edit(Dept obj)
{
db.EditDept(obj);
return RedirectToAction("Index");
}
public ActionResult Delete (int id)
{
Dept obj = db.GetDept(id);
return View(obj);
}
[HttpPost]
public ActionResult Delete(string id)
{
int n =int.Parse(id);
db.DeleteDept(n);
return RedirectToAction("index");
}
}
}
Step 7 Create View in view folder the views are following
Index.cshtml
@using MVC_Crud_Operation.Models
@model List<Dept>
<h1>Crud Operation In Asp.net MVC</h1>
<hr />
<a href="/Home/create">Create New</a><br /><br />
<table border="1" width="400">
<tr>
<th>Dept No</th>
<th>Dept Name</th>
<th>Location</th>
<th></th>
</tr>
@foreach(Dept item in Model)
{
<tr>
<td>@item.Deptno</td>
<td>@item.Dname</td>
<td>@item.Loc</td>
<td>
<a href="/Home/Details/@item.Deptno">Details</a>
<a href="/Home/Edit/@item.Deptno">Edit</a>
<a href="/Home/Delete/@item.Deptno">Delete</a>
</td>
</tr>
}
</table>
Edit.cshtml
@using MVC_Crud_Operation.Models
@model Dept
<h2> Edit Department</h2>
<hr />
@using (Html.BeginForm())
{
@Html.Label("Deptno")
@Html.TextBox("Deptno", Model.Deptno, new { @readonly=true})
<br /><br />
@Html.Label("Dname")
@Html.TextBox("Dname" ,Model.Dname)
<br /><br />
@Html.Label("Location")
@Html.TextBox("Loc" ,Model.Loc)
<input type="submit" id="sb1" name="sb1" value="Edit" />
<br />
<a href="/" >Back to index</a>
}
Details.cshtml
@using MVC_Crud_Operation.Models @model Dept <h1> Department Details</h1> <hr /> <span> Dept No :@Model.Deptno<br /> Dept No :@Model.Dname<br /> Dept No :@Model.Loc<br /> </span>
Delete.cshtml
@using MVC_Crud_Operation.Models
@model Dept
<h2> Delete Department</h2>
<hr />
<span>@Model.Deptno</span><br />
<span>@Model.Dname</span><br />
<span>@Model.Loc</span><br />
@using (Html.BeginForm())
{
<span>Do you want to delete ?</span>
<input type="submit" id="sb1" name="sb1" value="Delete" /><br />
<a href="/">Back to index</a>
}create.cshtml
<h2> Create New Dept</h2>
<hr />
@using(Html.BeginForm())
{
@Html.Label("Deptno")
@Html.TextBox("Deptno")
<br /><br />
@Html.Label("Dname")
@Html.TextBox("Dname")
<br /><br />
@Html.Label("Location")
@Html.TextBox("Loc")
<br /><br />
<input type="submit" id="sb1" name="sb1" value="Create" />
<br />
<a href="/">Back to index</a>
}
