CRUD OPERATIONS USING LINQ TO DATATABLE IN ASP.NET MVC 4
1) Create a Table called Profile
create table profile(ID int primary key identity(1,1),
name varchar(25), Mobile varchar(20),emailid varchar(20),doj datetime default getdate())
2) Create a Equivalent class for this Profile Table in Model folder
public class Profile
{
public int ID{get;set;}
public String Name{get;set;}
public String Mobile{get;set;}
public String EmailID{get;set;}
public DateTime? doj{get;set;}
}
3) Add a controller
right click on Controller Folder->Click on Add Controller name it as ProfileController
4) Add a SQL Connection in Profile Controller class
SqlConnection conn = new SqlConnection("server=.;database=pdb;trusted_connection=yes;");
and also add System.Data;
System.Data.SqlClient Namespaces
5) Index method -> add View
select Model class as Profile, Scaffolding as List
add following code in Index Method.
public ActionResult Index()
{
var list = (from p in GetProfileData().AsEnumerable()
select new Profile
{
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).ToList<Profile>();
return View(list);
}
//This method
//1 opens db connection
//2Queries the data
//returns table of records/record.
private DataTable GetProfileData(int id=-1)
{
conn.Open();
string cmdText = "select * from profile ";
if(id!=-1)
cmdText = cmdText+" where id="+id;
SqlDataAdapter ad = new SqlDataAdapter(cmdText,conn);
DataTable profileTable = new DataTable();
ad.Fill(profileTable);
return profileTable;
}
5) In Create method
add View select Model class as Profile, Scaffolding as Create
2 Create methods will be created
1. Create will allow user to input data
2. To Post user entered data to server
public ActionResult Create()
{
return View();
}
6) in Create(FormCollection) method
Change from Create(FormCollection) to Create(Profile p) and then
add following code.
[HttpPost]
public ActionResult Create(Profile p)//FormCollection collection)
{
try
{
conn.Open();
String cmdText = "INSERT INTO PROFILE(NAME,MOBILE,DOJ,EMAILID) VALUES('" + p.Name +
"','" + p.Mobile + "','" + p.doj + "','" + p.EmailID + "');";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return RedirectToAction("Index");
}
catch
{
return View();
}
}
7. Add Edit View
right click on Edit Method View Select Model class as Profile, Scaffolding as Edit
public ViewResult Edit(int id)
{
DataTable profileTable = GetProfileData(id);
Profile profile = (from p in profileTable.AsEnumerable()
select new Profile() {
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).FirstOrDefault();
return View(profile);
}
//Once user updates the record clicks on Save button, the information will be saved to database and then return to Index Page, which again queries all records from the database.
//
// POST: /Profile/Edit/5
[HttpPost]
public ActionResult Edit(int id, Profile p)
{
try
{
// TODO: Add update logic here
conn.Open();
String cmdText = "Update PROFILE set NAME='" + p.Name + "'" +
", MOBILE='" + p.Mobile + "'" +
",DOJ='" + p.doj + "'" +
",EMAILID='" + p.EmailID + "'" +
"where ID=" + p.ID;
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return RedirectToAction("Index");
}
catch
{
return View();
}
}
7. Add Details View
right click on Details Method View Select Model class as Profile, Scaffolding as Details
public ActionResult Details(int id)
{
DataTable profileTable = GetProfileData(id);
//conn.Open();
//SqlDataAdapter ad = new SqlDataAdapter("select * from profile where ID=" + id, conn);
//DataTable profileTable = new DataTable();
//ad.Fill(profileTable);
Profile profile = (from p in profileTable.AsEnumerable()
select new Profile()
{
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).FirstOrDefault();
return View(profile);
}
8. Add Delete View
right click on Delete Method View Select Model class as Profile, Scaffolding as Delete
2 Delete Methods will be displayed
1. Delete method displays record details
2.Second Delete method Posts delete record details
public ActionResult Delete(int id)
{
DataTable profileTable = GetProfileData(id);
Profile profile = (from p in profileTable.AsEnumerable()
select new Profile()
{
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).FirstOrDefault();
return View(profile);
}
//Change FormCollection to Profile object
[HttpPost]
public ActionResult Delete(int id, Profile p)//FormCollection collection
{
try
{
// TODO: Add delete logic here
conn.Open();
String cmdText = "delete from PROFILE " +
"where ID=" + id;
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return RedirectToAction("Index");
}
catch
{
return View();
}
}
Here is the Complete code of Profile 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 MvcApplication1.Models;
namespace MvcApplication1.Controllers
{
public class ProfileController : Controller
{
SqlConnection conn = new SqlConnection("server=.;database=pdb;trusted_connection=yes;");
//
// GET: /Profile/
public ActionResult Index()
{
var list = (from p in GetProfileData().AsEnumerable()
select new Profile
{
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).ToList<Profile>();
return View(list);
}
private DataTable GetProfileData(int id=-1)
{
conn.Open();
string cmdText = "select * from profile ";
if(id!=-1)
cmdText = cmdText+" where id="+id;
SqlDataAdapter ad = new SqlDataAdapter(cmdText,conn);
DataTable profileTable = new DataTable();
ad.Fill(profileTable);
return profileTable;
}
//
// GET: /Profile/Details/5
public ActionResult Details(int id)
{
DataTable profileTable = GetProfileData(id);
//conn.Open();
//SqlDataAdapter ad = new SqlDataAdapter("select * from profile where ID=" + id, conn);
//DataTable profileTable = new DataTable();
//ad.Fill(profileTable);
Profile profile = (from p in profileTable.AsEnumerable()
select new Profile()
{
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).FirstOrDefault();
return View(profile);
}
//
// GET: /Profile/Create
public ActionResult Create()
{
return View();
}
//
// POST: /Profile/Create
[HttpPost]
public ActionResult Create(Profile p)//FormCollection collection)
{
try
{
//Profile p = new Profile();
//p.Name=collection["Name"];
//p.Mobile = collection["Mobile"];
//p.doj=DateTime.Parse(collection["doj"]);
//p.EmailID=collection["EmailID"];
conn.Open();
String cmdText = "INSERT INTO PROFILE(NAME,MOBILE,DOJ,EMAILID) VALUES('" + p.Name +
"','" + p.Mobile + "','" + p.doj + "','" + p.EmailID + "');";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return RedirectToAction("Index");
}
catch
{
return View();
}
}
//
// GET: /Profile/Edit/5
public ViewResult Edit(int id)
{
DataTable profileTable = GetProfileData(id);
Profile profile = (from p in profileTable.AsEnumerable()
select new Profile() {
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).FirstOrDefault();
return View(profile);
}
//
// POST: /Profile/Edit/5
[HttpPost]
public ActionResult Edit(int id, Profile p)
{
try
{
// TODO: Add update logic here
conn.Open();
String cmdText = "Update PROFILE set NAME='" + p.Name + "'" +
", MOBILE='" + p.Mobile + "'" +
",DOJ='" + p.doj + "'" +
",EMAILID='" + p.EmailID + "'" +
"where ID=" + p.ID;
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return RedirectToAction("Index");
}
catch
{
return View();
}
}
//
// GET: /Profile/Delete/5
public ActionResult Delete(int id)
{
DataTable profileTable = GetProfileData(id);
Profile profile = (from p in profileTable.AsEnumerable()
select new Profile()
{
ID = p.Field<int>("ID"),
Name = p.Field<String>("Name"),
Mobile = p.Field<String>("Mobile"),
EmailID = p.Field<String>("EmailID"),
doj = p.Field<DateTime?>("DOJ"),
}).FirstOrDefault();
return View(profile);
}
//
// POST: /Profile/Delete/5
[HttpPost]
public ActionResult Delete(int id, Profile p)
{
try
{
// TODO: Add delete logic here
conn.Open();
String cmdText = "delete from PROFILE " +
"where ID=" + id;
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
return RedirectToAction("Index");
}
catch
{
return View();
}
}
}
}
Profile Model class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MvcApplication1.Models
{
public class Profile
{
public int ID { get; set; }
public String Name { get; set; }
public String Mobile { get; set; }
public String EmailID { get; set; }
public DateTime? doj { get; set; }
}
}
That is the beauty of ASP.NET MVC 4, HAPPY CODING....
No comments:
Post a Comment