/// <summary> /// Gets an IDataReader with rows from the mp_FileAttachment table. /// </summary> /// <param name="rowGuid"> rowGuid </param> public static IDataReader SelectBySpecial2(Guid specialGuid2) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_fileattachment "); sqlCommand.Append("WHERE "); sqlCommand.Append("specialguid2 = :specialguid2 "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("specialguid2", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = specialGuid2.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_GoogleCheckoutLog table. /// </summary> /// <param name="rowGuid"> rowGuid </param> public static IDataReader GetOne(Guid rowGuid) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("rowguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = rowGuid.ToString(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_googlecheckoutlog "); sqlCommand.Append("WHERE "); sqlCommand.Append("rowguid = :rowguid "); sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
public static IDataReader GetHistory(int moduleId, int itemId) { NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = moduleId }; arParams[1] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = itemId }; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.StoredProcedure, "mp_sharedfileshistory_select(:moduleid,:itemid)", arParams)); }
public static IDataReader GetRssFeed(int itemId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_rssfeeds "); sqlCommand.Append("WHERE "); sqlCommand.Append("itemid = :itemid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = itemId; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with rows from the mp_BannedIPAddresses table. /// </summary> /// <param name="ipAddress"> ipAddress </param> public static IDataReader GeByIpAddress(string ipAddress) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_bannedipaddresses "); sqlCommand.Append("WHERE "); sqlCommand.Append("bannedip = :bannedip "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("bannedip", NpgsqlTypes.NpgsqlDbType.Varchar, 50); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = ipAddress; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
public static IDataReader GetHistoryByModule(int moduleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_sharedfileshistory "); sqlCommand.Append("WHERE "); sqlCommand.Append("moduleid = :moduleid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleId; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
public static IDataReader GetByUser(string userId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_userclaims "); sqlCommand.Append("WHERE "); sqlCommand.Append("userid = :userid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 128); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = userId; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with rows from the mp_EmailSendQueue table where DateToSend >= CurrentTime. /// </summary> /// <param name="currentTime"> currentTime </param> public static IDataReader GetEmailToSend(DateTime currentTime) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_emailsendqueue "); sqlCommand.Append("WHERE "); sqlCommand.Append("datetosend >= :currenttime "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("currenttime", NpgsqlTypes.NpgsqlDbType.Timestamp); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = currentTime; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
public static IDataReader GetSharedFolders(int moduleId, int parentId) { NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleId; arParams[1] = new NpgsqlParameter("parentid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = parentId; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("sff.*, "); sqlCommand.Append("(SELECT COALESCE(SUM(sf.sizeinkb),0) "); sqlCommand.Append("FROM mp_sharedfiles sf "); sqlCommand.Append("WHERE sf.folderid = sff.folderid) As sizeinkb "); sqlCommand.Append("FROM mp_sharedfilefolders sff "); sqlCommand.Append("WHERE "); sqlCommand.Append("sff.moduleid = :moduleid "); sqlCommand.Append("AND sff.parentid = :parentid "); sqlCommand.Append("ORDER BY sff.foldername ;"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); //return NpgsqlHelper.ExecuteReader( // GetConnectionString(), // CommandType.StoredProcedure, // "mp_sharedfilefolders_selectbymodulev2(:moduleid,:parentid)", // arParams); }
/// <summary> /// Gets an IDataReader with one row from the mp_ContentRating table. /// </summary> /// <param name="contentGuid"> contentGuid </param> public static IDataReader GetStatsByContent(Guid contentGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT COALESCE(AVG(rating),0) As currentrating, "); sqlCommand.Append("Count(*) As totalratings "); sqlCommand.Append("FROM mp_contentrating "); sqlCommand.Append("WHERE "); sqlCommand.Append("contentguid = :contentguid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = contentGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_TaxClass table. /// </summary> /// <param name="guid"> guid </param> public static IDataReader GetBySite(Guid siteGuid) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_taxclass "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteguid = :siteguid "); sqlCommand.Append("ORDER BY title "); sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with rows from the mp_PlugNPayLog table. /// </summary> /// <param name="rowGuid"> rowGuid </param> public static IDataReader GetByCart(Guid cartGuid) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("cartguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = cartGuid.ToString(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_PlugNPaylog "); sqlCommand.Append("WHERE "); sqlCommand.Append("cartguid = :cartguid "); sqlCommand.Append("ORDER BY createdutc "); sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with all rows in the mp_GeoZone table. /// </summary> public static IDataReader GetByCountry(Guid countryGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_geozone "); sqlCommand.Append("WHERE "); sqlCommand.Append("countryguid = :countryguid "); sqlCommand.Append("ORDER BY name "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = countryGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
public static Guid GetDraftSubmitter(Guid contentWorkflowGuid) { Guid result = Guid.Empty; NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("contentworkflowguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = contentWorkflowGuid.ToString(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT userguid "); sqlCommand.Append("FROM mp_contentworkflowaudithistory "); sqlCommand.Append("WHERE "); sqlCommand.Append("contentworkflowguid = :contentworkflowguid "); sqlCommand.Append("AND "); sqlCommand.Append("newstatus = 'AwaitingApproval' "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("createddateutc DESC "); sqlCommand.Append("LIMIT 1 "); sqlCommand.Append(";"); using (IDataReader reader = NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)) { if (reader.Read()) { result = new Guid(reader[0].ToString()); } } return(result); }
public static DataTable GetDefaultModuleSettingsForModule(int moduleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("m.moduleid, "); sqlCommand.Append("m.guid AS moduleguid, "); sqlCommand.Append("ds.settingname, "); sqlCommand.Append("ds.settingvalue, "); sqlCommand.Append("ds.controltype, "); sqlCommand.Append("ds.controlsrc, "); sqlCommand.Append("ds.helpkey, "); sqlCommand.Append("ds.sortorder, "); sqlCommand.Append("ds.groupname, "); sqlCommand.Append("ds.regexvalidationexpression "); sqlCommand.Append("FROM mp_modules m "); sqlCommand.Append("JOIN mp_moduledefinitionSettings ds "); sqlCommand.Append("ON ds.moduledefid = m.moduledefid "); sqlCommand.Append("WHERE m.moduleid = :moduleid "); sqlCommand.Append("ORDER BY ds.sortorder, ds.groupname ;"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleId; IDataReader reader = NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(DBPortal.GetTableFromDataReader(reader)); }
public static IDataReader GetFeeds(int moduleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT f.*, "); sqlCommand.Append(" (SELECT COUNT(*) FROM mp_rssfeedentries e WHERE e.feedid = f.itemid) AS totalentries "); sqlCommand.Append("FROM mp_rssfeeds f "); sqlCommand.Append("WHERE "); sqlCommand.Append("f.moduleid = :moduleid "); sqlCommand.Append("ORDER BY f.sortrank, f.author "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleId; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with rows from the mp_Comments table. /// </summary> /// <param name="guid"> guid </param> public static IDataReader GetByContentDesc(Guid contentGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT c.*, "); sqlCommand.Append("COALESCE(u.name, c.username) AS postauthor, "); sqlCommand.Append("COALESCE(u.userid, -1) AS userid, "); sqlCommand.Append("COALESCE(u.email, c.useremail) AS authoremail, "); sqlCommand.Append("COALESCE(u.totalrevenue, 0) AS userrevenue, "); sqlCommand.Append("COALESCE(u.trusted, false) AS trusted, "); sqlCommand.Append("u.avatarurl AS postauthoravatar, "); sqlCommand.Append("COALESCE(c.userurl, u.websiteurl) AS postauthorwebsiteurl "); sqlCommand.Append("FROM mp_Comments c "); sqlCommand.Append("LEFT OUTER JOIN mp_users u "); sqlCommand.Append("ON c.userguid = u.userguid "); sqlCommand.Append("WHERE "); sqlCommand.Append("c.contentguid = :contentguid "); sqlCommand.Append("ORDER BY c.createdutc DESC "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = contentGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_SavedQuery table. /// </summary> /// <param name="id"> id </param> public static IDataReader GetOne(string name) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_savedquery "); sqlCommand.Append("WHERE "); sqlCommand.Append("name = :name "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 50); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = name; return NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); }
public static IDataReader GetByModule(Guid moduleGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_category "); sqlCommand.Append("WHERE "); sqlCommand.Append("moduleguid = :moduleguid "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("category "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_LetterInfo table. /// </summary> /// <param name="letterInfoGuid"> letterInfoGuid </param> public static IDataReader GetOne(Guid letterInfoGuid) { //NpgsqlParameter[] arParams = new NpgsqlParameter[1]; //arParams[0] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Char, 36); //arParams[0].Direction = ParameterDirection.Input; //arParams[0].Value = letterInfoGuid.ToString(); //return NpgsqlHelper.ExecuteReader( // ConnectionString.GetReadConnectionString(), // CommandType.StoredProcedure, // "mp_letterinfo_select_one(:letterinfoguid)", // arParams); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_letterinfo "); sqlCommand.Append("WHERE "); sqlCommand.Append("letterinfoguid = :letterinfoguid "); //sqlCommand.Append("ORDER BY "); // sqlCommand.Append("sortrank, title "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = letterInfoGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets a page of data from the mp_LetterInfo 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( Guid siteGuid, int pageNumber, int pageSize, out int totalPages) { int pageLowerBound = (pageSize * pageNumber) - pageSize; totalPages = 1; int totalRows = GetCount(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; } } NpgsqlParameter[] arParams = new NpgsqlParameter[3]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); arParams[1] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = pageSize; arParams[2] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pageLowerBound; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_letterinfo "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteguid = :siteguid "); sqlCommand.Append("ORDER BY sortrank, title "); //sqlCommand.Append(" "); sqlCommand.Append("LIMIT :pagesize"); if (pageNumber > 1) { sqlCommand.Append(" OFFSET :pageoffset "); } sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); //totalPages = 1; //int totalRows // = GetCount(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; // } //} //NpgsqlParameter[] arParams = new NpgsqlParameter[3]; //arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); //arParams[0].Direction = ParameterDirection.Input; //arParams[0].Value = siteGuid.ToString(); //arParams[1] = new NpgsqlParameter("pagenumber", NpgsqlTypes.NpgsqlDbType.Integer); //arParams[1].Direction = ParameterDirection.Input; //arParams[1].Value = pageNumber; //arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); //arParams[2].Direction = ParameterDirection.Input; //arParams[2].Value = pageSize; //return NpgsqlHelper.ExecuteReader( // ConnectionString.GetReadConnectionString(), // CommandType.StoredProcedure, // "mp_letterinfo_selectpage(:siteguid,:pagenumber,:pagesize)", // arParams); }
/// <summary> /// Gets a page of data from the mp_SystemLog 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 GetPageDescending( 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; } } NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = pageSize; arParams[1] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = pageLowerBound; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_systemlog "); //sqlCommand.Append("WHERE "); sqlCommand.Append("ORDER BY id DESC "); //sqlCommand.Append(" "); sqlCommand.Append("LIMIT :pagesize"); if (pageNumber > 1) { sqlCommand.Append(" OFFSET :pageoffset "); } sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with one row from the mp_RssFeedEntries table. /// </summary> /// <param name="rowGuid"> rowGuid </param> 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(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleGuid.ToString(); IDataReader reader = NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), 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); }
/// <summary> /// Gets a page of data from the mp_RedirectList table with search term /// </summary> public static IDataReader GetPage( int siteId, int pageNumber, int pageSize, out int totalPages, string searchTerm = "") { var useSearch = !string.IsNullOrWhiteSpace(searchTerm); int pageLowerBound = (pageSize * pageNumber) - pageSize; totalPages = 1; int totalRows = GetCount(siteId, searchTerm); if (pageSize > 0) { totalPages = totalRows / pageSize; } if (totalRows <= pageSize) { totalPages = 1; } else { Math.DivRem(totalRows, pageSize, out int remainder); if (remainder > 0) { totalPages += 1; } } var sqlParams = new List <NpgsqlParameter> { new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = siteId }, new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = pageSize }, new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input, Value = pageLowerBound } }; if (useSearch) { sqlParams.Add( new NpgsqlParameter("?searchterm", NpgsqlTypes.NpgsqlDbType.Varchar, 255) { Direction = ParameterDirection.Input, Value = "%" + searchTerm + "%" } ); } var sqlCommand = $@"SELECT * FROM mp_redirectlist WHERE siteid = :siteid {(useSearch ? "AND (newurl LIKE :searchterm OR oldurl Like :searchterm)" : "")} ORDER BY oldurl LIMIT :pagesize {(pageNumber > 1 ? " OFFSET :pageoffset " : "")};" ; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand, sqlParams.ToArray())); }
/// <summary> /// Gets a page of data from the mp_ContentStyle 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( Guid siteGuid, string skinName, int pageNumber, int pageSize, out int totalPages) { int pageLowerBound = (pageSize * pageNumber) - pageSize; totalPages = 1; int totalRows = GetCount(siteGuid, skinName); 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; } } NpgsqlParameter[] arParams = new NpgsqlParameter[4]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); arParams[1] = new NpgsqlParameter("skinname", NpgsqlTypes.NpgsqlDbType.Varchar, 100); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = skinName; arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pageSize; arParams[3] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = pageLowerBound; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_contentstyle "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteguid = :siteguid "); sqlCommand.Append("AND "); sqlCommand.Append("skinname = :skinname "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("name "); sqlCommand.Append("LIMIT :pagesize"); if (pageNumber > 1) { sqlCommand.Append(" OFFSET :pageoffset "); } sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
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 "); sqlCommand.Append("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 = true "); 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("createddateutc DESC "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleGuid.ToString(); arParams[1] = new NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Varchar, 20); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = status; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), 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 "); 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 u.name "); sqlCommand.Append("LIMIT :pagesize"); if (pageNumber > 1) { sqlCommand.Append(" OFFSET :pageoffset "); } sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[4]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteId; arParams[1] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = roleId; arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pageSize; arParams[3] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = pageLowerBound; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
public static IDataReader GetPageInfoForPage( Guid siteGuid, string status, int pageNumber, int pageSize) { int pageLowerBound = (pageSize * pageNumber) - pageSize; NpgsqlParameter[] arParams = new NpgsqlParameter[4]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); arParams[1] = new NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Varchar, 20); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = status; arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pageSize; arParams[3] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = pageLowerBound; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); 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("LIMIT :pagesize"); if (pageNumber > 1) { sqlCommand.Append(" OFFSET :pageoffset "); } sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), 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; } } NpgsqlParameter[] arParams = new NpgsqlParameter[4]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); arParams[1] = new NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Varchar, 20); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = status; arParams[2] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pageSize; arParams[3] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = pageLowerBound; 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("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 "); sqlCommand.Append("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_contentworkflowaudithistory cwah "); sqlCommand.Append("ON "); sqlCommand.Append("cwah.contentworkflowguid = cw.guid "); sqlCommand.Append("AND "); sqlCommand.Append("cwah.active = true "); 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.siteGuid = :siteguid "); sqlCommand.Append("AND "); sqlCommand.Append("cw.status = :status "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("cw.CreatedDateUtc DESC "); sqlCommand.Append("LIMIT :pagesize"); if (pageNumber > 1) { sqlCommand.Append(" OFFSET :pageoffset "); } sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets a page of data from the mp_GeoZone 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( Guid countryGuid, int pageNumber, int pageSize, out int totalPages) { int pageLowerBound = (pageSize * pageNumber) - pageSize; totalPages = 1; int totalRows = GetCount(countryGuid); 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; } } NpgsqlParameter[] arParams = new NpgsqlParameter[3]; arParams[0] = new NpgsqlParameter("countryguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = countryGuid.ToString(); arParams[1] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = pageSize; arParams[2] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pageLowerBound; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT gz.*, "); sqlCommand.Append("gc.name As countryname "); sqlCommand.Append("FROM mp_geozone gz "); sqlCommand.Append("JOIN mp_geocountry gc "); sqlCommand.Append("ON gz.countryguid = gc.Guid "); sqlCommand.Append("WHERE "); sqlCommand.Append("gz.countryguid = :countryguid "); sqlCommand.Append("ORDER BY name "); //sqlCommand.Append(" "); sqlCommand.Append("LIMIT :pagesize"); if (pageNumber > 1) { sqlCommand.Append(" OFFSET :pageoffset "); } sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }