CRUD (Create, Read, Update, Delete) in asp.net MVC 5 using ADO.Net In MVC a default approach for database operations in Entity framework. but we can also use an ADO.NET for CRUD operation in this article we will learn how to do CRUD with ADO.NET in MVC. Create a new web project using visual studio (created in 2015)
Add connection to your database in web config1
2
3 | <connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=NORTH\SQLSERVER2008;Initial Catalog=BlogDb;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55 | CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[FatherName] [varchar](100) NULL,
[JobTitle] [varchar](50) NULL,
[Address] [varchar](150) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] 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
/****** Object: StoredProcedure [dbo].[Update_Employee] Script Date: 12/01/2018 13:52:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[Update_Employee]
@employeeid int,
@name varchar(100),
@fname varchar(100),
@jobTitle varchar(50),
@addr varchar(150)
as
update Employee set Name=@name,FatherName=@fname,JobTitle=@jobTitle,[Address]=@addr
where EmployeeID=@employeeid
GO
/****** Object: StoredProcedure [dbo].[Insert_Employee] Script Date: 12/01/2018 13:52:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[Insert_Employee]
@name varchar(100),
@fname varchar(100),
@jobTitle varchar(50),
@addr varchar(150)
as
declare @employeeid int
set @employeeid=(select ISNULL(max(EmployeeID),0)+1 from Employee)
INSERT INTO [dbo].[Employee]
([EmployeeID]
,[Name]
,[FatherName]
,[JobTitle]
,[Address])
VALUES
(@employeeid
,@name
,@fname
,@jobTitle
,@addr)
GO
|
Add class Extensions to your project (DAL Folder) 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84 | using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using System.Web;
public static class Extensions
{
/// <summary>
/// Convert The DataTable object To Target List of Objects
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="table"></param>
/// <returns></returns>
public static IList<T> ToList<T>(this DataTable table) where T : new()
{
var props = typeof(T).GetProperties().ToList();
var result = new List<T>();
Parallel.ForEach(table.AsEnumerable(), row =>
result.Add(DataRowToObject<T>(row, props)));
return result;
}
private static T DataRowToObject<T>(DataRow row, IList<PropertyInfo> props) where T : new()
{
T item = new T();
foreach (var prop in props)
{
if (row.Table.Columns.Contains(prop.Name))
{
Type proptype = prop.PropertyType;
var targetType = IsNullableType(prop.PropertyType) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType;
var propertyVal = row[prop.Name];
propertyVal = Convert.ChangeType(propertyVal, targetType);
prop.SetValue(item, propertyVal, null);
}
}
return item;
}
private static bool IsNullableType(Type type)
{
return type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>));
}
public static bool HasProperty(this object obj, string propertyName)
{
return obj.GetType().GetProperty(propertyName) != null;
}
/// <summary>
/// Cast the object to target
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="myobj"></param>
/// <returns>Target Object</returns>
public static T Cast<T>(this object myobj) where T : new()
{
Type objectType = myobj.GetType();
Type target = typeof(T);
var x = Activator.CreateInstance(target, false);
var z = from source in objectType.GetMembers().ToList()
where source.MemberType == MemberTypes.Property
select source;
var d = from source in target.GetMembers().ToList()
where source.MemberType == MemberTypes.Property
select source;
List<MemberInfo> members = d.Where(memberInfo => d.Select(c => c.Name)
.ToList().Contains(memberInfo.Name)).ToList();
PropertyInfo propertyInfo;
object value;
foreach (var memberInfo in members)
{
propertyInfo = typeof(T).GetProperty(memberInfo.Name);
if (myobj.GetType().GetProperty(memberInfo.Name) != null)
{
value = myobj.GetType().GetProperty(memberInfo.Name).GetValue(myobj, null);
propertyInfo.SetValue(x, value, null);
}
}
return (T)x;
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 | using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace MVC_CRUD_ADO.DAL
{
public abstract class MyConnection : IDisposable
{
protected SqlConnection _connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
public void Dispose()
{
if (_connection.State == System.Data.ConnectionState.Open)
_connection.Close();
_connection.Dispose();
}
protected void OpenConnection()
{
if (_connection.State == System.Data.ConnectionState.Closed)
_connection.Open();
}
protected void CloseConnection()
{
if (_connection.State == System.Data.ConnectionState.Open)
_connection.Close();
}
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 | using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MVC_CRUD_ADO.DAL
{
public interface IDbCommon<TEntity> where TEntity : class
{
bool Add(TEntity entity);
bool Update(TEntity entity);
bool Delete(int key);
TEntity SelectOne(int key);
IList<TEntity> SelectAll();
IList<TEntity> SelectAll_Paging(int pageNo, int No_Of_records);
}
}
|
All the properties in a class should be checked with sql server table. (datatype and name should be same). the TEmployee structure in my case is the following.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 | using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace MVC_CRUD_ADO.Models
{
public class TEmployee
{
[Display(Name = "Employee ID")]
public int EmployeeID { get; set; }
[Required(ErrorMessage ="Please enter employee name")]
[Display(Name ="Employee Name")]
[MaxLength(100,ErrorMessage ="Length should not be more than 100 characters")]
public string Name { get; set; }
[Required(ErrorMessage = "Please enter father name")]
[Display(Name = "Father Name")]
[MaxLength(100, ErrorMessage = "Length should not be more than 100 characters")]
public string FatherName { get; set; }
[Display(Name = "Job Title")]
public string JobTitle { get; set; }
[Display(Name = "Address")]
public string Address { get; set; }
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98 | using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MVC_CRUD_ADO.Models;
using System.Data.SqlClient;
using System.Data;
namespace MVC_CRUD_ADO.DAL
{
public class EmployeeDAL : MyConnection, IDbCommon<TEmployee>
{
public bool Add(TEmployee entity)
{
using (SqlCommand _com = new SqlCommand())
{
_com.CommandText = "Insert_Employee";
_com.Connection = _connection;
_com.CommandType = CommandType.StoredProcedure;
_com.Parameters.AddWithValue("@name", entity.Name);
_com.Parameters.AddWithValue("@fname", entity.FatherName);
_com.Parameters.AddWithValue("@jobTitle", entity.JobTitle);
_com.Parameters.AddWithValue("@addr", entity.Address);
OpenConnection();
_com.ExecuteNonQuery();
CloseConnection();
return true;
}
}
public bool Delete(int key)
{
using (SqlCommand _com = new SqlCommand())
{
_com.CommandText = "delete from Employee where EmployeeID=" + key;
_com.Connection = _connection;
OpenConnection();
_com.ExecuteNonQuery();
CloseConnection();
return true;
}
}
public IList<TEmployee> SelectAll()
{
using (SqlDataAdapter da = new SqlDataAdapter("select * from Employee",_connection))
{
DataTable dt = new DataTable();
da.Fill(dt);
IList<TEmployee> lst = dt.ToList<TEmployee>();
return lst;
}
}
public IList<TEmployee> SelectAll_Paging(int pageNo, int No_Of_records)
{
throw new NotImplementedException();
}
public TEmployee SelectOne(int key)
{
using (SqlDataAdapter da = new SqlDataAdapter("select * from Employee where EmployeeID=" + key, _connection))
{
DataTable dt = new DataTable();
da.Fill(dt);
TEmployee emp = new TEmployee();
if (dt.Rows.Count>0)
{
emp = dt.ToList<TEmployee>().FirstOrDefault();
}
return emp;
}
}
public bool Update(TEmployee entity)
{
using (SqlCommand _com = new SqlCommand())
{
_com.CommandText = "Update_Employee";
_com.Connection = _connection;
_com.CommandType = CommandType.StoredProcedure;
_com.Parameters.AddWithValue("@employeeid", entity.EmployeeID);
_com.Parameters.AddWithValue("@name", entity.Name);
_com.Parameters.AddWithValue("@fname", entity.FatherName);
_com.Parameters.AddWithValue("@jobTitle", entity.JobTitle);
_com.Parameters.AddWithValue("@addr", entity.Address);
OpenConnection();
_com.ExecuteNonQuery();
CloseConnection();
return true;
}
}
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73 | using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVC_CRUD_ADO.DAL;
using MVC_CRUD_ADO.Models;
namespace MVC_CRUD_ADO.Controllers
{
public class EmployeeController : Controller
{
// GET: Employee
public ActionResult Index()
{
using (EmployeeDAL emp = new EmployeeDAL())
{
IList<TEmployee> employees = emp.SelectAll();
return View(employees);
}
}
public ActionResult Create()
{
TEmployee emp = new TEmployee();
return View(emp);
}
[HttpPost]
public ActionResult Create(TEmployee employee)
{
using (EmployeeDAL emp = new EmployeeDAL())
{
emp.Add(employee);
return RedirectToAction("Index");
}
}
public ActionResult Edit(int id)
{
using (EmployeeDAL emp = new EmployeeDAL())
{
TEmployee singleEmp = emp.SelectOne(id);
return View(singleEmp);
}
}
[HttpPost]
public ActionResult Edit(TEmployee employee)
{
using (EmployeeDAL empDal = new EmployeeDAL())
{
empDal.Update(employee);
return RedirectToAction("Index");
}
}
public ActionResult DeleteConfirm(int id)
{
using (EmployeeDAL empDal = new EmployeeDAL())
{
TEmployee emp= empDal.SelectOne(id);
return View(emp);
}
}
public ActionResult Delete(int EmployeeID)
{
using (EmployeeDAL empDal = new EmployeeDAL())
{
empDal.Delete(EmployeeID);
return RedirectToAction("Index");
}
}
}
}
|
Index view should be look like this
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56 | @model IEnumerable<MVC_CRUD_ADO.Models.TEmployee>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.EmployeeID)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.FatherName)
</th>
<th>
@Html.DisplayNameFor(model => model.JobTitle)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.EmployeeID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.FatherName)
</td>
<td>
@Html.DisplayFor(modelItem => item.JobTitle)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.EmployeeID }) |
@Html.ActionLink("Delete","DeleteConfirm", new { id=item.EmployeeID })
</td>
</tr>
}
</table>
|
create view should be look like this
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66 | @model MVC_CRUD_ADO.Models.TEmployee
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Employee</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FatherName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.FatherName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FatherName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.JobTitle, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.JobTitle, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.JobTitle, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
|
DeleteConfirm view should be look like this
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63 | @model MVC_CRUD_ADO.Models.TEmployee
@{
ViewBag.Title = "DeleteConfirm";
}
<h3>Are you sure you want to delete this?</h3>
<div>
<h4>TEmployee</h4>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.EmployeeID)
</dt>
<dd>
@Html.DisplayFor(model => model.EmployeeID)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd>
@Html.DisplayFor(model => model.Name)
</dd>
<dt>
@Html.DisplayNameFor(model => model.FatherName)
</dt>
<dd>
@Html.DisplayFor(model => model.FatherName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.JobTitle)
</dt>
<dd>
@Html.DisplayFor(model => model.JobTitle)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Address)
</dt>
<dd>
@Html.DisplayFor(model => model.Address)
</dd>
</dl>
@using (Html.BeginForm("Delete", "Employee", FormMethod.Post))
{
@Html.AntiForgeryToken()
@Html.HiddenFor(x=>x.EmployeeID)
<div class="form-actions no-color">
<input type="submit" value="Delete" class="btn btn-default" /> |
@Html.ActionLink("Back to List", "Index")
</div>
}
</div>
|
Edit view should be look like this
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66 | @model MVC_CRUD_ADO.Models.TEmployee
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>TEmployee</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model=>model.EmployeeID)
<div class="form-group">
@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FatherName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.FatherName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FatherName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.JobTitle, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.JobTitle, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.JobTitle, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
|
1 Comments
Awesome post. You Post is very informative. Thanks for Sharing.
ReplyDeleteASP.NET Training Institutes in Noida
Post a Comment