/// <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); }