Creating A .NET Core 2.1 Class Library Project Using ADO.NET

Introduction

In this article, I am going to show you how to create a .NET Core 2.1 Class Library Project using ADO.NET.

This class library project contains business logic functionality for inserting data into a database. Then, I am consuming this class library project in ASP.NET Core 2.1 MVC Application.

We will be using Visual Studio 2017 (version 15.9.13 or above) and SQL Server 2017 or you can use SQL server 2008 or above version.

What is the Class Library?

A class library contains types, classes, Interfaces and methods that are easily reusable, shareable, distributed or consume by an application. Also, a class library project is used for code reusability, Code reusability is one of the key features of modern programming languages. It helps to reduce Errors, code duplication and reduce the development time.

Prerequisites

Install Visual studio 2017 Updated version 15.9.13
Install .Net Core SDK 2.1 or above
SQL Server 2017

Now we will create our .Net Core 2.1 Class library project.

First of all, we will create a Database, table and a stored procedure.

Creating Database, Table, and Stored Procedures

Step 1 - To create a Database

Open your SQL server and use the following script to create the “CoreMvcDB” Database

Create database CoreMvcDB

Step 2 - To Create a Table

Open your SQL Server and use the following script to create “tbl_Employee” table.

create table tbl_Employee
(
Sr_no int not null primary key identity(1,1),
Emp_Name nvarchar(250),
City nvarchar(100),
State nvarchar(100),
Country nvarchar(100),
Department nvarchar(50)
)

Step 3 - To create a stored procedure

Now we will create a stored procedure to Add Employee data to DataBase.

Open your SQL Server and use the following script to create the procedure.

To insert an Employee Record

create procedure sp_Employee_Add
@Emp_Name nvarchar(250),
@City nvarchar(100),
@State nvarchar(100),
@Country nvarchar(100),
@Department nvarchar(50)
AS
BEGIN
Insert into tbl_Employee(Emp_Name,City,State,Country,Department)
values(@Emp_Name,@City,@State,@Country,@Department)
END

Now, our database part has been completed. So now we will create a .Net core 2.1 class library project.

Creating A .NET Core 2.1 Class Library Project

Step 1 - Creating a class library Empty solution

First creating an Empty solution for our class library project, it contains our class library project and its related projects. A Visual Studio Solution just serves as a container for one or more projects.

Open Visual Studio menu bar, select File -> New -> Project.

After selecting the project, A "New Project" dialog will open, expand the “Other ProjectTypes” node and select “Visual Studio Solutions”. Name the Solution “Chittaranjan” and select the OK button.

Note
My solution Name is “Chittaranjan”.

The full structure of Empty solution will look like this.

Creating A .NET Core 2.1 Class Library Project Using ADO.NET

Step 2 - Creating our .Net Core class library project

In the Solution Explorer, right-click on the Chittaranjan solution file and from the context menu, you can select Add -> New Project menu option.
In the Add New Project dialog, expand the Visual C# node, then you can select .NET Core node in the left side and select Class Library(.NET Core) project template.

Now you can give your project name and select OK to create the .NET Core class library project.

Here my project name is “ChittaDB”.

Now the solution structure is given below,

Now our Class library project is created. Solution ‘Chittaranjan’ contains one project and the project name is “ChittaDB”.

Step 3 - Add Class Library Project Functionality

In “ChittaDB” Class library project I have Added 2 Class files for Insert data and save data in the database.

Add a class in Class library project
Right-click on “ChittaDB” project and select add a new Item menu option. On the menu option dialog, Select VisualC#Items in the left side and select Class in the right-hand side. Then you can put your class name. Here Name of our class is EmployeeEntities.cs, Select Add button. This class will contain our Employee properties.
Now, open the EmployeeEntities.cs class and put the following code in it. We are also adding the required validators to the fields of EmployeeEntities class, so we need to use System.ComponentModel.DataAnnotations at the top.
1. using System.ComponentModel.DataAnnotations;
3. namespace ChittaDB
4. {
5. public class EmployeeEntities
6. {
8. [Required]
9. public string Emp_Name { get; set; }
10. [Required]
11. public string City { get; set; }
12. [Required]
13. public string State { get; set; }
14. [Required]
15. public string Country { get; set; }
16. [Required]
17. public string Department { get; set; }
18. }
19. }
Add another Class in Class Library Project for Insert Methods.

Right-click on “ChittaDB” project and select add a new Item menu option. On the menu option dialog, Select VisualC#Items in the left side and select Class in the right-hand side. Then you can put your class name. Here Name of our class is EmployeeDBAccessLayer, Select Add button. This class will contain our database related operations.

Now, The Class Library Project structure is given below.

Open EmployeeDBAccessLayer.cs and put the following code to handle the database operations. Make sure to put your connection string.

Note
For Ado.Net, I am adding “Microsoft.EntityFrameworkCore.SqlServer” in my class library project. This is available in Manage NuGet packages.

using System.Data;
using System.Data.SqlClient;
namespace ChittaDB
{
public class EmployeeDBAccessLayer
{
SqlConnection con = new SqlConnection("put your connection string here");
public string AddEmployeeRecord(EmployeeEntities employeeEntities)
{
try
{
SqlCommand cmd = new SqlCommand("sp_Employee_Add", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Emp_Name", employeeEntities.Emp_Name);
cmd.Parameters.AddWithValue("@City", employeeEntities.City);
cmd.Parameters.AddWithValue("@State", employeeEntities.State);
cmd.Parameters.AddWithValue("@Country", employeeEntities.Country);
cmd.Parameters.AddWithValue("@Department", employeeEntities.Department);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return ("Data save Successfully");
}
catch (Exception ex)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
return (ex.Message.ToString());
}
}
}
}

Step 5 - Build

Build your project by hitting F5 or by selecting the Build menu item in Visual Studio. A successful build looks like below:

Step 6 - Consuming A Class Library Project

Here I am consuming a class library project in ASP.NET Core 2.1 MVC application.

First, we will create an ASP.NET Core 2.1 MVC application.

Open Visual Studio and select File -> New -> Project.

After selecting the project, a "New Project" dialog will open. Select .NET Core inside the Visual C# menu from the left side panel.

Then, select “ASP.NET Core web application“from available project templates. Give a name to the project as “ChittaWeb” and press OK.

After clicking on the OK button, a new dialog will open to select the project template. You can saw two drop-down menus at the top left of the template window. Then, select “.NET Core” and “ASP.NET Core 2.1” from these dropdowns. Select “Web application (Model-View-Controller)” template and press OK to create Asp.Net Core MVC project. And select the tick mark for configuring for HTTPS.

Now the complete Solution structure is given below.

In Solution “Chittaranjan” contain 2 projects, First project Name is “ChittaDB” this the class library project. The second project name is “ChittaWeb” this is the Asp.Net Core 2.1 MVC application. Now our Asp.Net Core MVC application is created.

Now, we can use the .NET Core 2.1 class library functionality in ASP.NET Core 2.1 MVC application.

Step 1. Add Class Library project Reference

To use a class library project in your application, first you must add a reference to the class library project to access its functionality.

Right-click on the “ChittaWeb” asp.net core MVC application Dependencies in Solution Explorer and select Add ->Reference option.

On the next screen, you will see the ChittaDB is already available. You can tick the ChittaDB then click OK button.

Step 2. Import Namespace and Call Functions

First add a controller in Asp.Net core MVC application then using Import namespace and call functions.

Right click on Controllers folder and Select add New Item.

An “Add New Item” dialog box will open. Select Asp.Net Core from the left panel, then select “Controller Class” from templates panel, and put the name as EmployeeController.cs. Press Add.

In Employeecontroller I will Import ChittaDB namespace

Import Namespace

Before you can use a class library project and its classes, you must import the namespace by using the following code.

using ChittaDB;

If your class library project reference is added correctly, as soon as you start typing “using Chitt..”, you will see Visual Studio Intellisense will load the namespace in the list.

Call Functions

Once the reference is added, all classes, Methods and public members of the class library project should be available in your Asp.Net Core MVC application. Now we can call all the methods in Controller.

Here I will call all the class and functions from Class library project.

We will put our business logic into this controller.

To handle database operations, we will create an object of EmployeeDBAccessLayer class inside the EmployeeController class.

using Microsoft.AspNetCore.Mvc;
using ChittaDB;
namespace ChittaWeb.Controllers
{
public class EmployeeController : Controller
{
EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();

To handle the business logic of create operation, open EmployeeController.cs and put following code into it.

using Microsoft.AspNetCore.Mvc;
using ChittaDB;
namespace ChittaWeb.Controllers
{
public class EmployeeController : Controller
{
EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();
[HttpGet]
public IActionResult Create()
{
return View();
}
[HttpPost]
public IActionResult Create([Bind] EmployeeEntities employeeEntities)
{
try
{
if (ModelState.IsValid)
{
string resp = empdb.AddEmployeeRecord(employeeEntities);
TempData["msg"] = resp;
}
}
catch (Exception ex)
{
TempData["msg"] = ex.Message;
}
return View();
}
}
}

Adding View to the Application

To add views for our controller class, we need to create a folder inside Views folder with the same name as our controller and then add our views to that folder.

Right-click on the Views folder, and then Add >> New Folder and name the folder as Employee.

Now, right-click on the Views/Employee folder, and then select Add >> New Item.

An “Add New Item” dialog box will open. Select Asp.Net Core from the left panel, then select “Razor View” from templates panel, and put the name as Create.cshtml. Press OK.

Create View

This view will be used to Add new employee data to the database.

Open Create.cshtml and put following code into it.

@model ChittaDB.EmployeeEntities
@{
ViewData["Title"] = "Create Employee";
}
<h2>Create Employee</h2>
<hr />
<form asp-action="Create" class="form-horizontal">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<div class="form-group">
<label class="control-label">Name</label>
<input asp-for="Emp_Name" class="form-control" />
<span asp-validation-for="Emp_Name" class="text-danger"></span>
</div>
<div class="form-group">
<label class="control-label">City</label>
<input asp-for="City" class="form-control" />
<span asp-validation-for="City" class="text-danger"></span>
</div>
<div class="form-group">
<label class="control-label">State</label>
<input asp-for="State" class="form-control" />
<span asp-validation-for="State" class="text-danger"></span>
</div>
<div class="form-group">
<label class="control-label">Country</label>
<input asp-for="Country" class="form-control" />
<span asp-validation-for="Country" class="text-danger"></span>
</div>
<div class="form-group">
<label class="control-label">Department</label>
<input asp-for="Department" class="form-control" />
<span asp-validation-for="Department" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Submit" class="btn bg-primary" />
</div>
</form>
@{
if (@TempData["Msg"] != null)
{
<script>
alert('@TempData["msg"]')
</script>
}
}

Add a new menu

Edit the Views/Shared/_Layout page and add a new menu as “Add Employee”. For that, add the below code.

<ul class="nav navbar-nav">
<li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>
<li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>
<li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>
<li><a asp-area="" asp-controller="Employee" asp-action="Create">Add Employee</a></li>
</ul>

Build and Run

Now press F5 to launch the application or Run the application and you can see the page as shown below.

Click on Add Employee to navigate to Create view. Add a new Employee record as shown in the image below.

We have also added the validation using DataAnnotations on add Create View page. If we miss the data in any field while creating employee record, we will get a required field validation error message.

When data saved, the success message will show and click ok.

After data is saved you can check your database.

Conclusion

In this article, you have learned how to create a .NET Core 2.1 Class library project using ADO.NET with help of Visual Studio 2017. You have also learned how to consume a .Net Core class library in Asp.net core MVC application for insert data.

Also you can read my previous article: CRUD Operation with ASP.NET Core 2.1 MVC Using .Net Core Class Library Project.

12 Comments

Join the conversation! Your thoughts help the community grow.

Loran Hayden5y ago
Use dependency injection - create a class constructor that takes an object of type ApplicationDbContext and set it to a readonly private ApplicationDbContext _db like public MyClass(ApplicationDbContext db){ ... _db = db;...}
Muteen ul Haq5y ago
Nice article. I want to know how to access connection string define in appsettings.json in DB Access Layer class library?
Chinmaya Dash7y ago
Nice article
- Chittaranjan Swain7y ago
  Thanks Chinmaya.
Rajanikant Hawaldar7y ago
nice article chittaranjan
- Chittaranjan Swain7y ago
  Thank you Rajanikant.
prasanna p7y ago
Nice article,Thanks for sharing Chittaranjan
- Chittaranjan Swain7y ago
  Thank you Prasanna.
Amit Mohanty7y ago
Nicely described.
- Chittaranjan Swain7y ago
  Thank you Amit.
Abhishek Mishra7y ago
This is written very well. Cool !!!
- Chittaranjan Swain7y ago
  Thank you Abhishek.