예제 #1
0
        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());
        }