public static int Create(
int siteId,
int parentId,
string pageName,
string pageTitle,
string skin,
int pageOrder,
string authorizedRoles,
string editRoles,
string draftEditRoles,
string draftApprovalRoles,
string createChildPageRoles,
string createChildDraftRoles,
bool requireSsl,
bool allowBrowserCache,
bool showBreadcrumbs,
bool showChildPageBreadcrumbs,
string pageKeyWords,
string pageDescription,
string pageEncoding,
string additionalMetaTags,
bool useUrl,
string url,
bool openInNewWindow,
bool showChildPageMenu,
bool hideMainMenu,
bool includeInMenu,
String menuImage,
string changeFrequency,
string siteMapPriority,
Guid pageGuid,
Guid parentGuid,
bool hideAfterLogin,
Guid siteGuid,
string compiledMeta,
DateTime compiledMetaUtc,
bool includeInSiteMap,
bool isClickable,
bool showHomeCrumb,
bool isPending,
string canonicalOverride,
bool includeInSearchMap,
bool enableComments,
bool includeInChildSiteMap,
bool expandOnSiteMap,
Guid pubTeamId,
string bodyCssClass,
string menuCssClass,
int publishMode,
Guid createdBy,
string createdFromIp,
string menuDescription,
string linkRel,
string pageHeading,
bool showPageHeading,
DateTime pubDateUtc)
{
SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_Pages_Insert", 60);
sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
sph.DefineSqlParameter("@ParentID", SqlDbType.Int, ParameterDirection.Input, parentId);
sph.DefineSqlParameter("@PageName", SqlDbType.NVarChar, 255, ParameterDirection.Input, pageName);
sph.DefineSqlParameter("@PageOrder", SqlDbType.Int, ParameterDirection.Input, pageOrder);
sph.DefineSqlParameter("@AuthorizedRoles", SqlDbType.NVarChar, -1, ParameterDirection.Input, authorizedRoles);
sph.DefineSqlParameter("@EditRoles", SqlDbType.NVarChar, -1, ParameterDirection.Input, editRoles);
sph.DefineSqlParameter("@DraftEditRoles", SqlDbType.NVarChar, -1, ParameterDirection.Input, draftEditRoles);
sph.DefineSqlParameter("@DraftApprovalRoles", SqlDbType.NVarChar, -1, ParameterDirection.Input, draftApprovalRoles);
sph.DefineSqlParameter("@CreateChildPageRoles", SqlDbType.NVarChar, -1, ParameterDirection.Input, createChildPageRoles);
sph.DefineSqlParameter("@CreateChildDraftRoles", SqlDbType.NVarChar, -1, ParameterDirection.Input, createChildDraftRoles);
sph.DefineSqlParameter("@RequireSSL", SqlDbType.Bit, ParameterDirection.Input, requireSsl);
sph.DefineSqlParameter("@ShowBreadcrumbs", SqlDbType.Bit, ParameterDirection.Input, showBreadcrumbs);
sph.DefineSqlParameter("@ShowChildPageBreadcrumbs", SqlDbType.Bit, ParameterDirection.Input, showChildPageBreadcrumbs);
sph.DefineSqlParameter("@PageKeyWords", SqlDbType.NVarChar, 1000, ParameterDirection.Input, pageKeyWords);
sph.DefineSqlParameter("@PageDescription", SqlDbType.NVarChar, 255, ParameterDirection.Input, pageDescription);
sph.DefineSqlParameter("@PageEncoding", SqlDbType.NVarChar, 255, ParameterDirection.Input, pageEncoding);
sph.DefineSqlParameter("@AdditionalMetaTags", SqlDbType.NVarChar, 255, ParameterDirection.Input, additionalMetaTags);
sph.DefineSqlParameter("@UseUrl", SqlDbType.Bit, ParameterDirection.Input, useUrl);
sph.DefineSqlParameter("@Url", SqlDbType.NVarChar, 255, ParameterDirection.Input, url);
sph.DefineSqlParameter("@OpenInNewWindow", SqlDbType.Bit, ParameterDirection.Input, openInNewWindow);
sph.DefineSqlParameter("@ShowChildPageMenu", SqlDbType.Bit, ParameterDirection.Input, showChildPageMenu);
sph.DefineSqlParameter("@HideMainMenu", SqlDbType.Bit, ParameterDirection.Input, hideMainMenu);
sph.DefineSqlParameter("@Skin", SqlDbType.NVarChar, 100, ParameterDirection.Input, skin);
sph.DefineSqlParameter("@IncludeInMenu", SqlDbType.Bit, ParameterDirection.Input, includeInMenu);
sph.DefineSqlParameter("@MenuImage", SqlDbType.NVarChar, 255, ParameterDirection.Input, menuImage);
sph.DefineSqlParameter("@PageTitle", SqlDbType.NVarChar, 255, ParameterDirection.Input, pageTitle);
sph.DefineSqlParameter("@AllowBrowserCache", SqlDbType.Bit, ParameterDirection.Input, allowBrowserCache);
sph.DefineSqlParameter("@ChangeFrequency", SqlDbType.NVarChar, 20, ParameterDirection.Input, changeFrequency);
sph.DefineSqlParameter("@SiteMapPriority", SqlDbType.NVarChar, 10, ParameterDirection.Input, siteMapPriority);
sph.DefineSqlParameter("@LastModifiedUTC", SqlDbType.DateTime, ParameterDirection.Input, DateTime.UtcNow);
sph.DefineSqlParameter("@PageGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, pageGuid);
sph.DefineSqlParameter("@ParentGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, parentGuid);
sph.DefineSqlParameter("@HideAfterLogin", SqlDbType.Bit, ParameterDirection.Input, hideAfterLogin);
sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
sph.DefineSqlParameter("@CompiledMeta", SqlDbType.NVarChar, -1, ParameterDirection.Input, compiledMeta);
sph.DefineSqlParameter("@CompiledMetaUtc", SqlDbType.DateTime, ParameterDirection.Input, compiledMetaUtc);
sph.DefineSqlParameter("@IncludeInSiteMap", SqlDbType.Bit, ParameterDirection.Input, includeInSiteMap);
sph.DefineSqlParameter("@IsClickable", SqlDbType.Bit, ParameterDirection.Input, isClickable);
sph.DefineSqlParameter("@ShowHomeCrumb", SqlDbType.Bit, ParameterDirection.Input, showHomeCrumb);
sph.DefineSqlParameter("@IsPending", SqlDbType.Bit, ParameterDirection.Input, isPending);
sph.DefineSqlParameter("@CanonicalOverride", SqlDbType.NVarChar, 255, ParameterDirection.Input, canonicalOverride);
sph.DefineSqlParameter("@IncludeInSearchMap", SqlDbType.Bit, ParameterDirection.Input, includeInSearchMap);
sph.DefineSqlParameter("@EnableComments", SqlDbType.Bit, ParameterDirection.Input, enableComments);
sph.DefineSqlParameter("@IncludeInChildSiteMap", SqlDbType.Bit, ParameterDirection.Input, includeInChildSiteMap);
sph.DefineSqlParameter("@ExpandOnSiteMap", SqlDbType.Bit, ParameterDirection.Input, expandOnSiteMap);
sph.DefineSqlParameter("@PubTeamId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, pubTeamId);
sph.DefineSqlParameter("@BodyCssClass", SqlDbType.NVarChar, 50, ParameterDirection.Input, bodyCssClass);
sph.DefineSqlParameter("@MenuCssClass", SqlDbType.NVarChar, 50, ParameterDirection.Input, menuCssClass);
sph.DefineSqlParameter("@PublishMode", SqlDbType.Int, ParameterDirection.Input, publishMode);
sph.DefineSqlParameter("@PCreatedUtc", SqlDbType.DateTime, ParameterDirection.Input, DateTime.UtcNow);
sph.DefineSqlParameter("@PCreatedBy", SqlDbType.UniqueIdentifier, ParameterDirection.Input, createdBy);
sph.DefineSqlParameter("@PCreatedFromIp", SqlDbType.NVarChar, 36, ParameterDirection.Input, createdFromIp);
sph.DefineSqlParameter("@PLastModUtc", SqlDbType.DateTime, ParameterDirection.Input, DateTime.UtcNow);
sph.DefineSqlParameter("@PLastModBy", SqlDbType.UniqueIdentifier, ParameterDirection.Input, createdBy);
sph.DefineSqlParameter("@PLastModFromIp", SqlDbType.NVarChar, 36, ParameterDirection.Input, createdFromIp);
sph.DefineSqlParameter("@MenuDesc", SqlDbType.NVarChar, -1, ParameterDirection.Input, menuDescription);
sph.DefineSqlParameter("@LinkRel", SqlDbType.NVarChar, 20, ParameterDirection.Input, linkRel);
sph.DefineSqlParameter("@PageHeading", SqlDbType.NVarChar, 255, ParameterDirection.Input, pageHeading);
sph.DefineSqlParameter("@ShowPageHeading", SqlDbType.Bit, ParameterDirection.Input, showPageHeading);
if (pubDateUtc == DateTime.MaxValue)
{
sph.DefineSqlParameter("@PubDateUtc", SqlDbType.DateTime, ParameterDirection.Input, DBNull.Value);
}
else
{
sph.DefineSqlParameter("@PubDateUtc", SqlDbType.DateTime, ParameterDirection.Input, pubDateUtc);
}
int newID = Convert.ToInt32(sph.ExecuteScalar());
return(newID);
}
/// <summary>
/// Inserts a row in the mp_Comments table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="parentGuid"> parentGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="title"> title </param>
/// <param name="userComment"> userComment </param>
/// <param name="userName"> userName </param>
/// <param name="userEmail"> userEmail </param>
/// <param name="userUrl"> userUrl </param>
/// <param name="userIp"> userIp </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="moderationStatus"> moderationStatus </param>
/// <param name="moderatedBy"> moderatedBy </param>
/// <param name="moderationReason"> moderationReason </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid parentGuid,
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
Guid contentGuid,
Guid userGuid,
string title,
string userComment,
string userName,
string userEmail,
string userUrl,
string userIp,
DateTime createdUtc,
byte moderationStatus,
Guid moderatedBy,
string moderationReason)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_comments (");
sqlCommand.Append("guid, ");
sqlCommand.Append("parentguid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("featureguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("contentguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("title, ");
sqlCommand.Append("usercomment, ");
sqlCommand.Append("username, ");
sqlCommand.Append("useremail, ");
sqlCommand.Append("userurl, ");
sqlCommand.Append("userip, ");
sqlCommand.Append("createdutc, ");
sqlCommand.Append("lastmodutc, ");
sqlCommand.Append("moderationstatus, ");
sqlCommand.Append("moderatedby, ");
sqlCommand.Append("moderationreason )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":parentguid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":featureguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":contentguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":title, ");
sqlCommand.Append(":usercomment, ");
sqlCommand.Append(":username, ");
sqlCommand.Append(":useremail, ");
sqlCommand.Append(":userurl, ");
sqlCommand.Append(":userip, ");
sqlCommand.Append(":createdutc, ");
sqlCommand.Append(":lastmodutc, ");
sqlCommand.Append(":moderationstatus, ");
sqlCommand.Append(":moderatedby, ");
sqlCommand.Append(":moderationreason ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[18];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("parentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = parentGuid.ToString();
arParams[2] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new NpgsqlParameter("featureguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = featureGuid.ToString();
arParams[4] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = moduleGuid.ToString();
arParams[5] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = contentGuid.ToString();
arParams[6] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userGuid.ToString();
arParams[7] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = title;
arParams[8] = new NpgsqlParameter("usercomment", NpgsqlTypes.NpgsqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = userComment;
arParams[9] = new NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = userName;
arParams[10] = new NpgsqlParameter("useremail", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = userEmail;
arParams[11] = new NpgsqlParameter("userurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = userUrl;
arParams[12] = new NpgsqlParameter("userip", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = userIp;
arParams[13] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = createdUtc;
arParams[14] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = createdUtc;
arParams[15] = new NpgsqlParameter("moderationstatus", NpgsqlTypes.NpgsqlDbType.Smallint);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = moderationStatus;
arParams[16] = new NpgsqlParameter("moderatedby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = moderatedBy.ToString();
arParams[17] = new NpgsqlParameter("moderationreason", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = moderationReason;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_EmailTemplate table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="specialGuid1"> specialGuid1 </param>
/// <param name="specialGuid2"> specialGuid2 </param>
/// <param name="name"> name </param>
/// <param name="subject"> subject </param>
/// <param name="textBody"> textBody </param>
/// <param name="htmlBody"> htmlBody </param>
/// <param name="hasHtml"> hasHtml </param>
/// <param name="isEditable"> isEditable </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <param name="lastModBy"> lastModBy </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
Guid specialGuid1,
Guid specialGuid2,
string name,
string subject,
string textBody,
string htmlBody,
bool hasHtml,
bool isEditable,
DateTime createdUtc,
Guid lastModBy)
{
#region Bit Conversion
int intHasHtml = 0;
if (hasHtml)
{
intHasHtml = 1;
}
int intIsEditable = 0;
if (isEditable)
{
intIsEditable = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_EmailTemplate (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("SpecialGuid1, ");
sqlCommand.Append("SpecialGuid2, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("Subject, ");
sqlCommand.Append("TextBody, ");
sqlCommand.Append("HtmlBody, ");
sqlCommand.Append("HasHtml, ");
sqlCommand.Append("IsEditable, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("LastModUtc, ");
sqlCommand.Append("LastModBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?FeatureGuid, ");
sqlCommand.Append("?ModuleGuid, ");
sqlCommand.Append("?SpecialGuid1, ");
sqlCommand.Append("?SpecialGuid2, ");
sqlCommand.Append("?Name, ");
sqlCommand.Append("?Subject, ");
sqlCommand.Append("?TextBody, ");
sqlCommand.Append("?HtmlBody, ");
sqlCommand.Append("?HasHtml, ");
sqlCommand.Append("?IsEditable, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?LastModUtc, ");
sqlCommand.Append("?LastModBy )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[15];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?FeatureGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = featureGuid.ToString();
arParams[3] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = moduleGuid.ToString();
arParams[4] = new MySqlParameter("?SpecialGuid1", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = specialGuid1.ToString();
arParams[5] = new MySqlParameter("?SpecialGuid2", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = specialGuid2.ToString();
arParams[6] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = name;
arParams[7] = new MySqlParameter("?Subject", MySqlDbType.VarChar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = subject;
arParams[8] = new MySqlParameter("?TextBody", MySqlDbType.LongText);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = textBody;
arParams[9] = new MySqlParameter("?HtmlBody", MySqlDbType.LongText);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = htmlBody;
arParams[10] = new MySqlParameter("?HasHtml", MySqlDbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = intHasHtml;
arParams[11] = new MySqlParameter("?IsEditable", MySqlDbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = intIsEditable;
arParams[12] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = createdUtc;
arParams[13] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = createdUtc;
arParams[14] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = lastModBy.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_EmailSendQueue table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="specialGuid1"> specialGuid1 </param>
/// <param name="specialGuid2"> specialGuid2 </param>
/// <param name="fromAddress"> fromAddress </param>
/// <param name="replyTo"> replyTo </param>
/// <param name="toAddress"> toAddress </param>
/// <param name="ccAddress"> ccAddress </param>
/// <param name="bccAddress"> bccAddress </param>
/// <param name="subject"> subject </param>
/// <param name="textBody"> textBody </param>
/// <param name="htmlBody"> htmlBody </param>
/// <param name="type"> type </param>
/// <param name="dateToSend"> dateToSend </param>
/// <param name="createdUtc"> createdUtc </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid moduleGuid,
Guid userGuid,
Guid specialGuid1,
Guid specialGuid2,
string fromAddress,
string replyTo,
string toAddress,
string ccAddress,
string bccAddress,
string subject,
string textBody,
string htmlBody,
string type,
DateTime dateToSend,
DateTime createdUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_emailsendqueue (");
sqlCommand.Append("guid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("specialguid1, ");
sqlCommand.Append("specialguid2, ");
sqlCommand.Append("fromaddress, ");
sqlCommand.Append("replyto, ");
sqlCommand.Append("toaddress, ");
sqlCommand.Append("ccaddress, ");
sqlCommand.Append("bccaddress, ");
sqlCommand.Append("subject, ");
sqlCommand.Append("textbody, ");
sqlCommand.Append("htmlbody, ");
sqlCommand.Append("type, ");
sqlCommand.Append("datetosend, ");
sqlCommand.Append("createdutc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":specialguid1, ");
sqlCommand.Append(":specialguid2, ");
sqlCommand.Append(":fromaddress, ");
sqlCommand.Append(":replyto, ");
sqlCommand.Append(":toaddress, ");
sqlCommand.Append(":ccaddress, ");
sqlCommand.Append(":bccaddress, ");
sqlCommand.Append(":subject, ");
sqlCommand.Append(":textbody, ");
sqlCommand.Append(":htmlbody, ");
sqlCommand.Append(":type, ");
sqlCommand.Append(":datetosend, ");
sqlCommand.Append(":createdutc ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[17];
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("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new NpgsqlParameter("specialguid1", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = specialGuid1.ToString();
arParams[5] = new NpgsqlParameter("specialguid2", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = specialGuid2.ToString();
arParams[6] = new NpgsqlParameter("fromaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = fromAddress;
arParams[7] = new NpgsqlParameter("replyto", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = replyTo;
arParams[8] = new NpgsqlParameter("toaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = toAddress;
arParams[9] = new NpgsqlParameter("ccaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = ccAddress;
arParams[10] = new NpgsqlParameter("bccaddress", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = bccAddress;
arParams[11] = new NpgsqlParameter("subject", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = subject;
arParams[12] = new NpgsqlParameter("textbody", NpgsqlTypes.NpgsqlDbType.Text);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = textBody;
arParams[13] = new NpgsqlParameter("htmlbody", NpgsqlTypes.NpgsqlDbType.Text);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = htmlBody;
arParams[14] = new NpgsqlParameter("type", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = type;
arParams[15] = new NpgsqlParameter("datetosend", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = dateToSend;
arParams[16] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = createdUtc;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <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)
{
#region Bit Conversion
int intAllowArchiveView = 0;
if (allowArchiveView)
{
intAllowArchiveView = 1;
}
int intProfileOptIn = 0;
if (profileOptIn)
{
intProfileOptIn = 1;
}
int intEnabled = 0;
if (enabled)
{
intEnabled = 1;
}
int intAllowUserFeedback = 0;
if (allowUserFeedback)
{
intAllowUserFeedback = 1;
}
int intAllowAnonFeedback = 0;
if (allowAnonFeedback)
{
intAllowAnonFeedback = 1;
}
int intEnableViewAsWebPage = 0;
if (enableViewAsWebPage)
{
intEnableViewAsWebPage = 1;
}
int intEnableSendLog = 0;
if (enableSendLog)
{
intEnableSendLog = 1;
}
#endregion
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("AllowArchiveView, ");
sqlCommand.Append("ProfileOptIn, ");
sqlCommand.Append("SortRank, ");
sqlCommand.Append("DisplayNameDefault, ");
sqlCommand.Append("FirstNameDefault, ");
sqlCommand.Append("LastNameDefault, ");
sqlCommand.Append("CreatedUTC, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("LastModUTC, ");
sqlCommand.Append("LastModBy )");
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("0, ");
sqlCommand.Append("0, ");
sqlCommand.Append("?AllowArchiveView, ");
sqlCommand.Append("?ProfileOptIn, ");
sqlCommand.Append("?SortRank, ");
sqlCommand.Append("?DisplayNameDefault, ");
sqlCommand.Append("?FirstNameDefault, ");
sqlCommand.Append("?LastNameDefault, ");
sqlCommand.Append("?CreatedUTC, ");
sqlCommand.Append("?CreatedBy, ");
sqlCommand.Append("?LastModUTC, ");
sqlCommand.Append("?LastModBy );");
MySqlParameter[] arParams = new MySqlParameter[27];
arParams[0] = new MySqlParameter("?LetterInfoGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?Title", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = title;
arParams[3] = new MySqlParameter("?Description", MySqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = description;
arParams[4] = new MySqlParameter("?AvailableToRoles", MySqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = availableToRoles;
arParams[5] = new MySqlParameter("?Enabled", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = intEnabled;
arParams[6] = new MySqlParameter("?AllowUserFeedback", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = intAllowUserFeedback;
arParams[7] = new MySqlParameter("?AllowAnonFeedback", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = intAllowAnonFeedback;
arParams[8] = new MySqlParameter("?FromAddress", MySqlDbType.VarChar, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = fromAddress;
arParams[9] = new MySqlParameter("?FromName", MySqlDbType.VarChar, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = fromName;
arParams[10] = new MySqlParameter("?ReplyToAddress", MySqlDbType.VarChar, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = replyToAddress;
arParams[11] = new MySqlParameter("?SendMode", MySqlDbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = sendMode;
arParams[12] = new MySqlParameter("?EnableViewAsWebPage", MySqlDbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = intEnableViewAsWebPage;
arParams[13] = new MySqlParameter("?EnableSendLog", MySqlDbType.Int32);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = intEnableSendLog;
arParams[14] = new MySqlParameter("?RolesThatCanEdit", MySqlDbType.Text);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = rolesThatCanEdit;
arParams[15] = new MySqlParameter("?RolesThatCanApprove", MySqlDbType.Text);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = rolesThatCanApprove;
arParams[16] = new MySqlParameter("?RolesThatCanSend", MySqlDbType.Text);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = rolesThatCanSend;
arParams[17] = new MySqlParameter("?CreatedUTC", MySqlDbType.DateTime);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = createdUtc;
arParams[18] = new MySqlParameter("?CreatedBy", MySqlDbType.VarChar, 36);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = createdBy.ToString();
arParams[19] = new MySqlParameter("?LastModUTC", MySqlDbType.DateTime);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = lastModUtc;
arParams[20] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = lastModBy.ToString();
arParams[21] = new MySqlParameter("?AllowArchiveView", MySqlDbType.Int32);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = intAllowArchiveView;
arParams[22] = new MySqlParameter("?ProfileOptIn", MySqlDbType.Int32);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = intProfileOptIn;
arParams[23] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = sortRank;
arParams[24] = new MySqlParameter("?DisplayNameDefault", MySqlDbType.VarChar, 50);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = displayNameDefault;
arParams[25] = new MySqlParameter("?FirstNameDefault", MySqlDbType.VarChar, 50);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = firstNameDefault;
arParams[26] = new MySqlParameter("?LastNameDefault", MySqlDbType.VarChar, 50);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = lastNameDefault;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
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)
{
int exclude = 0;
if (excludeFromRecentContent)
{
exclude = 1;
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_HtmlContent ");
sqlCommand.Append("SET ");
sqlCommand.Append("BeginDate = ?BeginDate , ");
sqlCommand.Append("EndDate = ?EndDate , ");
sqlCommand.Append("Title = ?Title , ");
sqlCommand.Append("Excerpt = ?Excerpt , ");
sqlCommand.Append("Body = ?Body , ");
sqlCommand.Append("MoreLink = ?MoreLink, ");
sqlCommand.Append("ExcludeFromRecentContent = ?ExcludeFromRecentContent, ");
sqlCommand.Append("LastModUserGuid = ?LastModUserGuid, ");
sqlCommand.Append("LastModUtc = ?LastModUtc ");
sqlCommand.Append("WHERE ItemID = ?ItemID ;");
MySqlParameter[] arParams = new MySqlParameter[11];
arParams[0] = new MySqlParameter("?ItemID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new MySqlParameter("?Title", MySqlDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new MySqlParameter("?Excerpt", MySqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = excerpt;
arParams[3] = new MySqlParameter("?Body", MySqlDbType.LongText);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = body;
arParams[4] = new MySqlParameter("?MoreLink", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = moreLink;
arParams[5] = new MySqlParameter("?BeginDate", MySqlDbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = beginDate;
arParams[6] = new MySqlParameter("?EndDate", MySqlDbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = endDate;
arParams[7] = new MySqlParameter("?SortOrder", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = sortOrder;
arParams[8] = new MySqlParameter("?LastModUserGuid", MySqlDbType.VarChar, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModUserGuid.ToString();
arParams[9] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = lastModUtc;
arParams[10] = new MySqlParameter("?ExcludeFromRecentContent", MySqlDbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = exclude;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static IDataReader GetHtmlForMetaWeblogApi(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("pm.*, ");
sqlCommand.Append("m.ModuleTitle, ");
sqlCommand.Append("m.AuthorizedEditRoles, ");
sqlCommand.Append("m.IsGlobal, ");
sqlCommand.Append("h.Body, ");
sqlCommand.Append("h.ItemID, ");
sqlCommand.Append("h.ItemGuid, ");
sqlCommand.Append("h.LastModUserGuid, ");
sqlCommand.Append("h.LastModUtc, ");
sqlCommand.Append("p.PageGuid, ");
sqlCommand.Append("p.ParentID, ");
sqlCommand.Append("p.ParentGuid, ");
sqlCommand.Append("p.PageName, ");
sqlCommand.Append("p.UseUrl, ");
sqlCommand.Append("p.Url, ");
sqlCommand.Append("p.EditRoles, ");
sqlCommand.Append("p.PageOrder, ");
sqlCommand.Append("p.EnableComments, ");
sqlCommand.Append("p.IsPending, ");
sqlCommand.Append("pp.PageName As ParentName ");
sqlCommand.Append("FROM mp_PageModules pm ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_Modules m ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.ModuleID = m.ModuleID ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_HtmlContent h ");
sqlCommand.Append("ON ");
sqlCommand.Append("h.ModuleID = m.ModuleID ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_ModuleDefinitions md ");
sqlCommand.Append("ON ");
sqlCommand.Append("md.ModuleDefID = m.ModuleDefID ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_Pages p ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.PageID = p.PageID ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_Pages pp ");
sqlCommand.Append("ON ");
sqlCommand.Append("pp.PageID = p.ParentID ");
sqlCommand.Append("WHERE p.SiteID = ?SiteID ");
sqlCommand.Append("AND ");
sqlCommand.Append("md.Guid = '881e4e00-93e4-444c-b7b0-6672fb55de10' ");
sqlCommand.Append("AND ");
sqlCommand.Append("pm.PaneName = 'contentpane' ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("p.PageName, pm.ModuleOrder ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a page of data from the mp_AuthorizeNetLog table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount();
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageSize;
arParams[1] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_authorizenetlog ");
//sqlCommand.Append("ORDER BY ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// 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_ContentHistory table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="title"> title </param>
/// <param name="contentText"> contentText </param>
/// <param name="customData"> customData </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="historyUtc"> historyUtc </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid userGuid,
Guid contentGuid,
string title,
string contentText,
string customData,
DateTime createdUtc,
DateTime historyUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_contenthistory (");
sqlCommand.Append("guid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("contentguid, ");
sqlCommand.Append("title, ");
sqlCommand.Append("contenttext, ");
sqlCommand.Append("customdata, ");
sqlCommand.Append("createdutc, ");
sqlCommand.Append("historyutc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":contentguid, ");
sqlCommand.Append(":title, ");
sqlCommand.Append(":contenttext, ");
sqlCommand.Append(":customdata, ");
sqlCommand.Append(":createdutc, ");
sqlCommand.Append(":historyutc ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[9];
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("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userGuid.ToString();
arParams[3] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = contentGuid.ToString();
arParams[4] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = title;
arParams[5] = new NpgsqlParameter("contenttext", NpgsqlTypes.NpgsqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = contentText;
arParams[6] = new NpgsqlParameter("customdata", NpgsqlTypes.NpgsqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = customData;
arParams[7] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = createdUtc;
arParams[8] = new NpgsqlParameter("historyutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = historyUtc;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the mp_AuthorizeNetLog 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.Text, 1);
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.Text, 1);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = ccvCode;
arParams[12] = new NpgsqlParameter("cavcode", NpgsqlTypes.NpgsqlDbType.Text, 1);
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_authorizenetlog (");
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);
}
/// <summary>
/// Gets a page of data from the mp_ContentHistory table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
Guid contentGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(contentGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("contentguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
arParams[1] = new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ch.*, ");
sqlCommand.Append("u.name, ");
sqlCommand.Append("u.loginname, ");
sqlCommand.Append("u.email ");
sqlCommand.Append("FROM mp_contenthistory ch ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_users u ");
sqlCommand.Append("ON ");
sqlCommand.Append("u.userguid = ch.userguid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ch.contentguid = :contentguid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("ch.historyutc DESC ");
sqlCommand.Append("LIMIT :pagesize");
if (pageNumber > 1)
{
sqlCommand.Append(" OFFSET :pageoffset ");
}
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
///// <summary>
///// Gets the connection string.
///// </summary>
///// <returns></returns>
//private static string GetConnectionString()
//{
// return ConfigurationManager.AppSettings["MSSQLConnectionString"];
//}
/// <summary>
/// Inserts a row in the mp_GoogleCheckoutLog 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="notificationType"> notificationType </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="serialNumber"> serialNumber </param>
/// <param name="gTimestamp"> gTimestamp </param>
/// <param name="orderNumber"> orderNumber </param>
/// <param name="buyerId"> buyerId </param>
/// <param name="fullfillState"> fullfillState </param>
/// <param name="financeState"> financeState </param>
/// <param name="emailListOptIn"> emailListOptIn </param>
/// <param name="avsResponse"> avsResponse </param>
/// <param name="cvnResponse"> cvnResponse </param>
/// <param name="authExpDate"> authExpDate </param>
/// <param name="authAmt"> authAmt </param>
/// <param name="discountTotal"> discountTotal </param>
/// <param name="shippingTotal"> shippingTotal </param>
/// <param name="taxTotal"> taxTotal </param>
/// <param name="orderTotal"> orderTotal </param>
/// <param name="latestChgAmt"> latestChgAmt </param>
/// <param name="totalChgAmt"> totalChgAmt </param>
/// <param name="latestRefundAmt"> latestRefundAmt </param>
/// <param name="totalRefundAmt"> totalRefundAmt </param>
/// <param name="latestChargeback"> latestChargeback </param>
/// <param name="totalChargeback"> totalChargeback </param>
/// <param name="cartXml"> cartXml </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string notificationType,
string rawResponse,
string serialNumber,
DateTime gTimestamp,
string orderNumber,
string buyerId,
string fullfillState,
string financeState,
bool emailListOptIn,
string avsResponse,
string cvnResponse,
DateTime authExpDate,
decimal authAmt,
decimal discountTotal,
decimal shippingTotal,
decimal taxTotal,
decimal orderTotal,
decimal latestChgAmt,
decimal totalChgAmt,
decimal latestRefundAmt,
decimal totalRefundAmt,
decimal latestChargeback,
decimal totalChargeback,
string cartXml,
string providerName)
{
SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_GoogleCheckoutLog_Insert", 31);
sph.DefineSqlParameter("@RowGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, rowGuid);
sph.DefineSqlParameter("@CreatedUtc", SqlDbType.DateTime, ParameterDirection.Input, createdUtc);
sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
sph.DefineSqlParameter("@StoreGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, storeGuid);
sph.DefineSqlParameter("@CartGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, cartGuid);
sph.DefineSqlParameter("@NotificationType", SqlDbType.NVarChar, 255, ParameterDirection.Input, notificationType);
sph.DefineSqlParameter("@RawResponse", SqlDbType.NVarChar, -1, ParameterDirection.Input, rawResponse);
sph.DefineSqlParameter("@SerialNumber", SqlDbType.NVarChar, 50, ParameterDirection.Input, serialNumber);
sph.DefineSqlParameter("@GTimestamp", SqlDbType.DateTime, ParameterDirection.Input, gTimestamp);
sph.DefineSqlParameter("@OrderNumber", SqlDbType.NVarChar, 50, ParameterDirection.Input, orderNumber);
sph.DefineSqlParameter("@BuyerId", SqlDbType.NVarChar, 50, ParameterDirection.Input, buyerId);
sph.DefineSqlParameter("@FullfillState", SqlDbType.NVarChar, 50, ParameterDirection.Input, fullfillState);
sph.DefineSqlParameter("@FinanceState", SqlDbType.NVarChar, 50, ParameterDirection.Input, financeState);
sph.DefineSqlParameter("@EmailListOptIn", SqlDbType.Bit, ParameterDirection.Input, emailListOptIn);
sph.DefineSqlParameter("@AvsResponse", SqlDbType.NVarChar, 5, ParameterDirection.Input, avsResponse);
sph.DefineSqlParameter("@CvnResponse", SqlDbType.NVarChar, 5, ParameterDirection.Input, cvnResponse);
sph.DefineSqlParameter("@AuthExpDate", SqlDbType.DateTime, ParameterDirection.Input, authExpDate);
sph.DefineSqlParameter("@AuthAmt", SqlDbType.Decimal, ParameterDirection.Input, authAmt);
sph.DefineSqlParameter("@DiscountTotal", SqlDbType.Decimal, ParameterDirection.Input, discountTotal);
sph.DefineSqlParameter("@ShippingTotal", SqlDbType.Decimal, ParameterDirection.Input, shippingTotal);
sph.DefineSqlParameter("@TaxTotal", SqlDbType.Decimal, ParameterDirection.Input, taxTotal);
sph.DefineSqlParameter("@OrderTotal", SqlDbType.Decimal, ParameterDirection.Input, orderTotal);
sph.DefineSqlParameter("@LatestChgAmt", SqlDbType.Decimal, ParameterDirection.Input, latestChgAmt);
sph.DefineSqlParameter("@TotalChgAmt", SqlDbType.Decimal, ParameterDirection.Input, totalChgAmt);
sph.DefineSqlParameter("@LatestRefundAmt", SqlDbType.Decimal, ParameterDirection.Input, latestRefundAmt);
sph.DefineSqlParameter("@TotalRefundAmt", SqlDbType.Decimal, ParameterDirection.Input, totalRefundAmt);
sph.DefineSqlParameter("@LatestChargeback", SqlDbType.Decimal, ParameterDirection.Input, latestChargeback);
sph.DefineSqlParameter("@TotalChargeback", SqlDbType.Decimal, ParameterDirection.Input, totalChargeback);
sph.DefineSqlParameter("@CartXml", SqlDbType.NVarChar, -1, ParameterDirection.Input, cartXml);
sph.DefineSqlParameter("@ProviderName", SqlDbType.NVarChar, 255, ParameterDirection.Input, providerName);
int rowsAffected = sph.ExecuteNonQuery();
return(rowsAffected);
}
public static void DatabaseHelperDoForumVersion2203PostUpgradeTasks(
String overrideConnectionInfo)
{
string connectionString;
if (
(overrideConnectionInfo != null) &&
(overrideConnectionInfo.Length > 0)
)
{
connectionString = overrideConnectionInfo;
}
else
{
connectionString = ConnectionString.GetWriteConnectionString();
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT SubscriptionID ");
sqlCommand.Append("FROM mp_ForumSubscriptions ");
sqlCommand.Append(" where (SubGuid is null OR SubGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append(" ; ");
DataSet ds = FBSqlHelper.ExecuteDataset(
connectionString,
sqlCommand.ToString());
DataTable dataTable = ds.Tables[0];
foreach (DataRow row in dataTable.Rows)
{
DatabaseHelperUpdateTableField(
"mp_ForumSubscriptions",
"SubscriptionID",
row["SubscriptionID"].ToString(),
"SubGuid",
Guid.NewGuid().ToString(),
" ");
}
sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ThreadSubscriptionID ");
sqlCommand.Append("FROM mp_ForumThreadSubscriptions ");
sqlCommand.Append(" where (SubGuid is null OR SubGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append(" ; ");
ds = FBSqlHelper.ExecuteDataset(
connectionString,
sqlCommand.ToString());
dataTable = ds.Tables[0];
foreach (DataRow row in dataTable.Rows)
{
DatabaseHelperUpdateTableField(
"mp_ForumThreadSubscriptions",
"ThreadSubscriptionID",
row["ThreadSubscriptionID"].ToString(),
"SubGuid",
Guid.NewGuid().ToString(),
" ");
}
}
/// <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)
{
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(";");
MySqlParameter[] arParams = new MySqlParameter[11];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?Title", MySqlDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new MySqlParameter("?Code", MySqlDbType.VarChar, 3);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = code;
arParams[3] = new MySqlParameter("?SymbolLeft", MySqlDbType.VarChar, 15);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = symbolLeft;
arParams[4] = new MySqlParameter("?SymbolRight", MySqlDbType.VarChar, 15);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = symbolRight;
arParams[5] = new MySqlParameter("?DecimalPointChar", MySqlDbType.VarChar, 1);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = decimalPointChar;
arParams[6] = new MySqlParameter("?ThousandsPointChar", MySqlDbType.VarChar, 1);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = thousandsPointChar;
arParams[7] = new MySqlParameter("?DecimalPlaces", MySqlDbType.VarChar, 1);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = decimalPlaces;
arParams[8] = new MySqlParameter("?Value", MySqlDbType.Decimal);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = value;
arParams[9] = new MySqlParameter("?LastModified", MySqlDbType.DateTime);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = lastModified;
arParams[10] = new MySqlParameter("?Created", MySqlDbType.DateTime);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = created;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Gets a page of data from the mp_GeoZone table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
Guid countryGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(countryGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT gz.*, ");
sqlCommand.Append("gc.Name As CountryName ");
sqlCommand.Append("FROM mp_GeoZone gz ");
sqlCommand.Append("JOIN mp_GeoCountry gc ");
sqlCommand.Append("ON gz.CountryGuid = gc.Guid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("gz.CountryGuid = ?CountryGuid ");
sqlCommand.Append("ORDER BY gz.Name ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?CountryGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = countryGuid.ToString();
arParams[1] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static DataTable GetEventsTable(
int moduleId,
DateTime beginDate,
DateTime endDate)
{
SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_CalendarEvents_SelectByDate", 3);
sph.DefineSqlParameter("@ModuleID", SqlDbType.Int, ParameterDirection.Input, moduleId);
sph.DefineSqlParameter("@BeginDate", SqlDbType.DateTime, ParameterDirection.Input, beginDate);
sph.DefineSqlParameter("@EndDate", SqlDbType.DateTime, ParameterDirection.Input, endDate);
DataTable dt = new DataTable();
dt.Columns.Add("ItemID", typeof(int));
dt.Columns.Add("ModuleID", typeof(int));
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Description", typeof(string));
dt.Columns.Add("ImageName", typeof(string));
dt.Columns.Add("EventDate", typeof(DateTime));
dt.Columns.Add("StartTime", typeof(DateTime));
dt.Columns.Add("EndTime", typeof(DateTime));
dt.Columns.Add("CreatedDate", typeof(DateTime));
dt.Columns.Add("UserID", typeof(int));
dt.Columns.Add("ItemGuid", typeof(Guid));
dt.Columns.Add("ModuleGuid", typeof(Guid));
dt.Columns.Add("UserGuid", typeof(Guid));
dt.Columns.Add("Location", typeof(string));
dt.Columns.Add("LastModUserGuid", typeof(Guid));
dt.Columns.Add("LastModUtc", typeof(DateTime));
dt.Columns.Add("TicketPrice", typeof(decimal));
dt.Columns.Add("RequiresTicket", typeof(bool));
dt.Columns.Add("ShowMap", typeof(bool));
using (IDataReader reader = sph.ExecuteReader())
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["ItemID"] = reader["ItemID"];
row["ModuleID"] = reader["ModuleID"];
row["Title"] = reader["Title"];
row["Description"] = reader["Description"];
row["ImageName"] = reader["ImageName"];
row["EventDate"] = reader["EventDate"];
row["StartTime"] = reader["StartTime"];
row["EndTime"] = reader["EndTime"];
row["CreatedDate"] = reader["CreatedDate"];
row["UserID"] = reader["UserID"];
row["ItemGuid"] = reader["ItemGuid"];
row["ModuleGuid"] = reader["ModuleGuid"];
row["UserGuid"] = reader["UserGuid"];
row["Location"] = reader["Location"];
row["LastModUserGuid"] = reader["LastModUserGuid"];
row["LastModUtc"] = reader["LastModUtc"];
row["TicketPrice"] = reader["TicketPrice"];
row["RequiresTicket"] = reader["RequiresTicket"];
row["ShowMap"] = reader["ShowMap"];
dt.Rows.Add(row);
}
}
return(dt);
}
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)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_GalleryImages ");
sqlCommand.Append("SET ");
sqlCommand.Append("ModuleID = ?ModuleID, ");
sqlCommand.Append("DisplayOrder = ?DisplayOrder, ");
sqlCommand.Append("Caption = ?Caption, ");
sqlCommand.Append("Description = ?Description, ");
sqlCommand.Append("MetaDataXml = ?MetaDataXml, ");
sqlCommand.Append("ImageFile = ?ImageFile, ");
sqlCommand.Append("WebImageFile = ?WebImageFile, ");
sqlCommand.Append("ThumbnailFile = ?ThumbnailFile, ");
sqlCommand.Append("UploadDate = ?UploadDate, ");
sqlCommand.Append("UploadUser = ?UploadUser ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ItemID = ?ItemID ;");
MySqlParameter[] arParams = new MySqlParameter[11];
arParams[0] = new MySqlParameter("?ItemID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new MySqlParameter("?DisplayOrder", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = displayOrder;
arParams[3] = new MySqlParameter("?Caption", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = caption;
arParams[4] = new MySqlParameter("?Description", MySqlDbType.LongText);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = description;
arParams[5] = new MySqlParameter("?MetaDataXml", MySqlDbType.MediumText);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = metaDataXml;
arParams[6] = new MySqlParameter("?ImageFile", MySqlDbType.VarChar, 100);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = imageFile;
arParams[7] = new MySqlParameter("?WebImageFile", MySqlDbType.VarChar, 100);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = webImageFile;
arParams[8] = new MySqlParameter("?ThumbnailFile", MySqlDbType.VarChar, 100);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = thumbnailFile;
arParams[9] = new MySqlParameter("?UploadDate", MySqlDbType.DateTime);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = uploadDate;
arParams[10] = new MySqlParameter("?UploadUser", MySqlDbType.VarChar, 100);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = uploadUser;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
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)
{
int exclude = 0;
if (excludeFromRecentContent)
{
exclude = 1;
}
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("ExcludeFromRecentContent, ");
sqlCommand.Append("UserGuid, ");
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("?ExcludeFromRecentContent, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?CreatedDate )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[14];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new MySqlParameter("?Title", MySqlDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new MySqlParameter("?Excerpt", MySqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = excerpt;
arParams[3] = new MySqlParameter("?Body", MySqlDbType.LongText);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = body;
arParams[4] = new MySqlParameter("?MoreLink", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = moreLink;
arParams[5] = new MySqlParameter("?BeginDate", MySqlDbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = beginDate;
arParams[6] = new MySqlParameter("?EndDate", MySqlDbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = endDate;
arParams[7] = new MySqlParameter("?CreatedDate", MySqlDbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = createdDate;
arParams[8] = new MySqlParameter("?UserID", MySqlDbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = userId;
arParams[9] = new MySqlParameter("?SortOrder", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
arParams[10] = new MySqlParameter("?ItemGuid", MySqlDbType.VarChar, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = itemGuid.ToString();
arParams[11] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = moduleGuid.ToString();
arParams[12] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = userGuid.ToString();
arParams[13] = new MySqlParameter("?ExcludeFromRecentContent", MySqlDbType.Int32);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = exclude;
int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static int AddGalleryImage(
Guid itemGuid,
Guid moduleGuid,
int moduleId,
int displayOrder,
string caption,
string description,
string metaDataXml,
string imageFile,
string webImageFile,
string thumbnailFile,
DateTime uploadDate,
string uploadUser,
Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_GalleryImages (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("DisplayOrder, ");
sqlCommand.Append("Caption, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("MetaDataXml, ");
sqlCommand.Append("ImageFile, ");
sqlCommand.Append("WebImageFile, ");
sqlCommand.Append("ThumbnailFile, ");
sqlCommand.Append("UploadDate, ");
sqlCommand.Append("UploadUser, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?ModuleID, ");
sqlCommand.Append("?DisplayOrder, ");
sqlCommand.Append("?Caption, ");
sqlCommand.Append("?Description, ");
sqlCommand.Append("?MetaDataXml, ");
sqlCommand.Append("?ImageFile, ");
sqlCommand.Append("?WebImageFile, ");
sqlCommand.Append("?ThumbnailFile, ");
sqlCommand.Append("?UploadDate, ");
sqlCommand.Append("?UploadUser, ");
sqlCommand.Append("?ItemGuid, ");
sqlCommand.Append("?ModuleGuid, ");
sqlCommand.Append("?UserGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[13];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new MySqlParameter("?DisplayOrder", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = displayOrder;
arParams[2] = new MySqlParameter("?Caption", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = caption;
arParams[3] = new MySqlParameter("?Description", MySqlDbType.LongText);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = description;
arParams[4] = new MySqlParameter("?MetaDataXml", MySqlDbType.MediumText);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = metaDataXml;
arParams[5] = new MySqlParameter("?ImageFile", MySqlDbType.VarChar, 100);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = imageFile;
arParams[6] = new MySqlParameter("?WebImageFile", MySqlDbType.VarChar, 100);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = webImageFile;
arParams[7] = new MySqlParameter("?ThumbnailFile", MySqlDbType.VarChar, 100);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = thumbnailFile;
arParams[8] = new MySqlParameter("?UploadDate", MySqlDbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = uploadDate;
arParams[9] = new MySqlParameter("?UploadUser", MySqlDbType.VarChar, 100);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = uploadUser;
arParams[10] = new MySqlParameter("?ItemGuid", MySqlDbType.VarChar, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = itemGuid.ToString();
arParams[11] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = moduleGuid.ToString();
arParams[12] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = userGuid.ToString();
int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static IDataReader GetHtmlContentByPage(int siteId, int pageId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT h.*, ");
sqlCommand.Append("m.ModuleTitle, ");
sqlCommand.Append("m.ViewRoles, ");
sqlCommand.Append("m.IncludeInSearch, ");
sqlCommand.Append("md.FeatureName, ");
sqlCommand.Append("u1.Name AS CreatedByName, ");
sqlCommand.Append("u1.FirstName AS CreatedByFirstName, ");
sqlCommand.Append("u1.LastName AS CreatedByLastName, ");
sqlCommand.Append("u1.Email AS CreatedByEmail, ");
sqlCommand.Append("u1.AuthorBio, ");
sqlCommand.Append("u1.AvatarUrl, ");
sqlCommand.Append("COALESCE(u1.UserID, -1) As AuthorUserID ");
sqlCommand.Append("FROM mp_HtmlContent h ");
sqlCommand.Append("JOIN mp_Modules m ");
sqlCommand.Append("ON h.ModuleID = m.ModuleID ");
sqlCommand.Append("JOIN mp_ModuleDefinitions md ");
sqlCommand.Append("ON m.ModuleDefID = md.ModuleDefID ");
sqlCommand.Append("JOIN mp_PageModules pm ");
sqlCommand.Append("ON m.ModuleID = pm.ModuleID ");
sqlCommand.Append("JOIN mp_Pages p ");
sqlCommand.Append("ON p.PageID = pm.PageID ");
sqlCommand.Append("LEFT OUTER JOIN ");
sqlCommand.Append("mp_Users u1 ");
sqlCommand.Append("ON h.UserGuid = u1.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("p.SiteID = ?SiteID ");
sqlCommand.Append("AND pm.PageID = ?PageID ");
// 2007-09-02 don't filter on pubdate, we use this data
// to populate search index and we put pub date in the index
//sqlCommand.Append("AND pm.PublishBeginDate < now() ");
//sqlCommand.Append("AND (pm.PublishEndDate IS NULL OR pm.PublishEndDate > now()) ;");
sqlCommand.Append(" ; ");
MySqlParameter[] arParams = new MySqlParameter[2];
arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new MySqlParameter("?PageID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageId;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static DataTable GetWebImageByPage(
int moduleId,
int pageNumber)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_GalleryImages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = ?ModuleID ;");
MySqlParameter[] arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int totalRows = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
int pageSize = 1;
int totalPages = totalRows / pageSize;
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder = 0;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
int offset = pageSize * (pageNumber - 1);
sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("i.ItemID, ");
sqlCommand.Append("i.ModuleID, ");
sqlCommand.Append(totalPages.ToString(CultureInfo.InvariantCulture) + " As TotalPages ");
sqlCommand.Append("FROM mp_GalleryImages i ");
sqlCommand.Append("WHERE i.ModuleID = ?ModuleID ");
sqlCommand.Append("ORDER BY i.DisplayOrder, i.ItemID ");
if (pageNumber > 1)
{
sqlCommand.Append("LIMIT " + offset.ToString(CultureInfo.InvariantCulture)
+ ", " + pageSize.ToString(CultureInfo.InvariantCulture) + " ");
}
else
{
sqlCommand.Append("LIMIT "
+ pageSize.ToString(CultureInfo.InvariantCulture) + " ");
}
sqlCommand.Append(" ; ");
arParams = new MySqlParameter[1];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
DataTable dt = new DataTable();
dt.Columns.Add("ItemID", typeof(int));
//dt.Columns.Add("ModuleID", typeof(int));
//dt.Columns.Add("Caption", typeof(String));
//dt.Columns.Add("ThumbnailFile", typeof(String));
dt.Columns.Add("TotalPages", typeof(int));
using (IDataReader reader = MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams))
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["ItemID"] = reader["ItemID"];
// row["ModuleID"] = reader["ModuleID"];
//row["Caption"] = reader["Caption"];
row["TotalPages"] = reader["TotalPages"];
dt.Rows.Add(row);
}
}
return(dt);
}
/// <summary>
/// Inserts a row in the mp_Letter table. Returns rows affected count.
/// </summary>
/// <param name="letterGuid"> letterGuid </param>
/// <param name="letterInfoGuid"> letterInfoGuid </param>
/// <param name="subject"> subject </param>
/// <param name="htmlBody"> htmlBody </param>
/// <param name="textBody"> textBody </param>
/// <param name="createdBy"> createdBy </param>
/// <param name="createdUTC"> createdUTC </param>
/// <param name="lastModBy"> lastModBy </param>
/// <param name="lastModUTC"> lastModUTC </param>
/// <param name="isApproved"> isApproved </param>
/// <param name="approvedBy"> approvedBy </param>
/// <returns>int</returns>
public static int Create(
Guid letterGuid,
Guid letterInfoGuid,
string subject,
string htmlBody,
string textBody,
Guid createdBy,
DateTime createdUtc,
Guid lastModBy,
DateTime lastModUtc,
bool isApproved,
Guid approvedBy)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[11];
arParams[0] = new NpgsqlParameter("letterguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterGuid.ToString();
arParams[1] = new NpgsqlParameter("letterinfoguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = letterInfoGuid.ToString();
arParams[2] = new NpgsqlParameter("subject", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = subject;
arParams[3] = new NpgsqlParameter("htmlbody", NpgsqlTypes.NpgsqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = htmlBody;
arParams[4] = new NpgsqlParameter("textbody", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = textBody;
arParams[5] = new NpgsqlParameter("createdby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdBy.ToString();
arParams[6] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Date);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = createdUtc;
arParams[7] = new NpgsqlParameter("lastmodby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = lastModBy.ToString();
arParams[8] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Date);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModUtc;
arParams[9] = new NpgsqlParameter("isapproved", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = isApproved;
arParams[10] = new NpgsqlParameter("approvedby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = approvedBy.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"mp_letter_insert(:letterguid,:letterinfoguid,:subject,:htmlbody,:textbody,:createdby,:createdutc,:lastmodby,:lastmodutc,:isapproved,:approvedby)",
arParams);
return(rowsAffected);
}
/// <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);
}
/// <summary>
/// Updates a row in the mp_Comments table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="title"> title </param>
/// <param name="userComment"> userComment </param>
/// <param name="userName"> userName </param>
/// <param name="userEmail"> userEmail </param>
/// <param name="userUrl"> userUrl </param>
/// <param name="userIp"> userIp </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <param name="moderationStatus"> moderationStatus </param>
/// <param name="moderatedBy"> moderatedBy </param>
/// <param name="moderationReason"> moderationReason </param>
/// <returns>bool</returns>
public static bool Update(
Guid guid,
Guid userGuid,
string title,
string userComment,
string userName,
string userEmail,
string userUrl,
string userIp,
DateTime lastModUtc,
byte moderationStatus,
Guid moderatedBy,
string moderationReason)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_comments ");
sqlCommand.Append("SET ");
sqlCommand.Append("userguid = :userguid, ");
sqlCommand.Append("title = :title, ");
sqlCommand.Append("usercomment = :usercomment, ");
sqlCommand.Append("username = :username, ");
sqlCommand.Append("useremail = :useremail, ");
sqlCommand.Append("userurl = :userurl, ");
sqlCommand.Append("userip = :userip, ");
sqlCommand.Append("lastmodutc = :lastmodutc, ");
sqlCommand.Append("moderationstatus = :moderationstatus, ");
sqlCommand.Append("moderatedby = :moderatedby, ");
sqlCommand.Append("moderationreason = :moderationreason ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("guid = :guid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[12];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
arParams[2] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = title;
arParams[3] = new NpgsqlParameter("usercomment", NpgsqlTypes.NpgsqlDbType.Text);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userComment;
arParams[4] = new NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = userName;
arParams[5] = new NpgsqlParameter("useremail", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = userEmail;
arParams[6] = new NpgsqlParameter("userurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userUrl;
arParams[7] = new NpgsqlParameter("userip", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = userIp;
arParams[8] = new NpgsqlParameter("lastmodutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModUtc;
arParams[9] = new NpgsqlParameter("moderationstatus", NpgsqlTypes.NpgsqlDbType.Smallint);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = moderationStatus;
arParams[10] = new NpgsqlParameter("moderatedby", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = moderatedBy.ToString();
arParams[11] = new NpgsqlParameter("moderationreason", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = moderationReason;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets a page of data from the mp_RedirectList table with search term
/// </summary>
public static IDataReader GetPage(
int siteId,
int pageNumber,
int pageSize,
out int totalPages,
string searchTerm = "")
{
var useSearch = !string.IsNullOrWhiteSpace(searchTerm);
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteId, searchTerm);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
Math.DivRem(totalRows, pageSize, out int remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = siteId
},
new NpgsqlParameter("pagesize", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = pageSize
},
new NpgsqlParameter("pageoffset", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = pageLowerBound
}
};
if (useSearch)
{
sqlParams.Add(
new NpgsqlParameter("?searchterm", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input,
Value = "%" + searchTerm + "%"
}
);
}
var sqlCommand = $@"SELECT *
FROM mp_redirectlist
WHERE siteid = :siteid
{(useSearch ? "AND (newurl LIKE :searchterm OR oldurl Like :searchterm)" : "")}
ORDER BY oldurl
LIMIT :pagesize
{(pageNumber > 1 ? " OFFSET :pageoffset " : "")};" ;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand,
sqlParams.ToArray()));
}
/// <summary>
/// Updates a row in the mp_EmailTemplate table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="name"> name </param>
/// <param name="subject"> subject </param>
/// <param name="textBody"> textBody </param>
/// <param name="htmlBody"> htmlBody </param>
/// <param name="hasHtml"> hasHtml </param>
/// <param name="isEditable"> isEditable </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <param name="lastModBy"> lastModBy </param>
/// <returns>bool</returns>
public static bool Update(
Guid guid,
string name,
string subject,
string textBody,
string htmlBody,
bool hasHtml,
bool isEditable,
DateTime lastModUtc,
Guid lastModBy)
{
#region Bit Conversion
int intHasHtml = 0;
if (hasHtml)
{
intHasHtml = 1;
}
int intIsEditable = 0;
if (isEditable)
{
intIsEditable = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_EmailTemplate ");
sqlCommand.Append("SET ");
sqlCommand.Append("Name = ?Name, ");
sqlCommand.Append("Subject = ?Subject, ");
sqlCommand.Append("TextBody = ?TextBody, ");
sqlCommand.Append("HtmlBody = ?HtmlBody, ");
sqlCommand.Append("HasHtml = ?HasHtml, ");
sqlCommand.Append("IsEditable = ?IsEditable, ");
sqlCommand.Append("LastModUtc = ?LastModUtc, ");
sqlCommand.Append("LastModBy = ?LastModBy ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?Guid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[9];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = name;
arParams[2] = new MySqlParameter("?Subject", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = subject;
arParams[3] = new MySqlParameter("?TextBody", MySqlDbType.LongText);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = textBody;
arParams[4] = new MySqlParameter("?HtmlBody", MySqlDbType.LongText);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = htmlBody;
arParams[5] = new MySqlParameter("?HasHtml", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = intHasHtml;
arParams[6] = new MySqlParameter("?IsEditable", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = intIsEditable;
arParams[7] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = lastModUtc;
arParams[8] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModBy.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the mp_RedirectList table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="siteID"> siteID </param>
/// <param name="oldUrl"> oldUrl </param>
/// <param name="newUrl"> newUrl </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="expireUtc"> expireUtc </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
Guid siteGuid,
int siteID,
string oldUrl,
string newUrl,
DateTime createdUtc,
DateTime expireUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_redirectlist (");
sqlCommand.Append("rowguid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("siteid, ");
sqlCommand.Append("oldurl, ");
sqlCommand.Append("newurl, ");
sqlCommand.Append("createdutc, ");
sqlCommand.Append("expireutc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":rowguid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":siteid, ");
sqlCommand.Append(":oldurl, ");
sqlCommand.Append(":newurl, ");
sqlCommand.Append(":createdutc, ");
sqlCommand.Append(":expireutc ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[7];
arParams[0] = new NpgsqlParameter("rowguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteID;
arParams[3] = new NpgsqlParameter("oldurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = oldUrl;
arParams[4] = new NpgsqlParameter("newurl", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = newUrl;
arParams[5] = new NpgsqlParameter("createdutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdUtc;
arParams[6] = new NpgsqlParameter("expireutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = expireUtc;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static IDataReader GetPageByFeature(
Guid siteGuid,
Guid featureGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountByFeature(siteGuid, featureGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_EmailTemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND FeatureGuid = ?FeatureGuid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("Name ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?FeatureGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = featureGuid.ToString();
arParams[2] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageSize;
arParams[3] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static int CreateAuditHistory(
Guid guid,
Guid workflowGuid,
Guid moduleGuid,
Guid userGuid,
DateTime createdDateUtc,
string status,
string notes,
bool active)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_contentworkflowaudithistory (");
sqlCommand.Append("guid, ");
sqlCommand.Append("contentworkflowguid, ");
sqlCommand.Append("moduleguid, ");
sqlCommand.Append("userguid, ");
sqlCommand.Append("createddateutc, ");
sqlCommand.Append("newstatus, ");
sqlCommand.Append("notes, ");
sqlCommand.Append("active )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":guid, ");
sqlCommand.Append(":contentworkflowguid, ");
sqlCommand.Append(":moduleguid, ");
sqlCommand.Append(":userguid, ");
sqlCommand.Append(":createddateutc, ");
sqlCommand.Append(":newstatus, ");
sqlCommand.Append(":notes, ");
sqlCommand.Append(":active ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[8];
arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new NpgsqlParameter("contentworkflowguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = workflowGuid.ToString();
arParams[2] = new NpgsqlParameter("moduleguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = moduleGuid.ToString();
arParams[3] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new NpgsqlParameter("createddateutc", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = createdDateUtc;
arParams[5] = new NpgsqlParameter("newstatus", NpgsqlTypes.NpgsqlDbType.Varchar, 20);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = status;
arParams[6] = new NpgsqlParameter("notes", NpgsqlTypes.NpgsqlDbType.Text);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = notes;
arParams[7] = new NpgsqlParameter("active", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = active;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
