protected void ExcelImportWizard_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
if (!CustomValidatorValidateMapping.IsValid)
{
return;
}
string appendMode = ViewState["AppendMode"].ToString();
Dictionary <string, string> columnMapping = new Dictionary <string, string>();
foreach (GridViewRow row in dgvColumns.Rows)
{
string excelColumn = row.Cells[0].Text;
string dbColumn = (row.Cells[1].Controls[1] as DropDownList).SelectedValue;
if (dbColumn.Equals(ExcelUtils.IGNORE_COLUMN_VALUE, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
columnMapping.Add(dbColumn, excelColumn);
}
MemoryStream stream = new MemoryStream(ViewState["Stream"] as byte[]);
DataTable dt;
using (ExcelPackage spreadSheetDocument = new ExcelPackage(stream))
{
dt = ExcelUtils.GetDataTableFromExcel(spreadSheetDocument, true);
}
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
if (appendMode == "Overwrite")
{
foreach (var sp in context.Species)
{
sp.CharacterStates.Clear();
context.Species.Remove(sp);
}
foreach (var cs in context.CharacterStates)
{
cs.DisabledCharacters.Clear();
}
foreach (var st in context.CharacterStates)
{
context.CharacterStates.Remove(st);
}
foreach (var ch in context.Characters)
{
context.Characters.Remove(ch);
}
foreach (var cat in context.CharacterCategories)
{
context.CharacterCategories.Remove(cat);
}
context.SaveChanges();
}
foreach (DataRow dataRow in dt.Rows)
{
string categoryName = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CategoryName, columnMapping).ToString();
string characterCode = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterCode, columnMapping).ToString();
string characterDesc = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterDescription, columnMapping).ToString();
string stateCode = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterStateCode, columnMapping).ToString();
string stateDesc = ExcelUtils.GetExcelRowValueForColumn(dataRow, FriendlyColumnNames.CharacterStateDescription, columnMapping).ToString();
CharacterCategory cat = context.CharacterCategories.SingleOrDefault(i => i.CategoryName == categoryName);
if (cat == null)
{
cat = context.CharacterCategories.Create();
cat.AssignNewdCharacterCategoryId(context);
cat.CategoryName = categoryName;
context.CharacterCategories.Add(cat);
}
Character cha = context.Characters.SingleOrDefault(i => i.CharacterCode == characterCode);
if (cha == null)
{
cha = context.Characters.Create();
cha.AssignNewdCharacterId(context);
cha.CharacterCode = characterCode;
cha.CharacterDescription = characterDesc;
cat.Characters.Add(cha);
CharacterState st = context.CharacterStates.Create();
st.AssignNewCharacterStateId(context);
st.CharacterStateCode = stateCode;
st.CharacterStateDescription = stateDesc;
cha.CharacterStates.Add(st);
}
else
{
CharacterState st = context.CharacterStates.SingleOrDefault(i => i.CharacterStateCode == characterCode);
if (st == null)
{
st = context.CharacterStates.Create();
st.AssignNewCharacterStateId(context);
st.CharacterStateCode = stateCode;
st.CharacterStateDescription = stateDesc;
cha.CharacterStates.Add(st);
}
}
context.SaveChanges();
}
context.SaveChanges();
}
}
