Bulk Import of ID Card Data from Excel in ASP.NET MVC C#

Introduction

In many modern applications, ID (Radio Frequency Identification) technology plays a pivotal role in asset tracking, inventory management, and access control systems. Managing large volumes of ID data, such as card serial numbers, card tags, and associated information, can become cumbersome without efficient tools for bulk import. To simplify the process, integrating a feature to upload ID card data from Excel files into your ASP.NET MVC application is a practical solution.

This article explains how to implement a feature in an ASP.NET MVC application that allows users to upload ID card data stored in Excel files. The data is then processed and bulk-inserted into a SQL database. This feature is particularly useful for systems that require importing and managing large numbers of ID cards at once. We'll walk through the necessary steps, from creating the file upload form in the view to handling the import process in the controller, ensuring a smooth and efficient bulk import of ID card data.

By the end of this guide, you'll be equipped with the knowledge to implement a streamlined file upload and data import functionality for your own ID card management system.

Code

@using (Html.BeginForm("IDMaster", "Demoboard", FormMethod.Post, new { enctype = "multipart/form-data", id = "frmImport" }))
{
    @Html.AntiForgeryToken()
    <div class="form-body">
        <div class="row">
            <div class="col-md-3">
                <div class="form-group">
                    @Html.TextBoxFor(m => m.Importexcel, new { type = "file", @class = "form-control", accept = ".xls, .xlsx" })
                    @Html.ValidationMessageFor(m => m.Importexcel, "", new { @class = "validmsg" })
                </div>
            </div>
            <div class="col-md-3 pull-left">
                <a class="btn btn-primary btn-flat pull-left" id="btnConfrim">Submit</a>
            </div>
        </div>
    </div>
}

Setting Up the Excel Upload Feature

To start, you need a form in your ASP.NET MVC view where users can upload an Excel file containing RFID card data. This form will use an HTML input element with the file type to allow users to select the file for upload.

View (Razor) Code for File Upload

The following is the form that allows users to upload an Excel file.

The enctype="multipart/form-data" attribute ensures that the form can handle file uploads.
The @Html.TextBoxFor helper renders an input element that allows file selection. It restricts the file selection to Excel files (.xls, .xlsx) using the accept attribute.

Controller Action for Handling the File Upload

When the user submits the form, the file is posted to the server. The controller will handle the file, save it to the server, and process the data. The following code snippet demonstrates the core logic.

Controller Code (C#)

[HttpPost]
public ActionResult IDMaster(DemoImportModel _model)
{
    string Filepath = "";
    try
    {
        _model.Id = Convert.ToInt32(Session["Id"]);

        if (_model.Id != null)
        {
            #region ExcelImport
            if (ModelState.IsValid)
            {
                if (_model.Importexcel != null)
                {
                    // Define file path
                    string path = ConfigurationManager.AppSettings["IDexcelpath"];
                    if (!Directory.Exists(path))
                    {
                        Directory.CreateDirectory(path);
                    }
                    Filepath = path + Path.GetFileName(_model.Importexcel.FileName);

                    // Delete file if it already exists
                    if (System.IO.File.Exists(Filepath))
                    {
                        System.IO.File.Delete(Filepath);
                    }

                    string conString = string.Empty;
                    string extension = Path.GetExtension(_model.Importexcel.FileName);

                    // Only allow .xls and .xlsx file formats
                    if (extension == ".xls" || extension == ".xlsx")
                    {
                        _model.Importexcel.SaveAs(Filepath);

                        // Set connection string based on file extension
                        switch (extension)
                        {
                            case ".xls":
                                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                                break;
                            case ".xlsx":
                                conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                                break;
                        }
                    }
                    else
                    {
                        _model.Status = "1";
                        _model.StatusDesc = "Please Upload Only excel file.";
                        return View(_model);
                    }

                    // Read data from Excel file into DataTable
                    DataTable dt = new DataTable();
                    conString = string.Format(conString, Filepath);
                    using (OleDbConnection connExcel = new OleDbConnection(conString))
                    {
                        using (OleDbCommand cmdExcel = new OleDbCommand())
                        {
                            using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                            {
                                cmdExcel.Connection = connExcel;
                                connExcel.Open();
                                DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                                string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                                connExcel.Close();

                                // Read data from the first sheet
                                connExcel.Open();
                                cmdExcel.CommandText = "SELECT * From [Sheet1$]";
                                odaExcel.SelectCommand = cmdExcel;
                                odaExcel.Fill(dt);

                                // Add additional columns
                                dt.Columns.Add("Status", typeof(string), "0");
                                dt.Columns.Add("CreatedDt", typeof(DateTime));

                                // Trim column names and set creation date
                                foreach (DataColumn col in dt.Columns)
                                {
                                    col.ColumnName = col.ColumnName.Trim();
                                }
                                foreach (DataRow row in dt.Rows)
                                {
                                    row["CreatedDt"] = DateTime.Now; // Set current date for each row
                                }
                                connExcel.Close();
                            }
                        }
                    }

                    // Bulk insert data into the database using SqlBulkCopy
                    conString = ConfigurationManager.ConnectionStrings["DemoConstring"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(conString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            sqlBulkCopy.DestinationTableName = "dbo.ID_CardMaster";
                            sqlBulkCopy.ColumnMappings.Add("Number", "Number");
                            sqlBulkCopy.ColumnMappings.Add("TAGData", "TAGData");
                            sqlBulkCopy.ColumnMappings.Add("Status", "Status");
                            sqlBulkCopy.ColumnMappings.Add("CreatedDt", "CreatedDt");

                            con.Open();
                            try
                            {
                                sqlBulkCopy.WriteToServer(dt);
                            }
                            catch (SqlException ex)
                            {
                                // Handle error
                                clsErrorLog.ErrorLog(ex.ToString(), "ImportOrder", "Operations");
                            }

                            con.Close();
                        }
                    }

                    // Successful upload
                    TempData["SuccessMessage"] = "ID Cards were successfully imported!";
                    _model.Status = "0";
                }
                else
                {
                    _model.Status = "1";
                    _model.StatusDesc = "Please Upload an excel file.";
                    return View(_model);
                }
            }
            #endregion
        }
        else
        {
            Session.Abandon();
            return RedirectToAction("Login", "Account");
        }

        return View(_model);
    }
    catch (Exception ex)
    {
        _model.Status = "1";
        _model.StatusDesc = "Error occurred while uploading file: " + ex.Message;
        clsErrorLog.ErrorLog(ex.ToString(), "ImportOrder", "Operations");
    }
    return View(_model);
}

Explanation of Key Components

File Upload and Saving
- The uploaded Excel file is saved on the server, and its path is constructed.
- The file is saved, and its extension is checked to ensure only Excel files are processed.
Reading Data from Excel
- Using OleDbConnection, we read the content from the first sheet of the uploaded Excel file ([Sheet1$]).
- A DataTable is populated with the data, and additional columns for Status and CreatedDt are added.
Bulk Insertion into Database
- The data from the DataTable is inserted into the ID_Master table using SqlBulkCopy, which is efficient for bulk data insertion.
Error Handling
- Any exceptions during the process (e.g., invalid file format, database errors) are caught and logged.

Handling Errors and Success

If the file is not uploaded correctly or the file type is not supported, the model’s status is updated to reflect the error message.
Upon successful import, a success message is shown to the user.

Conclusion

This implementation allows users to upload an Excel file containing ID card data, which is then processed and inserted into a database using SqlBulkCopy. This approach is efficient and ensures that large datasets are handled seamlessly in ASP.NET MVC applications.