public ActionResult ImportExcel(string TableName)
{
if (Request.Files["FileUpload1"].ContentLength > 0)
{
string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
string connString = "";
DataTable dt = new DataTable();
string[] validFileTypes = { ".xls", ".xlsx", ".csv" };
string path1 = string.Format("{0}/{1}", Server.MapPath("~/ExcelData/Uploads"), Request.Files["FileUpload1"].FileName);
if (!Directory.Exists(path1)) // if upload folder path does not exist, create one.
{
Directory.CreateDirectory(Server.MapPath("~/ExcelData/Uploads"));
}
if (validFileTypes.Contains(extension))
{
if (System.IO.File.Exists(path1))
{
System.IO.File.Delete(path1); // if file exist previously, delete previous one
}
Request.Files["FileUpload1"].SaveAs(path1);
//add different connection string for different types of excel
if (extension == ".csv")
{
dt = XmlUtil.ConvertCSVtoDataTable(path1);
}
//Connection String to Excel Workbook
else if (extension.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
dt = XmlUtil.ConvertXSLXtoDataTable(path1, connString);
}
else if (extension.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
dt = XmlUtil.ConvertXSLXtoDataTable(path1, connString);
}
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestCoreConnectString"].ConnectionString))
{
//int count = GetTableStructure(con, TableName).Count;
var tablestructure = GetTableStructure(con, TableName);
var count = tablestructure.Count;
string[] columnNames = dt.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
//foreach (var col in columnNames)
//{
// if (!tablestructure.Keys.Contains(col.ToString()))
// {
// dt.Columns.Remove(col.ToString());
// }
//}
try
{
//string clientTableName = GetXmlTableNameList().Where(p => string.Equals(p.TableName, TableName)).FirstOrDefault().ClientTableName;
string clientTableName = TableName;
using (SqlTransaction transaction = con.BeginTransaction())
{
SqlBulkCopy sbc = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, transaction);
sbc.DestinationTableName = TableName;
////dt.Columns.Add("CreatedDate");
//dt.Columns.Add(new DataColumn("CreatedDate", typeof(DateTime)));
//dt.Columns.Add(new DataColumn("CreatedBy", typeof(string)));
//foreach (DataRow row in dt.Rows)
//{
// row["CreatedDate"] = DateTime.Now;
// row["CreatedBy"] = Session["Username"].ToString();
//}
if (!TableName.Equals("Course"))
{
dt = ModifyDataTable(dt);
}
if (count == (dt.Columns.Count + 1))
{
//dt.Columns.Add(new DataColumn("GCMID", typeof(int)));
foreach (DataColumn col in dt.Columns)
{
sbc.ColumnMappings.Add(col.ColumnName.ToString(), col.ColumnName.ToString());
//count++;
}
ModuleManager <GBS_CATEGORY_MATRIX> courseManager = new ModuleManager <GBS_CATEGORY_MATRIX>();
sbc.BatchSize = 1000;
sbc.NotifyAfter = 1000;
sbc.WriteToServer(dt);
transaction.Commit();
var dataInDatabase = courseManager.GetAllRestricted().ToList();
var courseList = CodeUtil.ToList <GBS_CATEGORY_MATRIX>(dt);
//List<Course> courseList = new List<Course>();
//courseList = courseManager.ConvertDataTable<Course>(dt);
//var courseList = (from DataRow dr in dt.Rows
// select new Course()
// {
// CourseID = Convert.ToInt32(dr["CourseID"]),
// Credits= Convert.ToInt32(dr["Credits"]),
// Title = dr["Title"].ToString()
// }).ToList();
//foreach (var course in courseList)
//{
// if (!dataInDatabase.Any(p=>p.Equals(course)))
// {
// ModelState.AddModelError("", "Following row insert failed, Credits:" + course.Credits + "Title:" + course.Title);
// logger.Error("Following row insert failed, Credits:" + course.Credits + "Title: " + course.Title);
// }
//}
foreach (var course in courseList)
{
if (!dataInDatabase.Any(p => CodeUtil.IfEqual <GBS_CATEGORY_MATRIX>(p, course)))
{
ModelState.AddModelError("", "Following row insert failed, SUB_sub_category_NAME:" + course.SUB_sub_category_NAME.ToString() + "Category_name:" + course.Category_name.ToString());
logger.Error("Following row insert failed, SUB_sub_category_NAME:" + course.SUB_sub_category_NAME.ToString() + "Category_name:" + course.Category_name.ToString());
}
}
//foreach (DataRow data in dt.Rows)
//{
// int Credits = Convert.ToInt32(data["Credits"]);
// string title = data["Title"].ToString();
// if (!dataInDatabase.Any(p => (p.Credits == Convert.ToInt32(data["Credits"])) && (p.Title.Equals(data["Title"].ToString()))))
// {
// ModelState.AddModelError("", "Following row insert failed, Credits:" + data["Credits"].ToString() + "Title:" + data["Title"].ToString());
// logger.Error("Following row insert failed, Credits:" + data["Credits"].ToString() + "Title:" + data["Title"].ToString());
// }
//}
tblEducation tblHistory = new tblEducation()
{
//CreatedBy = Session["Username"].ToString(),
CreatedDate = DateTime.Now,
TableName = TableName,
ClientTableName = clientTableName
};
excelHistoryManager.Insert(tblHistory);
//Data has been stored to respective table.
TempData["Notification"] = "Data of '" + clientTableName + "' has been stored to respective table.";
//log.Info("Data of '" + clientTableName + "' has been stored to respective table. Table Name is " + TableName);
return(RedirectToAction("ImportExcel"));
}
else
{
ModelState.AddModelError("", "Invalid Excel Format");
//log.Error("Invalid XML Schema");
List <XmlTable> xmlTableNames = GetXmlTableNameList();
ViewBag.XmlTable = new SelectList(xmlTableNames, "TableName", "ClientTableName");
return(View());
}
}
}
catch (Exception ex)
{
//if error occurred
ModelState.AddModelError("", ex.Message);
//log.Error(ex.Message);
}
finally
{
//close connection
con.Close();
}
//}
//ModelState.AddModelError("", "Invalid excel File");
////log.Error("Invalid excel File");
}
}
else
{
ModelState.AddModelError("", "Please Upload Files in .xls, .xlsx or .csv format");
////log.Error("File uploaded is not in .xls, .xlsx or.csv format but " + extension + "format");
}
}
List <XmlTable> xmlTableNameList = GetXmlTableNameList();
ViewBag.XmlTable = new SelectList(xmlTableNameList, "TableName", "ClientTableName");
return(View());
}