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));
}