private static void SetColumnDefinitionAdditional(XlsIo definitionFile)
{
var tableCopy = definitionFile.XlsSheet.ToList <XlsRow>();
var sheet = definitionFile.XlsSheet;
definitionFile.XlsSheet.Select(o => new
{
ModelName = o["ModelName"].ToStr(),
TableName = o["TableName"].ToStr(),
Label = o["Label"].ToStr(),
Base = o["Base"].ToBool()
})
.Where(o => !o.Base && o.TableName != "string")
.Distinct()
.ForEach(column =>
sheet.Where(o => o["Base"].ToBool()).ForEach(commonColumnDefinition =>
{
if (IsTargetColumn(sheet, commonColumnDefinition, column.TableName) &&
IsNotExists(tableCopy, commonColumnDefinition, column.TableName))
{
var copyColumnDefinition = new XlsRow();
definitionFile.XlsSheet.Columns.ForEach(xcolumn =>
copyColumnDefinition[xcolumn] = commonColumnDefinition[xcolumn]);
copyColumnDefinition["Id"] =
column.TableName + "_" + copyColumnDefinition["ColumnName"];
copyColumnDefinition["ModelName"] = column.ModelName;
copyColumnDefinition["TableName"] = column.TableName;
copyColumnDefinition["Label"] = column.Label;
copyColumnDefinition["Base"] = "0";
copyColumnDefinition["ItemId"] = "0";
tableCopy.Add(copyColumnDefinition);
}
}));
definitionFile.XlsSheet = new XlsSheet(tableCopy, definitionFile.XlsSheet.Columns);
}
private static void SetSqlIo(string tableName, XlsIo xlsIo, SqlIo sqlIo)
{
SetIdentityInsert(tableName, sqlIo, identityInsert: true);
xlsIo.XlsSheet.ForEach(xlsRow =>
sqlIo.AddSqlStatement(SqlStatement(tableName, xlsIo, xlsRow)));
SetIdentityInsert(tableName, sqlIo, identityInsert: false);
}
private static void UpdateOrInsertToTable(string tableName, XlsIo xlsIo)
{
var sqlIo = Def.SqlIoByAdmin();
SetSqlIo(tableName, xlsIo, sqlIo);
sqlIo.ExecuteNonQuery();
}
private static SqlUpdateOrInsert SqlStatement(
string tableName, XlsIo xlsIo, XlsRow xlsRow)
{
var sqlStatement = new SqlUpdateOrInsert();
sqlStatement.TableBracket = "[" + tableName + "]";
xlsIo.XlsSheet.Columns.ForEach(xlsColumn =>
AddParam(tableName, xlsRow, sqlStatement, xlsColumn));
return(sqlStatement);
}
public static XlsIo DefinitionFile(string fileName)
{
var tempFile = new FileInfo(Files.CopyToTemp(
Directories.Definitions(fileName), Directories.Temp()));
var xlsIo = new XlsIo(tempFile.FullName);
tempFile.Delete();
if (fileName == "definition_Column.xlsm")
{
SetColumnDefinitionAdditional(xlsIo);
}
return(xlsIo);
}
