I have a scenario where in the uploaded excel has to be validated across database and invalid data out of excel has to be displayed in grid for user confirmation. On User's confirmation, the only valid data out of excel has to be updated in the database. I'm exactly able to achieve upload, validate, and displaying it in the grid.
Scenario:
Table contains user data such as Name,Designation, and company.
Name field provided in the uploaded excel has to be cross-validated across the database. If anyone of the Names is not available, the data has to be displayed to a user in the grid.
On press of 'Continue' button, the invalid data has to be ignored and only valid data has to be taken for updating the table.
I don't want to read the Uploaded excel twice for this.
There can be huge data available in excel for upload. So this operation has to be faster as possible. Is there a better solution
It sounds like you're trying to determine how to persist the data through a few round trips. First the user uploads it. Then you potentially have to render it back to the user, and by continuing, they effectively have to re-submit it, and on the second submission the valid rows are stored in a table.
I agree that you wouldn't want the user to re-submit the spreadsheet. But all of that data needs to be persisted somewhere so that it can be processed after the user has seen the invalid rows.
I'd consider storing the data in a SQL table immediately after it's uploaded from Excel. Now you can perform validation against the data in the table and mark rows as valid or invalid. If you're just validating that certain data exists in other lookup tables then that validation could be done in bulk by a stored procedure.
Now you've got a set of rows, all with some common key that identifies them as belonging to a particular user operation. Now you can return the invalid rows for the user to confirm that they will be ignored. When the user confirms, you migrate the valid rows to your "real" table and delete all of them. You can also expire those rows after a time. If the user hasn't approved them after a given time then they'll need to upload again.
A benefit of this approach is that you can break this process into distinct, smaller steps which helps to manage complexity. One step performs an initial upload. Another returns a set of invalid rows. Another approves a set of valid rows. Smaller steps makes maintenance and unit testing easier.
Another benefit is that you can decouple the process from an Excel upload. Perhaps at some future point you'll want to handle uploads in XML. Only the first step needs to change. Once your data gets into the table it no longer matters where it comes from.