public int? FindUserActivityDTOPageIndex([CanBeNull] UserActivityDTOFinder f, int pageSize, int useractivityid_, string orderBy = null)
{
if (string.IsNullOrEmpty(orderBy)) orderBy = "[UserActivityId]";
using (SqlConnection con = new SqlConnection(ConnectionString))
using (SqlCommand com = PrepareCommand(con))
{
string whereClause = GetWhereClause(f, com);
if (f == null) f = new UserActivityDTOFinder();
com.CommandText = string.Format(@"select r/@pageSize from (
select *, cast(row_number() over(order by {0}) as int) r
from dbo.[UserActivities] ua
{1}
)t where t.[UserActivityId]=@UserActivityId", orderBy, whereClause);
com.Parameters.AddWithValue("UserActivityId", useractivityid_);
com.Parameters.AddWithValue("pageSize", pageSize);
object o = com.ExecuteScalar();
return o==null? (int?)null: (int?)(int)o;
}
}
static partial void GetWhereClauseAddon(UserActivityDTOFinder f, List<string> wheres, SqlCommand com)
{
if (f.UserIdNotIn != null && f.UserIdNotIn.Count > 0)
wheres.Add(@" ua.UserId not in (" + string.Join(", ", f.UserIdNotIn) + ") ");
}
public List<UserActivityDTO> FindUserActivityDTOs([CanBeNull] UserActivityDTOFinder f, string orderBy = null, int startIndex = 0, int count = int.MaxValue)
{
if (string.IsNullOrEmpty(orderBy)) orderBy = "[UserActivityId]";
using (SqlConnection con = new SqlConnection(ConnectionString))
using (SqlCommand com = PrepareCommand(con))
{
string whereClause = GetWhereClause(f, com);
if(f==null) f = new UserActivityDTOFinder();
com.CommandText = string.Format(startIndex == 0 ? count == int.MaxValue
? @" select ua.* from dbo.[UserActivities] ua {1} order by {0} "
: @" select top " + count + @" ua.* from dbo.[UserActivities] ua {1} order by {0} "
: @" select * from (
select ua.*, row_number() over(order by {0}) r from dbo.[UserActivities] ua
{1}
) t where r >= @startIndex and r < @startIndex + @count
order by {0}", orderBy, whereClause);
com.Parameters.AddWithValue("startIndex", startIndex + 1);
com.Parameters.AddWithValue("count", count);
List<UserActivityDTO> rv = ExecUserActivityDTOList(com);
return rv;
}
}
