How Can I Speed Up The Loading Process

See more:

C#3.5

i have an excel sheet which have 1 lakh of record in it now i want to load this whole sheeet into postgres database it take lotup time approx 20 to 30 min
i want to reduce time to load into database how can i do this pls suggest me



code is this

public static DataTable GetDataTableExcel(string strFileName, string Table, string sheet, string expProp)
        {
            string date = DateTime.Now.ToString();
            date = date.Replace("/", "").Replace(":", "").Replace(" ", "").ToString();
            string values = string.Empty;
            string Column = string.Empty;
            string strFilename = System.IO.Path.GetFileNameWithoutExtension(strFileName);
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"" + expProp + ";HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + sheet + "]";
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
            System.Data.DataTable ds = new System.Data.DataTable();
            //string DestFileName = "D:\\"+Table;
            adapter.Fill(ds);
            //DataSet DaSe = new DataSet();
            //DaSe.Tables.Add(ds);
            //ExportToCSVFastly(DaSe, DestFileName);
            //Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
            //string ext = Path.GetExtension(strFileName);
            //if (ext.ToLower() == ".xls" || ext.ToLower() == ".xlsx")
            //{
            //    Microsoft.Office.Interop.Excel.Workbook excelBook = excelapp.Workbooks.Open(strFileName);
            //    String[] excelSheets = new String[excelBook.Worksheets.Count];
            //    int n = 0;
            //    foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
            //    {
            //        excelSheets[n] = wSheet.Name;
            //        n++;
            //    }
            //}

            conn.Close();

            string Columnname = string.Empty;
            for (var i = 0; i < ds.Columns.Count; i++)
            {
                //    if (ds.Columns[i].DataType.ToString() == "System.Int32")
                //    {
                //        //Replace(@"([^a-zA-Z0-9_]|^\s)", string.Empty);
                //        Columnname += ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "integer"/* + "," + "new_" + ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "integer"*/ + ",";
                //    }
                //    else if (ds.Columns[i].DataType.ToString() == "System.Double")
                //    {
                //        Columnname += ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "character varying(255)"/* + "," + "new_" + ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "character varying(255)"*/ + ",";
                //    }
                //    else
                //    {
                Columnname += ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty).Replace("?", string.Empty).Replace("@", string.Empty).Replace("&", string.Empty).Replace("$", string.Empty).Replace("%", string.Empty).Replace("!", string.Empty).Replace("/", string.Empty).Replace("-", string.Empty).Replace("~", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty) + " " + "character varying(255)"/* + "," + "new_" + ds.Columns[i].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + " " + "character varying(255*/ + ",";
                //    }
                //    // Column += ds.Columns[i].ToString() + "," +"new_"+ ds.Columns[i].ToString()+",";
            }

            Columnname = Columnname.TrimEnd(',');
            Columnname = Columnname + "," + "pid character varying(255)";
            //string date = DateTime.Now.ToString();
            //date = date.Replace("/", "").Replace(":", "").Replace(" ", "").ToString();
            string sql_Create = "CREATE TABLE " + Table + "(" + Columnname + ")";
            obj.ExecuteNonQuery(sql_Create);
            //string savefilepath = ConfigurationManager.AppSettings["upload_path"] + DateTime.Now.Day.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "_" + DateTime.Now.Second + "_" + DateTime.Now.Millisecond + "_" + DateTime.Now.Ticks + "_Data.csv";
            //System.IO.File.Copy(strFileName, savefilepath);
            //string vFTPLocation = ConfigurationManager.AppSettings["upload_path"] + Path.GetFileName(savefilepath);

            for (var j = 0; j < ds.Columns.Count; j++)
            {
                Column += ds.Columns[j].ToString().Replace(" ", string.Empty).Replace("@", string.Empty).Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim().Replace("/", string.Empty).Replace("-", string.Empty) + ","/* + "new_" + ds.Columns[j].ToString().Replace(" ", string.Empty).Replace("#", string.Empty) + ","*/;
            }

            Column = Column.TrimEnd(',');
            Column = Column + "," + "pid";
            // obj.ExecuteNonQuery("copy " + strFilename + " from '" + vFTPLocation.Replace("\\", "\\\\") + "' csv header");
            // obj.ExecuteNonQuery("copy " + strFilename + "(" + Column + ")  from '" + vFTPLocation.Replace("\\", "\\\\") + "' csv header");
            int counter = 0;
            for (int k = 0; k < ds.Rows.Count; k++)
            {
                counter = counter + 1;
                for (int i = 0; i < ds.Columns.Count; i++)
                {
                    string val = ds.Rows[k][i].ToString();
                    if (val.Contains("'") || val.Contains(@"\"))
                    {
                        val = val.Replace("\"", "").Replace("'", "").Replace(@"\", "-").Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim();
                    }
                    values += "'" + val.Replace("!", string.Empty).Replace("?", string.Empty).Replace("#", string.Empty).Replace("$", string.Empty).Replace("^", string.Empty).Replace("%", string.Empty).Replace("&", string.Empty).Replace("*", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty).Replace("=", string.Empty).Replace("+", string.Empty).Replace(":", string.Empty).Replace("'", string.Empty).Replace("[", string.Empty).Replace("]", string.Empty).Replace("{", string.Empty).Replace("}", string.Empty).Replace("`", string.Empty).Replace("~", string.Empty).Replace("<", string.Empty).Replace(">", string.Empty).Replace("|", string.Empty).Trim() +"'" + ","/* + "'" + val + "'" + ","*/;
                }
                Column = Column.TrimEnd(',');
                values = values.TrimEnd(',');
                values = values + "," + counter;
                obj.ExecuteNonQuery("insert into " + Table + "(" + Column + ") values(" + values + ")");
                values = string.Empty;
            }
            return ds;
        }