Simple Insert And Select (CRUD) Operation Using .NET Core MVC With ADO.NET And Entity Framework Core

Introduction 

 
Hello Folks,
 
In this article, I am going to show you guys how to use ADO.NET and Entity framework core in .NET Core MVC. This way you can use either both or on way to implement your application as per your requirements. I attached the project solution which you can refer to. Also, I have shown a repository pattern to complete this project.
 
Step 1
 
I have used MS SQLServer for the database. 
  1. USE [CrudDB]  
  2. GO  
  3. /****** Object: Table [dbo].[Crud_Data] Script Date: 22-11-2020 09:33:14 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[Crud_Data](  
  9. [id] [int] IDENTITY(1,1) NOT NULL,  
  10. [Name] [varchar](50) NULL,  
  11. [City] [varchar](50) NULL,  
  12. [InsertDate] [datetime] NULL,  
  13. [FatherName] [varchar](50) NULL,  
  14. CONSTRAINT [PK_Crud_Data] PRIMARY KEY CLUSTERED  
  15. (  
  16. [id] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFFON [PRIMARY]  
  18. ON [PRIMARY]  
  19. GO  
Step-2
 
Change in the startup.cs file in the project root
  1. public void ConfigureServices(IServiceCollection services)  
  2.      {  
  3.          services.AddControllersWithViews();  
  4.          //By changing the service reference we are switching to ado.net  to entity framwork core vice versa  
  5.          //----Start  
  6.          services.AddScoped<ICrudRepository, CrudRepository>();  
  7.          //services.AddScoped<ICrudRepository, CrudContextRepository>();  
  8.          //-----End  
  9.          services.AddDbContext<DBAccessContext>(options => options.UseSqlServer(Configuration.GetConnectionString("MyKey")));  
  10.      }  
Simple Insert And Select Operation Using .NET Core MVC With ADO.NET And Entity Framework Core
 
Step 3
 
Connection string in appsettings.json:
  1. {  
  2.   "Logging": {  
  3.     "LogLevel": {  
  4.       "Default""Information",  
  5.       "Microsoft""Warning",  
  6.       "Microsoft.Hosting.Lifetime""Information"  
  7.     }  
  8.   },  
  9.   "AllowedHosts""*",  
  10.   "ConnectionStrings": {  
  11.     "MyKey""Data Source=PRO-ACQER8AM;Initial Catalog=CrudDB;Integrated Security=True;"  
  12.   }  
  13. }  
Simple Insert And Select Operation Using .NET Core MVC With ADO.NET And Entity Framework Core
 
Step 4
 
Made model to communicate with View and DB Layer.
  1. namespace TestDemo.Models  
  2. {  
  3.     [Table("Crud_Data", Schema = "dbo")]  
  4.     public class CrudModel  
  5.     {  
  6.         public int Id { getset; }  
  7.         [Required]  
  8.         public string Name { getset; }  
  9.         [Required]  
  10.         public string City { getset; }  
  11.   
  12.         public DateTime? InsertDate { getset; }  
  13.            
  14.         public string? FatherName { getset; }  
  15.     }  
  16. }  
Simple Insert And Select Operation Using .NET Core MVC With ADO.NET And Entity Framework Core
 
Step 5
 
Controller class CrudController.cs with dependency injection use with helps to lose coupling while switching from ADO.NET to Entity framework and vice-versa.
  1. public class CrudController : Controller  
  2.    {  
  3.        private readonly ICrudRepository crudRepository;  
  4.        public CrudController(ICrudRepository crudRepository)  
  5.        {  
  6.            this.crudRepository = crudRepository;  
  7.        }  
  8.        public IActionResult Index()  
  9.        {  
  10.            ViewBag.ModelList = crudRepository.GetData();  
  11.            return View();  
  12.        }  
  13.        [HttpPost]  
  14.        public IActionResult Index(CrudModel crudModel)  
  15.        {  
  16.            try  
  17.            {  
  18.                if (ModelState.IsValid)  
  19.                {  
  20.                    // ICrudRepository crudRepository = new CrudRepository();  
  21.                    var result = crudRepository.insert(new string[] { crudModel.Name, crudModel.City, System.DateTime.Now.ToString(), crudModel.FatherName });  
  22.                    ViewBag.ModelList = crudRepository.GetData();  
  23.                    if (result)  
  24.                    {  
  25.                        ViewBag.Msg = "Succeed";  
  26.                        ViewBag.alertType = "alert alert-success";  
  27.                    }  
  28.                    else  
  29.                    {  
  30.                        ViewBag.Msg = "Insertion failed";  
  31.                        ViewBag.alertType = "alert alert-danger";  
  32.                    }  
  33.   
  34.                }  
  35.            }  
  36.            catch (Exception ex)  
  37.            {  
  38.                ViewBag.Msg = "Insertion failed";  
  39.                ViewBag.alertType = "alert alert-danger";  
  40.                ModelState.AddModelError("Message", ex.Message);  
  41.            }  
  42.   
  43.            return View();  
  44.   
  45.        }  
  46.    } 
Simple Insert And Select Operation Using .NET Core MVC With ADO.NET And Entity Framework Core
 
Step 6
 
The project consists of class files for the DB layer and Repository folder and one partial view for showing the detail view.
  • We have created an interface in the repository folder to make a service and use both the classes as a service.
  • We have created the DBLayer folder and created 2 classes one consists of ado.net insert and select the method and another one for Entity framework Core DBContext.
  • In Partial View, I have injected the service directly so that you no need to pass the model to communicate with a partial view. you can check the getdata() method by debugging and uncommenting the specific section over there.
  • System.Data.sqlclient reference required to use ado.net, you can add this by using NuGet package manager.
DataAccessDB.cs
  1. public class DataAccessDB : IDataAccessDB  
  2.    {  
  3.        private readonly IConfiguration config;  
  4.        string connsString = string.Empty;  
  5.        public DataAccessDB(IConfiguration config)  
  6.        {  
  7.            this.config = config;  
  8.            connsString = config.GetConnectionString("MyKey");  
  9.        }  
  10.        public List<CrudModel> GetData()  
  11.        {  
  12.            // string connsString = config.GetConnectionString("MyKey");// "Data Source=PRO-ACQER8AM;Initial Catalog=CrudDB;Integrated Security=True;";  
  13.            List<TestDemo.Models.CrudModel> ModelList = new List<Models.CrudModel>();  
  14.            using (SqlConnection conn = new SqlConnection(connsString))  
  15.            {  
  16.                conn.Open();  
  17.                using (SqlCommand command = new SqlCommand($"select * from [dbo].[Crud_Data]", conn))  
  18.                {  
  19.                    try  
  20.                    {  
  21.                        using (var result = command.ExecuteReader())  
  22.                        {  
  23.   
  24.                            while (result.Read())  
  25.                            {  
  26.   
  27.                                ModelList.Add(  
  28.                                    new Models.CrudModel { Id = (int)result.GetValue("Id"), Name = (string)result.GetValue("Name"), City = (string)result.GetValue("City"),  FatherName = (string)result.GetValue("FatherName").ToString() });  
  29.                            }  
  30.                        }  
  31.                    }  
  32.                    catch (Exception ex)  
  33.                    {  
  34.   
  35.                    }  
  36.                    finally  
  37.                    {  
  38.                        conn.Close();  
  39.                    }  
  40.   
  41.                    return ModelList;  
  42.                }  
  43.   
  44.   
  45.            }  
  46.        }  
  47.   
  48.          
  49.   
  50.        public bool insert(string[] Param)  
  51.        {  
  52.   
  53.            //   string connsString = config.GetConnectionString("MyKey");// "Data Source=PRO-ACQER8AM;Initial Catalog=CrudDB;Integrated Security=True;";  
  54.   
  55.            using (SqlConnection conn = new SqlConnection(connsString))  
  56.            {  
  57.                conn.Open();  
  58.                using (SqlCommand command = new SqlCommand($"INSERT INTO [dbo].[Crud_Data] ([Name],[City],[InsertDate],FatherName) VALUES ('{Param[0]}','{Param[1]}',getdate(),'{Param[3]}')", conn))  
  59.                {  
  60.                    try  
  61.                    {  
  62.                        var result = command.ExecuteNonQuery();  
  63.   
  64.                        if (result > 0)  
  65.                        {  
  66.                            return true;  
  67.                        }  
  68.                    }  
  69.                    catch (Exception)  
  70.                    {  
  71.   
  72.                    }  
  73.                    finally  
  74.                    {  
  75.                        conn.Close();  
  76.                    }  
  77.                    return false;  
  78.                }  
  79.   
  80.   
  81.            }  
  82.   
  83.        }  
  84.    }  
 DBAccessContext.cs
  1. public class DBAccessContext:DbContext  
  2.   {  
  3.   
  4.       public DBAccessContext(DbContextOptions<DBAccessContext> options):base(options)  
  5.       {   
  6.       }  
  7.   
  8.       public DbSet<CrudModel> Crud_Data { getset; }  
  9.   
  10.   }  
CrudRepository.cs
  1. public class CrudRepository : ICrudRepository  
  2. {  
  3.     private readonly IConfiguration configuration;  
  4.     private readonly DataAccessDB DB;  
  5.   
  6.     public CrudRepository(IConfiguration configuration)  
  7.     {  
  8.         this.configuration = configuration;  
  9.         this.DB = new DataAccessDB(configuration);   
  10.     }  
  11.     public List<CrudModel> GetData()  
  12.     {  
  13.         return DB.GetData();  
  14.     }  
  15.   
  16.     public bool insert(string[] Param)  
  17.     {  
  18.   
  19.         return DB.insert(Param);  
  20.   
  21.     }  
  22.   
  23.   
  24. }  
 CrudContextRepository.cs
  1. public class CrudContextRepository : ICrudRepository  
  2.    {  
  3.        private readonly DBAccessContext dBAccessContext;  
  4.        private readonly IConfiguration configuration;  
  5.   
  6.        public CrudContextRepository(DBAccessContext dBAccessContext,IConfiguration configuration)  
  7.        {  
  8.            this.dBAccessContext = dBAccessContext;  
  9.            this.configuration = configuration;  
  10.        }  
  11.        public  List<CrudModel> GetData()  
  12.        {  
  13.           return dBAccessContext.Crud_Data.ToList();  
  14.        }  
  15.   
  16.        public bool insert(string[] Param)  
  17.        {  
  18.            var model = new CrudModel()  
  19.            {  
  20.                Name = Param[0],  
  21.                City = Param[1],  
  22.                InsertDate = System.DateTime.Now,  
  23.                FatherName= Param[3]  
  24.   
  25.            };  
  26.            
  27.         dBAccessContext.Crud_Data.Add(model);  
  28.           var result= dBAccessContext.SaveChanges();  
  29.   
  30.            if (result>0)  
  31.            {  
  32.                return true;  
  33.            }       
  34.            return false;  
  35.        }  
  36.    }  
 ICrudRepository.cs
  1. public interface ICrudRepository  
  2.   {  
  3.       bool insert(string[] Param);  
  4.       List<TestDemo.Models.CrudModel> GetData();  
  5.   
  6.   }  
 Index.cshtml under View/Crud/
  1. @model TestDemo.Models.CrudModel  
  2.   
  3. <partial name="_ValidationScriptsPartial" />  
  4. <style>  
  5.     .bg-secondary {  
  6.         background-color: #f4f5f7 !important;  
  7.     }  
  8. </style>  
  9.   
  10. <h1>Welcome to Crud</h1>  
  11.   
  12. <form id="Form1" method="post" asp-action="Index" asp-controller="Crud" >  
  13.     @if (ViewBag.Msg != null)  
  14.     {  
  15.   
  16.         <div class="@ViewBag.alertType" role="alert">  
  17.             <div asp-validation-summary="All">@ViewBag.Msg</div>  
  18.         </div>  
  19.         @*<script>  
  20.                 $(function () {  
  21.   
  22.                     $.notify({  
  23.                         title: "<strong>Message:</strong> ",  
  24.                         message: "@ViewBag.Msg"  
  25.                     });  
  26.                 });  
  27.   
  28.             </script>*@  
  29.   
  30.         ViewBag.Msg = null;  
  31.     }  
  32.   
  33.     <div class="card">  
  34.         <div class="card-body">  
  35.             <div class="form-group">  
  36.                 <label asp-for="Name">Name</label>  
  37.                 <input asp-for="Name" class="form-control">  
  38.                 <span asp-validation-for="Name" class="text-danger"></span>  
  39.             </div>  
  40.             <div class="form-group">  
  41.                 <label asp-for="City">City</label>  
  42.                 <input asp-for="City" class="form-control" />  
  43.                 <span asp-validation-for="City" class="text-danger"></span>  
  44.             </div>  
  45.             <div class="form-group">  
  46.                 <label asp-for="FatherName">Father Name</label>  
  47.                 <input asp-for="FatherName" class="form-control" />  
  48.                 <span asp-validation-for="FatherName" class="text-danger"></span>  
  49.             </div>  
  50.         </div>  
  51.         <div class="card-footer">  
  52.             <button type="submit" class="btn btn-primary">Submit</button>  
  53.         </div>  
  54.     </div>  
  55.   
  56.     @{ if (ViewBag.ModelList != null)  
  57.         { <div class="card">  
  58.                 <div class="card-body">  
  59.                     <partial name="_ListPartial" model="@ViewBag.ModelList" />  
  60.                 </div>  
  61.             </div>  
  62.         }}  
  63.   
  64.     @*@await Html.PartialAsync("_ListPartial", Model)*@  
  65.   
  66.     @*@(await Html.RenderComponentAsync<TestDemo.Components.Component>(RenderMode.ServerPrerendered,null))*@  
  67.   
  68. </form>  
Simple Insert And Select Operation Using .NET Core MVC With ADO.NET And Entity Framework Core
 
I hope it helps. If you have any questions or discussion remarks, please comment below. Also, I strongly recommend to download the project file and debug the process.