public void Loadprocess() { objtbl_Process = new Processtble_grid(); dt = new DataTable(); objQualitySheetdclassDataContext = new QualitySheetdclassDataContext(); //var query = (from c in (objQualitySheetdclassDataContext.CycleTimeEntries) select c).ToList(); da = new SqlDataAdapter("select * from Processtble_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 = CurrentPage; ViewState["totalpage"] = paging.PageCount; link_previous.Enabled = !paging.IsFirstPage; link_next.Enabled = !paging.IsLastPage; } else { div_paging.Visible = false; } grid_process.DataSource = paging; grid_process.DataBind(); div_process.Visible = true; div_actualerror.Visible = false; createpaging(); } else { div_process.Visible = false; div_actualerror.Visible = true; } }
public void fileupload1() { SqlConnection conn = new SqlConnection(constr); try { if (FileUpload1.HasFile) { FileUpload1.Dispose(); string fileName = FileUpload1.ResolveClientUrl(FileUpload1.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 Process from [Process$]"; string file = FileUpload1.PostedFile.FileName; string serverpath = Server.MapPath("~/uploads/" + file); if (File.Exists(serverpath)) { File.Delete(serverpath); } FileUpload1.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 Process 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_Process ", 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_Process", conn); SqlDataAdapter daa3 = new SqlDataAdapter(cmd1); conn.Open(); cmd1.ExecuteNonQuery(); conn.Close(); } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string Process = Convert.ToString(ds.Tables[0].Rows[i]["Process"].ToString()); ds.Locale = System.Globalization.CultureInfo.InvariantCulture; SqlCommand cmd = new SqlCommand("insert into tbl_Process(Process)values ('" + Process + "')", conn); SqlDataAdapter daa = new SqlDataAdapter(cmd); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } //objBL_Mac.Mode = "ExcelUpload1"; //grdMac.DataSource = ds.Tables[0]; //grdMac.DataBind(); //DataSet SQlResult = objDL_Mac.BulkInsert1(objBL_Mac); //data.Dispose(); //con.Close(); //con.Dispose(); objtbl_Process = new Processtble_grid(); objtbl_Process.FileUpload = "Process"; objtbl_Process.UserName = HttpContext.Current.Session["User_Name"].ToString(); objtbl_Process.Time = HttpContext.Current.Session["Logtime"].ToString(); objtbl_Process.Date = HttpContext.Current.Session["LogDate"].ToString(); objQualitySheetdclassDataContext.Processtble_grids.InsertOnSubmit(objtbl_Process); objQualitySheetdclassDataContext.SubmitChanges(); objQualitySheetdclassDataContext = null; con.Close(); ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Process File Uploaded Successfully !');", true); Loadprocess(); } } 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(); FileUpload1.Dispose(); } }