asp.net fie upload bulk record

Question

Hello, I am working in c# asp.net.

I have bulk upload functionality with 45000 records in excel.

When i upload it through my code it uploads approximate 5000-6000 records.

why it does not upload all 45000 records in db ?

Also, any asp.net c# code will be very useul, that upload all records from excel ...

Thanks in advance !

Prasad Raveendran · Answer

give a try with the below packages Install-Package ExcelDataReader Install-Package ExcelDataReader.DataSet using System; using System.Data; using System.Data.SqlClient; using System.IO; using ExcelDataReader; public class ExcelBulkUploader { private readonly string _connectionString; public ExcelBulkUploader(string connectionString) { _connectionString = connectionString; } public void UploadExcelToDb(string filePath, string destinationTable) { // Step 1 – Load Excel into DataTable System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); DataTable dataTable; using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) using (var reader = ExcelReaderFactory.CreateReader(stream)) { var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration { ConfigureDataTable = (_) => new ExcelDataTableConfiguration { UseHeaderRow = true // assumes first row is header } }); dataTable = dataSet.Tables[0]; // first sheet } Console.WriteLine($"Rows loaded from Excel: {dataTable.Rows.Count}"); // Step 2 – Bulk Copy to SQL using (var connection = new SqlConnection(_connectionString)) { connection.Open(); using (var bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = destinationTable; bulkCopy.BatchSize = 5000; bulkCopy.BulkCopyTimeout = 0; // unlimited // Optional: map columns by name if Excel headers differ // bulkCopy.ColumnMappings.Add("ExcelColumnName", "DbColumnName"); bulkCopy.WriteToServer(dataTable); } } Console.WriteLine("Upload complete ?"); } }

Sepehr Shn · Answer

You can convert Excel to JSON using an existing library, and then use Parallel.ForEach to enhance performance

Sangeetha S · Answer

bulk upload is only inserting around 5,000–6,000 records out of 45,000, there could be several reasons behind this memory issue and time out , Excel read limitation possible of reason. recommended to use EPPlus library using OfficeOpenXml; using System.Data; using System.Data.SqlClient; public void UploadExcelToDatabase(string filePath) { ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (var package = new ExcelPackage(new FileInfo(filePath))) { var worksheet = package.Workbook.Worksheets[0]; var rowCount = worksheet.Dimension.Rows; var dataTable = new DataTable(); dataTable.Columns.Add("Name"); dataTable.Columns.Add("Email"); dataTable.Columns.Add("Phone"); for (int row = 2; row

asp.net fie upload bulk record

Answers (3)