protected virtual string ToSingleSqlString(List <IGrouping <int, DbColumnInfo> > groupList)
{
string columnsString = string.Join(",", groupList.First().Where(it => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Select(it =>
{
if (SetValues.IsValuable())
{
var setValue = SetValues.Where(sv => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName) || sv.Key == Builder.GetTranslationColumnName(it.PropertyName));
if (setValue != null && setValue.Any())
{
return(setValue.First().Value);
}
}
var result = Builder.GetTranslationColumnName(it.DbColumnName) + "=" + this.Context.Ado.SqlParameterKeyWord + it.DbColumnName;
return(result);
}));
string whereString = null;
if (this.WhereValues.HasValue())
{
foreach (var item in WhereValues)
{
var isFirst = whereString == null;
whereString += (isFirst ? " WHERE " : " AND ");
whereString += item;
}
}
else if (PrimaryKeys.HasValue())
{
if (IsWhereColumns == false)
{
foreach (var item in PrimaryKeys)
{
var isFirst = whereString == null;
whereString += (isFirst ? " WHERE " : " AND ");
whereString += Builder.GetTranslationColumnName(item) + "=" + this.Context.Ado.SqlParameterKeyWord + item;
}
}
}
if (PrimaryKeys.HasValue() && IsWhereColumns)
{
foreach (var item in PrimaryKeys)
{
var isFirst = whereString == null;
whereString += (isFirst ? " WHERE " : " AND ");
whereString += Builder.GetTranslationColumnName(item) + "=" + this.Context.Ado.SqlParameterKeyWord + item;
}
}
return(string.Format(SqlTemplate, GetTableNameString, columnsString, whereString));
}
protected virtual string TomultipleSqlString(List <IGrouping <int, DbColumnInfo> > groupList)
{
Check.Exception(PrimaryKeys == null || PrimaryKeys.Count == 0, " Update List<T> need Primary key");
int pageSize = 200;
int pageIndex = 1;
int totalRecord = groupList.Count;
int pageCount = (totalRecord + pageSize - 1) / pageSize;
StringBuilder batchUpdateSql = new StringBuilder();
while (pageCount >= pageIndex)
{
StringBuilder updateTable = new StringBuilder();
string setValues = string.Join(",", groupList.First().Where(it => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Select(it =>
{
if (SetValues.IsValuable())
{
var setValue = SetValues.Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName));
if (setValue != null && setValue.Any())
{
return(setValue.First().Value);
}
}
var result = string.Format("S.{0}=T.{0}", Builder.GetTranslationColumnName(it.DbColumnName));
return(result);
}));
batchUpdateSql.AppendFormat(SqlTemplateBatch.ToString(), setValues, GetTableNameStringNoWith, TableWithString);
int i = 0;
foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
{
var isFirst = i == 0;
if (!isFirst)
{
updateTable.Append(SqlTemplateBatchUnion);
}
updateTable.Append("\r\n SELECT " + string.Join(",", columns.Select(it => string.Format(SqlTemplateBatchSelect, FormatValue(it.Value), Builder.GetTranslationColumnName(it.DbColumnName)))));
++i;
}
pageIndex++;
updateTable.Append("\r\n");
string whereString = null;
if (this.WhereValues.HasValue())
{
foreach (var item in WhereValues)
{
var isFirst = whereString == null;
whereString += (isFirst ? null : " AND ");
whereString += Regex.Replace(item, "\\" + this.Builder.SqlTranslationLeft, "S." + this.Builder.SqlTranslationLeft);
}
}
if (PrimaryKeys.HasValue())
{
foreach (var item in PrimaryKeys)
{
var isFirst = whereString == null;
whereString += (isFirst ? null : " AND ");
whereString += string.Format("S.{0}=T.{0}", Builder.GetTranslationColumnName(item));
}
}
batchUpdateSql.AppendFormat(SqlTemplateJoin, updateTable, whereString);
}
return(batchUpdateSql.ToString());
}
protected override string TomultipleSqlString(List <IGrouping <int, DbColumnInfo> > groupList)
{
Check.Exception(PrimaryKeys == null || PrimaryKeys.Count == 0, " Update List<T> need Primary key");
int pageSize = 200;
int pageIndex = 1;
int totalRecord = groupList.Count;
int pageCount = (totalRecord + pageSize - 1) / pageSize;
StringBuilder batchUpdateSql = new StringBuilder();
while (pageCount >= pageIndex)
{
StringBuilder updateTable = new StringBuilder();
string setValues = string.Join(",", groupList.First().Where(it => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Select(it =>
{
if (SetValues.IsValuable())
{
var setValue = SetValues.Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName));
if (setValue != null && setValue.Any())
{
return(setValue.First().Value);
}
}
var result = string.Format("{0}=T.{0}", Builder.GetTranslationColumnName(it.DbColumnName));
return(result);
}));
string tempColumnValue = string.Join(",", groupList.First().Select(it =>
{
if (SetValues.IsValuable())
{
var setValue = SetValues.Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName));
if (setValue != null && setValue.Any())
{
return(setValue.First().Value);
}
}
var result = Builder.GetTranslationColumnName(it.DbColumnName);
return(result);
}));
batchUpdateSql.AppendFormat(SqlTemplateBatch.ToString(), setValues, GetTableNameStringNoWith, TableWithString);
int i = 0;
var tableColumnList = this.Context.DbMaintenance.GetColumnInfosByTableName(GetTableNameStringNoWith);
foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
{
var isFirst = i == 0;
if (!isFirst)
{
updateTable.Append(SqlTemplateBatchUnion);
}
updateTable.Append("\r\n (" + string.Join(",", columns.Select(it =>
{
var columnInfo = tableColumnList.FirstOrDefault(x => x.DbColumnName.Equals(it.DbColumnName, StringComparison.OrdinalIgnoreCase));
var dbType = columnInfo?.DataType;
if (dbType == null)
{
var typeName = it.PropertyType.Name.ToLower();
if (typeName == "int32")
{
typeName = "int";
}
if (typeName == "int64")
{
typeName = "long";
}
if (typeName == "int16")
{
typeName = "short";
}
var isAnyType = PostgreSQLDbBind.MappingTypesConst.Where(x => x.Value.ToString().ToLower() == typeName).Any();
if (isAnyType)
{
dbType = PostgreSQLDbBind.MappingTypesConst.Where(x => x.Value.ToString().ToLower() == typeName).FirstOrDefault().Key;
}
else
{
dbType = "varchar";
}
}
return(string.Format("CAST({0} AS {1})", FormatValue(it.Value), dbType));
})) + ")");
++i;
}
pageIndex++;
updateTable.Append("\r\n");
string whereString = null;
if (this.WhereValues.HasValue())
{
foreach (var item in WhereValues)
{
var isFirst = whereString == null;
whereString += (isFirst ? null : " AND ");
whereString += item;
}
}
else if (PrimaryKeys.HasValue())
{
foreach (var item in PrimaryKeys)
{
var isFirst = whereString == null;
whereString += (isFirst ? null : " AND ");
whereString += string.Format("{0}.{1}=T.{1}", GetTableNameStringNoWith, Builder.GetTranslationColumnName(item));
}
}
var format = string.Format(SqlTemplateJoin, updateTable, whereString, tempColumnValue);
batchUpdateSql.Replace("${0}", format);
batchUpdateSql.Append(";");
}
return(batchUpdateSql.ToString());
}