ASP.NET CORE — Learn CRUD Operations in ADO.NET from Zero to Hero

In this ASP.NET Core Tutorial you will learn to do CRUD operations in ADO.NET from the very beginning. This tutorial will help you to learn ADO.NET so that you can start doing database programming in ASP.NET CORE with C#. Download the full Source code of this application from GitHub __________________________________________________________ Audience This tutorial is made for everyone who wants to learn and ADO.NET quickly. All the things are explained in easy & step by step manner. You can download the complete source code and run them in your PC. ASP.NET Core Here I will do the CRUD (CREATE, READ, UPDATE & DELETE) Operations on a teachers table in the SQL Server database. I personally believe that “ The best way to learn any programming language is by building the CRUD operations at the first place”. This tutorial is divided into following section: 1. Creating a new ASP.NET Core Web Application 2. Configuring ASP.NET Core Web Application 3. Create Layout, ViewImports, ViewStart and add Bootstrap 4. Creating a Database 5. Adding Connection String of the Database in the Application 6. Adding Model class for Teacher 7. Adding Client Side Validation feature 8. Performing the CREATE Teacher Functionality 9. Performing the READ Teacher Functionality 10. Performing the UPDATE Teacher Functionality 11. Performing the DELETE Teacher Functionality 1. Creating a new ASP.NET Core Web Application In your Visual Studio, select ‘File > New Project’. A new window will get opened, here: a. On the left side, select ‘ ’. Installed > Visual C# > Web b. On the middle part, select ‘ ’. ASP.NET Core Web Application c. On the lower part, give your project name as ‘ ’ or anything you want, and select the location on the drive where project files will be stored. Also select the option — ‘ ’ and finally click the ‘ ’ button to create your project. CRUDADO Create directory for solution OK Create ASP.NET Core Web Application Next, a new window will open that ask you to select the project template, dot net framework and a few more things. In this window make sure you select these things: a. On the 2 drop downs at the top, select ‘ ’ and ‘ ’. You can also select ASP.NET Core version greater than 2.0. .NET Core ASP.NET Core 2.0 b. Select the ‘ ’ project template. Empty c. Click the ‘ ’ button to create your ASP.NET Core Web Application OK Select Properties for ASP.NET Core Application 2. Configuring ASP.NET Core Web Application Your application should have support of MVC framework, routing and Static files. All this can be done from the ‘Startup.cs’ class which is located in the root of the application. Open the Startup.cs class and add these supports inside the and methods as shown below: ConfigureServices() Configure() using Microsoft.AspNetCore.Builder;using Microsoft.AspNetCore.Hosting;using Microsoft.Extensions.DependencyInjection; namespace CRUDADO{public class Startup{public void ConfigureServices(IServiceCollection services){services.AddMvc();} public void Configure(IApplicationBuilder app, IHostingEnvironment env) { app.UseStaticFiles(); app.UseDeveloperExceptionPage(); app.UseMvc(routes => { routes.MapRoute( name: "default", template: "{controller=Home}/{action=Index}/{id?}"); }); } } } 3. Create Layout, ViewImports, ViewStart and add Bootstrap Create ‘ ’ folder in the root of your project, and inside it create a ‘ ’ folder. Views Shared Next, right click on the ‘ ’ folder, and select ‘ ’. You will get a new window showing the item list, from this list select ‘ ’ and click the ‘ ’ button. Shared Add > New Item MVC View Layout Page Add This will add file in the Shared folder and it will be open for editing. You need to add the following code to it: _Layout.cshtml @ViewData["Title"] @RenderBody() Next, download the Package and extract the Bootstrap zip file contents inside the ‘ ’ folder of your project. Bootstrap wwwroot > lib Bootstrap is used to create responsive layout that are mobile friendly. It is the most popular CSS framework in the world. Next, right click on the ‘ ’ folder and select ‘ ’. On the items list, select ‘ ’ and click the ‘ ’ button. This will add file. Views Add > New Item MVC View Imports Page Add _ViewImports.cshtml Import the built-in tag helpers & models namespace for the Views, by adding the following code to this file: @using CRUDADO.Models@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers In the same way, right click the ‘ ’ folder and select ‘ ’. On the items list, select ‘ ’ and click the ‘ ’ button. Views Add > New Item MVC View Start Page Add This will add the file with the following initial code: _ViewStart.cshtml @{Layout = "_Layout";} It tells that all the Views will have the Layout from the _Layout.cshtml file which is located inside the Shared folder. 4. Creating a Database You must have SQL Server 2017 (any edition) installed in your PC. However, any other edition of SQL Server like 2016, 2008, 2012, will work. You can use the Developer and Express editions of SQL Server 2017 which are free to use — . Download SQL Server 2017 Next, you need to connect to the SQL Server which you can do with . So select ‘ ’ in Visual Studio. SQL Server Object Explorer in Visual Studio View > SQL Server Object Explorer in Visual Studio When ‘ ’ window opens, right click on the SQL Server node and select . SQL Server Object Explorer Add SQL Server Add SQL Server Next, a window opens up. In this window, select the ‘ ’ tab then click on the ‘ ’ node to open it. Connect Browse Local Inside this node, select the 2nd option that says . After selecting it, you will notice the ‘ ’ field, in the same window, now has the value. (localdb)\MSSQLLocalDB Server Name: (localdb)\MSSQLLocalDB Now you have to simply click the button to connect to the SQL Server. See the below image: Connect Connecting SQL Server in SQL Server Object Explorer In a few seconds time the SQL Server will be connected and ready to use. Next, right click on node and select Databases Add New Database. Add New Database In the dialog box, enter as the database name and click to create the database. School OK Create School Database The school database is created and is empty. So now first create the ‘Teacher’ table. Click the ► sign given in front of database name to open the various nodes inside it. School Next, right click on the node and select . Tables Add New Table A will open up in the Visual Studio. Here you can add the fields for the Teacher table. Table Design Window In this window, inside the tab, you can enter the below script to create the ‘ ’ table: T-SQL Teacher CREATE TABLE [dbo].[Teacher]([Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,[Name] VARCHAR(50) NOT NULL,[Skills] VARCHAR(250) NOT NULL,[TotalStudents] INT NOT NULL,[Salary] MONEY NOT NULL,[AddedOn] DATE NOT NULL DEFAULT GETDATE()) Next, you click the button to start the creation process of this table, see the below image which explains this: Update Table Design Window You will get a new dialog box called ‘ ’. Here click the ‘ ’ button and within a few seconds the Inventory table will be created. Preview Database Updates Update Database Preview Database Updates window You can find this newly created table inside the ‘Tables’ node. The ‘Teacher’ table has 6 columns which are: 1. Id — a primary key column that will be auto-generated from 1 since it is provided with Identity(1,1) attribute. 2. Name — a varchar(50) column for storing the name of the teacher. 3. Skills — for storing the skills of the teacher. 4. TotalStudents — an Int column for storing the total number of students which a given teacher teaches. 5. Salary — for storing the salary of the teacher. 6. AddedOn — for storing the time of the creation of the record. The column has a Date type and is it automatically gets the current time using the GETDATE() SQL function. Note that the columns have attribute so they cannot contain NULL in them. NOT NULL 5. Adding Connection String of the Database in the Application A Connection String stores the parameters to the database and is used to connect the application to it. These parameters are the name of the driver, Server name and Database name. It also contains security information such as user name and password needed for the database connection. The connection string is stored inside the file which resides in the root of the application. appsettings.json Right click on the project name in the Solution Explorer and select . Then when the ‘ ’ window opens up, select file, and give it the name as (see below image). Finally click the ‘ ’ button. Add > New Item Add New Item ASP.NET Configuration file appsettings.json Add Adding appsettings.json file The file will be created and open up for editing in Visual Studio. Add the below connection string to it: {"ConnectionStrings": {"DefaultConnection": "Server= (localdb)\\MSSQLLocalDB;Database=School;Trusted_Connection=True;MultipleActiveResultSets=true"}} I defined trusted Connection as true in the connection string so the Windows credentials of the current user are used to authenticate against the SQL Server. 6. Adding Model class for Teacher Right click on the project name in the Solutions Explorer and select ‘ ’. Name this folder as ‘ ’. Then right click this ‘ ’ folder and select ‘ ’. Add > New Folder Models Models Add > New Item In the window that opens, select a new Class and name it teachers. Add the following properties to this class: using CRUDADO.CustomValidation;using System;using System.Collections.Generic;using System.ComponentModel.DataAnnotations;using System.Linq;using System.Threading.Tasks; namespace CRUDADO.Models{public class Teacher{[Required]public int Id { get; set; } \[Required\] public string Name { get; set; } \[Required\] \[SkillsValidate(Allowed = new string\[\] { "ASP.NET Core", "ASP.NET MVC", "ASP.NET Web Forms" }, ErrorMessage = "You skills are invalid")\] public string Skills { get; set; } \[Range(5, 50)\] public int TotalStudents { get; set; } \[Required\] public decimal Salary { get; set; } \[Required\] public DateTime AddedOn { get; set; } } } This class has 6 properties for the 6 columns in the Teacher table in the database. I have applied the attributes on Id, Name, Skills, Salary and AddedOn properties, the TotalStudent property is applied with the [Range] attribute so it will only allow values from 5 to 50. [Required] Also note the attribute applied to the Skills property. The attribute Is a custom validation attribute through which I will force Skills property to only accept 3 values which are: [SkillsValidate] [SkillsValidate] 1. ASP.NET Core 2. ASP.NET MVC 3. ASP.NET Web Forms Next, I will create the custom validation attribute. So create a new folder on the root of the application and name it as ‘ ’. Inside this folder add a new class called ‘ ’ and add the following code to it: [SkillsValidate] CustomValidation SkillsValidate.cs using Microsoft.AspNetCore.Mvc.ModelBinding.Validation;using System;using System.Collections.Generic;using System.Linq;using System.Threading.Tasks; namespace CRUDADO.CustomValidation{public class SkillsValidate : Attribute, IModelValidator{public string[] Allowed { get; set; }public string ErrorMessage { get; set; } public IEnumerable Validate(ModelValidationContext context) { if (Allowed.Contains(context.Model as string)) return Enumerable.Empty (); else return new List { new ModelValidationResult("", ErrorMessage) }; } } } 7. Adding Client Side Validation feature The Client Side validation feature is performed by ‘ ’ and 2 validation plugins which are: jQuery 1. Validation jQuery 2. jQuery Validation Unobtrusive To install these 3 run the following commands in the ‘ ’ window: Package Manager Console PM> Install-Package jQuery -Version 3.3.1 PM> Install-Package jQuery.Validation -Version 1.17.0 PM> Install-Package jQuery.Validation.Unobtrusive -Version 2.0.20710 8. Performing the CREATE Teacher Functionality Create ‘ ’ folder on the root of the Application. Inside this folder add a new controller called ‘ . The controller will have the initial code as shown below: Controllers HomeController.cs’ using System.Collections.Generic;using System.Linq;using Microsoft.AspNetCore.Mvc; namespace CRUDADO.Controllers{public class HomeController : Controller{public IActionResult Index(){return View();}}} The connection string is kept in file. To access this file I have to get the object of interface through Dependency Injection feature. Therefore I have to add the interface in the constructor of the HomeController and MVC framework will automatically provide me the object for it. appsettings.json IConfiguration IConfiguration So change the Home Controller’s code to as shown below: using System.Collections.Generic;using System.Linq;using Microsoft.AspNetCore.Mvc;using Microsoft.Extensions.Configuration; namespace CRUDADO.Controllers{public class HomeController : Controller{public IConfiguration Configuration { get; }public HomeController(IConfiguration configuration){Configuration = configuration;} public IActionResult Index() { return View(); } } } The CREATE Teacher Functionality is done through a new Action method called ‘ ’. So add the Create Action methods to the Home Controller as shown below: CREATE using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Threading.Tasks;using CRUDADO.Models;using Microsoft.AspNetCore.Mvc;using Microsoft.Extensions.Configuration; namespace CRUDADO.Controllers{public class HomeController : Controller{public IConfiguration Configuration { get; }public HomeController(IConfiguration configuration){Configuration = configuration;} public IActionResult Index() { return View(); } public IActionResult Create() { return View(); } \[HttpPost\] public IActionResult Create\_Post(Teacher teacher) { if (ModelState.IsValid) { string connectionString = Configuration\["ConnectionStrings:DefaultConnection"\]; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"Insert Into Teacher (Name, Skills, TotalStudents, Salary) Values ('{teacher.Name}', '{teacher.Skills}','{teacher.TotalStudents}','{teacher.Salary}')"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; connection.Open(); command.ExecuteNonQuery(); connection.Close(); } return RedirectToAction("Index"); } } else return View(); } } } In the Create Action method I got the connection string in the below code line: string connectionString = Configuration["ConnectionStrings:DefaultConnection"]; Then I used the ‘ ’ object to insert the teacher’s records by running the insert SQL Query: SqlCommand string sql = $"Insert Into Teacher (Name, Skills, TotalStudents, Salary) Values ('{teacher.Name}', '{teacher.Skills}','{teacher.TotalStudents}','{teacher.Salary}')"; Next, add the ‘ ’ folder in the root of the application. Inside this folder create a new folder called ‘Home’. Views Next, create a new View called ‘ ’ inside this ‘ ’ folder (i.e. ‘ ’). Add the following code to this View: Create.cshtml Home Views>Home @model Teacher @{Layout = "_Layout";var title = "CREATE Teacher";ViewData["Title"] = title;} .input-validation-error {border-color: red;} @title div asp-validation-summary div form label asp-for label input asp-for span asp-validation-for span Submit The View has a Model of type ‘ ’ and it creates Input fields for ‘ ’, so that user can fill and submit it. Teacher Name, Skills, TotalStudents and Salary When the Submit button is clicked then the Create Action of type , is called and the new teacher records is created. HttpPost Notice the 3 script files which do the Client Side Validation of the input fields in the View: If you are performing the Insertion of records through EF core then you need to see this tutorial — . Insert Records in Entity Framework Core Testing the Create Teacher functionality Run the application and in the browser go to the URL of ‘ ’ View, which is — . You will see the Create Teacher form in the browser. Create http://localhost:52730/Home/Create Without filling any fields press the Submit button and you will see the validation errors displayed by jQuery Validation plugins. jQuery Validations on Teacher Form Now fill all the fields (as shown by the below image) and click the submit button. Create Teacher The teacher record will be created in the database and you will be redirected to the Index View which is currently empty. You can confirm the record is inserted in the Teacher’s table. For this go to the ‘ ’, then right click the ‘ ’ table and select ‘ ’. SQL Server Object Explorer Teacher View Data The teacher table will open and you will see the new teacher record in it, see the below image: Teacher Record Inserted in Database 9. Performing the READ Teacher Functionality Now we will create the READ Teacher Functionality. So change the Index Action in the Home Controller to return all the teachers to the View as shown below: public IActionResult Index(){List teacherList = new List ();string connectionString = Configuration["ConnectionStrings:DefaultConnection"]; using (SqlConnection connection = new SqlConnection(connectionString)) { //SqlDataReader connection.Open(); string sql = "Select \* From Teacher";SqlCommand command = new SqlCommand(sql, connection); using (SqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { Teacher teacher = new Teacher(); teacher.Id = Convert.ToInt32(dataReader\["Id"\]); teacher.Name = Convert.ToString(dataReader\["Name"\]); teacher.Skills = Convert.ToString(dataReader\["Skills"\]); teacher.TotalStudents = Convert.ToInt32(dataReader\["TotalStudents"\]); teacher.Salary = Convert.ToDecimal(dataReader\["Salary"\]); teacher.AddedOn = Convert.ToDateTime(dataReader\["AddedOn"\]); teacherList.Add(teacher); } } connection.Close(); } return View(teacherList); } In this action I execute the ‘ ’ query with the method to get an object. Select * from Teacher SqlCommand’s ExecuteReader() SqlDataReader I then loop through all the records using SqlDataReader’s ‘ ’ method, and create a teacher list object which contains all the teacher’s records. This object is returned to the View as the Model in the very end of the code. .Read() Next, add the ‘ ’ View inside the ‘ ’ folder with the following code: Index Views/Home/ @model IEnumerable @{Layout = "_Layout";var title = "READ Teacher";ViewData["Title"] = title;} @title Create a asp-action a Id Name Skills Total Students Salary Added On Update Delete @if (Model == null){ No Model Data }else{@foreach (var p in Model){ @p.Id @p.Name @p.Skills @p.TotalStudents @string.Format(new System.Globalization.CultureInfo("en-US"), "{0:C2}", p.Salary) @string.Format("{0:dddd, dd MMMM yyyy}", p.AddedOn) Update Delete }} a asp-action asp-route-id a form asp-action asp-route-id form Notice that I have also created the columns for Update and Delete functionality in the table’s thead and tbody elements: Id Name Skills Total Students Salary Added On Update Delete … else { @foreach (var p in Model) { … Update Delete } } We will create the Update and Delete Functionality in the next sections. Related tutorial which you can’t miss — . Read Records in Entity Framework Core Testing the Read Teacher functionality Run your application and you will see the Teacher’s table records get displayed in the Index View. This is shown by the below image: Read Teacher View 10. Performing the UPDATE Teacher functionality The Update functionality is done by adding Update Action methods to the Home Controller. The codes to add to the Controller are given below: public IActionResult Update(int id){string connectionString = Configuration["ConnectionStrings:DefaultConnection"];Teacher teacher = new Teacher(); using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"Select \* From Teacher Where Id='{id}'"; SqlCommand command = new SqlCommand(sql, connection); connection.Open(); using (SqlDataReader dataReader = command.ExecuteReader()) { while (dataReader.Read()) { teacher.Id = Convert.ToInt32(dataReader\["Id"\]); teacher.Name = Convert.ToString(dataReader\["Name"\]); teacher.Skills = Convert.ToString(dataReader\["Skills"\]); teacher.TotalStudents = Convert.ToInt32(dataReader\["TotalStudents"\]); teacher.Salary = Convert.ToDecimal(dataReader\["Salary"\]); teacher.AddedOn = Convert.ToDateTime(dataReader\["AddedOn"\]); } } connection.Close(); } return View(teacher); } [HttpPost][ActionName("Update")]public IActionResult Update_Post(Teacher teacher){string connectionString = Configuration["ConnectionStrings:DefaultConnection"];using (SqlConnection connection = new SqlConnection(connectionString)){string sql = $"Update Teacher SET Name='{teacher.Name}', Skills='{teacher.Skills}', TotalStudents='{teacher.TotalStudents}', Salary='{teacher.Salary}' Where Id='{teacher.Id}'"; using (SqlCommand command = new SqlCommand(sql, connection)) { connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } return RedirectToAction("Index"); } The version of Update action method takes the ‘ ’ of the teacher in it’s parameter and then runs the SQL Query called with the SqlCommand object. It gets the teacher record’s value and returns it to the Update View. HttpGet id Select * From Teacher Where Id='{id}' The HttpPost version of Update Action method does the actual update of the teacher’s record using ADO.NET ‘ ’ object. The important thing to note is the update query which is: SqlCommand string sql = $"Update Teacher SET Name='{teacher.Name}', Skills='{teacher.Skills}', TotalStudents='{teacher.TotalStudents}', Salary='{teacher.Salary}' Where Id='{teacher.Id}'"; Next, create the Update View inside the ‘Views/Home/’ folder with the following code: @model Teacher@{Layout = "_Layout";var title = "UPDATE Teacher";ViewData["Title"] = title;} .input-validation-error {border-color: red;} @title Submit div asp-validation-summary div form label asp-for label input asp-for type label asp-for label input asp-for type span asp-validation-for span label asp-for label input asp-for type span asp-validation-for span label asp-for label input asp-for type span asp-validation-for span label asp-for label input asp-for type span asp-validation-for span label asp-for label input asp-for type asp-format form The View is similar to the Index View we created earlier. I have made the ‘Id’ field as readonly so that user cannot change it. Testing the Update Teacher functionality Run your application and click the ‘Update’ link for the 1st teacher records, see below image: Click Update Link The record will open for updation. Change name to ‘ ’ and salary to ‘ ’. Finally click the submit button as shown by the below image: Bill Gates 100000000 Update Record Record will be updated and you will be redirected to the Index View where you can see the updated record fields as shown by the below image: Record Updated 11. Performing the DELETE Teacher Functionality Create Delete Action method in the Home Controller whose code is given below: HttpPost]public IActionResult Delete(int id){string connectionString = Configuration["ConnectionStrings:DefaultConnection"]using (SqlConnection connection = new SqlConnection(connectionString)){string sql = $"Delete From Teacher Where Id='{id}'";using (SqlCommand command = new SqlCommand(sql, connection)){connection.Open();try{command.ExecuteNonQuery();}catch (SqlException ex){ViewBag.Result = "Operation got error:" + ex.Message;}connection.Close();}}return RedirectToAction("Index");} This method takes the id of the teacher’s record in it’s parameter and then execute the Delete SQL query: string sql = $"Delete From Teacher Where Id='{id}'"; Testing the Delete Teacher functionality Run your application and click the ‘Delete’ link given against any of the record. Check the below images which shows the record deletion process: Delete Link Record Deleted Download the full Source code of this application from GitHub __________________________________________________________ Conclusion This completes this CRUD operations tutorial in ASP.NET Core using ADO.NET. I hope you loved reading this and understand how the codes are working. If you have any confusion go through all the code which you can download from here. Follow ME I hope you found something new to add to your web development knowledge in this tutorial. If it is so then don’t forget to to show your like. It will not only bring a smile to my bad face but also motivate me to write more and more good articles to web developers. clap for a few times And as always, to get notified whenever I publish a new article on Medium. follow me I have also published another tutorial on HACKERNOON, you would like to see it too — 7 Common Web Development problems which every developer from Beginners to Experts should know [with multiple solutions]