asp.net fie upload bulk record

There can be several possible reasons:

1. Timeout or request length exceeded

ASP.NET web apps have limits to avoid long-running requests:

• Default request timeout = 110 seconds.

• MaxRequestLength (in web.config) limits upload file size (default is 4MB).

If your Excel or operation takes too long, the request stops mid-way.

Fix: Increase timeouts in web.config

(Above allows 50MB file and 10-minute timeout.)

2. Connection or SQL Timeout

SQL commands may time out when inserting huge data.

Fix:

SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandTimeout = 600; // 10 minutes

3. Reading Excel inefficiently (row-by-row)

If you’re using OleDbConnection or looping per row with INSERT, it’s very slow and sometimes stops after a while due to memory leaks.

Better Solution: Read Excel ? DataTable ? Bulk Copy to SQL

Efficient ASP.NET C# Code Example

Here’s a robust method using EPPlus + SqlBulkCopy (best for large uploads):

Install NuGet packages:

EPPlus
System.Data.SqlClient

Code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using OfficeOpenXml;

public void UploadExcelToDatabase(string filePath)
{
    try
    {
        // Step 1: Read Excel into DataTable
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        DataTable dt = new DataTable();

        using (var package = new ExcelPackage(new FileInfo(filePath)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
            int colCount = worksheet.Dimension.End.Column;
            int rowCount = worksheet.Dimension.End.Row;

            // Add columns
            for (int col = 1; col <= colCount; col++)
            {
                dt.Columns.Add(worksheet.Cells[1, col].Value?.ToString() ?? $"Column{col}");
            }

            // Add rows
            for (int row = 2; row <= rowCount; row++)
            {
                DataRow dr = dt.NewRow();
                for (int col = 1; col <= colCount; col++)
                {
                    dr[col - 1] = worksheet.Cells[row, col].Value ?? DBNull.Value;
                }
                dt.Rows.Add(dr);
            }
        }

        // Step 2: Bulk Copy to SQL Server
        using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=YourDB;Integrated Security=True"))
        {
            con.Open();
            using (SqlBulkCopy bulk = new SqlBulkCopy(con))
            {
                bulk.DestinationTableName = "YourTableName";
                bulk.BulkCopyTimeout = 0; // no timeout
                bulk.BatchSize = 5000; // commit every 5000 rows
                bulk.WriteToServer(dt);
            }
        }

        Console.WriteLine("Upload successful!");
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: " + ex.Message);
    }
}

Tips for Large Uploads (45,000+ rows)

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 ?");
    }
}

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

 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 <= rowCount; row++) // Assuming row 1 is header
        {
            var name = worksheet.Cells[row, 1].Text;
            var email = worksheet.Cells[row, 2].Text;
            var phone = worksheet.Cells[row, 3].Text;

            dataTable.Rows.Add(name, email, phone);
        }

        using (var connection = new SqlConnection("YourConnectionString"))
        {
            connection.Open();
            using (var bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = "YourTableName";
                bulkCopy.WriteToServer(dataTable);
            }
        }
    }
}