How to Upload Excel File to Database in Entity Framework Database First Using C#

If you desire to Import Excel File into your database in ASP.Net MVC then this tutorial will teach y'all the fastest manner to achieve this. In this tutorial you will learn:

• How to upload an Excel File to the Server with an input file control.
• How to Read the uploaded Excel File data with OleDbDataReader grade.
• How to Import Excel File'due south information into a Database table with SqlBulkCopy class. This is the fastest method of importing excel's data.

Would you like to dig deep into the earth of database programming with C#? So y'all can cheque this series of tutorials on Entity Framework Core that assistance y'all to acquire database programming within a few hours time.

The Excel File Construction

You can Import Excel file that have any number of rows and columns. Make sure you provide the names to each and every column of the excel file.

Here I am using an excel file that contains the auction report of a shop. Information technology has 7 columns:

• ane. Date – containing engagement values.
• 2. Region – contains string.
• iii. Person – contains string.
• iv. Item – contains string.
• 5. Units – contains values of type int.
• 6. Unit Cost – contains decimal values.
• 7. Total – contains decimal values.

You tin download this excel file by clicking here.

Creating Database Table

Based on the construction of my excel file, I have to create a database tabular array. To this table the information of this excel file will exist copied.

I proper noun this table every bit Sale and create its structure similar this:

• 1. Id – Master central and identity cavalcade of type int.
• 2. Region – varchar(25).
• 3. Person – varchar(25).
• 4. Item – varchar(25).
• 5. Units – int.
• vi. UnitCost – money.
• 7. Total – coin.
• eight. AddedOn – date.

Discover that I used varchar columns for storing string values from excel, and for decimal values I created the columns of money blazon.

You cna download this table's script from here.

The Create table script of my Sale table is:

CREATE Tabular array [dbo].[Auction](     [Id] [int] IDENTITY(ane,ane) NOT NULL,     [Region] [varchar](25) Not Zip,     [Person] [varchar](25) NOT Zero,     [Item] [varchar](25) Non NULL,     [Units] [int] NOT NULL,     [UnitCost] [coin] NOT NULL,     [Full] [coin] NOT Null,     [AddedOn] [appointment] NOT NULL,  CONSTRAINT [PK_Sale] PRIMARY Central CLUSTERED  (     [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]          

Pace 1: Create Model

The model code is:

public class ImportExcel {     [Required(ErrorMessage = "Please select file")]     [FileExt(Permit = ".xls,.xlsx", ErrorMessage = "Only excel file")]     public HttpPostedFileBase file { get; ready; } }          

The FileExt() is the Custom Validation attribute used for doing the Server Side Validation of file upload control.

Thus it will allow file upload control to upload only .xls and .xlsx files.

Stride 2: Create FileExt() Class

Create a new class called FileExt and add the beneath code to information technology:

public class FileExt : ValidationAttribute {     public string Allow;     protected override ValidationResult IsValid(object value, ValidationContext validationContext)     {         if (value != null)         {             string extension = ((System.Web.HttpPostedFileBase)value).FileName.Split('.')[one];             if (Allow.Contains(extension))                 return ValidationResult.Success;             else                 return new ValidationResult(ErrorMessage);         }         else             render ValidationResult.Success;     } }          

Pace 3: Create Controller

In the controller add Alphabetize Activity of type mail service equally shown below:

[HttpPost] public ActionResult Index(ImportExcel importExcel) {     if (ModelState.IsValid)     {         string path = Server.MapPath("~/Content/Upload/" + importExcel.file.FileName);         importExcel.file.SaveAs(path);           string excelConnectionString = @"Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + path + "';Extended Backdrop='Excel 12.0 Xml;IMEX=1'";         OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);           //Canvas Name         excelConnection.Open();         string tableName = excelConnection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();         excelConnection.Shut();         //End           OleDbCommand cmd = new OleDbCommand("Select * from [" + tableName + "]", excelConnection);           excelConnection.Open();           OleDbDataReader dReader;         dReader = cmd.ExecuteReader();         SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["CS"].ConnectionString);           //Give your Destination table name         sqlBulk.DestinationTableName = "auction";           //Mappings         sqlBulk.ColumnMappings.Add("Date", "AddedOn");         sqlBulk.ColumnMappings.Add("Region", "Region");         sqlBulk.ColumnMappings.Add together("Person", "Person");         sqlBulk.ColumnMappings.Add("Item", "Item");         sqlBulk.ColumnMappings.Add("Units", "Units");         sqlBulk.ColumnMappings.Add("Unit of measurement Cost", "UnitCost");         sqlBulk.ColumnMappings.Add("Full", "Total");           sqlBulk.WriteToServer(dReader);         excelConnection.Close();           ViewBag.Effect = "Successfully Imported";     }     return View(); }          

Also y'all need to add together the following namespaces to the controller:

using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration;          

Explanation

• The first step to Import Excel file is by uploading it into the Upload folder. And so a connection cord is made using Microsoft.ACE.OLEDB.12.0 provider.
• One time the Excel's file is fetched into the variable chosen tableName, I am and so creating an OleDbCommand object and pass the 'select' Query and the connexion string to it.
• So I am creating an OleDbDataReader object that will read every tape of the Excel file.
• Next, the Import Excel procedure starts, for this I create the a SqlBulkCopy course object and sets the SQL Database Table name to it'south DestinationTableName property. Then through the sqlBulk.ColumnMappings.Add(SourceColumn,DesinationColumn), the column mappings are done.
• Finally using the .WriteToServe() part the Excel File's Data is copied into the database table.

Footstep 4: Create View

Add the following code to the Index View:

<h4>@ViewBag.Result</h4> @using (Html.BeginForm("Index", "ImportExcel", FormMethod.Mail service, new { enctype = "multipart/form-data" })) {     @Html.TextBoxFor(m => thousand.file, new { type = "file" })     <button id="submitButton" type="submit">Submit</button>     @Html.ValidationMessageFor(model => model.file) }          

You lot can also create PDF Files from ASP.NET website. For this cheque my tutorial – How to Create a PDF file in ASP.NET MVC using iTextSharp.

Conclusion
Now Run the application and Import the Excel File. After a couple of second the excel records volition exist copied into the database tabular array. Yous can check your database table to find the newly copied records.

The below prototype shows the records added to my SQL database tabular array:

You can download the source code by using the below link:

DOWNLOAD

Next:

You lot tin can also check my another related tutorial How to Read Excel file and bear witness it in the form of a Grid that also has Paging.

winstonwhange.blogspot.com

Source: https://www.yogihosting.com/import-excel-asp-net-mvc/

Share this post