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);
}
private static void AddWhere(
string tableName,
SqlUpdateOrInsert sqlStatement,
string columnName,
string value)
{
sqlStatement.SqlWhereCollection.Add(
new SqlWhere(new string[] { "[{0}].[{1}]".Params(tableName, columnName) },
columnName, value));
}
private static void AddParam_NoUpdate(
string tableName,
SqlUpdateOrInsert sqlStatement,
string columnName,
string value)
{
sqlStatement.SqlParamCollection.Add(
new SqlParam("[{0}].[{1}]".Params(tableName, columnName),
columnName, value, updating: false));
}
private static void AddParam_Hash(
string tableName,
SqlUpdateOrInsert sqlStatement,
string columnName,
string value)
{
sqlStatement.SqlParamCollection.Add(
new SqlParam("[{0}].[{1}]".Params(tableName, columnName),
columnName,
value.Sha512Cng()));
}
private static void AddParam_DateTime(
string tableName,
SqlUpdateOrInsert sqlStatement,
string columnName,
string value)
{
sqlStatement.SqlParamCollection.Add(
new SqlParam("[{0}].[{1}]".Params(tableName, columnName),
columnName,
TimeZoneInfo.ConvertTime(
DateTime.FromOADate(double.Parse(value)),
Environments.TimeZoneInfoDefault,
Environments.RdsTimeZoneInfo).ToString()));
}
private static void AddParam(
string tableName,
XlsRow xlsRow,
SqlUpdateOrInsert sqlStatement,
string columnName)
{
var value = xlsRow[columnName].ToString();
if (!value.IsNullOrEmpty())
{
if (value == "''")
{
value = string.Empty;
}
if (IsUniqueColumn(tableName, columnName))
{
AddParam_NoUpdate(tableName, sqlStatement, columnName, value);
AddWhere(tableName, sqlStatement, columnName, value);
}
else if (IsPkColumn(tableName, columnName))
{
AddParam_NoUpdate(tableName, sqlStatement, columnName, value);
if (!HasUniqueColumn(tableName))
{
AddWhere(tableName, sqlStatement, columnName, value);
}
}
else if (IsIdentityColumn(tableName, columnName))
{
AddParam_NoUpdate(tableName, sqlStatement, columnName, value);
}
else if (IsHashColumn(tableName, columnName))
{
AddParam_Hash(tableName, sqlStatement, columnName, value);
}
else if (IsDateTimeColumn(tableName, columnName))
{
AddParam_DateTime(tableName, sqlStatement, columnName, value);
}
else
{
AddParam_General(tableName, sqlStatement, columnName, value);
}
}
}