public static int GetCountOfSiteRoles(int siteId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("COUNT(*) "); sqlCommand.Append("FROM mp_roles "); sqlCommand.Append("WHERE siteid = :siteid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteId; return(Convert.ToInt32(NpgsqlHelper.ExecuteScalar( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), 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)); }
public static bool DeleteBySite(int siteId) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteId; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_contactformmessage "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid) "); sqlCommand.Append(";"); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <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 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)); }
/// <summary> /// Deletes rows from the mp_RssFeedEntries table. Returns true if row deleted. /// </summary> /// <param name="feedId"> feedId </param> /// <returns>bool</returns> public static bool DeleteEntriesByFeed(int feedId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_rssfeedentries "); sqlCommand.Append("WHERE "); sqlCommand.Append("feedid = :feedid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("feedid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = feedId; int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
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 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)); }
///// <summary> ///// Updates a row in the mp_IndexingQueue table. Returns true if row updated. ///// </summary> ///// <param name="rowId"> rowId </param> ///// <param name="indexPath"> indexPath </param> ///// <param name="serializedItem"> serializedItem </param> ///// <param name="itemKey"> itemKey </param> ///// <param name="removeOnly"> removeOnly </param> ///// <returns>bool</returns> //public static bool Update( // Int64 rowId, // string indexPath, // string serializedItem, // string itemKey, // bool removeOnly) //{ // NpgsqlParameter[] arParams = new NpgsqlParameter[5]; // arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Bigint); // arParams[0].Direction = ParameterDirection.Input; // arParams[0].Value = rowId; // arParams[1] = new NpgsqlParameter("indexpath", NpgsqlTypes.NpgsqlDbType.Varchar,255); // arParams[1].Direction = ParameterDirection.Input; // arParams[1].Value = indexPath; // arParams[2] = new NpgsqlParameter("serializeditem", NpgsqlTypes.NpgsqlDbType.Text); // arParams[2].Direction = ParameterDirection.Input; // arParams[2].Value = serializedItem; // arParams[3] = new NpgsqlParameter("itemkey", NpgsqlTypes.NpgsqlDbType.Varchar,255); // arParams[3].Direction = ParameterDirection.Input; // arParams[3].Value = itemKey; // arParams[4] = new NpgsqlParameter("removeonly", NpgsqlTypes.NpgsqlDbType.Boolean); // arParams[4].Direction = ParameterDirection.Input; // arParams[4].Value = removeOnly; // StringBuilder sqlCommand = new StringBuilder(); // sqlCommand.Append("UPDATE mp_indexingqueue "); // sqlCommand.Append("SET "); // sqlCommand.Append("indexpath = :indexpath, "); // sqlCommand.Append("serializeditem = :serializeditem, "); // sqlCommand.Append("itemkey = :itemkey, "); // sqlCommand.Append("removeonly = :removeonly "); // sqlCommand.Append("WHERE "); // sqlCommand.Append("rowid = :rowid "); // sqlCommand.Append(";"); // int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), // CommandType.Text, // sqlCommand.ToString(), // arParams); // return (rowsAffected > -1); //} /// <summary> /// Deletes a row from the mp_IndexingQueue table. Returns true if row deleted. /// </summary> /// <param name="rowId"> rowId </param> /// <returns>bool</returns> public static bool Delete(Int64 rowId) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Bigint); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = rowId; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_indexingqueue "); sqlCommand.Append("WHERE "); sqlCommand.Append("rowid = :rowid "); sqlCommand.Append(";"); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <summary> /// Gets an IDataReader with one row from the mp_Language table. /// </summary> /// <param name="guid"> guid </param> public static IDataReader GetOne(Guid guid) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_language "); sqlCommand.Append("WHERE "); sqlCommand.Append("guid = :guid "); sqlCommand.Append(";"); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
public static bool DeleteByExtraGuid(Guid extraGuid) { const string sqlCommand = @"DELETE FROM mp_tagitem WHERE extraguid = :extraguid;"; var arParams = new List <NpgsqlParameter> { new NpgsqlParameter("extraguid", NpgsqlDbType.Char, 36) { Direction = ParameterDirection.Input, Value = extraGuid.ToString() } }.ToArray(); int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand, arParams ); return(rowsAffected > -1); }
/// <summary> /// Deletes a row from the mp_Language table. Returns true if row deleted. /// </summary> /// <param name="guid"> guid </param> /// <returns>bool</returns> public static bool Delete(Guid guid) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_language "); sqlCommand.Append("WHERE "); sqlCommand.Append("guid = :guid "); sqlCommand.Append(";"); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
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 a count of rows in the mp_ContentRating table. /// </summary> public static int GetCountByContent(Guid contentGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT Count(*) "); 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(Convert.ToInt32(NpgsqlHelper.ExecuteScalar( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams))); }
public static byte[] GetPersonalizationBlob( int siteId, String path, Guid userGuid) { Guid pathID = GetOrCreatePathId(siteId, path); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = userGuid.ToString(); arParams[1] = new NpgsqlParameter("pathid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = pathID.ToString(); byte[] result = null; try { result = (byte[])NpgsqlHelper.ExecuteScalar( ConnectionString.GetReadConnectionString(), CommandType.StoredProcedure, "mp_sitepersonalizationperuser_getpersonalizationblob(:userid,:pathid)", arParams); } catch (System.InvalidCastException ex) { if (log.IsErrorEnabled) { log.Error("dbPortal.SitePersonalization_GetPersonalizationBlob", ex); } } return(result); }
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); }
public static bool DeleteHistoryByItemID(int itemId) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = itemId; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_sharedfileshistory "); sqlCommand.Append("WHERE "); sqlCommand.Append("itemid = :itemid "); sqlCommand.Append(";"); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
public static bool DeleteModuleDefinitionFromSites(int moduleDefId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_sitemoduledefinitions "); sqlCommand.Append("WHERE "); sqlCommand.Append("moduledefid = :moduledefid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("moduledefid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleDefId; int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <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 bool DeleteByLetterInfo(Guid letterInfoGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_lettersendlog "); sqlCommand.Append("WHERE "); sqlCommand.Append("letterguid IN (SELECT letterguid FROM mp_letter WHERE letterinfoguid = :letterinfoguid) "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = letterInfoGuid.ToString(); int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <summary> /// Deletes rows from the mp_RssFeedEntries table. Returns true if row deleted. /// </summary> /// <param name="moduleGuid"> moduleGuid </param> /// <returns>bool</returns> public static bool DeleteUnPublishedEntriesByModule(Guid moduleGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_rssfeedentries "); sqlCommand.Append("WHERE "); sqlCommand.Append("moduleguid = :moduleguid "); sqlCommand.Append(" AND confirmed = false "); 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(); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
public static void ResetPersonalizationBlob( int siteId, String path, Guid userGuid) { Guid pathID = GetOrCreatePathId(siteId, path); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = userGuid.ToString(); arParams[1] = new NpgsqlParameter("pathid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = pathID.ToString(); NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.StoredProcedure, "mp_sitepersonalizationperuser_deletebypath(:userid,:pathid)", 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)); }
public static bool DeleteByUser(string userId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE 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; int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <summary> /// Deletes rows from the mp_SystemLog table. Returns true if rows deleted. /// </summary> /// <param name="id"> id </param> /// <returns>bool</returns> public static bool DeleteByLevel(string logLevel) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_systemlog "); sqlCommand.Append("WHERE "); sqlCommand.Append("loglevel = :loglevel "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("loglevel", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = logLevel; int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <summary> /// Deletes rows from the mp_SystemLog table. Returns true if rows deleted. /// </summary> /// <param name="id"> id </param> /// <returns>bool</returns> public static bool DeleteOlderThan(DateTime cutoffDate) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_systemlog "); sqlCommand.Append("WHERE "); sqlCommand.Append("logdate < :cutoffdate "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("cutoffdate", NpgsqlTypes.NpgsqlDbType.Timestamp); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = cutoffDate; int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <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> /// Updates the subscriber count on a row in the mp_LetterInfo table. Returns true if row updated. /// </summary> /// <param name="letterInfoGuid"> letterInfoGuid </param> /// <returns>bool</returns> public static bool UpdateSubscriberCount(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(); StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_letterInfo "); sqlCommand.Append("SET "); sqlCommand.Append("subscribercount = ( "); sqlCommand.Append("SELECT COUNT(*) "); sqlCommand.Append("FROM mp_lettersubscribe "); sqlCommand.Append("WHERE "); sqlCommand.Append("letterinfoguid = :letterinfoguid "); sqlCommand.Append("), "); sqlCommand.Append("unverifiedcount = ( "); sqlCommand.Append("SELECT COUNT(*) "); sqlCommand.Append("FROM mp_lettersubscribe "); sqlCommand.Append("WHERE "); sqlCommand.Append("letterinfoguid = :letterinfoguid AND isverified = false "); sqlCommand.Append(") "); sqlCommand.Append("WHERE "); sqlCommand.Append("letterinfoguid = :letterinfoguid ;"); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
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)); }
/// <summary> /// Deletes a row from the mp_ContentStyle table. Returns true if row deleted. /// </summary> /// <param name="guid"> guid </param> /// <returns>bool</returns> public static bool SetActivationBySkin(Guid siteGuid, string skinName, bool isActive) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_contentstyle "); sqlCommand.Append("SET "); sqlCommand.Append("isactive = :isactive "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteguid = :siteguid "); sqlCommand.Append("AND "); sqlCommand.Append("skinname = :skinname "); sqlCommand.Append(";"); 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("skinname", NpgsqlTypes.NpgsqlDbType.Varchar, 100); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = skinName; arParams[2] = new NpgsqlParameter("isactive", NpgsqlTypes.NpgsqlDbType.Boolean); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = isActive; int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }