/// <summary>
/// Gets a page of data from the mp_TaskQueue table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="siteGuid"> guid </param>
public static IDataReader GetPageUnfinishedBySite(
Guid siteGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountUnfinished(siteGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * ");
sqlCommand.Append("FROM mp_TaskQueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("[CompleteUTC] IS NULL ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("[QueuedUTC] DESC ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid;
return(SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPage(
Guid siteGuid,
string status,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteGuid, status);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * ");
sqlCommand.Append("FROM mp_ContentWorkflow ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("[Status] = @Status ");
//sqlCommand.Append("ORDER BY ");
//sqlCommand.Append("DESC ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid;
arParams[1] = new SqlCeParameter("@Status", SqlDbType.NVarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
return(SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPageInfoForPage(
Guid siteGuid,
string status,
int pageNumber,
int pageSize)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") ");
sqlCommand.Append("p.PageID, ");
sqlCommand.Append("p.PageGuid, ");
sqlCommand.Append("p.PageName, ");
sqlCommand.Append("p.UseUrl, ");
sqlCommand.Append("p.Url As PageUrl, ");
sqlCommand.Append("cw.[Guid] As WorkflowGuid ");
sqlCommand.Append("FROM mp_ContentWorkflow cw ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_Modules m ");
sqlCommand.Append("ON ");
sqlCommand.Append("cw.ModuleGuid = m.[Guid] ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("[mp_PageModules] pm ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.ModuleID = m.ModuleID ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("[mp_Pages] p ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.PageID = p.PageID ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("cw.SiteGuid = @SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cw.[Status] = @Status ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("cw.CreatedDateUtc DESC ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid;
arParams[1] = new SqlCeParameter("@Status", SqlDbType.NVarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
return(SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static DataTable GetWebImageByPage(int moduleId, int pageNumber)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_GalleryImages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = @ModuleID ;");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@ModuleID", SqlDbType.Int);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int totalRows = Convert.ToInt32(SqlHelper.ExecuteScalar(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
int pageSize = 1;
int totalPages = totalRows / pageSize;
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder = 0;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
int offset = 0;
if (pageNumber > 1)
{
offset = pageSize * (pageNumber - 1);
}
sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("i.ItemID, ");
sqlCommand.Append("i.ModuleID, ");
sqlCommand.Append(totalPages.ToString(CultureInfo.InvariantCulture) + " As TotalPages ");
sqlCommand.Append("FROM mp_GalleryImages i ");
sqlCommand.Append("WHERE i.ModuleID = @ModuleID ");
sqlCommand.Append("ORDER BY i.DisplayOrder, i.ItemID ");
sqlCommand.Append("OFFSET " + offset.ToString(CultureInfo.InvariantCulture) + " ROWS ");
sqlCommand.Append("FETCH NEXT " + pageSize.ToString(CultureInfo.InvariantCulture) + "ROWS ONLY ");
sqlCommand.Append(" ; ");
arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@ModuleID", SqlDbType.Int);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
DataTable dt = new DataTable();
dt.Columns.Add("ItemID", typeof(int));
//dt.Columns.Add("ModuleID", typeof(int));
//dt.Columns.Add("Caption", typeof(String));
//dt.Columns.Add("ThumbnailFile", typeof(String));
dt.Columns.Add("TotalPages", typeof(int));
using (IDataReader reader = SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams))
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["ItemID"] = reader["ItemID"];
// row["ModuleID"] = reader["ModuleID"];
//row["Caption"] = reader["Caption"];
row["TotalPages"] = reader["TotalPages"];
dt.Rows.Add(row);
}
}
return(dt);
}
public static IDataReader GetWorkInProgress(Guid moduleGuid, string status)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT cw.*, ");
sqlCommand.Append("m.ModuleID, ");
sqlCommand.Append("m.ModuleTitle, ");
sqlCommand.Append("createdBy.[Name] as CreatedByUserName, ");
sqlCommand.Append("createdBy.LoginName as CreatedByUserLogin, ");
sqlCommand.Append("createdBy.Email as CreatedByUserEmail, ");
sqlCommand.Append("createdBy.FirstName as CreatedByFirstName, ");
sqlCommand.Append("createdBy.LastName as CreatedByLastName, ");
sqlCommand.Append("createdBy.UserID as CreatedByUserID, ");
sqlCommand.Append("createdBy.AvatarUrl as CreatedByAvatar, ");
sqlCommand.Append("createdBy.AuthorBio as CreatedByAuthorBio, ");
sqlCommand.Append("modifiedBy.[Name] as ModifiedByUserName, ");
sqlCommand.Append("modifiedBy.FirstName as ModifiedByFirstName, ");
sqlCommand.Append("modifiedBy.LastName as ModifiedByLastName, ");
sqlCommand.Append("modifiedBy.LoginName as ModifiedByUserLogin, ");
sqlCommand.Append("modifiedBy.Email as ModifiedByUserEmail, ");
sqlCommand.Append("cwah.Notes as Notes, ");
sqlCommand.Append("cwah.CreatedDateUtc as RecentActionOn, ");
sqlCommand.Append("recentActionBy.[Name] as RecentActionByUserName,");
sqlCommand.Append("recentActionBy.LoginName as RecentActionByUserLogin, ");
sqlCommand.Append("recentActionBy.Email as RecentActionByUserEmail ");
sqlCommand.Append("FROM mp_ContentWorkflow cw ");
sqlCommand.Append("JOIN mp_Modules m ");
sqlCommand.Append("ON ");
sqlCommand.Append("cw.ModuleGuid = m.[Guid] ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("[mp_Users] createdBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("createdBy.UserGuid = cw.UserGuid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("[mp_Users] modifiedBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("modifiedBy.UserGuid = cw.LastModUserGuid ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("[mp_ContentWorkflowAuditHistory] cwah ");
sqlCommand.Append("ON ");
sqlCommand.Append("cwah.ContentWorkflowGuid = cw.[Guid] ");
sqlCommand.Append("AND ");
sqlCommand.Append("cwah.Active = 1 ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("[mp_Users] recentActionBy ");
sqlCommand.Append("ON ");
sqlCommand.Append("recentActionBy.UserGuid = cwah.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("cw.ModuleGuid = @ModuleGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cw.[Status] = @Status ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("cw.CreatedDateUtc DESC ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@ModuleGuid", SqlDbType.UniqueIdentifier);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid;
arParams[1] = new SqlCeParameter("@Status", SqlDbType.NVarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
return(SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_LetterSubscriber table.
/// </summary>
/// <param name="letterInfoGuid"> letterGuid </param>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
Guid letterInfoGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountByLetter(letterInfoGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") ");
sqlCommand.Append("ls.[Guid], ");
sqlCommand.Append("ls.[SiteGuid], ");
sqlCommand.Append("ls.[LetterInfoGuid], ");
sqlCommand.Append("ls.[UserGuid], ");
sqlCommand.Append("ls.[IsVerified], ");
sqlCommand.Append("ls.[VerifyGuid], ");
sqlCommand.Append("ls.[BeginUtc], ");
sqlCommand.Append("ls.[UseHtml], ");
sqlCommand.Append("ls.[IpAddress], ");
sqlCommand.Append("COALESCE(u.Email, ls.[Email]) As Email, ");
sqlCommand.Append("u.[Email] AS UserEmail, ");
sqlCommand.Append("COALESCE(u.[Name], ls.[Email]) AS [Name], ");
sqlCommand.Append("u.FirstName, ");
sqlCommand.Append("u.LastName ");
sqlCommand.Append("FROM mp_LetterSubscribe ls ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("[mp_Users] u ");
sqlCommand.Append("ON ");
sqlCommand.Append("u.[UserGuid] = ls.[UserGuid] ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ls.LetterInfoGuid = @LetterInfoGuid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ls.BeginUtc DESC ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@LetterInfoGuid", SqlDbType.UniqueIdentifier);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid;
return(SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_Language table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount();
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * ");
sqlCommand.Append("FROM mp_Language ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("[Name] ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
//SqlCeParameter[] arParams = new SqlCeParameter[1];
//arParams[0] = new SqlCeParameter("@ApplicationID", SqlDbType.UniqueIdentifier);
//arParams[0].Direction = ParameterDirection.Input;
//arParams[0].Value = applicationId;
return(SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
null));
}
public static DataTable GetEntries(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("f.Author As FeedName, ");
sqlCommand.Append("e.* ");
sqlCommand.Append("FROM mp_RssFeedEntries e ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_RssFeeds f ");
sqlCommand.Append("ON ");
sqlCommand.Append("e.FeedID = f.ItemID ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("e.ModuleGuid = @ModuleGuid ");
sqlCommand.Append("ORDER BY e.PubDate DESC ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[1];
arParams[0] = new SqlCeParameter("@ModuleGuid", SqlDbType.UniqueIdentifier);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid;
IDataReader reader = SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
DataTable dataTable = new DataTable();
dataTable.Columns.Add("FeedId", typeof(int));
dataTable.Columns.Add("FeedName", typeof(string));
dataTable.Columns.Add("PubDate", typeof(DateTime));
dataTable.Columns.Add("Author", typeof(string));
dataTable.Columns.Add("Title", typeof(string));
dataTable.Columns.Add("Description", typeof(string));
dataTable.Columns.Add("BlogUrl", typeof(string));
dataTable.Columns.Add("Link", typeof(string));
dataTable.Columns.Add("Confirmed", typeof(bool));
dataTable.Columns.Add("EntryHash", typeof(int));
using (reader)
{
while (reader.Read())
{
DataRow row = dataTable.NewRow();
row["FeedId"] = reader["FeedId"];
row["FeedName"] = reader["FeedName"];
row["PubDate"] = Convert.ToDateTime(reader["PubDate"]);
row["Author"] = reader["Author"];
row["Title"] = reader["Title"];
row["Description"] = reader["Description"];
row["BlogUrl"] = reader["BlogUrl"];
row["Link"] = reader["Link"];
row["Confirmed"] = Convert.ToBoolean(reader["Confirmed"]);
row["EntryHash"] = reader["EntryHash"];
dataTable.Rows.Add(row);
}
}
return(dataTable);
}
public static IDataReader GetPage(
int siteId,
string searchTerm,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteId, searchTerm);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") * ");
sqlCommand.Append("FROM mp_FriendlyUrls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND ");
sqlCommand.Append("FriendlyUrl LIKE '%' + @SearchTerm + '%' ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("FriendlyUrl ");
sqlCommand.Append(") AS t1 ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("t1.FriendlyUrl DESC ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("t2.FriendlyUrl ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@SearchTerm", SqlDbType.NVarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = searchTerm;
return(SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetUsersInRole(
int siteId,
int roleId,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountOfUsersInRole(siteId, roleId);
if (pageSize > 0) totalPages = totalRows / pageSize;
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") ");
sqlCommand.Append("u.UserID, ");
sqlCommand.Append("u.[Name], ");
sqlCommand.Append("u.Email, ");
sqlCommand.Append("u.LoginName ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("JOIN mp_UserRoles ur ");
sqlCommand.Append("ON u.UserID = ur.UserID ");
sqlCommand.Append("AND ur.RoleID = @RoleID ");
sqlCommand.Append("WHERE u.SiteID = @SiteID ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("u.[Name] ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@RoleID", SqlDbType.Int);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = roleId;
return SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
}
