Make Three Tier Architecture In ASP.NET And Bulk Insert Data Into SQL Server

Our project is divided into three layers:

1. Data Access Layer
   In this layer, we will put all the class files that interact with the SQL Server. It is used mainly for CRUD operations.
   
   
2. Business Access Layer
   In this layer, we will put all the business logic.
   
   
3. Presentation Layer
   This layer is mainly used for the user nitration.

We will also add a layer: Business Entity. In this project, we will add all the class files  with the property. Hence, our project will look as shown below:



Now, we move step by step:

Step 1

Create a blank solution. In this blank solution, add three class projects with the names:  Data Access Layer, Business Access Layer and Business Entity.

For Presentation Layer, I used a console project.

Now, add reference from one project to the other project.

In Data Access Layer, add reference to the Business Entity.

In Business Service Layer, add reference to the Business Entity and Data Access Layer.

In Presentation Layer, add reference to the Business Entity and Business Access Layer.

Hence, our project will look as shown below:



Step 2

In Business Entity,  add a class file Item.cs and define some property.



Step 3

In SQL server, create a Table, store the procedure and the table type.


In this article, we are using bulk insert through a Table type variable.

Step 4

Now, add a class file in the Data Access Layer, Item Dal.cs, and use the insert code in SQL Server.

In this class file, we will convert the list to the data table. We must remember that data schema columns created in C# code must be in the same order of the table type created in SQL Server.



Step 5

In the Business Access Layer, add a class file, ItemBal.cs.



Step 6

In our Presentation Layer, we will create a list of the items and pass it to the Business Layer.



Step 7

Run the project and see in M_item table, where the data is inserted successfully.