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?

 
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
  1. Install Visual studio 2017 Updated version 15.9.13
  2. Install .Net Core SDK 2.1 or above
  3. 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 theCoreMvcDB” Database
 
Create database CoreMvcDB
 
Step 2 - To Create a Table
 
Open your SQL Server and use the following script to createtbl_Employee” table.
  1. create table tbl_Employee  
  2. (  
  3. Sr_no int not null primary key identity(1,1),  
  4. Emp_Name  nvarchar(250),  
  5.  City nvarchar(100),  
  6.  State nvarchar(100),  
  7.  Country nvarchar(100),  
  8.  Department nvarchar(50)  
  9. )  
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
  1. create procedure sp_Employee_Add  
  2. @Emp_Name  nvarchar(250),  
  3.  @City nvarchar(100),  
  4.  @State nvarchar(100),  
  5.  @Country nvarchar(100),  
  6.  @Department nvarchar(50)  
  7.  AS  
  8.  BEGIN  
  9.  Insert into tbl_Employee(Emp_Name,City,State,Country,Department)  
  10.  values(@Emp_Name,@City,@State,@Country,@Department)  
  11.  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.
  1. Open Visual Studio menu bar, select File -> New -> Project.

    Creating A .NET Core 2.1 Class Library Project Using ADO.NET
  1. 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”.

    Creating A .NET Core 2.1 Class Library Project Using ADO.NET
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
  1. In the Solution Explorer, right-click on the Chittaranjan solution file and from the context menu, you can select Add -> New Project menu option.

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

  2. 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”.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET 
 
Now the solution structure is given below,
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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.

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

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

  • 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;  
    2.   
    3. namespace ChittaDB  
    4. {  
    5.     public class EmployeeEntities  
    6.     {  
    7.   
    8.         [Required]  
    9.         public string Emp_Name { getset; }  
    10.         [Required]  
    11.         public string City { getset; }  
    12.         [Required]  
    13.         public string State { getset; }  
    14.         [Required]  
    15.         public string Country { getset; }  
    16.         [Required]  
    17.         public string Department { getset; }  
    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.

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

    Creating A .NET Core 2.1 Class Library Project Using ADO.NET
Now, The Class Library Project structure is given below.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET 
 
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.
  1. using System.Data;  
  2. using System.Data.SqlClient;  
  3.   
  4. namespace ChittaDB  
  5. {  
  6.     public class EmployeeDBAccessLayer  
  7.     {  
  8.         SqlConnection con = new SqlConnection("put your connection string here");  
  9.   
  10.         public string AddEmployeeRecord(EmployeeEntities employeeEntities)  
  11.         {  
  12.             try  
  13.             {  
  14.                 SqlCommand cmd = new SqlCommand("sp_Employee_Add", con);  
  15.                 cmd.CommandType = CommandType.StoredProcedure;  
  16.                 cmd.Parameters.AddWithValue("@Emp_Name", employeeEntities.Emp_Name);  
  17.                 cmd.Parameters.AddWithValue("@City", employeeEntities.City);  
  18.                 cmd.Parameters.AddWithValue("@State", employeeEntities.State);  
  19.                 cmd.Parameters.AddWithValue("@Country", employeeEntities.Country);  
  20.                 cmd.Parameters.AddWithValue("@Department", employeeEntities.Department);  
  21.                 con.Open();  
  22.                 cmd.ExecuteNonQuery();  
  23.                 con.Close();  
  24.                 return ("Data save Successfully");  
  25.             }  
  26.             catch (Exception ex)  
  27.             {  
  28.                 if (con.State == ConnectionState.Open)  
  29.                 {  
  30.                     con.Close();  
  31.                 }  
  32.                 return (ex.Message.ToString());  
  33.             }  
  34.         }  
  35.   
  36.     }  
  37. }  
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:
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET 
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET 
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET 
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET 
 
Now the complete Solution structure is given below.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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. 
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
On the next screen, you will see the ChittaDB is already available. You can tick the ChittaDB then click OK button.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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.
  1. using Microsoft.AspNetCore.Mvc;  
  2. using ChittaDB;  
  3.   
  4. namespace ChittaWeb.Controllers  
  5. {  
  6.     public class EmployeeController : Controller  
  7.     {  
  8.         EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();  
To handle the business logic of create operation, open EmployeeController.cs and put following code into it.
  1. using Microsoft.AspNetCore.Mvc;  
  2. using ChittaDB;  
  3.   
  4. namespace ChittaWeb.Controllers  
  5. {  
  6.     public class EmployeeController : Controller  
  7.     {  
  8.         EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();  
  9.   
  10.   
  11.         [HttpGet]  
  12.         public IActionResult Create()  
  13.         {  
  14.             return View();  
  15.         }  
  16.         [HttpPost]  
  17.         public IActionResult Create([Bind] EmployeeEntities employeeEntities)  
  18.         {  
  19.             try  
  20.             {  
  21.                 if (ModelState.IsValid)  
  22.                 {  
  23.                     string resp = empdb.AddEmployeeRecord(employeeEntities);  
  24.                     TempData["msg"] = resp;  
  25.                 }  
  26.             }  
  27.             catch (Exception ex)  
  28.             {  
  29.                 TempData["msg"] = ex.Message;  
  30.             }  
  31.             return View();  
  32.         }  
  33.     }  
  34. }  

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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
Now, right-click on the Views/Employee folder, and then select Add >> New Item.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
Create View
 
This view will be used to Add new employee data to the database.
 
Open Create.cshtml and put following code into it.
  1. @model ChittaDB.EmployeeEntities  
  2. @{  
  3.     ViewData["Title"] = "Create Employee";  
  4. }  
  5. <h2>Create Employee</h2>  
  6. <hr />  
  7. <form asp-action="Create" class="form-horizontal">  
  8.     <div asp-validation-summary="ModelOnly" class="text-danger"></div>  
  9.     <div class="form-group">  
  10.         <label class="control-label">Name</label>  
  11.         <input asp-for="Emp_Name" class="form-control" />  
  12.         <span asp-validation-for="Emp_Name" class="text-danger"></span>  
  13.     </div>  
  14.     <div class="form-group">  
  15.         <label class="control-label">City</label>  
  16.         <input asp-for="City" class="form-control" />  
  17.         <span asp-validation-for="City" class="text-danger"></span>  
  18.     </div>  
  19.     <div class="form-group">  
  20.         <label class="control-label">State</label>  
  21.         <input asp-for="State" class="form-control" />  
  22.         <span asp-validation-for="State" class="text-danger"></span>  
  23.     </div>  
  24.     <div class="form-group">  
  25.         <label class="control-label">Country</label>  
  26.         <input asp-for="Country" class="form-control" />  
  27.         <span asp-validation-for="Country" class="text-danger"></span>  
  28.     </div>  
  29.     <div class="form-group">  
  30.         <label class="control-label">Department</label>  
  31.         <input asp-for="Department" class="form-control" />  
  32.         <span asp-validation-for="Department" class="text-danger"></span>  
  33.     </div>  
  34.     <div class="form-group">  
  35.         <input type="submit" value="Submit" class="btn bg-primary" />  
  36.   
  37.     </div>  
  38. </form>  
  39. @{  
  40.     if (@TempData["Msg"] != null)  
  41.     {  
  42.         <script>  
  43.             alert('@TempData["msg"]')  
  44.         </script>  
  45.     }  
  46. }  
Add a new menu
 
Edit the Views/Shared/_Layout page and add a new menu as “Add Employee”. For that, add the below code.
  1. <ul class="nav navbar-nav">  
  2.                     <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>  
  3.                     <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>  
  4.                     <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>  
  5.                     <li><a asp-area="" asp-controller="Employee" asp-action="Create">Add Employee</a></li>  
  6.                 </ul>  
Build and Run
 
Now press F5 to launch the application or Run the application and you can see the page as shown below.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
Click on Add Employee to navigate to Create view. Add a new Employee record as shown in the image below.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
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.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
When data saved, the success message will show and click ok.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 
After data is saved you can check your database.
 
Creating A .NET Core 2.1 Class Library Project Using ADO.NET
 

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.


Similar Articles