CRUD in asp.net MVC using ADO.NET

byZafar Ali Khan 1 Comments
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 config
1
2
3
<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=NORTH\SQLSERVER2008;Initial Catalog=BlogDb;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
Run the following script in database. for creating table and stored procedures 
 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
Folder structure in the project



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;
        }
 
    }


Add class MyConnection 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
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();
        }


    }
}


Add an Interface IDbCommon to your project (DAL folder)

 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);
    }
}


Add class TEmployee to your project (Models folder)
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; }
    }
}


Add class EmployeeDAL 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
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;
            }

        }
    }
}

Add controller EmployeeController to your project
 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");
            }
        }

    }
}


Add view to index action method
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>

Add view to create action method
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")
}

Add view to DeleteConfirm action method
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>
Add view to Edit action method
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")
}
Tagged In:
Previous Post Next Post

1 Comments

Post a Comment