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.