public DbCommand GetDbCommand()
{
int contentId;
var siteId = 0;
if (ContentId != 0)
{
contentId = ContentId;
}
else
{
siteId = DbConnector.GetSiteId(SiteName);
if (siteId == 0)
{
throw new ApplicationException($"Site '{SiteName}' is not found");
}
contentId = DbConnector.GetContentId(DbConnector.GetSiteId(SiteName), ContentName);
if (contentId == 0)
{
throw new ApplicationException($"Content '{SiteName}.{ContentName}' is not found");
}
}
var select = GetSqlCommandSelect(contentId);
var from = GetSqlCommandFrom(contentId);
if (UseSecurity)
{
from = from.Replace(InsertKey, DbConnector.GetSecuritySql(contentId, UserId, GroupId, StartLevel, EndLevel));
}
var cmd = DbConnector.CreateDbCommand();
var where = GetSqlCommandWhere(siteId, cmd);
var orderBy = GetSqlCommandOrderBy();
var startRow = StartRowExpression <= 0 ? 1 : StartRowExpression;
var endRow = new long[] { 0, int.MaxValue, int.MaxValue - 1 }.Contains(PageSizeExpression) ? 0 : startRow + PageSizeExpression - 1;
CountSql = $"SELECT cast(COUNT(*) as bigint) FROM {from} WHERE {where}";
var sb = new StringBuilder();
sb.AppendLine($@"SELECT {select} FROM {from} WHERE {where} ORDER BY {orderBy}");
if (endRow > 0 || startRow > 1)
{
cmd.Parameters.AddWithValue("@startRow", startRow - 1);
if (endRow != int.MaxValue)
{
cmd.Parameters.AddWithValue("@endRow", endRow);
sb.AppendLine(DbConnector.DatabaseType == DatabaseType.SqlServer ?
@"OFFSET @startRow ROWS FETCH NEXT @endRow - @startRow ROWS ONLY" :
@"LIMIT @endRow - @startRow OFFSET @startRow"
);
}
else
{
if (startRow > 1)
{
sb.AppendLine(DbConnector.DatabaseType == DatabaseType.SqlServer ?
@"OFFSET @startRow ROWS" : @"OFFSET @startRow"
);
}
}
}
if (Parameters != null)
{
foreach (var param in Parameters)
{
cmd.Parameters.Add(param);
}
}
cmd.CommandText = sb.ToString();
return(cmd);
}
