Asp .Net MVC with Ado .Net

Though using an ORM as the Model with Asp .net MVC is the preferred approach to follow,  it is possible to use ADO .Net along with Asp .Net MVC.

Here I am creating a class in Models  folder which contains properties corresponding to the table and  also the CRUD code.

public class ContactGroups
{
SqlConnection cnn;


public int GroupId { get; set; }
public string GroupName { get; set; }

public void InsertGroup(string GroupName)
{
 using (cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("AppCnn")))
 {
     cnn.Open();
     SqlCommand cmd = new SqlCommand("insert into  ContactGroups values ('" + GroupName + "')", cnn);
     cmd.ExecuteNonQuery();
 }
}
public List<ContactGroups> GetGroups()
{
 using (cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("AppCnn")))
 {
  cnn.Open();
  SqlCommand cmd = new SqlCommand("select * from ContactGroups", cnn);
  SqlDataReader dr;
  dr = cmd.ExecuteReader();
  List<ContactGroups> Cg = new List<ContactGroups>();
  while (dr.Read())
  {
   int intGroupId = Int32.Parse(dr["GroupId"].ToString());
   ContactGroups C = new ContactGroups
   {
    GroupId = intGroupId,
    GroupName = dr["GroupName"].ToString()
   };
   Cg.Add(C);
  }
  dr.Close();
  return Cg;
 }
}
}
}
}

HomeController.cs

using MvcAdoDotNetShalvin.Models;

namespace MvcAdoDotNetShalvin.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
  ContactGroups CG = new ContactGroups();
  ViewBag.ContactGroups = CG.GetGroups();

  return View();
}

[HttpPost]
public ActionResult Index(FormCollection coll)
{
  ContactGroups CG = new ContactGroups();
  ViewBag.ContactGroups = CG.GetGroups();
          

  CG.InsertGroup(Request.Form["txtName"]);
  return View();
}

}
}

Index.cshtml

@using MvcAdoDotNetShalvin.Models

@{
    ViewBag.Title = "Index";
    var CG = ViewBag.ContactGroups;
    
    foreach(ContactGroups C in CG)
    {
     <div>@C.GroupId &nbsp @C.GroupName  </div>   
    }
}

<form method ="post">
    <div><input type ="text" name ="txtName" /> </div>
    <div><input type ="submit" /> </div>
</form>
<h2>Index</h2>

}

Multiple Tables

USE [ContactManagement]
GO
/****** Object:  Table [dbo].[Contacts]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contacts](
    [ContactId] [int] IDENTITY(100,1) NOT NULL,
    [GroupId] [int] NULL,
    [ContactName] [varchar](40) NULL,
    [Location] [varchar](40) NULL,
PRIMARY KEY CLUSTERED 
(
    [ContactId] 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
/****** Object:  Table [dbo].[Groups]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Groups](
    [GroupId] [int] IDENTITY(1,1) NOT NULL,
    [GroupName] [varchar](40) NULL,
PRIMARY KEY CLUSTERED 
(
    [GroupId] 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
ALTER TABLE [dbo].[Contacts]  WITH CHECK ADD FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groups] ([GroupId])
GO
/****** Object:  StoredProcedure [dbo].[spAdd]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spAdd](@p1 int, @p2 int)
as
    print @p1 + @p2
GO
/****** Object:  StoredProcedure [dbo].[spAllContacts]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spAllContacts]
as
select C.ContactId, G.GroupName, C.ContactName, C.Location from Groups G, Contacts C where G.GroupId = C.GroupId
GO
/****** Object:  StoredProcedure [dbo].[spAllGroups]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spAllGroups] as
select * from Groups
GO
/****** Object:  StoredProcedure [dbo].[spGetContactById]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spGetContactById](@id int)
as
select C.ContactId, G.GroupName, C.ContactName, C.Location 
from Groups G, Contacts C where G.GroupId = C.GroupId 
and C.ContactId = @id
GO
/****** Object:  StoredProcedure [dbo].[spHello]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spHello]
as 
print 'Hello Stored Procedures'
GO
/****** Object:  StoredProcedure [dbo].[spHelloName]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spHelloName] (@Name varchar(20))
as 
print 'Hello ' + @Name
GO
/****** Object:  StoredProcedure [dbo].[spInsertGroup]    Script Date: 4/5/2020 6:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spInsertGroup] (@GroupName varchar(40))
as
insert into Groups values (@GroupName)
GO

Models

using System.ComponentModel.DataAnnotations;

namespace DomainModel
{
    public class Group
    {
        public int GroupId { get; set; }
        [Display(Name = "Group Name")]
        public string GroupName { get; set; }
    }
}

using System.ComponentModel.DataAnnotations;

namespace DomainModel
{
    public class Contact
    {
        public int ContactId { get; set; }
        [Display(Name = "Group Name")]
        public int GroupId { get; set; }

        [Display(Name = "Contact Name")]
        public string ContactName { get; set; }
        public string Location { get; set; }
    }
}

using System.ComponentModel.DataAnnotations;

namespace DomainModel.ViewModels
{
    public class ContactViewModel
    {
        public int ContactId { get; set; }
        [Display(Name = "Group Name")]
        public string GroupName { get; set; }
        [Display(Name = "Contact Name")]
        public string ContactName { get; set; }
        public string Location { get; set; }
    }
}

GroupsRepository

using DomainModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace RepositoryLayer
{
    public class GroupsRepository
    {
        SqlConnection cnn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ContactManagement;Integrated Security=True");
        SqlCommand cmd = null;
        public List<Group> GetGroups()
        {
            cnn.Open();

            cmd = new SqlCommand("spAllGroups", cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Group> GroupsList = new List<Group>();

            while(dr.Read())
            {
                int GId = Int32.Parse(dr["GroupId"].ToString());
                Group g = new Group
                {
                    GroupId = GId,
                    GroupName = dr["GroupName"].ToString()
                };
                GroupsList.Add(g);
            }

            cnn.Close();
            return GroupsList;
        }

        public void Insert(Group g)
        {
            cnn.Open();
            cmd = new SqlCommand("spAllGroups", cnn);
            cmd.Parameters.AddWithValue("@GroupName", g.GroupName);
            cmd.ExecuteNonQuery();

        }
    }
}

ContactsRepository

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

using DomainModel;
using DomainModel.ViewModels;

namespace RepositoryLayer
{
    public class ContactsRepository
    {
        SqlConnection cnn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ContactManagement;Integrated Security=True");
        SqlCommand cmd = null;
        public List<ContactViewModel> GetContacts()
        {
            cnn.Open();
             cmd = new SqlCommand("spAllContacts", cnn);
            
            List<ContactViewModel> ContactsList = new List<ContactViewModel>();
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    int CId = Int32.Parse(dr["ContactId"].ToString());
                    ContactViewModel cvm = new ContactViewModel
                    {
                        ContactId = CId,

                        GroupName = dr["GroupName"].ToString(),
                        ContactName = dr["ContactName"].ToString(),
                        Location = dr["Location"].ToString()
                    };
                    ContactsList.Add(cvm);
                }
                
             
            }
            return ContactsList;
        }

        public ContactViewModel GetContactById(int id)
        {
            cnn.Open();
            string strSql = "spGetContactById " + id;

            cmd = new SqlCommand(strSql, cnn);
           // cmd.Parameters.AddWithValue("@id", id);
           // cmd.CommandType = System.Data.CommandType.StoredProcedure;


            ContactViewModel Contact = null;
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                 int CId = Int32.Parse(dr["ContactId"].ToString());
                Contact = new ContactViewModel
                {
                     ContactId = CId,

                    GroupName = dr["GroupName"].ToString(),
                    ContactName = dr["ContactName"].ToString(),
                    Location = dr["Location"].ToString()
                };
            }
             return Contact;
        }

        public void InsertContact(Contact c)
        {
            cnn.Open();

            cmd = new SqlCommand("insert into Contacts values (@GroupId, @ContactName, @Location)", cnn);
            cmd.Parameters.AddWithValue("@GroupId", c.GroupId);
            cmd.Parameters.AddWithValue("@ContactName", c.ContactName);
            cmd.Parameters.AddWithValue("@Location", c.Location);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}