In this article we will discuss Import (Insert) Excel file data into Database ASP.Net Core MVC.
Here we will explain how to uploaded Excel file data will be read using OLEDB library and the read data will be inserted into SQL Server database using SqlBulkCopy.
Before start this article, please visit our previous article Display data in GridView (Grid) in ASP.Net Core
What is SQLBulkcopy in C#
SqlBulkCopy class as the name suggests does bulk insert from one source to another and hence all rows from the Excel sheet can be easily read and inserted using the SqlBulkCopy class.
Database
Let's we have a table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
Downloading and installing Microsoft.ACE.OLEDB.12.0 provider
As we are using OLEDB provider so we need to install this. Please follow the below link we have already discussed how to install Microsoft OLEDB provider.
Add the package using Nuget - System.Data.OleDb
You will need to install the System.Data.OleDb package using the following command.
Install-Package System.Data.OleDb -Version 4.7.1
Connection Strings in AppSettings.json file
The connection string to the Database and Excel file are saved in the AppSettings.json file like below.
Controller
The Controller consists of two Action methods.
Action method for handling GET operation, Inside this Action method, simply the View is returned.
Action method for handling POST operation for uploading and reading Excel file.
This Action method gets called when the Excel File is selected and the Import Button is clicked, and it gets the uploaded file in the IFormFile parameter.
The uploaded Excel file is saved to a folder named Uploads and then connection string is read from the Web.Config file and Placeholder is replaced by the path of the Excel file.
Using the fetched Sheet name, a SELECT statement is executed and all the records from the Excel sheet are fetched into a DataTable.
Now a connection is established with the database and the SqlBulkCopy object is initialized and I have specified the name of the Table using the DestinationTableName property.
Finally the columns are mapped and all the rows from the DataTable are inserted into the SQL Server table.
View
The View consists of an HTML Form with following ASP.Net Tag Helpers attributes.
asp-action – Name of the Action. In this case the name is Index.
asp-controller – Name of the Controller. In this case the name is Home.
method – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
The Form consists of an HTML FileUpload element and a Submit Button.
Excel File
The excel file look like that we have, you can download the excel file under the project file that are shared in Github.
You can see the output like below.
</> Find the Source Code in GithubSummary
In this tutorial we discussed about how to Import (Insert) Excel file data into Database ASP.Net Core MVC. If have any question related to this topic then give your feedback.
Post a Comment