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

Shivam Tiwari
1y
27.8k
0
6

Article

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 one 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 an MS SQL server for the database.

USE [CrudDB]
GO

/****** Object: Table [dbo].[Crud_Data] Script Date: 22-11-2020 09:33:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Crud_Data](
    [id] [int] IDENTITY(1,1) NOT NULL,
      NULL,
      NULL,
    [InsertDate] [datetime] NULL,
      NULL,
 CONSTRAINT [PK_Crud_Data] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2. Change in the startup.cs file in the project root.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
    // By changing the service reference we are switching to ADO.NET or Entity Framework Core
    // ----Start
    services.AddScoped<ICrudRepository, CrudRepository>();
    // services.AddScoped<ICrudRepository, CrudContextRepository>();
    // ----End
    services.AddDbContext<DBAccessContext>(options => options.UseSqlServer(Configuration.GetConnectionString("MyKey")));
}

Project root

Step 3. Connection string in appsettings.json.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "MyKey": "Data Source=PRO-ACQER8AM;Initial Catalog=CrudDB;Integrated Security=True;"
  }
}

Logging

Step 4. Made a model to communicate with View and DB Layer.

namespace TestDemo.Models
{
    [Table("Crud_Data", Schema = "dbo")]
    public class CrudModel
    {
        public int Id { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public string City { get; set; }

        public DateTime? InsertDate { get; set; }

        public string? FatherName { get; set; }
    }
}

Testdemo

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.

public class CrudController : Controller
{
    private readonly ICrudRepository crudRepository;

    public CrudController(ICrudRepository crudRepository)
    {
        this.crudRepository = crudRepository;
    }

    public IActionResult Index()
    {
        ViewBag.ModelList = crudRepository.GetData();
        return View();
    }

    [HttpPost]
    public IActionResult Index(CrudModel crudModel)
    {
        try
        {
            if (ModelState.IsValid)
            {
                // ICrudRepository crudRepository = new CrudRepository();
                var result = crudRepository.insert(new string[] { crudModel.Name, crudModel.City, System.DateTime.Now.ToString(), crudModel.FatherName });
                ViewBag.ModelList = crudRepository.GetData();
                if (result)
                {
                    ViewBag.Msg = "Succeed";
                    ViewBag.alertType = "alert alert-success";
                }
                else
                {
                    ViewBag.Msg = "Insertion failed";
                    ViewBag.alertType = "alert alert-danger";
                }
            }
        }
        catch (Exception ex)
        {
            ViewBag.Msg = "Insertion failed";
            ViewBag.alertType = "alert alert-danger";
            ModelState.AddModelError("Message", ex.Message);
        }

        return View();
    }
}

Controller

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 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 do not 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. SQL client reference is required to use ado.net, you can add this by using the NuGet package manager.

DataAccessDB.cs

public class DataAccessDB : IDataAccessDB
{
    private readonly IConfiguration config;
    string connsString = string.Empty;

    public DataAccessDB(IConfiguration config)
    {
        this.config = config;
        connsString = config.GetConnectionString("MyKey");
    }
    public List<CrudModel> GetData()
    {
        List<TestDemo.Models.CrudModel> ModelList = new List<Models.CrudModel>();
        using (SqlConnection conn = new SqlConnection(connsString))
        {
            conn.Open();
            using (SqlCommand command = new SqlCommand($"select * from [dbo].[Crud_Data]", conn))
            {
                try
                {
                    using (var result = command.ExecuteReader())
                    {
                        while (result.Read())
                        {
                            ModelList.Add(
                                new Models.CrudModel { Id = (int)result.GetValue("Id"), Name = (string)result.GetValue("Name"), City = (string)result.GetValue("City"), FatherName = (string)result.GetValue("FatherName").ToString() });
                        }
                    }
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    conn.Close();
                }
                return ModelList;
            }
        }
    }

    public bool insert(string[] Param)
    {
        using (SqlConnection conn = new SqlConnection(connsString))
        {
            conn.Open();
            using (SqlCommand command = new SqlCommand($"INSERT INTO [dbo].[Crud_Data] ([Name],[City],[InsertDate],FatherName) VALUES ('{Param[0]}','{Param[1]}',getdate(),'{Param[3]}')", conn))
            {
                try
                {
                    var result = command.ExecuteNonQuery();

                    if (result > 0)
                    {
                        return true;
                    }
                }
                catch (Exception)
                {

                }
                finally
                {
                    conn.Close();
                }
                return false;
            }
        }
    }
}

DBAccessContext.cs

public class DBAccessContext : DbContext
{
    public DBAccessContext(DbContextOptions<DBAccessContext> options) : base(options)
    {
    }
    public DbSet<CrudModel> Crud_Data { get; set; }
}

CrudRepository.cs

public class CrudRepository : ICrudRepository
{
    private readonly IConfiguration configuration;
    private readonly DataAccessDB DB;
    public CrudRepository(IConfiguration configuration)
    {
        this.configuration = configuration;
        this.DB = new DataAccessDB(configuration);
    }
    public List<CrudModel> GetData()
    {
        return DB.GetData();
    }

    public bool insert(string[] Param)
    {
        return DB.insert(Param);
    }
}

CrudContextRepository.cs

public class CrudContextRepository : ICrudRepository
{
    private readonly DBAccessContext dBAccessContext;
    private readonly IConfiguration configuration;
    public CrudContextRepository(DBAccessContext dBAccessContext, IConfiguration configuration)
    {
        this.dBAccessContext = dBAccessContext;
        this.configuration = configuration;
    }
    public List<CrudModel> GetData()
    {
        return dBAccessContext.Crud_Data.ToList();
    }
    public bool insert(string[] Param)
    {
        var model = new CrudModel()
        {
            Name = Param[0],
            City = Param[1],
            InsertDate = System.DateTime.Now,
            FatherName = Param[3]
        };
        dBAccessContext.Crud_Data.Add(model);
        var result = dBAccessContext.SaveChanges();

        if (result > 0)
        {
            return true;
        }
        return false;
    }
}

ICrudRepository.cs

public interface ICrudRepository
{
    bool Insert(string[] Param);
    List<TestDemo.Models.CrudModel> GetData();
}

Index. cshtml under View/Crud/

@model TestDemo.Models.CrudModel

<partial name="_ValidationScriptsPartial" />
<style>
    .bg-secondary {
        background-color: #f4f5f7 !important;
    }
</style>

<h1>Welcome to Crud</h1>

<form id="Form1" method="post" asp-action="Index" asp-controller="Crud">
    @if (ViewBag.Msg != null)
    {

        <div class="@ViewBag.alertType" role="alert">
            <div asp-validation-summary="All">@ViewBag.Msg</div>
        </div>
        @*<script>
                $(function () {

                    $.notify({
                        title: "<strong>Message:</strong> ",
                        message: "@ViewBag.Msg"
                    });
                });

            </script>*@

        ViewBag.Msg = null;
    }

    <div class="card">
        <div class="card-body">
            <div class="form-group">
                <label asp-for="Name">Name</label>
                <input asp-for="Name" class="form-control">
                <span asp-validation-for="Name" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="City">City</label>
                <input asp-for="City" class="form-control" />
                <span asp-validation-for="City" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="FatherName">Father Name</label>
                <input asp-for="FatherName" class="form-control" />
                <span asp-validation-for="FatherName" class="text-danger"></span>
            </div>
        </div>
        <div class="card-footer">
            <button type="submit" class="btn btn-primary">Submit</button>
        </div>
    </div>

    @{ if (ViewBag.ModelList != null)
        { <div class="card">
                <div class="card-body">
                    <partial name="_ListPartial" model="@ViewBag.ModelList" />
                </div>
            </div>
        }}

    @*@await Html.PartialAsync("_ListPartial", Model)*@

    @*@(await Html.RenderComponentAsync<TestDemo.Components.Component>(RenderMode.ServerPrerendered,null))*@

</form>

DBLayer

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.