private void PopulateData()
{
using (MuDatabaseEntities dc = new MuDatabaseEntities())
{
gvData.DataSource = dc.EmployeeMasters.ToList();
gvData.DataBind();
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" ||
FileUpload1.PostedFile.ContentType ==
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
try
{
string fileName = Path.Combine(Server.MapPath("~/ImportDocument"),
Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));
FileUpload1.PostedFile.SaveAs(fileName);
string conString = "";
string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
if (ext.ToLower() == ".xls")
{
conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName +
";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;';";
}
else if (ext.ToLower() == ".xlsx")
{
conString = "Provider=Microsoft.ACE.OLEDB.12.0" +
"" +
"" +
"" +
"" +
"" +
"" +
"" +
"" +
"" +
";Data Source=" + fileName +
";Extended Properties='Excel 12.0 xml;HDR=Yes;IMEX=1;';";
}
string query =
"Select [Student ID],[Student Roll],[Student Name],[Department Name],[Student Address],[Contact Title],[Student Email] from [EmployeeData$]";
OleDbConnection con = new OleDbConnection(conString);
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
OleDbCommand cmd = new OleDbCommand(query, con);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
con.Close();
con.Dispose();
// Import to Database
using (MuDatabaseEntities dc = new MuDatabaseEntities())
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string stdID = dr["Student ID"].ToString();
var v = dc.EmployeeMasters.Where(a => a.StudentID.Equals(stdID)).FirstOrDefault();
if (v != null)
{
//update here
v.StudentRoll = dr["Student Roll"].ToString();
v.StudentName = dr["Student Name"].ToString();
v.DepartmentName = dr["Department Name"].ToString();
v.StudentAddress = dr["Student Address"].ToString();
v.ContactTitle = dr["Contact Title"].ToString();
v.StudentEmail = dr["Student Email"].ToString();
}
else
{
// Insert
dc.EmployeeMasters.Add(new EmployeeMaster
{
StudentID = dr["Student ID"].ToString(),
StudentRoll = dr["Student Roll"].ToString(),
StudentName = dr["Student Name"].ToString(),
DepartmentName = dr["Department Name"].ToString(),
StudentAddress = dr["Student Address"].ToString(),
ContactTitle = dr["Contact Title"].ToString(),
StudentEmail = dr["Student Email"].ToString()
});
}
}
dc.SaveChanges();
}
PopulateData();
lblMessage.Text = "Successfully data import done!!!";
}
catch (Exception)
{
throw;
}
}
}
