ASP.NET CORE - CRUD Using Dependency Injection

Introduction

In this article, we will learn how to create, retrieve, update and delete data using dependency injection. it helps to remove hard-coded dependency among objects.

Creating Table

Open SQL Server and use the following script to create the DETAILS table.

Create database Web
use Web
Create table Details(  
   Name varchar(50),
   Username varchar(50),
   Password varchar(50) 
); 
Insert into Details values ('divya','[email protected]',1234)
Insert into Details values ('maneesha','[email protected]',5678)
Insert into Details values ('Ishika','[email protected]',8976)
SELECT * FROM Details  

Now, our Database part is completed. So, we will proceed to CRUD operations using dependency injection.

Step 1

Create a new project by selecting “ASP.NET Core Web Application” from available project types. 

Step 2

In this step, provide your project name and select the location of the project, after that click on the Next button, then a new dialog will open with additional information in which click on create button.

Step 3

Now, our project will open. You can see the project files in Solution Explorer. Open Solution Explorer and right-click on Project and select Add >> New Folder. A new folder will be created inside the project.

Step 4

Now, we will be adding our class files to the Models folder. After creating a Models folder, right-click on the  Models folder, select Add, and then click on the class. 

After that a new dialog will pop up here you have to click on the class and then decide the suitable name of the class and last click on Add button

Step 5

In this class(here I have named the class Connection) create a property that returns the datatype string and then create a constructor, write the connection string of the database in the constructor. For the connection string open Server Explorer, and click on data connections.

namespace dependency.Model {
    public class Connection {
        public string connectionstring {
            get;
            set;
        }
        public Connection() {
            this.connectionstring = $ "Data Source=.\\SQLEXPRESS;Initial 
            Catalog=WEB;Integrated Security=True";
        }
    }
}

Step 6

Create another class to communicate with the database (here I have named the class DAL) and then create a method that returns the datatype string and names the method GETVALUE and passes the string and then adds a NuGet package of System.Data.SqlClient from NuGet package manager so that you can use the namespace of System.Data.SqlClient for connection to the database. This function is used to read the value from the database. Similarly create a method for inserting data, updating data, and deleting data from the database.

using System.Data.SqlClient;
namespace dependency.Model{
   public class DAL {
        Connection Conn;
        public DAL(Connection _CONN) {
            Conn = _CONN;
        }
        public string GETVALUE(string QUERY){
            SqlConnection sqlConnection = new SqlConnection(Conn.connectionstring);
            Conn.connectionstring = sqlConnection.ConnectionString;
            SqlCommand COMMAND = new SqlCommand();
            COMMAND.Connection = sqlConnection;
            COMMAND.CommandText = QUERY;
            sqlConnection.Open();
            var RES1 = COMMAND.ExecuteScalar();
            sqlConnection.Close();
            return RES1.ToString();
        }
        public string VALUE(string QUERY) {
            SqlConnection sqlConnection = new SqlConnection(Conn.connectionstring);
            Conn.connectionstring = sqlConnection.ConnectionString;
            SqlCommand COMMAND = new SqlCommand();
            COMMAND.Connection = sqlConnection;
            COMMAND.CommandText = QUERY;
            sqlConnection.Open();
            var RES2 = COMMAND.ExecuteNonQuery();
            sqlConnection.Close();
            return RES2.ToString();
        }       
    }
}

Step 7

Create another class for SQL query(here I have named the class Logic) and write a method that includes a query for reading the data from the database and returns a bool value. Similarly, write a method that includes a query for inserting the data to the database and that returns string datatype. Update method that includes update query and deletes method that includes deleting query to delete data from the database.

using System;
namespace dependency.Model {
    public class Logic {
        DAL DL;
        public Logic(DAL _DL) {
            DL = _DL;
        }
        public bool login(string Username, string Password) {
            string Pass = DL.GETVALUE($ "select Password from Details where Username='{Username}'");
            return Pass == Password;
        }
        public String INSERT(String Name, String Username, String Password) {
            string Pass = DL.VALUE($ "insert into Details (Name,Username,Password)VALUES('{Name}','{Username}','{Password}')");
            return Pass;
        }
        public String UPDATE(String Name, String Username, String Password) {
            string Pass = DL.VALUE($ "UPDATE Details SET Username='{Username}',Password='{Password}' WHERE Name='{Name}'");
            return Pass;
        }
        public String DELETE(String Name, String Username, String Password) {
            string Pass = DL.VALUE($ "DELETE FROM Details WHERE Name='{Name}'");
            return Pass;
        }
    }
}

Now, we will proceed to create our Razor pages.

Step 8

First, we will be creating a page to read data and as I have mentioned we will be adding our Razor pages inside the Pages folder, just right click on the Pages folder and select Add >> Razor pages

To Read/Retrieve the data  

To read the data from the database, click on the READ.cshtml page and write the given code to create a form. 

@page
@model dependency.Pages.READModel
@{
}
<div class="container col-md-4">    
    <form method="POST">
        <label for="Uname" class="col-form-label">USERNAME</label>
        <input type="text" class="form-control" name="Username" placeholder="Username..." />
        <label for="pass" class="col-form-label">PASSWORD</label>
        <input type="text" class="form-control" name="Password" placeholder="Password..." />
        <div class="row">
        <input type="submit" class="btn btn-success" value="SIGN UP" />          
        </div>
    </form>
</div>

Open READ.cshtml.cs page as you can see, the READModel inherits from the PageModel which plays an important role in the Razor Pages. Normally, this page has some methods that start with On+Http Method, such as OnGet, and OnPost. First of all, create a constructor to inject the dependency and write the given code for reading the data from the database.        

using System;
using dependency.Model;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace dependency.Pages {
    public class READModel: PageModel {
        public void OnGet() {}
        Logic _logic;
        public READModel(Logic logic1) {
            _logic = logic1;
        }
        public IActionResult OnPost(String Username, String Password) {
            if (_logic.login(Username, Password)) {
                return Redirect("/DEMO");
            } else {
                return Redirect("/Error");
            }
        }
    }
}

Output Screen

To Insert the data  

To Insert the data into the database, first of all, create a Razor page named the page INSERT then click on the INSERT.cshtml page and write the given code to create a form. 

@page
@model dependency.Pages.INSERTModel
@{
}
<div class="container col-md-4">
    <form method="POST">
        <label for="name" class="col-form-label">NAME</label>
        <input type="text" class="form-control" name="Name" placeholder="NAME..." />
        <label for="Uname" class="col-form-label">USERNAME</label>
        <input type="text" class="form-control" name="Username" placeholder="USERNAME..." />
        <label for="pass" class="col-form-label">PASSWORD</label>
        <input type="text" class="form-control" name="PASSWORD" placeholder="PASSWORD..." />
        <div class="row">
        <input type="submit" class="btn btn-success" value="INSERT" />
        </div>
    </form>
</div>

Open INSERT.cshtml.cs page and create a constructor to inject the dependency and write the given code for inserting the data into the database.      

using System;
using dependency.MODEL;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace dependency.Pages {
    public class INSERTModel: PageModel {
        public void OnGet() {}
        Logic LOGICS;
        public INSERTModel(Logic LOGIC1) {
            LOGICS = LOGIC1;
        }
        public IActionResult OnPost(String Name, String Username, String Password) {
            LOGICS.INSERT(NAME, USERNAME, PASSWORD);
            return Redirect("/DEMO");
        }
    }
}

You can observe that here we are using the OnPost request to insert the data using the LOGIC method.

Output Screen

To Edit/Update the data  

To update the data from the database, first, create a Razor page and name the page UPDATE then click on the UPDATE.cshtml page and write the given code to create a form. 

@page
@model dependency.Pages.UPDATEModel
@{
}
<div class="container col-md-4">
    <form method="POST">
        <label for="fname" class="col-form-label">NAME</label>
        <input type="text" class="form-control" name="NAME" placeholder="NAME..." />
        <label for="fname" class="col-form-label">USERNAME</label>
        <input type="text" class="form-control" name="USERNAME" placeholder="USERNAME..." />
        <label for="lname" class="col-form-label">PASSWORD</label>
        <input type="text" class="form-control" name="PASSWORD" placeholder="PASSWORD..." />
        <div class="row">
        <input type="submit" class="btn btn-success" value="UPDATE" />
        </div>
    </form>
</div>

Open UPDATE.cshtml.cs page and create a constructor to inject the dependency and write the given code for updating the data from the database.     

using System;
using dependency.MODEL;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace dependency.Pages {
    public class UPDATEModel: PageModel {
        public void OnGet() {}
        Logic LOGICS;
        public UPDATEModel(Logic _LOGIC) {
            LOGICS = _LOGIC;
        }
        public IActionResult OnPost(String Name, String Username, String Password) {
            LOGICS.UPDATE(Name, Username, Password);
            return Redirect("/DEMO");
        }
    }
}

This Razor page will enable us to edit/update the data of an existing table.

Output Screen

To Delete the data

To delete the data from the database, first, create a Razor page named the page  DELETE then click on the DELETE.cshtml page and write the given code to create a form.

@page
@model dependency.Pages.DELETEModel
@{
}
<div class="container col-md-4">
    <form method="POST">
        <label for="name" class="col-form-label">NAME</label>
        <input type="text" class="form-control" name="NAME" placeholder="NAME..." />
        <label for="uname" class="col-form-label">USERNAME</label>
        <input type="text" class="form-control" name="USERNAME" placeholder="USERNAME..." />
        <label for="pass" class="col-form-label">PASSWORD</label>
        <input type="text" class="form-control" name="PASSWORD" placeholder="PASSWORD..." />
        <div class="row">
        <input type="submit" class="btn btn-success" value="DELETE" />
        </div>
    </form>
</div>

 Open DELETE.cshtml.cs page and create a constructor to inject the dependency and write the given code for deleting the data from the database.     

using System;
using dependency.MODEL;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace dependency.Pages {
    public class DELETEModel: PageModel {
        public void OnGet() {}
        Logic LOGICS;
        public DELETEModel(Logic _LOGIC) {
            LOGICS = _LOGIC;
        }
        public IActionResult OnPost(String Name, String Username, String Password) {
            LOGICS.DELETE(NAME, USERNAME, PASSWORD);
            return Redirect("/DEMO");
        }
    }
}

Output Screen

Step 9

Open Startup. cs and write the services for dependency injection (here I have written singleton services).

public IConfiguration Configuration {
    get;
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services) {
    services.AddRazorPages();
    services.AddSingleton < dependency.MODEL.CONNECTION > ();
    services.AddSingleton < dependency.MODEL.DAL > ();
    services.AddSingleton < dependency.MODEL.LOGIC > ();
}

 And that’s it. We have created our ASP.NET Core application using dependency injection.

Conclusion

We have learned how to fetch data using dependency injection in ASP.NET Core. In this article, we have used SQL database for creating data, reading data, editing data, and deleting data from the database.