public ActionResult DeleteMapping(ExcelSettingVM vm) { #region 從暫存 Session ExcelMappings 刪除 List <tblExcelMapping> ExcelMappings = new List <tblExcelMapping>();; string ColumnName = string.Empty; try { ExcelMappings = JsonConvert.DeserializeObject <List <tblExcelMapping> >(Cache.GetCache("ExcelMappings")); } catch { } tblExcelMapping mapping = ExcelMappings.Find(x => x.ColumnName.Equals(vm.ColumnName, StringComparison.OrdinalIgnoreCase)); if (mapping != null) { ColumnName = mapping.FieldName; ExcelMappings.Remove(mapping); } Cache.SetLimitedCache("ExcelMappings", ExcelMappings); #endregion #region 從暫存 Session ExcelsetedColumns 刪除 List <tblSQLColumns> ExcelsetedColumns = new List <tblSQLColumns>();; try { ExcelsetedColumns = JsonConvert.DeserializeObject <List <tblSQLColumns> >(Cache.GetCache("ExcelsetedColumns")); } catch { } tblSQLColumns column = ExcelsetedColumns.Find(x => x.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase)); if (column != null) { ExcelsetedColumns.Remove(column); } Cache.SetLimitedCache("ExcelsetedColumns", ExcelsetedColumns); #endregion var options = new StringBuilder(); options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository()) { List <tblSQLColumns> allColumns = rep.getAllColumns(vm.SQLName).ToList(); IEnumerable <tblSQLColumns> unsetColumns = allColumns.Except(ExcelsetedColumns, new ColumnComparer()); foreach (var c in unsetColumns) { options.AppendFormat("<option value='{0}'>{1}</option>", c.ColumnName, c.ColumnName); } } var jsonData = new { Options = options.ToString() }; return(Json(jsonData)); }
public ActionResult InsertMapping(ExcelSettingVM vm) { List <tblExcelMapping> ExcelMappings = new List <tblExcelMapping>(); string ColumnName = string.Empty; #region 寫入暫存 Session ExcelMappings try { ExcelMappings = JsonConvert.DeserializeObject <List <tblExcelMapping> >(Cache.GetCache("ExcelMappings")); } catch { } // 判斷 Column Name 是否有重複 if (string.IsNullOrEmpty(vm.ColumnName) || !vm.ColumnName.Equals(vm.newColumnName, StringComparison.OrdinalIgnoreCase)) { tblExcelMapping map = ExcelMappings.Find(x => x.ColumnName.Equals(vm.newColumnName, StringComparison.OrdinalIgnoreCase)); if (map != null) { var result = new { status = "Column Name 已存在!", }; return(Json(result)); } } // 判斷 X 是否 >0 if (vm.X <= 0) { var result = new { status = "X 起始值為 1", }; return(Json(result)); } else { tblExcelMapping map = ExcelMappings.Find(x => x.ColumnName != vm.ColumnName && x.X == vm.X); if (map != null) { var result = new { status = "X 重複!", }; return(Json(result)); } } // 更新時 將舊暫存資料刪除 tblExcelMapping mapping = ExcelMappings.Find(x => x.ColumnName.Equals(vm.ColumnName, StringComparison.OrdinalIgnoreCase)); if (mapping != null) { ColumnName = mapping.FieldName; ExcelMappings.Remove(mapping); } // 將新的設定值寫入 mapping = new tblExcelMapping() { ColumnName = vm.newColumnName, FieldName = vm.FieldName ?? string.Empty, DefaultValue = vm.DefaultValue, SheetName = vm.SheetName, X = vm.X, DataType = vm.DataType.GetDescription(), NewLineChar = vm.NewLineChar, CanRepeat = vm.CanRepeat }; ExcelMappings.Add(mapping); Cache.SetLimitedCache("ExcelMappings", ExcelMappings); #endregion #region 寫入暫存 Session ExcelsetedColumns List <tblSQLColumns> ExcelsetedColumns = new List <tblSQLColumns>();; try { ExcelsetedColumns = JsonConvert.DeserializeObject <List <tblSQLColumns> >(Cache.GetCache("ExcelsetedColumns")); } catch { } // 更換指定欄位時 if (!ColumnName.Equals(vm.FieldName)) { // 更新時 將原指定的欄位刪除 tblSQLColumns column = ExcelsetedColumns.Find(x => x.ColumnName.Equals(ColumnName, StringComparison.OrdinalIgnoreCase)); if (column != null) { ExcelsetedColumns.Remove(column); } // 記錄 新指定的欄位 if (!string.IsNullOrEmpty(vm.FieldName)) { tblSQLColumns setedColumn = new tblSQLColumns() { SQLName = vm.SQLName, ColumnName = vm.FieldName }; ExcelsetedColumns.Add(setedColumn); } } Cache.SetLimitedCache("ExcelsetedColumns", ExcelsetedColumns); #endregion var options = new StringBuilder(); options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-"); using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository()) { List <tblSQLColumns> allColumns = rep.getAllColumns(vm.SQLName).ToList(); IEnumerable <tblSQLColumns> unsetColumns = allColumns.Except(ExcelsetedColumns, new ColumnComparer()); foreach (var c in unsetColumns) { options.AppendFormat("<option value='{0}'>{1}</option>", c.ColumnName, c.ColumnName); } } var jsonData = new { status = "ok", Options = options.ToString() }; return(Json(jsonData)); }