public override string ToSqlString()
{
if (IsNoInsertNull)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
}
var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
var isSingle = groupList.Count() == 1;
string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
if (isSingle)
{
string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
}
else
{
StringBuilder batchInsetrSql = new StringBuilder();
int pageSize = 200;
int pageIndex = 1;
int totalRecord = groupList.Count;
int pageCount = (totalRecord + pageSize - 1) / pageSize;
while (pageCount >= pageIndex)
{
batchInsetrSql.AppendFormat(SqlTemplateBatch, GetTableNameString, columnsString);
int i = 0;
foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
{
var isFirst = i == 0;
if (isFirst)
{
batchInsetrSql.Append(SqlTemplateBatchUnion);
}
batchInsetrSql.Append("\r\n ( " + string.Join(",", columns.Select(it =>
{
object value = null;
if (it.Value is DateTime)
{
value = ((DateTime)it.Value).ToString("O");
}
else
{
value = it.Value;
}
if (value == null || value == DBNull.Value)
{
return(string.Format(SqlTemplateBatchSelect, "NULL"));
}
return(string.Format(SqlTemplateBatchSelect, "'" + value.ObjToString().ToSqlFilter() + "'"));
})) + "),");
++i;
}
pageIndex++;
batchInsetrSql.Remove(batchInsetrSql.Length - 1, 1).Append("\r\n;\r\n");
}
return(batchInsetrSql.ToString());
}
}
public virtual string ToSqlString()
{
if (IsNoInsertNull)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
}
var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
var isSingle = groupList.Count() == 1;
string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
if (isSingle)
{
string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
}
else
{
StringBuilder batchInsetrSql = new StringBuilder();
int pageSize = 200;
if (this.EntityInfo.Columns.Count > 30)
{
pageSize = 50;
}
else if (this.EntityInfo.Columns.Count > 20)
{
pageSize = 100;
}
int pageIndex = 1;
int totalRecord = groupList.Count;
int pageCount = (totalRecord + pageSize - 1) / pageSize;
while (pageCount >= pageIndex)
{
batchInsetrSql.AppendFormat(SqlTemplateBatch, GetTableNameString, columnsString);
int i = 0;
foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
{
var isFirst = i == 0;
if (!isFirst)
{
batchInsetrSql.Append(SqlTemplateBatchUnion);
}
batchInsetrSql.Append("\r\n SELECT " + string.Join(",", columns.Select(it => string.Format(SqlTemplateBatchSelect, FormatValue(it.Value), Builder.GetTranslationColumnName(it.DbColumnName)))));
++i;
}
pageIndex++;
batchInsetrSql.Append("\r\n;\r\n");
}
var result = batchInsetrSql.ToString();
if (this.Context.CurrentConnectionConfig.DbType == DbType.SqlServer)
{
result += "select @@identity;";
}
return(result);
}
}
public override string ToSqlString()
{
if (IsNoInsertNull)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
}
var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
var isSingle = groupList.Count() == 1;
string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
if (isSingle)
{
string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
}
else
{
StringBuilder batchInsetrSql = new StringBuilder();
batchInsetrSql.Append("INSERT INTO " + GetTableNameString + " ");
batchInsetrSql.Append("(");
batchInsetrSql.Append(columnsString);
batchInsetrSql.Append(") VALUES");
string insertColumns = "";
int i = 0;
foreach (var item in groupList)
{
batchInsetrSql.Append("(");
insertColumns = string.Join(",", item.Select(it => FormatValue(i, it.DbColumnName, it.Value)));
batchInsetrSql.Append(insertColumns);
if (groupList.Last() == item)
{
batchInsetrSql.Append(") ");
}
else
{
batchInsetrSql.Append("), ");
}
i++;
}
batchInsetrSql.AppendLine(";SELECT LAST_INSERT_ROWID();");
var result = batchInsetrSql.ToString();
return(result);
}
}
public virtual string ToSqlString()
{
if (IsNoUpdateNull)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
}
var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
var isSingle = groupList.Count() == 1;
if (isSingle)
{
return(ToSingleSqlString(groupList));
}
else
{
return(TomultipleSqlString(groupList));
}
}
public virtual string ToSqlString()
{
if (IsNoUpdateNull)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
}
if (IsNoUpdateDefaultValue)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value.ObjToString() != UtilMethods.DefaultForType(it.PropertyType).ObjToString()).ToList();
}
var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
var isSingle = groupList.Count() == 1;
if (isSingle)
{
return(ToSingleSqlString(groupList));
}
else
{
return(TomultipleSqlString(groupList));
}
}
public override string ToSqlString()
{
var identities = this.EntityInfo.Columns.Where(it => it.OracleSequenceName.HasValue()).ToList();
if (IsNoInsertNull)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
}
var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
var isSingle = groupList.Count() == 1;
string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
if (isSingle)
{
string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
if (identities.HasValue())
{
columnsString = columnsString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
columnParametersString = columnParametersString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => it.OracleSequenceName + ".nextval"));
}
return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
}
else
{
StringBuilder batchInsetrSql = new StringBuilder();
int pageSize = 200;
int pageIndex = 1;
int totalRecord = groupList.Count;
int pageCount = (totalRecord + pageSize - 1) / pageSize;
if (identities.HasValue())
{
columnsString = columnsString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
}
while (pageCount >= pageIndex)
{
batchInsetrSql.AppendFormat(SqlTemplateBatch, GetTableNameString, columnsString);
int i = 0;
foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
{
var isFirst = i == 0;
if (!isFirst)
{
batchInsetrSql.Append(SqlTemplateBatchUnion);
}
var insertColumns = string.Join(",", columns.Select(it => string.Format(SqlTemplateBatchSelect, FormatValue(it.Value), Builder.GetTranslationColumnName(it.DbColumnName))));
if (identities.HasValue())
{
insertColumns = insertColumns.TrimEnd(',') + "," + string.Join(",", identities.Select(it =>
{
var seqValue = this.OracleSeqInfoList[it.OracleSequenceName];
this.OracleSeqInfoList[it.OracleSequenceName] = this.OracleSeqInfoList[it.OracleSequenceName] + 1;
return(seqValue + 1 + " AS " + it.DbColumnName);
}));
}
batchInsetrSql.Append("\r\n SELECT " + insertColumns + " FROM DUAL ");
++i;
}
pageIndex++;
batchInsetrSql.Append("\r\n;\r\n");
}
return("BEGIN\r\n" + batchInsetrSql.ToString() + "\r\nEND;");
}
}
public override string ToSqlString()
{
var identities = this.EntityInfo.Columns.Where(it => it.OracleSequenceName.HasValue()).ToList();
if (IsNoInsertNull)
{
DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
}
var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
var isSingle = groupList.Count() == 1;
string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
if (isSingle && this.EntityInfo.EntityName != "Dictionary`2")
{
string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
if (identities.HasValue())
{
columnsString = columnsString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
columnParametersString = columnParametersString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => it.OracleSequenceName + ".nextval"));
}
return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
}
else
{
StringBuilder batchInsetrSql = new StringBuilder();
batchInsetrSql.AppendLine("INSERT ALL");
foreach (var item in groupList)
{
batchInsetrSql.Append("INTO " + GetTableNameString + " ");
string insertColumns = "";
batchInsetrSql.Append("(");
batchInsetrSql.Append(columnsString);
if (identities.HasValue())
{
batchInsetrSql.Append("," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName))));
}
batchInsetrSql.Append(") VALUES");
batchInsetrSql.Append("(");
insertColumns = string.Join(",", item.Select(it => FormatValue(it.Value)));
batchInsetrSql.Append(insertColumns);
if (identities.HasValue())
{
batchInsetrSql.Append(",");
foreach (var idn in identities)
{
var seqvalue = this.OracleSeqInfoList[idn.OracleSequenceName];
this.OracleSeqInfoList[idn.OracleSequenceName] = this.OracleSeqInfoList[idn.OracleSequenceName] + 1;
if (identities.Last() == idn)
{
batchInsetrSql.Append(seqvalue);
}
else
{
batchInsetrSql.Append(seqvalue + ",");
}
}
}
batchInsetrSql.AppendLine(") ");
}
if (identities.HasValue())
{
batchInsetrSql.AppendLine("SELECT " + (this.OracleSeqInfoList.First().Value - 1) + " FROM DUAL");
}
else
{
batchInsetrSql.AppendLine("SELECT 1 FROM DUAL");
}
var result = batchInsetrSql.ToString();
return(result);
}
}
