Click here to Skip to main content
14,699,489 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 26-Oct-15 23:52pm
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900