protected void Button1_Click(object sender, EventArgs e)
{
string fileName = FileUpload1.ResolveClientUrl(FileUpload1.PostedFile.FileName);
int count = 0;
StudentOrganizationDBClassDataContext dataContext = new StudentOrganizationDBClassDataContext();
try
{
DataTable dtExcel = new DataTable();
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);
string query = "Select * from [Sheet1$]";
OleDbDataAdapter data = new OleDbDataAdapter(query, con);
data.Fill(dtExcel);
for (int i = 1; i < dtExcel.Rows.Count; i++)
{
try
{
string lastName = dtExcel.Rows[i][1].ToString();
string firstName = dtExcel.Rows[i][0].ToString();
string email = dtExcel.Rows[i][2].ToString();
string phoneNumber = dtExcel.Rows[i][3].ToString();
bool offCampus;
if (dtExcel.Rows[i][4].ToString() == "0")
{
offCampus = false;
}
else
{
offCampus = true;
}
//count += dataContext.sp_NewAdvisor(lastName, firstName, email, phoneNumber, offCampus);
//dataContext.SubmitChanges();
}
catch (Exception ex)
{
continue;
}
}
if (count == dtExcel.Rows.Count)
{
lblMessage.Text = "Success";
}
else
{
lblMessage.Text = "failure";
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string fileName = FileUpload1.ResolveClientUrl(FileUpload1.PostedFile.FileName);
int count = 0;
SqlConnection conS = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DataConnect"].ConnectionString);
try
{
DataTable dtExcel = new DataTable();
string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Maharshi\Documents\CityDB.accdb;Persist Security Info=False;";
OleDbConnection con = new OleDbConnection(SourceConstr);
string query = "Select * from CityTb";
OleDbDataAdapter data = new OleDbDataAdapter(query, con);
data.Fill(dtExcel);
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
try
{
string q = "insert into City_master(State,City) values('" + dtExcel.Rows[i]["city_state"].ToString() + "','" + dtExcel.Rows[i]["city_name"].ToString() + "')";
SqlCommand cmd = new SqlCommand(q, conS);
conS.Open();
cmd.ExecuteNonQuery();
conS.Close();
}
catch (Exception ex)
{
continue;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conS.Close();
}
}
protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
{
//try
//{
// objBL_Mac = new BL_Machine();
// objDL_Mac = new DL_Machine();
// //string connString = "";
// //
// //string FullPath = "C:\\Users\\System06\\Desktop\\Machinecode.xlsx";
// if (FileUpload1.HasFile)
// {
// string FullPath = Server.MapPath(FileUpload1.FileName);
// string MyPath = System.IO.Path.GetDirectoryName(FullPath);
// string myFilename = System.IO.Path.GetFileName(FullPath);
// string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FullPath +
// ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
// string query = "SELECT [Machine Code] FROM [Sheet1$]";
// OleDbConnection conn = new OleDbConnection(connString);
// //if (conn.State == ConnectionState.Closed)
// OleDbCommand cmd = new OleDbCommand(query, conn);
// conn.Open();
// OleDbDataAdapter da = new OleDbDataAdapter(cmd);
// DataSet ds = new DataSet();
// da.Fill(ds);
// objBL_Mac.Mode = "ExcelUpload";
// objBL_Mac.ByExcel = ds.GetXml();
// grdMac.DataSource = ds.Tables[0];
// grdMac.DataBind();
// int SQlResult = objDL_Mac.BulkInsert(objBL_Mac);
// da.Dispose();
// conn.Close();
// conn.Dispose();
// }
// else
// {
// ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Please Select Ofarticle file !');", true);
// }
//}
//catch (Exception ex)
//{
//}
//finally
//{
//}
string fileName = FileUpload1.ResolveClientUrl(FileUpload1.PostedFile.FileName);
int count = 0;
QualitySheetdclassDataContext conLinq = new QualitySheetdclassDataContext("Data Source=HOME;Initial Catalog=DBWIS;Integrated Security=true");
try
{
DataTable dtExcel = new DataTable();
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);
string query = "Select Partno,Description from [Sheet1$]";
OleDbDataAdapter data = new OleDbDataAdapter(query, con);
data.Fill(dtExcel);
for (int i = 1; i < dtExcel.Rows.Count; i++)
{
try
{
count += conLinq.ExecuteCommand("insert into tbl_PartNo(PartNo,Description)values(" + dtExcel.Rows[i]["PartNo"] + "," + dtExcel.Rows[i]["Description"] + ") ");
}
catch (Exception ex)
{
continue;
}
}
if (count == dtExcel.Rows.Count)
{
}
else
{
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conLinq.Dispose();
}
}
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();
}
}
