Uploading Excel File Data into SQL Server Database using ASP.Net C#

Efficiently managing data is pivotal for modern web applications, often involving uploading and storing Excel file data in a database. This article guides you through achieving this seamlessly using ASP.NET Web Forms in C#. We’ll leverage a table-type variable to simplify data insertion, focusing on a practical example involving a product table.

Part 1: Setting Up Your ASP.NET Web Form Project
Before we dive into the data integration process, ensure you have an ASP.NET Web Forms project set up. Create a new project or utilize an existing one.

Part 2: Creating the Table Type in the Database
In your database, create a table type that mirrors the structure of the Excel data you intend to upload. For this example, let’s assume your Excel file contains columns like ProductID, ProductName, Category, and Price. Your table type might look like this:

CREATE TYPE dbo.ProductTableType AS TABLE
(
 ProductID INT,
 ProductName NVARCHAR(100),
 Category NVARCHAR(50),
 Price DECIMAL(10, 2)
);

Part 3: Implementing the Web Form

Step 1: Design the Web Form:
In your ASP.NET Web Forms project, create a form that enables users to upload an Excel file. Add appropriate UI elements and controls, such as a FileUpload control and a button to initiate the upload process.

Step 2: Code-Behind Logic:
In the code-behind file (e.g., productUpload.aspx.cs), manage the file upload and data insertion process.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using OfficeOpenXml; // Install EPPlus NuGet package
protected void UploadButton_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string filePath = Server.MapPath("~/Uploads/" + FileUpload1.FileName);
        FileUpload1.SaveAs(filePath);
        using (var package = new ExcelPackage(new FileInfo(filePath)))
        {
            DataTable dt = new DataTable();
            ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
            foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
            {
                dt.Columns.Add(firstRowCell.Text);
            }
            for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
            {
                var newRow = dt.NewRow();
                for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
                {
                    newRow[col - 1] = worksheet.Cells[row, col].Text;
                }
                dt.Rows.Add(newRow);
            }
            using (var connection = new SqlConnection("YourConnectionString"))
            {
                connection.Open();
                using (var command = new SqlCommand("InsertExcelData", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    SqlParameter parameter = command.Parameters.AddWithValue("@ExcelData", dt);
                    parameter.SqlDbType = SqlDbType.Structured;
                    parameter.TypeName = "dbo.ProductTableType";
                    command.ExecuteNonQuery();
                }
            }
        }
        // Display a success message or perform further actions
    }
}

Step 3: Database Interaction:
Create a stored procedure (InsertExcelData) that takes the table type parameter and inserts the data into the product table.

CREATE PROCEDURE dbo.InsertExcelData
    @ExcelData dbo.ProductTableType READONLY
AS
BEGIN
    INSERT INTO Product (ProductID, ProductName, Category, Price)
    SELECT ProductID, ProductName, Category, Price
    FROM @ExcelData;
END;

Conclusion

Uploading and saving Excel file data into a database using a table-type variable within an ASP.NET Web Forms project streamlines the process and enhances data management. By following the steps outlined in this article and adapting them to your use case, you can create a robust mechanism to integrate Excel data into your database seamlessly. This approach contributes to the efficiency and effectiveness of your web application, ultimately benefiting both users and developers.