public void Loadofarticle() { objofarticle = new ofarticle_grid(); dt = new DataTable(); objQualitySheetdclassDataContext = new QualitySheetdclassDataContext(); //var query = (from c in (objQualitySheetdclassDataContext.CycleTimeEntries) select c).ToList(); da = new SqlDataAdapter("select * from ofarticle_grid", constr); DataSet ds = new DataSet(); da.Fill(dt); da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { paging.DataSource = dt.DefaultView; if (ds.Tables[0].Rows.Count > 8) { paging.AllowPaging = true; paging.PageSize = 8; paging.CurrentPageIndex = CurrentPage1; ViewState["totalpage"] = paging.PageCount; link_previous1.Enabled = !paging.IsFirstPage; link_next1.Enabled = !paging.IsLastPage; } else { div_paging1.Visible = false; } Grid_ofarticle.DataSource = paging; Grid_ofarticle.DataBind(); div_ofarticle.Visible = true; div_actualerror.Visible = false; createpaging1(); } else { div_ofarticle.Visible = false; div_actualerror.Visible = true; } }
public void fileupload2() { SqlConnection conn = new SqlConnection(constr); try { if (FileUpload2.HasFile) { FileUpload2.Dispose(); string fileName = FileUpload2.ResolveClientUrl(FileUpload2.PostedFile.FileName); DataTable datatble = new DataTable(); DataSet ds = new DataSet(); //string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"; //OleDbConnection con = new OleDbConnection(SourceConstr); ////con.Open(); ////string query = "Select Partno,Description from [Sheet1$]"; //string query = "Select * from [ofarticles$]"; //OleDbDataAdapter data = new OleDbDataAdapter(query, SourceConstr); //data.Fill(datatble); //ds.Tables.Add(datatble); //string filename = Path.GetFileName(FileUpload2.PostedFile.FileName); //FileUpload2.SaveAs(Server.MapPath("uploads/" + filename)); //string filepath = "uploads/" + filename; //string file = FileUpload2.PostedFile.FileName; //string serverpath = Server.MapPath("~/uploads/" + file); //if (File.Exists(serverpath)) // File.Delete(serverpath); //FileUpload2.SaveAs(Server.MapPath("~/uploads/" + file)); //string filepath = Server.MapPath("~/uploads/" + file); //DataTable res = ConvertCSVtoDataTable(filepath); //ds.Tables.Add(res); string file = FileUpload2.PostedFile.FileName; string serverpath = Server.MapPath("~/uploads/" + file); if (File.Exists(serverpath)) { File.Delete(serverpath); } FileUpload2.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]; //DataRow schemaRow = schemaTable.Rows[1]; string sheet = schemaRow["TABLE_NAME"].ToString(); if (!sheet.EndsWith("_")) { string query = "SELECT * 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 tbl_PartNo ", 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 tbl_PartNo", conn); SqlDataAdapter daa3 = new SqlDataAdapter(cmd1); conn.Close(); conn.Open(); cmd1.ExecuteNonQuery(); conn.Close(); } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string Partno = Convert.ToString(ds.Tables[0].Rows[i][2].ToString()); string Description = Convert.ToString(ds.Tables[0].Rows[i][3].ToString()); ds.Locale = System.Globalization.CultureInfo.InvariantCulture; SqlCommand cmd = new SqlCommand("insert into tbl_PartNo(Partno,Description)values ('" + Partno + "','" + Description + "')", conn); SqlDataAdapter daa = new SqlDataAdapter(cmd); conn.Close(); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } if (HttpContext.Current.Session["User_Name"].ToString() != "" && HttpContext.Current.Session["User_Name"].ToString() != null) { objofarticle = new ofarticle_grid(); objofarticle.FileUpload = "Part No"; objofarticle.UserName = HttpContext.Current.Session["User_Name"].ToString(); objofarticle.Time = HttpContext.Current.Session["Logtime"].ToString(); objofarticle.Date = HttpContext.Current.Session["LogDate"].ToString(); objQualitySheetdclassDataContext.ofarticle_grids.InsertOnSubmit(objofarticle); objQualitySheetdclassDataContext.SubmitChanges(); objQualitySheetdclassDataContext = null; ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Part No File Uploaded Successfully !');", true); Loadofarticle(); } } } catch (Exception ex) { ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Invalid file !');", true); Exception ex2 = ex; string errorMessage = string.Empty; while (ex2 != null) { errorMessage += ex2.ToString(); ex2 = ex2.InnerException; } HttpContext.Current.Response.Write(errorMessage); } finally { //conn.Close(); FileUpload2.Dispose(); } }