First I have an Index action method on my server side that is responsible for inserting the data on my Excel table (HISTORY) my database the idea is if file with this name already exists, the Excel file will not be uploaded else it will upload the EXCEL filename on my history and also the excel elements, the students will be uploaded.
Also on the condition count > 0 if true a new modal pop up will show my overwrite modal and when I click the replace button I want to upload the Excel file that supposed to be uploaded but when I call my InsertStudentData again the data is not being inserted.
// GET: Student
public ActionResult Index()
{
var studentList = _studentDAL.GetallStudents();
if (studentList.Count == 0)
{
TempData["InfoMessage"] = "Student data unavailable in the Database.";
}
return View(studentList);
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
if (file != null && file.ContentLength > 0)
{
// get the file name
var fileName = Path.GetFileName(file.FileName);
// store the file name in TempData
TempData["FileName"] = fileName;
// check if a file with this name already exists in the database
using (var cmd = new SqlCommand("SELECT COUNT(*) FROM Excel WHERE FileName = @FileName", con))
{
cmd.Parameters.AddWithValue("@FileName", fileName);
con.Open();
int count = (int)cmd.ExecuteScalar();
con.Close();
// if the count is greater than 0, a file with this name already exists in the database
if (count > 0)
{
ViewBag.FileExists = true;
ViewBag.Message = "A file with this name already exists in the database.";
return View();
}
}
// if the count is 0, save the file and insert the filename into the database
string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
string filepath = "/excelfolder/" + filename;
TempData["FilePath"] = filepath;
file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
InsertStudentdata(filepath, filename);
using (var cmd = new SqlCommand("INSERT INTO Excel (FileName) VALUES (@FileName)", con))
{
cmd.Parameters.AddWithValue("@FileName", fileName);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
int fileID = 0;
using (var cmd = new SqlCommand("SELECT MAX(ID) FROM Excel", con))
{
con.Open();
object result = cmd.ExecuteScalar();
if (result != DBNull.Value)
{
fileID = Convert.ToInt32(result);
}
con.Close();
}
using (var cmd = new SqlCommand("UPDATE Student SET FileID = @fileID WHERE ImportDate = (SELECT MAX(ImportDate) FROM Student)", con))
{
cmd.Parameters.AddWithValue("@FileID", fileID);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
var studentList = _studentDAL.GetallStudents();
if (studentList.Count == 0)
{
TempData["InfoMessage"] = "Student data unavailable in the Database.";
}
using (SqlConnection connection = new SqlConnection(conString))
{
connection.Open();
using (SqlCommand command2 = new SqlCommand("UpdateMclStat", connection))
{
command2.CommandType = CommandType.StoredProcedure;
command2.ExecuteNonQuery();
}
}
return View(studentList);
}
[HttpPost]
public ActionResult Overwrite()
{
// retrieve the file name from TempData
var fileName = TempData["FileName"] as string;
var filepath = TempData["FilePath"] as string;
// get the ID of the Excel file with the same filename
int fileId = 0;
using (var cmd = new SqlCommand("SELECT TOP 1 ID FROM Excel WHERE FileName = @FileName ORDER BY ID DESC", con))
{
cmd.Parameters.AddWithValue("@FileName", fileName);
con.Open();
object result = cmd.ExecuteScalar();
if (result != DBNull.Value)
{
fileId = Convert.ToInt32(result);
}
con.Close();
}
// delete all records from the Student table where FileID matches the Excel file ID
using (var cmd = new SqlCommand("DELETE FROM Student WHERE FileID = @FileID", con))
{
cmd.Parameters.AddWithValue("@FileID", fileId);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
return Json(new { success = true });
}
First modal that accepts the Excel file:
<form method="post" enctype="multipart/form-data">
<div style="margin-bottom:20px;">
<div style="display: flex; justify-content: flex-end;">
<input name="file" type="file" required style="background-color: lightgray; margin-right: 10px;" />
<button id="importBtn" type="button" class="btn btn-primary" style="height: 34px; line-height: 34px;">Import</button>
</div>
<div id="confirmationModal" class="modal">
<div class="modal-content ">
<p>Are you sure you want to import?</p>
<div class="modal-footer">
<button id="yesBtn" type="submit" class="btn btn-primary">Yes</button>
<button id="noBtn" type="button" class="btn btn-secondary">No</button>
</div>
</div>
</div>
</div>
</form>
<script src="~/Scripts/CustomJS/ModalImport.js"></script>
The overwrite modal that shows the replace button:
<form id="myForm" method="post" action="@Url.Action("Index", "Student")">
<!-- Add a hidden input field to store the file name -->
<input type="hidden" name="filename" value="@ViewBag.FileName" />
@if (ViewBag.FileExists == true)
{
<!-- File exists modal -->
<div class="modal" id="file-exists-modal" tabindex="-1" role="dialog" style="display: block; background: rgba(0,0,0,0.5);">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Overwrite</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<p>A file with the same name already exists.</p>
</div>
<div class="modal-footer">
<!-- Add the value "replace" to the "action" parameter when the "Replace" button is clicked -->
<button id="replaceButton" type="submit" name="action" value="replace">Replace</button>
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
}
</form>
<script>
$('#replaceButton').click(function (event) {
event.preventDefault(); // Prevent default form submission behavior
$.ajax({
type: 'POST',
url: '/Student/OverWrite',
success: function (data) {
// Reload the page to update the table
location.reload();
},
error: function () {
alert('An error occurred while deleting the data.');
}
});
});
</script>
The InsertStudentdata method is responsible for inserting the data from the Excel file into the database:
private void InsertStudentdata(string fileepath, string filename)
{
string fullpath = Server.MapPath("/excelfolder/") + filename;
ExcelConn(fullpath);
string query = string.Format("Select * from [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(query, Econ);
Econ.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable dt = ds.Tables[0];
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "dbo.Student";
objbulk.ColumnMappings.Add("StudentID", "StudentID");
objbulk.ColumnMappings.Add("FirstName", "FirstName");
objbulk.ColumnMappings.Add("LastName", "LastName");
objbulk.ColumnMappings.Add("College", "College");
objbulk.ColumnMappings.Add("Program", "Program");
objbulk.ColumnMappings.Add("YearGraduate", "YearGraduate");
objbulk.ColumnMappings.Add("DateExam", "DateExam");
objbulk.ColumnMappings.Add("TakeNumber", "TakeNumber");
objbulk.ColumnMappings.Add("BoardScore", "BoardScore");
objbulk.ColumnMappings.Add("FileID", "FileID");
objbulk.ColumnMappings.Add("ImportDate", "ImportDate");
con.Open();
objbulk.WriteToServer(dt);
con.Close();
}
