public void fileuploadfrequency() { SqlConnection conn = new SqlConnection(constr); try { if (FileUpload4.HasFile) { FileUpload4.Dispose(); string fileName = FileUpload4.ResolveClientUrl(FileUpload4.PostedFile.FileName); DataTable datatble = new DataTable(); DataSet ds = new DataSet(); string file = FileUpload4.PostedFile.FileName; string serverpath = Server.MapPath("~/uploads/" + file); if (File.Exists(serverpath)) { File.Delete(serverpath); } FileUpload4.SaveAs(Server.MapPath("~/uploads/" + file)); string filePath = Server.MapPath("~/uploads/" + file); bool hasHeaders = false; string HDR = hasHeaders ? "Yes" : "No"; // string HDR = "Yes"; string strConn; if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx") { strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\""; } else { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\""; } OleDbConnection con = new OleDbConnection(strConn); con.Open(); DataTable schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); DataRow schemaRow = schemaTable.Rows[0]; string sheet = schemaRow["TABLE_NAME"].ToString(); if (!sheet.EndsWith("_")) { string query = "SELECT frequency FROM [" + sheet + "]"; OleDbDataAdapter data = new OleDbDataAdapter(query, con); data.Fill(datatble); DataTable filteredRows = datatble.Rows.Cast <DataRow>() .Where(row => !row.ItemArray.All(field => field is System.DBNull)) .CopyToDataTable(); ds.Tables.Add(filteredRows); } SqlDataAdapter daa1 = new SqlDataAdapter("Select * from frequency ", conn); DataSet dss = new DataSet(); daa1.Fill(dss); if (dss.Tables[0].Rows.Count > 0) { dss.Locale = System.Globalization.CultureInfo.InvariantCulture; SqlCommand cmd1 = new SqlCommand("Truncate table frequency", conn); SqlDataAdapter daa3 = new SqlDataAdapter(cmd1); conn.Open(); cmd1.ExecuteNonQuery(); conn.Close(); } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string frequency = Convert.ToString(ds.Tables[0].Rows[i]["frequency"].ToString()); ds.Locale = System.Globalization.CultureInfo.InvariantCulture; SqlCommand cmd = new SqlCommand("insert into Frequency(frequency)values ('" + frequency + "')", conn); SqlDataAdapter daa = new SqlDataAdapter(cmd); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } con.Close(); ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Frequency File Uploaded Successfully !');", true); } } catch (Exception ex) { ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Invalid file !');", true); } finally { //conn.Close(); FileUpload4.Dispose(); } }