How to use Crud Operations in MVC4 with Ms-Access
In this Article, I am going to explain about how to use CRUD Operations in MVC4 with Pure Ms-Access. I have implemented CRUD Operations (Create,Retrieve,Update,Delete) and explain server side code for MVC4 With Pure Ms-Access . I have post source which is given below.
How to List the Records from Database using MVC4 With Pure Ms-Access
In the Index method by using the Ms-Access it is fetching the data.
public ActionResult Index()
{
string mycon = @"PROVIDER=Microsoft.JET.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("\\Temp.mdb");
DataTable Dt = new DataTable();
string Query = "SELECT * FROM Table1";
using (OleDbConnection con = new OleDbConnection(mycon))
{
using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM Table1",con))
{
OleDbDataAdapter Da = new OleDbDataAdapter(cmd);
Da.Fill(Dt);
}
}
foreach (DataRow dr in Dt.Rows)
{
lstemp.Add(new Employee() { EmpId = int.Parse(dr[0].ToString()), EmpName = dr[1].ToString(), EmpNo = dr[2].ToString() });
}
return View(lstemp);
}How to Add Records using Ms-Access Database in MVC4 With Pure Ms-Access
There are two types of methods HttpPost and HttpGet Post method is used to save the record by inserting the values. Get method is used to show the entry form design
[HttpPost]
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(FormCollection frm1)
{
Employee emp = new Employee();
emp.EmpId = Convert.ToInt32(frm1["EmpId"]);
emp.EmpName = frm1["EmpName"];
emp.EmpNo = frm1["EmpNo"];
string mycon = @"PROVIDER=Microsoft.JET.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("\\Temp.mdb");
OleDbConnection con = new OleDbConnection(mycon);
con.Open();
OleDbCommand command = new OleDbCommand("Insert into Table1 values(@EmpId,@EmpNo,@EmpName)", con);
command.CommandType = CommandType.Text;
command.Parameters.Add("@EmpId", emp.EmpId);
command.Parameters.Add("@Empno", emp.EmpNo);
command.Parameters.Add("@EmpName",emp.EmpName);
int rows = command.ExecuteNonQuery();
return View();
}How to Edit Records using Ms-Access Database in MVC4 With Pure Ms-Access
There are two types of methods HttpPost and HttpGet Post method is used to edit the record which is inserted.Get method is used to show the entry form design while the record is selected
[HttpGet]
public ActionResult Edit(string id)
{
string mycon = @"PROVIDER=Microsoft.JET.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("\\Temp.mdb");
DataTable Dt = new DataTable();
string Query = "SELECT * FROM Table1";
using (OleDbConnection con = new OleDbConnection(mycon))
{
using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM Table1 Where EmpId=" + id, con))
{
OleDbDataAdapter Da = new OleDbDataAdapter(cmd);
Da.Fill(Dt);
}
}
Employee emp=new Employee();
foreach (DataRow dr in Dt.Rows)
{
emp.EmpId=int.Parse(dr[0].ToString());
emp.EmpName=dr[1].ToString();
emp.EmpNo = dr[2].ToString();
}
return View(emp);
}
[HttpPost]
public ActionResult Edit(FormCollection frm1)
{
string mycon = @"PROVIDER=Microsoft.JET.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("\\Temp.mdb");
OleDbConnection con = new OleDbConnection(mycon);
con.Open();
OleDbCommand command = new OleDbCommand("Update Table1 set EmpNo='" + frm1["EmpNo"] + "',EmpName='" + frm1["EmpName"] + "' where EmpId=" + Convert.ToInt32(frm1["EmpId"]), con);
command.CommandType = CommandType.Text;
int rows = command.ExecuteNonQuery();
return View();
}How to Delete Records using Ms-Access Database in MVC4 With Pure Ms-Access
There are two types of methods HttpPost and HttpGet Post method is used to delete the record.Get method is used to show the entry form design while the record is selected
[HttpGet]
public ActionResult Delete(string id)
{
string mycon = @"PROVIDER=Microsoft.JET.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("\\Temp.mdb");
OleDbConnection con = new OleDbConnection(mycon);
con.Open();
OleDbCommand command = new OleDbCommand("Delete from Table1 where EmpId=" + id, con);
command.CommandType = CommandType.Text;
int rows = command.ExecuteNonQuery();
return View();
}
[HttpPost]
public ActionResult Delete()
{
return View();
}Razor View
This is View for Create The Records
Create View
<fieldset>
<legend>Employee</legend>
<div class="editor-label">
@Html.LabelFor(model => model.EmpId)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmpId)
@Html.ValidationMessageFor(model => model.EmpId)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.EmpNo)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmpNo)
@Html.ValidationMessageFor(model => model.EmpNo)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.EmpName)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmpName)
@Html.ValidationMessageFor(model => model.EmpName)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
List View
<table>
<tr>
<th>
<%: Html.DisplayNameFor(model => model.EmpId) %>
</th>
<th>
<%: Html.DisplayNameFor(model => model.EmpNo) %>
</th>
<th>
<%: Html.DisplayNameFor(model => model.EmpName) %>
</th>
<th></th>
</tr>
<% foreach (var item in Model) { %>
<tr>
<td>
<%: Html.DisplayFor(modelItem => item.EmpId) %>
</td>
<td>
<%: Html.DisplayFor(modelItem => item.EmpNo) %>
</td>
<td>
<%: Html.DisplayFor(modelItem => item.EmpName) %>
</td>
<td>
<%: Html.ActionLink("Edit", "Edit", new { id=item.EmpId }) %> |
<%: Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) %> |
<%: Html.ActionLink("Delete", "Delete", new { id=item.EmpId }) %>
</td>
</tr>
<% } %>
</table>
This is View for Edit The Records
Edit View
<fieldset>
<legend>Employee</legend>
<div class="editor-label">
@Html.LabelFor(model => model.EmpId)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmpId)
@Html.ValidationMessageFor(model => model.EmpId)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.EmpNo)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmpNo)
@Html.ValidationMessageFor(model => model.EmpNo)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.EmpName)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmpName)
@Html.ValidationMessageFor(model => model.EmpName)
</div>
<p>
<input type="submit" value="Save" />
</p>
</fieldset>This is View for Delete The Records
Delete View
<fieldset>
<legend>Employee</legend>
<div class="display-label">
@Html.DisplayNameFor(model => model.EmpId)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.EmpId)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.EmpNo)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.EmpNo)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.EmpName)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.EmpName)
</div>
</fieldset>