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();
}
}
