public int? FindUserDTOPageIndex([CanBeNull] UserDTOFinder f, int pageSize, int userid_, string orderBy = null)
{
if (string.IsNullOrEmpty(orderBy)) orderBy = "[UserId]";
using (SqlConnection con = new SqlConnection(ConnectionString))
using (SqlCommand com = PrepareCommand(con))
{
string whereClause = GetWhereClause(f, com);
if (f == null) f = new UserDTOFinder();
com.CommandText = string.Format(@"select r/@pageSize from (
select *, cast(row_number() over(order by {0}) as int) r
from dbo.[Users] u
{1}
)t where t.[UserId]=@UserId", orderBy, whereClause);
com.Parameters.AddWithValue("UserId", userid_);
com.Parameters.AddWithValue("pageSize", pageSize);
object o = com.ExecuteScalar();
return o==null? (int?)null: (int?)(int)o;
}
}
static partial void GetWhereClauseAddon(UserDTOFinder f, List<string> wheres, SqlCommand com)
{
if (f.Roles != null && f.Roles.Count > 0 || f.SearchByNullRoles)
{
List<string> w = new List<string>(2);
if (f.Roles != null && f.Roles.Count > 0)
w.Add(string.Format(@" exists(select * from dbo.UsersByRoles ubr where ubr.UserId=u.UserId and ubr.UserRoleId in ({0}) ) ",
string.Join(", ", f.Roles)));
if (f.SearchByNullRoles)
w.Add("not exists(select * from dbo.UsersByRoles ubr where ubr.UserId=u.UserId)");
wheres.Add(string.Join(" or ", w));
}
}
public List<UserDTO> FindUserDTOs([CanBeNull] UserDTOFinder f, string orderBy = null, int startIndex = 0, int count = int.MaxValue, bool needCustomSelect = false)
{
if (string.IsNullOrEmpty(orderBy)) orderBy = "[UserId]";
using (SqlConnection con = new SqlConnection(ConnectionString))
using (SqlCommand com = PrepareCommand(con))
{
string whereClause = GetWhereClause(f, com);
if(f==null) f = new UserDTOFinder();
com.CommandText = string.Format(startIndex == 0 ? count == int.MaxValue
? @" select u.* from dbo.[Users] u {1} order by {0} "
: @" select top " + count + @" u.* from dbo.[Users] u {1} order by {0} "
: @" select * from (
select u.*, row_number() over(order by {0}) r from dbo.[Users] u
{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<UserDTO> rv = ExecUserDTOList(com);
if(needCustomSelect) rv = rv.Select(x => FindUserDTOById(userid_:x.UserId)).ToList();
return rv;
}
}
