Excel Upload using ASP.NET MVC

See more:

ASP.NET

MVC4

Hi All,

I want to upload 1Lac accounts from Excel to sql DB.It should upload fast and there should not be any performance issue.So please tell me what can i do to improve my performance for reading from Excel ?

Code:

DataSet ds = new DataSet();
            if (Request.Files["file"].ContentLength > 0)
            {
                string fileExtension =
                                     System.IO.Path.GetExtension(Request.Files["file"].FileName);

                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string fileLocation = Path.Combine(Server.MapPath("~/Input"), Path.GetFileName(FilePath.FileName));
                    if (System.IO.File.Exists(fileLocation))
                    {

                        System.IO.File.Delete(fileLocation);
                    }
                    // File is saving into Input Folder 

                    Request.Files["file"].SaveAs(fileLocation);

                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

                    //connection String for xls file format.
                    if (fileExtension == ".xls")
                    {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }

                    //connection String for xlsx file format.
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }

                    //Create Connection to Excel work book and add oledb namespace
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();

                    string query = string.Format("Select " + FieldName + " from [Sheet1$]");                   

                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection))
                    {
                        dataAdapter.Fill(ds);
                    }

DB:

ALTER PROCEDURE [dbo].[usp_insert_data_to_ATB_Report]
	
	@ATB ATBReport READONLY, // ATBReport is a Table Variable
	@STATUS INT OUTPUT
	
AS
BEGIN

 BEGIN TRY	
	
	
	INSERT INTO TB_IMPORT_MASTER 
	SELECT * FROM @ATB
	
	SELECT @STATUS = 1
	
 END TRY
 
 BEGIN CATCH
 
 SELECT @STATUS = 0
 
 END CATCH;
	
END