///// <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);
}
public static bool DeleteSettingsByFeature(int moduleDefId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_moduledefinitionsettings ");
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>
/// Deletes a row from the mp_SavedQuery table. Returns true if row deleted.
/// </summary>
/// <param name="id"> id </param>
/// <returns>bool</returns>
public static bool Delete(Guid id)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_savedquery ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("id = :id ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static bool DeleteBySite(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_links ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("WHERE moduleid IN (SELECT moduleid FROM mp_modules 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;
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="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 bool DeleteByType(string taskType)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_taskqueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("serializedtasktype LIKE :tasktype ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("tasktype", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = taskType + "%";
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
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);
}
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 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 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 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>
/// 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);
}
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>
/// 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);
}
/// <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);
}
private static bool Update(
int siteID,
string keyName,
string keyValue)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_sitesettingsex ");
sqlCommand.Append("SET ");
sqlCommand.Append("keyvalue = :keyvalue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteid = :siteid AND ");
sqlCommand.Append("keyname = :keyname ");
sqlCommand.Append(";");
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("keyname", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = keyName;
arParams[2] = new NpgsqlParameter("keyvalue", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = keyValue;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static bool DeleteBySite(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_userlogins ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("userid IN (SELECT userguid FROM mp_users WHERE siteguid = :siteguid) ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Deletes all completed tasks from mp_TaskQueue table
/// </summary>
public static void DeleteCompleted()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_taskqueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("completeutc IS NOT NULL ");
sqlCommand.Append(";");
NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
null);
//NpgsqlHelper.ExecuteNonQuery(
// GetConnectionString(),
// CommandType.StoredProcedure,
// "mp_taskqueue_deletecompleted()",
// null);
}
/// <summary>
/// Inserts a row in the mp_LetterHtmlTemplate table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="title"> title </param>
/// <param name="html"> html </param>
/// <param name="lastModUTC"> lastModUTC </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
string title,
string html,
DateTime lastModUTC)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[5];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = title;
arParams[3] = new NpgsqlParameter("html", NpgsqlTypes.NpgsqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = html;
arParams[4] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Date);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = lastModUTC;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_letterhtmltemplate_insert(:guid,:siteguid,:title,:html,:lastmodutc)",
arParams);
return(rowsAffected);
}
public static bool UpdateSiteModulePermissions(int siteId, int moduleDefId, string authorizedRoles)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_sitemoduledefinitions ");
sqlCommand.Append("SET ");
sqlCommand.Append("authorizedroles = :authorizedroles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteid = :siteid AND ");
sqlCommand.Append("moduledefid = :moduledefid ");
sqlCommand.Append(";");
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("moduledefid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleDefId;
arParams[2] = new NpgsqlParameter("authorizedroles", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = authorizedRoles;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Updates a row in the mp_RssFeedEntries table. Returns true if row updated.
/// </summary>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="confirmed"> confirmed </param>
/// <param name="entryHash"> entryHash </param>
/// <returns>bool</returns>
public static bool UpdatePublishing(
Guid moduleGuid,
bool confirmed,
int entryHash)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_rssfeedentries ");
sqlCommand.Append("SET ");
sqlCommand.Append("confirmed = :confirmed ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleguid = :moduleguid ");
sqlCommand.Append(" AND entryhash = :entryhash ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new NpgsqlParameter("confirmed", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = confirmed;
arParams[2] = new NpgsqlParameter("entryhash", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = entryHash;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static bool Delete(
string loginProvider,
string providerKey,
string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_userlogins ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("loginprovider = :loginprovider AND ");
sqlCommand.Append("providerkey = :providerkey AND ");
sqlCommand.Append("userid = :userid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("loginprovider", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = loginProvider;
arParams[1] = new NpgsqlParameter("providerkey", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = providerKey;
arParams[2] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 128);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
private static bool UpdateStatus(
Guid guid,
DateTime startUTC,
DateTime lastStatusUpdateUTC,
double completeRatio,
string status)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[5];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("startutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = startUTC;
arParams[2] = new NpgsqlParameter("laststatusupdateutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = lastStatusUpdateUTC;
arParams[3] = new NpgsqlParameter("completeratio", NpgsqlTypes.NpgsqlDbType.Double);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = completeRatio;
arParams[4] = new NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = status;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_taskqueue_updatestart(:guid,:startutc,:laststatusupdateutc,:completeratio,:status)",
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the mp_LetterInfo table. Returns rows affected count.
/// </summary>
/// <param name="letterInfoGuid"> letterInfoGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="title"> title </param>
/// <param name="description"> description </param>
/// <param name="availableToRoles"> availableToRoles </param>
/// <param name="enabled"> enabled </param>
/// <param name="allowUserFeedback"> allowUserFeedback </param>
/// <param name="allowAnonFeedback"> allowAnonFeedback </param>
/// <param name="fromAddress"> fromAddress </param>
/// <param name="fromName"> fromName </param>
/// <param name="replyToAddress"> replyToAddress </param>
/// <param name="sendMode"> sendMode </param>
/// <param name="enableViewAsWebPage"> enableViewAsWebPage </param>
/// <param name="enableSendLog"> enableSendLog </param>
/// <param name="rolesThatCanEdit"> rolesThatCanEdit </param>
/// <param name="rolesThatCanApprove"> rolesThatCanApprove </param>
/// <param name="rolesThatCanSend"> rolesThatCanSend </param>
/// <param name="createdUTC"> createdUTC </param>
/// <param name="createdBy"> createdBy </param>
/// <param name="lastModUTC"> lastModUTC </param>
/// <param name="lastModBy"> lastModBy </param>
/// <returns>int</returns>
public static int Create(
Guid letterInfoGuid,
Guid siteGuid,
string title,
string description,
string availableToRoles,
bool enabled,
bool allowUserFeedback,
bool allowAnonFeedback,
string fromAddress,
string fromName,
string replyToAddress,
int sendMode,
bool enableViewAsWebPage,
bool enableSendLog,
string rolesThatCanEdit,
string rolesThatCanApprove,
string rolesThatCanSend,
DateTime createdUtc,
Guid createdBy,
DateTime lastModUtc,
Guid lastModBy,
bool allowArchiveView,
bool profileOptIn,
int sortRank,
string displayNameDefault,
string firstNameDefault,
string lastNameDefault)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_letterinfo (");
sqlCommand.Append("letterinfoguid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("title, ");
sqlCommand.Append("description, ");
sqlCommand.Append("availabletoroles, ");
sqlCommand.Append("enabled, ");
sqlCommand.Append("allowuserfeedback, ");
sqlCommand.Append("allowanonfeedback, ");
sqlCommand.Append("fromaddress, ");
sqlCommand.Append("fromname, ");
sqlCommand.Append("replytoaddress, ");
sqlCommand.Append("sendmode, ");
sqlCommand.Append("enableviewaswebpage, ");
sqlCommand.Append("enablesendlog, ");
sqlCommand.Append("rolesthatcanedit, ");
sqlCommand.Append("rolesthatcanapprove, ");
sqlCommand.Append("rolesthatcansend, ");
sqlCommand.Append("subscribercount, ");
sqlCommand.Append("unverifiedcount, ");
sqlCommand.Append("createdutc, ");
sqlCommand.Append("createdby, ");
sqlCommand.Append("lastmodutc, ");
sqlCommand.Append("lastmodby, ");
sqlCommand.Append("allowarchiveview, ");
sqlCommand.Append("profileoptin, ");
sqlCommand.Append("displaynamedefault, ");
sqlCommand.Append("firstnamedefault, ");
sqlCommand.Append("lastnamedefault, ");
sqlCommand.Append("sortrank )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":letterinfoguid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":title, ");
sqlCommand.Append(":description, ");
sqlCommand.Append(":availabletoroles, ");
sqlCommand.Append(":enabled, ");
sqlCommand.Append(":allowuserfeedback, ");
sqlCommand.Append(":allowanonfeedback, ");
sqlCommand.Append(":fromaddress, ");
sqlCommand.Append(":fromname, ");
sqlCommand.Append(":replytoaddress, ");
sqlCommand.Append(":sendmode, ");
sqlCommand.Append(":enableviewaswebpage, ");
sqlCommand.Append(":enablesendlog, ");
sqlCommand.Append(":rolesthatcanedit, ");
sqlCommand.Append(":rolesthatcanapprove, ");
sqlCommand.Append(":rolesthatcansend, ");
sqlCommand.Append(":subscribercount, ");
sqlCommand.Append("0, ");
sqlCommand.Append(":createdutc, ");
sqlCommand.Append(":createdby, ");
sqlCommand.Append(":lastmodutc, ");
sqlCommand.Append(":lastmodby, ");
sqlCommand.Append(":allowarchiveview, ");
sqlCommand.Append(":profileoptin, ");
sqlCommand.Append(":displaynamedefault, ");
sqlCommand.Append(":firstnamedefault, ");
sqlCommand.Append(":lastnamedefault, ");
sqlCommand.Append(":sortrank ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[28];
arParams[0] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 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("availabletoroles", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = availableToRoles;
arParams[5] = new NpgsqlParameter("enabled", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = enabled;
arParams[6] = new NpgsqlParameter("allowuserfeedback", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = allowUserFeedback;
arParams[7] = new NpgsqlParameter("allowanonfeedback", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = allowAnonFeedback;
arParams[8] = new NpgsqlParameter("fromaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = fromAddress;
arParams[9] = new NpgsqlParameter("fromname", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = fromName;
arParams[10] = new NpgsqlParameter("replytoaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = replyToAddress;
arParams[11] = new NpgsqlParameter("sendmode", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = sendMode;
arParams[12] = new NpgsqlParameter("enableviewaswebpage", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = enableViewAsWebPage;
arParams[13] = new NpgsqlParameter("enablesendlog", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = enableSendLog;
arParams[14] = new NpgsqlParameter("rolesthatcanedit", NpgsqlTypes.NpgsqlDbType.Text);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = rolesThatCanEdit;
arParams[15] = new NpgsqlParameter("rolesthatcanapprove", NpgsqlTypes.NpgsqlDbType.Text);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = rolesThatCanApprove;
arParams[16] = new NpgsqlParameter("rolesthatcansend", NpgsqlTypes.NpgsqlDbType.Text);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = rolesThatCanSend;
arParams[17] = new NpgsqlParameter("subscribercount", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = 0;
arParams[18] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = createdUtc;
arParams[19] = new NpgsqlParameter("createdby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = createdBy.ToString();
arParams[20] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = lastModUtc;
arParams[21] = new NpgsqlParameter("lastmodby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = lastModBy.ToString();
arParams[22] = new NpgsqlParameter("allowarchiveview", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = allowArchiveView;
arParams[23] = new NpgsqlParameter("profileoptin", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = profileOptIn;
arParams[24] = new NpgsqlParameter("sortrank", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = sortRank;
arParams[25] = new NpgsqlParameter("displaynamedefault", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = displayNameDefault;
arParams[26] = new NpgsqlParameter("firstnamedefault", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = firstNameDefault;
arParams[27] = new NpgsqlParameter("lastnamedefault", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[27].Direction = ParameterDirection.Input;
arParams[27].Value = lastNameDefault;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_Currency table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="title"> title </param>
/// <param name="code"> code </param>
/// <param name="symbolLeft"> symbolLeft </param>
/// <param name="symbolRight"> symbolRight </param>
/// <param name="decimalPointChar"> decimalPointChar </param>
/// <param name="thousandsPointChar"> thousandsPointChar </param>
/// <param name="decimalPlaces"> decimalPlaces </param>
/// <param name="value"> value </param>
/// <param name="lastModified"> lastModified </param>
/// <param name="created"> created </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
string title,
string code,
string symbolLeft,
string symbolRight,
string decimalPointChar,
string thousandsPointChar,
string decimalPlaces,
decimal value,
DateTime lastModified,
DateTime created)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[11];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new NpgsqlParameter("code", NpgsqlTypes.NpgsqlDbType.Text, 3);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = code;
arParams[3] = new NpgsqlParameter("symbolleft", NpgsqlTypes.NpgsqlDbType.Varchar, 15);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = symbolLeft;
arParams[4] = new NpgsqlParameter("symbolright", NpgsqlTypes.NpgsqlDbType.Varchar, 15);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = symbolRight;
arParams[5] = new NpgsqlParameter("decimalpointchar", NpgsqlTypes.NpgsqlDbType.Text, 1);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = decimalPointChar;
arParams[6] = new NpgsqlParameter("thousandspointchar", NpgsqlTypes.NpgsqlDbType.Text, 1);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = thousandsPointChar;
arParams[7] = new NpgsqlParameter("decimalplaces", NpgsqlTypes.NpgsqlDbType.Text, 1);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = decimalPlaces;
arParams[8] = new NpgsqlParameter("value", NpgsqlTypes.NpgsqlDbType.Numeric);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = value;
arParams[9] = new NpgsqlParameter("lastmodified", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = lastModified;
arParams[10] = new NpgsqlParameter("created", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = created;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_currency (");
sqlCommand.Append("guid, ");
sqlCommand.Append("title, ");
sqlCommand.Append("code, ");
sqlCommand.Append("symbolleft, ");
sqlCommand.Append("symbolright, ");
sqlCommand.Append("decimalpointchar, ");
sqlCommand.Append("thousandspointchar, ");
sqlCommand.Append("decimalplaces, ");
sqlCommand.Append("value, ");
sqlCommand.Append("lastmodified, ");
sqlCommand.Append("created )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":title, ");
sqlCommand.Append(":code, ");
sqlCommand.Append(":symbolleft, ");
sqlCommand.Append(":symbolright, ");
sqlCommand.Append(":decimalpointchar, ");
sqlCommand.Append(":thousandspointchar, ");
sqlCommand.Append(":decimalplaces, ");
sqlCommand.Append(":value, ");
sqlCommand.Append(":lastmodified, ");
sqlCommand.Append(":created ");
sqlCommand.Append(")");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_ContentMeta table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="name"> name </param>
/// <param name="scheme"> scheme </param>
/// <param name="langCode"> langCode </param>
/// <param name="dir"> dir </param>
/// <param name="metaContent"> metaContent </param>
/// <param name="sortRank"> sortRank </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="createdBy"> createdBy </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid moduleGuid,
Guid contentGuid,
string name,
string scheme,
string langCode,
string dir,
string metaContent,
int sortRank,
DateTime createdUtc,
Guid createdBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_contentmeta (");
sqlCommand.Append("guid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("contentguid, ");
sqlCommand.Append("name, ");
sqlCommand.Append("scheme, ");
sqlCommand.Append("langcode, ");
sqlCommand.Append("dir, ");
sqlCommand.Append("metacontent, ");
sqlCommand.Append("sortrank, ");
sqlCommand.Append("createdutc, ");
sqlCommand.Append("createdby, ");
sqlCommand.Append("lastmodutc, ");
sqlCommand.Append("lastmodby )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":contentguid, ");
sqlCommand.Append(":name, ");
sqlCommand.Append(":scheme, ");
sqlCommand.Append(":langcode, ");
sqlCommand.Append(":dir, ");
sqlCommand.Append(":metacontent, ");
sqlCommand.Append(":sortrank, ");
sqlCommand.Append(":createdutc, ");
sqlCommand.Append(":createdby, ");
sqlCommand.Append(":lastmodutc, ");
sqlCommand.Append(":lastmodby ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[14];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = moduleGuid.ToString();
arParams[3] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = contentGuid.ToString();
arParams[4] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = name;
arParams[5] = new NpgsqlParameter("scheme", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = scheme;
arParams[6] = new NpgsqlParameter("langcode", NpgsqlTypes.NpgsqlDbType.Varchar, 10);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = langCode;
arParams[7] = new NpgsqlParameter("dir", NpgsqlTypes.NpgsqlDbType.Varchar, 3);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = dir;
arParams[8] = new NpgsqlParameter("metacontent", NpgsqlTypes.NpgsqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = metaContent;
arParams[9] = new NpgsqlParameter("sortrank", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortRank;
arParams[10] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = createdUtc;
arParams[11] = new NpgsqlParameter("createdby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = createdBy.ToString();
arParams[12] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = createdUtc;
arParams[13] = new NpgsqlParameter("lastmodby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = createdBy.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_SavedQuery table. Returns rows affected count.
/// </summary>
/// <param name="id"> id </param>
/// <param name="name"> name </param>
/// <param name="statement"> statement </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="createdBy"> createdBy </param>
/// <returns>int</returns>
public static int Create(
Guid id,
string name,
string statement,
DateTime createdUtc,
Guid createdBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_savedquery (");
sqlCommand.Append("id, ");
sqlCommand.Append("name, ");
sqlCommand.Append("statement, ");
sqlCommand.Append("createdutc, ");
sqlCommand.Append("createdby, ");
sqlCommand.Append("lastmodutc, ");
sqlCommand.Append("lastmodby )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":id, ");
sqlCommand.Append(":name, ");
sqlCommand.Append(":statement, ");
sqlCommand.Append(":createdutc, ");
sqlCommand.Append(":createdby, ");
sqlCommand.Append(":lastmodutc, ");
sqlCommand.Append(":lastmodby ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[7];
arParams[0] = new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id.ToString();
arParams[1] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = name;
arParams[2] = new NpgsqlParameter("statement", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = statement;
arParams[3] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = createdUtc;
arParams[4] = new NpgsqlParameter("createdby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = createdBy.ToString();
arParams[5] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdUtc;
arParams[6] = new NpgsqlParameter("lastmodby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = createdBy.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_UserLocation table. Returns rows affected count.
/// </summary>
/// <param name="rowID"> rowID </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="iPAddress"> iPAddress </param>
/// <param name="iPAddressLong"> iPAddressLong </param>
/// <param name="hostname"> hostname </param>
/// <param name="longitude"> longitude </param>
/// <param name="latitude"> latitude </param>
/// <param name="iSP"> iSP </param>
/// <param name="continent"> continent </param>
/// <param name="country"> country </param>
/// <param name="region"> region </param>
/// <param name="city"> city </param>
/// <param name="timeZone"> timeZone </param>
/// <param name="captureCount"> captureCount </param>
/// <param name="firstCaptureUTC"> firstCaptureUTC </param>
/// <param name="lastCaptureUTC"> lastCaptureUTC </param>
/// <returns>int</returns>
public static int Create(
Guid rowID,
Guid userGuid,
Guid siteGuid,
string iPAddress,
long iPAddressLong,
string hostname,
double longitude,
double latitude,
string iSP,
string continent,
string country,
string region,
string city,
string timeZone,
int captureCount,
DateTime firstCaptureUTC,
DateTime lastCaptureUTC)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[17];
arParams[0] = new NpgsqlParameter("rowid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowID.ToString();
arParams[1] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
arParams[2] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new NpgsqlParameter("ipaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = iPAddress;
arParams[4] = new NpgsqlParameter("ipaddresslong", NpgsqlTypes.NpgsqlDbType.Bigint);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = iPAddressLong;
arParams[5] = new NpgsqlParameter("hostname", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = hostname;
arParams[6] = new NpgsqlParameter("longitude", NpgsqlTypes.NpgsqlDbType.Double);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = longitude;
arParams[7] = new NpgsqlParameter("latitude", NpgsqlTypes.NpgsqlDbType.Double);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = latitude;
arParams[8] = new NpgsqlParameter("isp", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = iSP;
arParams[9] = new NpgsqlParameter("continent", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = continent;
arParams[10] = new NpgsqlParameter("country", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = country;
arParams[11] = new NpgsqlParameter("region", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = region;
arParams[12] = new NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = city;
arParams[13] = new NpgsqlParameter("timezone", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = timeZone;
arParams[14] = new NpgsqlParameter("capturecount", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = captureCount;
arParams[15] = new NpgsqlParameter("firstcaptureutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = firstCaptureUTC;
arParams[16] = new NpgsqlParameter("lastcaptureutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = lastCaptureUTC;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_userlocation_insert(:rowid,:userguid,:siteguid,:ipaddress,:ipaddresslong,:hostname,:longitude,:latitude,:isp,:continent,:country,:region,:city,:timezone,:capturecount,:firstcaptureutc,:lastcaptureutc)",
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_PlugNPayLog table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="responseCode"> responseCode </param>
/// <param name="responseReasonCode"> responseReasonCode </param>
/// <param name="reason"> reason </param>
/// <param name="avsCode"> avsCode </param>
/// <param name="ccvCode"> ccvCode </param>
/// <param name="cavCode"> cavCode </param>
/// <param name="transactionId"> transactionId </param>
/// <param name="transactionType"> transactionType </param>
/// <param name="method"> method </param>
/// <param name="authCode"> authCode </param>
/// <param name="amount"> amount </param>
/// <param name="tax"> tax </param>
/// <param name="duty"> duty </param>
/// <param name="freight"> freight </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string rawResponse,
string responseCode,
string responseReasonCode,
string reason,
string avsCode,
string ccvCode,
string cavCode,
string transactionId,
string transactionType,
string method,
string authCode,
decimal amount,
decimal tax,
decimal duty,
decimal freight)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[21];
arParams[0] = new NpgsqlParameter("rowguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new NpgsqlParameter("storeguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new NpgsqlParameter("cartguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new NpgsqlParameter("rawresponse", NpgsqlTypes.NpgsqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = rawResponse;
arParams[7] = new NpgsqlParameter("responsecode", NpgsqlTypes.NpgsqlDbType.Varchar, 10);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = responseCode;
arParams[8] = new NpgsqlParameter("responsereasoncode", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = responseReasonCode;
arParams[9] = new NpgsqlParameter("reason", NpgsqlTypes.NpgsqlDbType.Text);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = reason;
arParams[10] = new NpgsqlParameter("avscode", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = avsCode;
arParams[11] = new NpgsqlParameter("ccvcode", NpgsqlTypes.NpgsqlDbType.Varchar, 10);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = ccvCode;
arParams[12] = new NpgsqlParameter("cavcode", NpgsqlTypes.NpgsqlDbType.Varchar, 10);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = cavCode;
arParams[13] = new NpgsqlParameter("transactionid", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = transactionId;
arParams[14] = new NpgsqlParameter("transactiontype", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = transactionType;
arParams[15] = new NpgsqlParameter("method", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = method;
arParams[16] = new NpgsqlParameter("authcode", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = authCode;
arParams[17] = new NpgsqlParameter("amount", NpgsqlTypes.NpgsqlDbType.Numeric);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = amount;
arParams[18] = new NpgsqlParameter("tax", NpgsqlTypes.NpgsqlDbType.Numeric);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = tax;
arParams[19] = new NpgsqlParameter("duty", NpgsqlTypes.NpgsqlDbType.Numeric);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = duty;
arParams[20] = new NpgsqlParameter("freight", NpgsqlTypes.NpgsqlDbType.Numeric);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = freight;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_PlugNPaylog (");
sqlCommand.Append("rowguid, ");
sqlCommand.Append("createdutc, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("storeguid, ");
sqlCommand.Append("cartguid, ");
sqlCommand.Append("rawresponse, ");
sqlCommand.Append("responsecode, ");
sqlCommand.Append("responsereasoncode, ");
sqlCommand.Append("reason, ");
sqlCommand.Append("avscode, ");
sqlCommand.Append("ccvcode, ");
sqlCommand.Append("cavcode, ");
sqlCommand.Append("transactionid, ");
sqlCommand.Append("transactiontype, ");
sqlCommand.Append("method, ");
sqlCommand.Append("authcode, ");
sqlCommand.Append("amount, ");
sqlCommand.Append("tax, ");
sqlCommand.Append("duty, ");
sqlCommand.Append("freight )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":rowguid, ");
sqlCommand.Append(":createdutc, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":storeguid, ");
sqlCommand.Append(":cartguid, ");
sqlCommand.Append(":rawresponse, ");
sqlCommand.Append(":responsecode, ");
sqlCommand.Append(":responsereasoncode, ");
sqlCommand.Append(":reason, ");
sqlCommand.Append(":avscode, ");
sqlCommand.Append(":ccvcode, ");
sqlCommand.Append(":cavcode, ");
sqlCommand.Append(":transactionid, ");
sqlCommand.Append(":transactiontype, ");
sqlCommand.Append(":method, ");
sqlCommand.Append(":authcode, ");
sqlCommand.Append(":amount, ");
sqlCommand.Append(":tax, ");
sqlCommand.Append(":duty, ");
sqlCommand.Append(":freight ");
sqlCommand.Append(")");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}