internal static string GetUpdateCommandText(string key, DbEntity dbEntity, IEnumerable <ColumnField> fields)
{
ThrowIfNoColumns(fields);
var columns = fields.SelectList(field => field.Name);
columns.Add("IsActive");
DbConst.InsertProperties.Each(column => columns.Remove(column));
var primaryKeys = dbEntity.PrimaryKeys.Split(',');
primaryKeys.Each(primaryKey => columns.Remove(primaryKey));
var builder = new StringBuilder(string.Format("UPDATE {0}.{1} SET ", dbEntity.Schema, dbEntity.Name));
builder.Append(string.Join(",", columns.Select(column => string.Format("[{0}]=@{0}", column))).Replace("@IsActive", "1"));
builder.Append(" WHERE ");
builder.Append(string.Join(" AND ", primaryKeys.Select(primaryKey => string.Format("[{0}]=@{0}", primaryKey))));
return(CacheCommandText(key, builder.ToString()));
}
internal static string GetValuesCommandText(string key, DbEntity dbEntity, string column, IEnumerable <string> fields, string[] conditions, string orderBy)
{
var builder = new StringBuilder();
builder.AppendFormat("SELECT {0} FROM {1}.{2} ", column, dbEntity.Schema, dbEntity.Name);
builder.Append(dbEntity.Type == DbEntityType.Table ? "WHERE IsActive=1 " : "WHERE 1=1 ");
if (conditions != null && conditions.Length > 0)
{
builder.Append(string.Join(" ", conditions.Select(condition => $"AND {condition}")));
}
else
{
builder.Append(string.Join(" ", fields.Select(field => string.Format("AND [{0}]=@{0}", field))));
}
if (!string.IsNullOrEmpty(orderBy))
{
builder.Append(string.Format(" ORDER BY {0}", orderBy));
}
return(CacheCommandText(key, builder.ToString()));
}
internal static string GetMultiQueryCommandText(string key, DbEntity dbEntity, IEnumerable <ColumnField> fields, string[] conditions, string orderBy, bool activeOnly)
{
var builder = new StringBuilder();
builder.AppendFormat("SELECT * FROM {0}.{1} ", dbEntity.Schema, dbEntity.Name);
builder.Append(dbEntity.Type == DbEntityType.Table && activeOnly ? "WHERE IsActive=1 " : "WHERE 1=1 ");
if (conditions != null && conditions.Length > 0)
{
builder.Append(string.Join(" ", conditions.Select(condition => $"AND {condition}")));
}
else
{
builder.Append(string.Join(" ", fields.Select(field => $"AND { BuildMultiQueryCondition(field)}")));
}
if (!string.IsNullOrEmpty(orderBy))
{
builder.Append(string.Format(" ORDER BY {0}", orderBy));
}
return(CacheCommandText(key, builder.ToString()));
}
internal static string GetSplitQueryCommandText(string tableName, string column, DbEntity dbEntity, IEnumerable <ColumnField> fields, string[] conditions, string orderBy, bool activeOnly)
{
var builder = new StringBuilder();
builder.AppendFormat("SELECT T.* FROM {0} AS T ", dbEntity.FullName);
builder.AppendFormat("INNER JOIN {0} AS S ", tableName);
builder.AppendFormat("ON T.{0}=S.Item ", column);
builder.AppendFormat(dbEntity.Type == DbEntityType.Table && activeOnly ? "WHERE T.IsActive=1 " : "WHERE 1=1 ");
if (conditions != null && conditions.Length > 0)
{
builder.Append(string.Join(" ", conditions.Select(condition => $"AND T.{condition}")));
}
else
{
builder.Append(string.Join(" ", fields.Select(field => $"AND { BuildMultiQueryCondition("T", field)}")));
}
if (!string.IsNullOrEmpty(orderBy))
{
builder.Append(string.Format(" ORDER BY T.{0}", orderBy));
}
builder.AppendFormat(";DROP TABLE {0};", tableName);
return(builder.ToString());
}