Click here to Skip to main content
14,667,966 members
Rate this:
Please Sign up or sign in to 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 22:52pm
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100