public static Guid GetPathId(int siteId, String path)
{
Guid result = Guid.Empty;
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("loweredpath", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = path.ToLower();
String guidString = NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_sitepaths_getpathid(:siteid,:loweredpath)",
arParams).ToString();
result = new Guid(guidString);
return(result);
}
public static bool PersonalizationBlobExists(Guid pathId, Guid userGuid)
{
bool result = false;
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("pathid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pathId.ToString();
arParams[1] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
int count = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_sitepersonalizationperuser_countbypath(:pathid,:userguid)",
arParams));
result = (count > 0);
return(result);
}
public static bool Exists(
int siteId,
string friendlyUrl)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("friendlyurl", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = friendlyUrl;
int count = 0;
count = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_friendlyurls_exists(:siteid,:friendlyurl)",
arParams));
return(count > 0);
}
public static int GetCountOfStateByUser(
Guid userGuid,
Guid pathId)
{
int result = 0;
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();
result = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_sitepersonalizationperuser_countbyuserpath(:userid,:pathid)",
arParams));
return(result);
}
/// <summary>
/// gets the max sort rank or 1 if null
/// </summary>
/// <returns>int</returns>
public static int GetMaxSortRank(Guid contentGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COALESCE(MAX(sortrank),1) ");
sqlCommand.Append("FROM mp_contentmeta ");
sqlCommand.Append("WHERE 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 int GetCountOfStateByUserAllPaths(
Guid userGuid,
DateTime inactiveSinceTime)
{
int result = 0;
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("sincetime", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = inactiveSinceTime;
result = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_sitepersonalizationperuser_countbyusersince(:userid,:sincetime)",
arParams));
return(result);
}
public static bool Exists(Int32 siteId, String className, String assemblyName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("classname", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = className;
arParams[2] = new NpgsqlParameter("assemblyname", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = assemblyName;
int count = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.StoredProcedure,
"mp_webparts_exists(:siteid,:classname,:assemblyname)",
arParams));
return(count > 0);
}
/// <summary>
/// Returns true if the passed in address is banned
/// </summary>
/// <param name="rowID"> rowID </param>
/// <returns>bool</returns>
public static bool IsBanned(string ipAddress)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("bannedip", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = ipAddress;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_bannedipaddresses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("bannedip = :bannedip ");
sqlCommand.Append(";");
int foundRows = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(foundRows > 0);
}
public static bool AddHistory(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
int itemId,
int moduleId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int uploadUserId,
DateTime archiveDate)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[12];
arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new NpgsqlParameter("friendlyname", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = friendlyName;
arParams[3] = new NpgsqlParameter("originalfilename", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = originalFileName;
arParams[4] = new NpgsqlParameter("serverfilename", NpgsqlTypes.NpgsqlDbType.Text, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = serverFileName;
arParams[5] = new NpgsqlParameter("sizeinkb", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sizeInKB;
arParams[6] = new NpgsqlParameter("uploaddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = uploadDate;
arParams[7] = new NpgsqlParameter("uploaduserid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = uploadUserId;
arParams[8] = new NpgsqlParameter("archivedate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = archiveDate;
arParams[9] = new NpgsqlParameter("itemguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = itemGuid.ToString();
arParams[10] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = moduleGuid.ToString();
arParams[11] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = userGuid.ToString();
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_sharedfileshistory_insert(:itemid,:moduleid,:friendlyname,:originalfilename,:serverfilename,:sizeinkb,:uploaddate,:uploaduserid,:archivedate,:itemguid,:moduleguid,:userguid)",
arParams));
return(newID > 0);
}
public static int AddSharedFile(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
Guid folderGuid,
int moduleId,
int uploadUserId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int folderId,
string description)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_sharedfiles (");
sqlCommand.Append("moduleid, ");
sqlCommand.Append("uploaduserid, ");
sqlCommand.Append("friendlyname, ");
sqlCommand.Append("originalfilename, ");
sqlCommand.Append("serverfilename, ");
sqlCommand.Append("sizeinkb, ");
sqlCommand.Append("uploaddate, ");
sqlCommand.Append("folderid, ");
sqlCommand.Append("itemguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("folderguid, ");
sqlCommand.Append("downloadcount, ");
sqlCommand.Append("description )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":moduleid, ");
sqlCommand.Append(":uploaduserid, ");
sqlCommand.Append(":friendlyname, ");
sqlCommand.Append(":originalfilename, ");
sqlCommand.Append(":serverfilename, ");
sqlCommand.Append(":sizeinkb, ");
sqlCommand.Append(":uploaddate, ");
sqlCommand.Append(":folderid, ");
sqlCommand.Append(":itemguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":folderguid, ");
sqlCommand.Append("0, ");
sqlCommand.Append(":description )");
sqlCommand.Append(";");
sqlCommand.Append(" SELECT CURRVAL('mp_sharedfiles_itemid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[13];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new NpgsqlParameter("uploaduserid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = uploadUserId;
arParams[2] = new NpgsqlParameter("friendlyname", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = friendlyName;
arParams[3] = new NpgsqlParameter("originalfilename", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = originalFileName;
arParams[4] = new NpgsqlParameter("serverfilename", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = serverFileName;
arParams[5] = new NpgsqlParameter("sizeinkb", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sizeInKB;
arParams[6] = new NpgsqlParameter("uploaddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = uploadDate;
arParams[7] = new NpgsqlParameter("folderid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = folderId;
arParams[8] = new NpgsqlParameter("itemguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = itemGuid.ToString();
arParams[9] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = moduleGuid.ToString();
arParams[10] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = userGuid.ToString();
arParams[11] = new NpgsqlParameter("folderguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = folderGuid.ToString();
arParams[12] = new NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = description;
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(newID);
}
/// <summary>
/// Inserts a row in the mp_ModuleDefinitions table. Returns new integer id.
/// </summary>
/// <param name="featureName"> featureName </param>
/// <param name="controlSrc"> controlSrc </param>
/// <param name="sortOrder"> sortOrder </param>
/// <param name="isAdmin"> isAdmin </param>
/// <param name="icon"> icon </param>
/// <param name="defaultCacheTime"> defaultCacheTime </param>
/// <param name="guid"> guid </param>
/// <param name="resourceFile"> resourceFile </param>
/// <param name="isCacheable"> isCacheable </param>
/// <param name="isSearchable"> isSearchable </param>
/// <param name="searchListName"> searchListName </param>
public static int AddModuleDefinition(
Guid featureGuid,
int siteId,
string featureName,
string controlSrc,
int sortOrder,
int defaultCacheTime,
String icon,
bool isAdmin,
string resourceFile,
bool isCacheable,
bool isSearchable,
string searchListName,
bool supportsPageReuse,
string deleteProvider,
string partialView)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_moduledefinitions (");
sqlCommand.Append("featurename, ");
sqlCommand.Append("controlsrc, ");
sqlCommand.Append("sortorder, ");
sqlCommand.Append("isadmin, ");
sqlCommand.Append("icon, ");
sqlCommand.Append("defaultcachetime, ");
sqlCommand.Append("guid, ");
sqlCommand.Append("resourcefile, ");
sqlCommand.Append("supportspagereuse, ");
sqlCommand.Append("deleteprovider, ");
sqlCommand.Append("iscacheable, ");
sqlCommand.Append("issearchable, ");
sqlCommand.Append("partialview, ");
sqlCommand.Append("searchlistname )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":featurename, ");
sqlCommand.Append(":controlsrc, ");
sqlCommand.Append(":sortorder, ");
sqlCommand.Append(":isadmin, ");
sqlCommand.Append(":icon, ");
sqlCommand.Append(":defaultcachetime, ");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":resourcefile, ");
sqlCommand.Append(":supportspagereuse, ");
sqlCommand.Append(":deleteprovider, ");
sqlCommand.Append(":iscacheable, ");
sqlCommand.Append(":issearchable, ");
sqlCommand.Append(":partialview, ");
sqlCommand.Append(":searchlistname )");
sqlCommand.Append(";");
sqlCommand.Append(" SELECT CURRVAL('mp_moduledefinitions_moduledefid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[14];
arParams[0] = new NpgsqlParameter("featurename", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = featureName;
arParams[1] = new NpgsqlParameter("controlsrc", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = controlSrc;
arParams[2] = new NpgsqlParameter("sortorder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = sortOrder;
arParams[3] = new NpgsqlParameter("isadmin", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = isAdmin;
arParams[4] = new NpgsqlParameter("icon", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = icon;
arParams[5] = new NpgsqlParameter("defaultcachetime", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = defaultCacheTime;
arParams[6] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = featureGuid.ToString();
arParams[7] = new NpgsqlParameter("resourcefile", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = resourceFile;
arParams[8] = new NpgsqlParameter("iscacheable", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = isCacheable;
arParams[9] = new NpgsqlParameter("issearchable", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = isSearchable;
arParams[10] = new NpgsqlParameter("searchlistname", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = searchListName;
arParams[11] = new NpgsqlParameter("supportspagereuse", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = supportsPageReuse;
arParams[12] = new NpgsqlParameter("deleteprovider", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = deleteProvider;
arParams[13] = new NpgsqlParameter("partialview", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = partialView;
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
if (siteId > -1)
{
// now add to mp_SiteModuleDefinitions
sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_sitemoduledefinitions (");
sqlCommand.Append("siteid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("featureguid, ");
sqlCommand.Append("moduledefid, ");
sqlCommand.Append("authorizedroles ");
sqlCommand.Append(")");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":siteid, ");
sqlCommand.Append("(SELECT siteguid FROM mp_sites WHERE siteid = :siteid LIMIT 1), ");
sqlCommand.Append("(SELECT guid FROM mp_moduledefinitions WHERE moduledefid = :moduledefid LIMIT 1), ");
sqlCommand.Append(":moduledefid, ");
sqlCommand.Append("'All Users' ");
sqlCommand.Append(" ) ;");
arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new NpgsqlParameter("moduledefid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = newID;
NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
}
return(newID);
}
/// <summary>
/// Inserts a row in the mp_CalendarEvents table. Returns new integer id.
/// </summary>
/// <returns>int</returns>
public static int AddCalendarEvent(
Guid itemGuid,
Guid moduleGuid,
int moduleId,
string title,
string description,
string imageName,
DateTime eventDate,
DateTime startTime,
DateTime endTime,
int userId,
Guid userGuid,
string location,
bool requiresTicket,
decimal ticketPrice,
DateTime createdDate,
bool showMap)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[16];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = description;
arParams[3] = new NpgsqlParameter("imagename", NpgsqlTypes.NpgsqlDbType.Text, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = imageName;
arParams[4] = new NpgsqlParameter("eventdate", NpgsqlTypes.NpgsqlDbType.Date);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = eventDate;
arParams[5] = new NpgsqlParameter("starttime", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = startTime;
arParams[6] = new NpgsqlParameter("endtime", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = endTime;
arParams[7] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = userId;
arParams[8] = new NpgsqlParameter("itemguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = itemGuid.ToString();
arParams[9] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = moduleGuid.ToString();
arParams[10] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = userGuid.ToString();
arParams[11] = new NpgsqlParameter("location", NpgsqlTypes.NpgsqlDbType.Text);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = location;
arParams[12] = new NpgsqlParameter("createddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = createdDate;
arParams[13] = new NpgsqlParameter("ticketprice", NpgsqlTypes.NpgsqlDbType.Numeric);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = ticketPrice;
arParams[14] = new NpgsqlParameter("requiresticket", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = requiresTicket;
arParams[15] = new NpgsqlParameter("showmap", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = showMap;
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_calendarevents_insert(:moduleid,:title,:description,:imagename,:eventdate,:starttime,:endtime,:userid,:itemguid,:moduleguid,:userguid,:location,:createddate,:ticketprice,:requiresticket,:showmap)",
arParams));
return(newID);
}
/// <summary>
/// Updates a row in the mp_CalendarEvents table. Returns true if row updated.
/// </summary>
/// <returns>bool</returns>
public static bool UpdateCalendarEvent(
int itemId,
int moduleId,
string title,
string description,
string imageName,
DateTime eventDate,
DateTime startTime,
DateTime endTime,
string location,
bool requiresTicket,
decimal ticketPrice,
DateTime lastModUtc,
Guid lastModUserGuid,
bool showMap)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[15];
arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = title;
arParams[3] = new NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = description;
arParams[4] = new NpgsqlParameter("imagename", NpgsqlTypes.NpgsqlDbType.Text, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = imageName;
arParams[5] = new NpgsqlParameter("eventdate", NpgsqlTypes.NpgsqlDbType.Date);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = eventDate;
arParams[6] = new NpgsqlParameter("starttime", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = startTime;
arParams[7] = new NpgsqlParameter("endtime", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = endTime;
arParams[8] = new NpgsqlParameter("lastmoduserguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModUserGuid.ToString();
arParams[9] = new NpgsqlParameter("location", NpgsqlTypes.NpgsqlDbType.Text);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = location;
arParams[10] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = lastModUtc;
arParams[11] = new NpgsqlParameter("ticketprice", NpgsqlTypes.NpgsqlDbType.Numeric);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = ticketPrice;
arParams[13] = new NpgsqlParameter("requiresticket", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = requiresTicket;
arParams[14] = new NpgsqlParameter("showmap", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = showMap;
int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_calendarevents_update(:itemid,:moduleid,:title,:description,:imagename,:eventdate,:starttime,:endtime,:lastmoduserguid,:location,:lastmodutc,:ticketprice,:requiresticket,:showmap)",
arParams));
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the mp_ModuleDefinitions table. Returns new integer id.
/// </summary>
/// <param name="featureName"> featureName </param>
/// <param name="controlSrc"> controlSrc </param>
/// <param name="sortOrder"> sortOrder </param>
/// <param name="isAdmin"> isAdmin </param>
/// <param name="icon"> icon </param>
/// <param name="defaultCacheTime"> defaultCacheTime </param>
/// <param name="guid"> guid </param>
/// <param name="resourceFile"> resourceFile </param>
/// <param name="isCacheable"> isCacheable </param>
/// <param name="isSearchable"> isSearchable </param>
/// <param name="searchListName"> searchListName </param>
public static int AddModuleDefinition(
Guid featureGuid,
int siteId,
string featureName,
string controlSrc,
int sortOrder,
int defaultCacheTime,
String icon,
bool isAdmin,
string resourceFile,
bool isCacheable,
bool isSearchable,
string searchListName,
bool supportsPageReuse,
string deleteProvider,
string partialView,
string skinFileName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.AppendFormat("INSERT INTO mp_moduledefinitions ({0}) VALUES ({1});"
, @"featurename
,controlsrc
,sortorder
,isadmin
,icon
,defaultcachetime
,guid
,resourcefile
,supportspagereuse
,deleteprovider
,iscacheable
,issearchable
,partialview
,skinfilename
,searchlistname"
, @":featurename
,:controlsrc
,:sortorder
,:isadmin
,:icon
,:defaultcachetime
,:guid
,:resourcefile
,:supportspagereuse
,:deleteprovider
,:iscacheable
,:issearchable
,:partialview
,:skinfilename
,:searchlistname" );
sqlCommand.Append(" SELECT CURRVAL('mp_moduledefinitions_moduledefid_seq');");
var sqlParams = new List <NpgsqlParameter>()
{
new NpgsqlParameter("featurename", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = featureName
},
new NpgsqlParameter("controlsrc", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = controlSrc
},
new NpgsqlParameter("sortorder", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = sortOrder
},
new NpgsqlParameter("isadmin", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = isAdmin
},
new NpgsqlParameter("icon", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = icon
},
new NpgsqlParameter("defaultcachetime", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = defaultCacheTime
},
new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input, Value = featureGuid.ToString()
},
new NpgsqlParameter("resourcefile", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = resourceFile
},
new NpgsqlParameter("iscacheable", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = isCacheable
},
new NpgsqlParameter("issearchable", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = isSearchable
},
new NpgsqlParameter("searchlistname", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = searchListName
},
new NpgsqlParameter("supportspagereuse", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = supportsPageReuse
},
new NpgsqlParameter("deleteprovider", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = deleteProvider
},
new NpgsqlParameter("partialview", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = partialView
},
new NpgsqlParameter("skinfilename", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = skinFileName
}
};
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray()));
if (siteId > -1)
{
// now add to mp_SiteModuleDefinitions
sqlCommand = new StringBuilder();
sqlCommand.AppendFormat("INSERT INTO mp_sitemoduledefinitions ({0}) VALUES ({1});"
, @"siteid
,siteguid
,featureguid
,moduledefid
,authorizedroles"
, @":siteid
,(SELECT siteguid FROM mp_sites WHERE siteid = :siteid LIMIT 1)
,(SELECT guid FROM mp_moduledefinitions WHERE moduledefid = :moduledefid LIMIT 1)
,:moduledefid
,'All Users'"
);
sqlParams = new List <NpgsqlParameter>()
{
new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = siteId
},
new NpgsqlParameter("moduledefid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = newID
}
};
NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray());
}
return(newID);
}
/// <summary>
/// Add a new folder to the module.
/// </summary>
/// <param name="folderGuid">folderGuid</param>
/// <param name="moduleGuid">moduleGuid</param>
/// <param name="parentGuid">parentGuid</param>
/// <param name="moduleId">moduleId</param>
/// <param name="folderName">folderName</param>
/// <param name="parentId">parentId</param>
/// <param name="viewRoles">viewRoles</param>
/// <returns></returns>
public static int AddSharedFileFolder(
Guid folderGuid,
Guid moduleGuid,
Guid parentGuid,
int moduleId,
string folderName,
int parentId,
string viewRoles
)
{
string sqlCommand = @"
mp_sharedfilefolders_insert(
:moduleid,
:foldername,
:parentid,
:moduleguid,
:folderguid,
:parentguid,
:viewroles
)" ;
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = moduleId
},
new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Text, 255)
{
Direction = ParameterDirection.Input,
Value = folderName
},
new NpgsqlParameter("parentid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = parentId
},
new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = moduleGuid.ToString()
},
new NpgsqlParameter("folderguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = folderGuid.ToString()
},
new NpgsqlParameter("parentguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = parentGuid.ToString()
},
new NpgsqlParameter("viewroles", NpgsqlTypes.NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input,
Value = viewRoles
}
};
int newID = Convert.ToInt32(
NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
sqlCommand,
arParams.ToArray()
)
);
return(newID);
}
/// <summary>
/// Inserts a row in the mp_SystemLog table. Returns new integer id.
/// </summary>
/// <param name="logDate"> logDate </param>
/// <param name="ipAddress"> ipAddress </param>
/// <param name="culture"> culture </param>
/// <param name="url"> url </param>
/// <param name="shortUrl"> shortUrl </param>
/// <param name="thread"> thread </param>
/// <param name="logLevel"> logLevel </param>
/// <param name="logger"> logger </param>
/// <param name="message"> message </param>
/// <returns>int</returns>
public static int Create(
DateTime logDate,
string ipAddress,
string culture,
string url,
string shortUrl,
string thread,
string logLevel,
string logger,
string message)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_systemlog (");
sqlCommand.Append("logdate, ");
sqlCommand.Append("ipaddress, ");
sqlCommand.Append("culture, ");
sqlCommand.Append("url, ");
sqlCommand.Append("shorturl, ");
sqlCommand.Append("thread, ");
sqlCommand.Append("loglevel, ");
sqlCommand.Append("logger, ");
sqlCommand.Append("message )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":logdate, ");
sqlCommand.Append(":ipaddress, ");
sqlCommand.Append(":culture, ");
sqlCommand.Append(":url, ");
sqlCommand.Append(":shorturl, ");
sqlCommand.Append(":thread, ");
sqlCommand.Append(":loglevel, ");
sqlCommand.Append(":logger, ");
sqlCommand.Append(":message )");
sqlCommand.Append(";");
sqlCommand.Append(" SELECT CURRVAL('mp_systemlogid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[9];
arParams[0] = new NpgsqlParameter("logdate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = logDate;
arParams[1] = new NpgsqlParameter("ipaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = ipAddress;
arParams[2] = new NpgsqlParameter("culture", NpgsqlTypes.NpgsqlDbType.Varchar, 10);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = culture;
arParams[3] = new NpgsqlParameter("url", NpgsqlTypes.NpgsqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = url;
arParams[4] = new NpgsqlParameter("shorturl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = shortUrl;
arParams[5] = new NpgsqlParameter("thread", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = thread;
arParams[6] = new NpgsqlParameter("loglevel", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = logLevel;
arParams[7] = new NpgsqlParameter("logger", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = logger;
arParams[8] = new NpgsqlParameter("message", NpgsqlTypes.NpgsqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = message;
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(newID);
}
public static int AddHtmlContent(
Guid itemGuid,
Guid moduleGuid,
int moduleId,
string title,
string excerpt,
string body,
string moreLink,
int sortOrder,
DateTime beginDate,
DateTime endDate,
DateTime createdDate,
int userId,
Guid userGuid,
bool excludeFromRecentContent)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_htmlcontent (");
sqlCommand.Append("moduleid, ");
sqlCommand.Append("title, ");
sqlCommand.Append("excerpt, ");
sqlCommand.Append("body, ");
sqlCommand.Append("morelink, ");
sqlCommand.Append("sortorder, ");
sqlCommand.Append("begindate, ");
sqlCommand.Append("enddate, ");
sqlCommand.Append("createddate, ");
sqlCommand.Append("userid, ");
sqlCommand.Append("itemguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("excludefromrecentcontent, ");
sqlCommand.Append("lastmoduserguid, ");
sqlCommand.Append("lastmodutc ) ");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":moduleid, ");
sqlCommand.Append(":title, ");
sqlCommand.Append(":excerpt, ");
sqlCommand.Append(":body, ");
sqlCommand.Append(":morelink, ");
sqlCommand.Append(":sortorder, ");
sqlCommand.Append(":begindate, ");
sqlCommand.Append(":enddate, ");
sqlCommand.Append(":createddate, ");
sqlCommand.Append(":userid, ");
sqlCommand.Append(":itemguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":excludefromrecentcontent, ");
sqlCommand.Append(":lastmoduserguid, ");
sqlCommand.Append(":lastmodutc ) ");
sqlCommand.Append("; ");
sqlCommand.Append(" SELECT CURRVAL('mp_htmlcontent_itemid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[16];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new NpgsqlParameter("excerpt", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = excerpt;
arParams[3] = new NpgsqlParameter("body", NpgsqlTypes.NpgsqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = body;
arParams[4] = new NpgsqlParameter("morelink", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = moreLink;
arParams[5] = new NpgsqlParameter("sortorder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sortOrder;
arParams[6] = new NpgsqlParameter("begindate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = beginDate;
arParams[7] = new NpgsqlParameter("enddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = endDate;
arParams[8] = new NpgsqlParameter("createddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = createdDate;
arParams[9] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = userId;
arParams[10] = new NpgsqlParameter("itemguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = itemGuid.ToString();
arParams[11] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = moduleGuid.ToString();
arParams[12] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = userGuid.ToString();
arParams[13] = new NpgsqlParameter("lastmoduserguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = userGuid.ToString();
arParams[14] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = createdDate;
arParams[15] = new NpgsqlParameter("excludefromrecentcontent", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = excludeFromRecentContent;
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
//int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
// ConnectionString.GetWriteConnectionString(),
// CommandType.StoredProcedure,
// "mp_htmlcontent_insert(:moduleid,:title,:excerpt,:body,:morelink,:sortorder,:begindate,:enddate,:createddate,:userid,:itemguid,:moduleguid,:userguid)",
// arParams));
return(newID);
}
public static bool UpdateHtmlContent(
int itemId,
int moduleId,
string title,
string excerpt,
string body,
string moreLink,
int sortOrder,
DateTime beginDate,
DateTime endDate,
DateTime lastModUtc,
Guid lastModUserGuid,
bool excludeFromRecentContent)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_htmlcontent ");
sqlCommand.Append("SET ");
sqlCommand.Append("title = :title, ");
sqlCommand.Append("excerpt = :excerpt, ");
sqlCommand.Append("body = :body, ");
sqlCommand.Append("morelink = :morelink, ");
sqlCommand.Append("sortorder = :sortorder, ");
sqlCommand.Append("begindate = :begindate, ");
sqlCommand.Append("enddate = :enddate, ");
sqlCommand.Append("excludefromrecentcontent = :excludefromrecentcontent, ");
sqlCommand.Append("lastmoduserguid = :lastmoduserguid, ");
sqlCommand.Append("lastmodutc = :lastmodutc ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("itemid = :itemid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[11];
arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new NpgsqlParameter("excerpt", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = excerpt;
arParams[3] = new NpgsqlParameter("body", NpgsqlTypes.NpgsqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = body;
arParams[4] = new NpgsqlParameter("morelink", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = moreLink;
arParams[5] = new NpgsqlParameter("sortorder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sortOrder;
arParams[6] = new NpgsqlParameter("begindate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = beginDate;
arParams[7] = new NpgsqlParameter("enddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = endDate;
arParams[8] = new NpgsqlParameter("lastmoduserguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModUserGuid.ToString();
arParams[9] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = lastModUtc;
arParams[10] = new NpgsqlParameter("excludefromrecentcontent", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = excludeFromRecentContent;
//int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
// ConnectionString.GetWriteConnectionString(),
// CommandType.StoredProcedure,
// "mp_htmlcontent_update(:itemid,:moduleid,:title,:excerpt,:body,:morelink,:sortorder,:begindate,:enddate,:lastmoduserguid,:lastmodutc)",
// arParams));
int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(rowsAffected > -1);
}
public static bool CreateModuleSetting(
Guid settingGuid,
Guid moduleGuid,
int moduleId,
string settingName,
string settingValue,
string controlType,
string regexValidationExpression,
string controlSrc,
string helpKey,
int sortOrder)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_modulesettings (");
sqlCommand.Append("moduleid, ");
sqlCommand.Append("settingname, ");
sqlCommand.Append("settingvalue, ");
sqlCommand.Append("controltype, ");
sqlCommand.Append("regexvalidationexpression, ");
sqlCommand.Append("settingguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("controlsrc, ");
sqlCommand.Append("sortorder, ");
sqlCommand.Append("helpkey )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":moduleid, ");
sqlCommand.Append(":settingname, ");
sqlCommand.Append(":settingvalue, ");
sqlCommand.Append(":controltype, ");
sqlCommand.Append(":regexvalidationexpression, ");
sqlCommand.Append(":settingguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":controlsrc, ");
sqlCommand.Append(":sortorder, ");
sqlCommand.Append(":helpkey )");
sqlCommand.Append(";");
//sqlCommand.Append(" SELECT CURRVAL('mp_modulesettingsid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[10];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new NpgsqlParameter("settingname", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new NpgsqlParameter("settingvalue", NpgsqlTypes.NpgsqlDbType.Varchar, -1);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new NpgsqlParameter("controltype", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new NpgsqlParameter("regexvalidationexpression", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new NpgsqlParameter("settingguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = settingGuid.ToString();
arParams[6] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = moduleGuid.ToString();
arParams[7] = new NpgsqlParameter("controlsrc", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new NpgsqlParameter("sortorder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = sortOrder;
arParams[9] = new NpgsqlParameter("helpkey", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = helpKey;
int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_LetterSendLog table. Returns new integer id.
/// </summary>
/// <param name="letterGuid"> letterGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="emailAddress"> emailAddress </param>
/// <param name="uTC"> uTC </param>
/// <param name="errorOccurred"> errorOccurred </param>
/// <param name="errorMessage"> errorMessage </param>
/// <returns>int</returns>
public static int Create(
Guid letterGuid,
Guid userGuid,
Guid subscribeGuid,
string emailAddress,
DateTime uTC,
bool errorOccurred,
string errorMessage)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_lettersendlog (");
sqlCommand.Append("letterguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("emailaddress, ");
sqlCommand.Append("utc, ");
sqlCommand.Append("erroroccurred, ");
sqlCommand.Append("errormessage, ");
sqlCommand.Append("subscribeguid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":letterguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":emailaddress, ");
sqlCommand.Append(":utc, ");
sqlCommand.Append(":erroroccurred, ");
sqlCommand.Append(":errormessage, ");
sqlCommand.Append(":subscribeguid )");
sqlCommand.Append(";");
sqlCommand.Append(" SELECT CURRVAL('mp_lettersendlogrowid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[7];
arParams[0] = new NpgsqlParameter("letterguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterGuid.ToString();
arParams[1] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
arParams[2] = new NpgsqlParameter("emailaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = emailAddress;
arParams[3] = new NpgsqlParameter("utc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = uTC;
arParams[4] = new NpgsqlParameter("erroroccurred", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = errorOccurred;
arParams[5] = new NpgsqlParameter("errormessage", NpgsqlTypes.NpgsqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = errorMessage;
arParams[6] = new NpgsqlParameter("subscribeguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = subscribeGuid.ToString();
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(newID);
}
public static int AddLink(
Guid itemGuid,
Guid moduleGuid,
int moduleId,
string title,
string url,
int viewOrder,
string description,
DateTime createdDate,
int createdBy,
string target,
Guid userGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[11];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new NpgsqlParameter("url", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = url;
arParams[3] = new NpgsqlParameter("vieworder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = viewOrder;
arParams[4] = new NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = description;
arParams[5] = new NpgsqlParameter("createddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdDate;
arParams[6] = new NpgsqlParameter("createdby", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = createdBy;
arParams[7] = new NpgsqlParameter("target", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = target;
arParams[8] = new NpgsqlParameter("itemguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = itemGuid.ToString();
arParams[9] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = moduleGuid.ToString();
arParams[10] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = userGuid.ToString();
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_links_insert(:moduleid,:title,:url,:vieworder,:description,:createddate,:createdby,:target,:itemguid,:moduleguid,:userguid)",
arParams));
return(newID);
}
/// <summary>
/// Update a folder
/// </summary>
/// <param name="folderId">folderId</param>
/// <param name="moduleId">moduleId</param>
/// <param name="folderName">folderName</param>
/// <param name="parentId">parentId</param>
/// <param name="parentGuid">parentGuid</param>
/// <param name="viewRoles">viewRoles</param>
/// <returns></returns>
public static bool UpdateSharedFileFolder(
int folderId,
int moduleId,
string folderName,
int parentId,
Guid parentGuid,
string viewRoles
)
{
string sqlCommand = @"
mp_sharedfilefolders_update(
:folderid,
:moduleid,
:foldername,
:parentid,
:parentguid,
:viewroles
)" ;
NpgsqlParameter[] arParams = new NpgsqlParameter[6];
arParams[0] = new NpgsqlParameter("folderid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = folderId
};
arParams[1] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = moduleId
};
arParams[2] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Text, 255)
{
Direction = ParameterDirection.Input,
Value = folderName
};
arParams[3] = new NpgsqlParameter("parentid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = parentId
};
arParams[4] = new NpgsqlParameter("parentguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = parentGuid.ToString()
};
arParams[5] = new NpgsqlParameter("viewroles", NpgsqlTypes.NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input,
Value = viewRoles
};
int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
sqlCommand,
arParams));
return(rowsAffected > -1);
}
public static bool UpdateGalleryImage(
int itemId,
int moduleId,
int displayOrder,
string caption,
string description,
string metaDataXml,
string imageFile,
string webImageFile,
string thumbnailFile,
DateTime uploadDate,
string uploadUser)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[11];
arParams[0] = new NpgsqlParameter("itemid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new NpgsqlParameter("displayorder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = displayOrder;
arParams[3] = new NpgsqlParameter("caption", NpgsqlTypes.NpgsqlDbType.Text, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = caption;
arParams[4] = new NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = description;
arParams[5] = new NpgsqlParameter("metadataxml", NpgsqlTypes.NpgsqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = metaDataXml;
arParams[6] = new NpgsqlParameter("imagefile", NpgsqlTypes.NpgsqlDbType.Text, 100);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = imageFile;
arParams[7] = new NpgsqlParameter("webimagefile", NpgsqlTypes.NpgsqlDbType.Text, 100);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = webImageFile;
arParams[8] = new NpgsqlParameter("thumbnailfile", NpgsqlTypes.NpgsqlDbType.Text, 100);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = thumbnailFile;
arParams[9] = new NpgsqlParameter("uploaddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = uploadDate;
arParams[10] = new NpgsqlParameter("uploaduser", NpgsqlTypes.NpgsqlDbType.Text, 100);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = uploadUser;
int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_galleryimages_update(:itemid,:moduleid,:displayorder,:caption,:description,:metadataxml,:imagefile,:webimagefile,:thumbnailfile,:uploaddate,:uploaduser)",
arParams));
return(rowsAffected > -1);
}
public static int AddRssFeed(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
int moduleId,
int userId,
string author,
string url,
string rssUrl,
DateTime createdUtc,
string imageUrl,
string feedType,
bool publishByDefault,
int sortRank)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_rssfeeds (");
sqlCommand.Append("moduleid, ");
sqlCommand.Append("createddate, ");
sqlCommand.Append("userid, ");
sqlCommand.Append("author, ");
sqlCommand.Append("url, ");
sqlCommand.Append("rssurl, ");
sqlCommand.Append("itemguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("lastmoduserguid, ");
sqlCommand.Append("lastmodutc, ");
sqlCommand.Append("imageurl, ");
sqlCommand.Append("feedtype, ");
sqlCommand.Append("sortrank, ");
sqlCommand.Append("publishbydefault )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":moduleid, ");
sqlCommand.Append(":createddate, ");
sqlCommand.Append(":userid, ");
sqlCommand.Append(":author, ");
sqlCommand.Append(":url, ");
sqlCommand.Append(":rssurl, ");
sqlCommand.Append(":itemguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":lastmoduserguid, ");
sqlCommand.Append(":lastmodutc, ");
sqlCommand.Append(":imageurl, ");
sqlCommand.Append(":feedtype, ");
sqlCommand.Append(":sortrank, ");
sqlCommand.Append(":publishbydefault )");
sqlCommand.Append(";");
sqlCommand.Append(" SELECT CURRVAL('mp_rssfeeds_itemid_seq');");
NpgsqlParameter[] arParams = new NpgsqlParameter[15];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new NpgsqlParameter("createddate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userId;
arParams[3] = new NpgsqlParameter("author", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = author;
arParams[4] = new NpgsqlParameter("url", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = url;
arParams[5] = new NpgsqlParameter("rssurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = rssUrl;
arParams[6] = new NpgsqlParameter("itemguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = itemGuid.ToString();
arParams[7] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = moduleGuid.ToString();
arParams[8] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = userGuid.ToString();
arParams[9] = new NpgsqlParameter("lastmoduserguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = userGuid.ToString();
arParams[10] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = createdUtc;
arParams[11] = new NpgsqlParameter("imageurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = imageUrl;
arParams[12] = new NpgsqlParameter("feedtype", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = feedType;
arParams[13] = new NpgsqlParameter("publishbydefault", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = publishByDefault;
arParams[14] = new NpgsqlParameter("sortrank", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = sortRank;
int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
return(newID);
}