public static int GetCountByModule(Guid moduleGuid, int moderationStatus)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Comments ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = @ModuleGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ModerationStatus = @ModerationStatus ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ModuleGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new FbParameter("@ModerationStatus", FbDbType.SmallInt);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moderationStatus;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static int GetCountBySite(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Comments ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("@SiteGuid = '00000000-0000-0000-0000-000000000000' ");
sqlCommand.Append("OR ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
private static int GetCount(
int siteID,
string keyName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SiteSettingsEx ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID AND ");
sqlCommand.Append("KeyName = @KeyName ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteID;
arParams[1] = new FbParameter("@KeyName", FbDbType.VarChar, 128);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = keyName;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// returns true if the record exists
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static bool Exists(int siteId, string oldUrl)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_RedirectList ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND OldUrl = @OldUrl ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@OldUrl", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = oldUrl;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public static int GetCountOfUsersInRole(int siteId, int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COUNT(*) ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("JOIN mp_UserRoles ur ");
sqlCommand.Append("ON u.UserID = ur.UserID ");
sqlCommand.Append("AND ur.RoleID = @RoleID ");
sqlCommand.Append("WHERE u.SiteID = @SiteID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@RoleID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = roleId;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_FriendlyUrls table.
/// </summary>
public static int GetCount(int siteId, string searchTerm)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_FriendlyUrls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("AND ");
sqlCommand.Append("FriendlyUrl LIKE @SearchTerm ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@SearchTerm", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = "%" + searchTerm + "%";
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_RssFeedEntries table.
/// </summary>
public static bool EntryExists(Guid moduleGuid, int entryHash)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_RssFeedEntries ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = @ModuleGuid ");
sqlCommand.Append(" AND EntryHash = @EntryHash ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ModuleGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new FbParameter("@EntryHash", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = entryHash;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public static bool SchemaScriptHistoryExists(Guid applicationId, String scriptFile)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SchemaScriptHistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ApplicationID = @ApplicationID ");
sqlCommand.Append("AND ScriptFile = @ScriptFile ");
sqlCommand.Append(" ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ApplicationID", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = applicationId.ToString();
arParams[1] = new FbParameter("@ScriptFile", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = scriptFile;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public static int GetCount(Guid siteGuid, string status)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_ContentWorkflow ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("Status = @Status ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@Status", FbDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = status;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static bool Exists(int siteId, string roleName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = @SiteID AND RoleName = @RoleName ; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@RoleName", FbDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = roleName;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
/// <summary>
/// Gets a count of rows in the mp_EmailTemplate table.
/// </summary>
public static int GetCountByModuleAndName(Guid moduleGuid, string name)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_EmailTemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = @ModuleGuid ");
sqlCommand.Append("AND Name = @Name ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ModuleGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new FbParameter("@Name", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = name;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static int GetWorkInProgressCountByPage(Guid pageGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_ContentWorkflow cw ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_PageModules pm ");
sqlCommand.Append("ON ");
sqlCommand.Append("pm.ModuleGuid = cw.ModuleGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pm.PageGuid = @PageGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("cw.Status Not In ('Cancelled','Approved') ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_ContentRating table.
/// </summary>
public static int GetCountByContentAndUser(Guid contentGuid, Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_ContentRating ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ContentGuid = @ContentGuid ");
sqlCommand.Append(" AND UserGuid = @UserGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@ContentGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
arParams[1] = new FbParameter("@UserGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_LetterSubscribe table.
/// </summary>
/// <param name="letterGuid"> letterGuid </param>
public static bool Exists(Guid letterInfoGuid, string email)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_LetterSubscribe ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LetterInfoGuid = @LetterInfoGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("Email = @Email ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@LetterInfoGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
arParams[1] = new FbParameter("@Email", FbDbType.VarChar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = email;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public static bool PathExists(int siteId, String path)
{
bool result = false;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SitePaths ");
sqlCommand.Append("WHERE SiteID = @SiteID AND LoweredPath = @LoweredPath ; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@LoweredPath", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = path.ToLower();
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
result = (count > 0);
return(result);
}
public static int Create(
string userId,
string claimType,
string claimValue)
{
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter(":UserId", FbDbType.VarChar, 128);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userId;
arParams[1] = new FbParameter(":ClaimType", FbDbType.VarChar, -1);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = claimType;
arParams[2] = new FbParameter(":ClaimValue", FbDbType.VarChar, -1);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = claimValue;
string statement = "EXECUTE PROCEDURE mp_USERCLAIMS_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")";
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
statement,
arParams));
return(newID);
}
public static bool PersonalizationBlobExists(Guid pathId)
{
bool result = false;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SitePersonalizationAllUsers ");
sqlCommand.Append("WHERE PathID = @PathID ; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PathID", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pathId.ToString();
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
result = (count > 0);
return(result);
}
public static Guid GetPathId(int siteId, String path)
{
Guid result = Guid.Empty;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 PathID ");
sqlCommand.Append("FROM mp_SitePaths ");
sqlCommand.Append("WHERE SiteID = @SiteID AND LoweredPath = @LoweredPath ; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@LoweredPath", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = path.ToLower();
string guidString = FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString();
result = new Guid(guidString);
return(result);
}
public static int GetCountOfStateByUserAllPaths(
Guid userGuid,
DateTime inactiveSinceTime)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(pu.*) ");
sqlCommand.Append("FROM mp_SitePersonalizationPerUser pu ");
sqlCommand.Append("JOIN mp_Users u ");
sqlCommand.Append("ON pu.UserID = u.UserGuid ");
sqlCommand.Append("WHERE pu.UserID = @UserID ");
sqlCommand.Append("AND u.LastActivityDate <= @LastActivityDate ; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@UserID", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
arParams[1] = new FbParameter("@LastActivityDate", FbDbType.TimeStamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = inactiveSinceTime;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
public static int GetCountOfStateByUser(
Guid userGuid,
Guid pathId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_SitePersonalizationPerUser ");
sqlCommand.Append("WHERE PathID = @PathID AND UserID = @UserID ; ");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@PathID", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pathId.ToString();
arParams[1] = new FbParameter("@UserID", FbDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
public static int GetCountByFeature(Guid siteGuid, Guid featureGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_EmailTemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append("AND FeatureGuid = @FeatureGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@FeatureGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = featureGuid.ToString();
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Inserts a row in the mp_BannedIPAddresses table. Returns new integer id.
/// </summary>
/// <param name="bannedIP"> bannedIP </param>
/// <param name="bannedUTC"> bannedUTC </param>
/// <param name="bannedReason"> bannedReason </param>
/// <returns>int</returns>
public static int Add(
string bannedIP,
DateTime bannedUtc,
string bannedReason)
{
#region Bit Conversion
#endregion
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter(":BannedIP", FbDbType.VarChar, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = bannedIP;
arParams[1] = new FbParameter(":BannedUTC", FbDbType.TimeStamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = bannedUtc;
arParams[2] = new FbParameter(":BannedReason", FbDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = bannedReason;
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_BANNEDIPADDRESSES_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}
public static int GetNextPageOrder(Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COALESCE(MAX(PageOrder),-1) ");
sqlCommand.Append("FROM mp_UserPages ");
sqlCommand.Append("WHERE UserGuid = @UserGuid ; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@UserGuid", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
int nextPageOrder = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)) + 2;
if (nextPageOrder == 1)
{
nextPageOrder = 3;
}
return(nextPageOrder);
}
/// <summary>
/// Gets a count of rows in the mp_ContentRating table.
/// </summary>
public static int GetCountOfRatingsSince(Guid contentGuid, string ipAddress, DateTime beginUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_ContentRating ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ContentGuid = @ContentGuid ");
sqlCommand.Append(" AND UserGuid = '00000000-0000-0000-0000-000000000000' ");
sqlCommand.Append(" AND IpAddress = @IpAddress ");
sqlCommand.Append(" AND CreatedUtc > @BeginUtc ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@ContentGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
arParams[1] = new FbParameter("@IpAddress", FbDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = ipAddress;
arParams[2] = new FbParameter("@BeginUtc", FbDbType.TimeStamp);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = beginUtc;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static bool Exists(Int32 siteId, String className, String assemblyName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_WebParts ");
sqlCommand.Append("WHERE SiteID = @SiteID ");
sqlCommand.Append(" AND ClassName = @ClassName ");
sqlCommand.Append(" AND AssemblyName = @AssemblyName ; ");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@ClassName", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = className;
arParams[2] = new FbParameter("@AssemblyName", FbDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = assemblyName;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
public static int CountUsersNotSubscribedByLetter(Guid siteGuid, Guid letterInfoGuid, bool excludeIfAnyUnsubscribeHx)
{
int intExcludeIfAnyUnsubscribeHx = 0;
if (excludeIfAnyUnsubscribeHx)
{
intExcludeIfAnyUnsubscribeHx = 1;
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COUNT(*) ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("u.SiteGuid = @SiteGuid ");
sqlCommand.Append("AND u.IsDeleted = 0 ");
sqlCommand.Append("AND u.ProfileApproved = 1 ");
sqlCommand.Append("AND u.IsLockedOut = 0 ");
sqlCommand.Append("AND (u.RegisterConfirmGuid IS NULL OR u.RegisterConfirmGuid = '00000000-0000-0000-0000-000000000000') ");
sqlCommand.Append("AND u.UserGuid NOT IN ");
sqlCommand.Append("(SELECT ls.UserGuid ");
sqlCommand.Append("FROM mp_LetterSubscribe ls ");
sqlCommand.Append("WHERE ls.LetterInfoGuid = @LetterInfoGuid ");
sqlCommand.Append(") ");
sqlCommand.Append("AND u.UserGuid NOT IN ");
sqlCommand.Append("(SELECT lsx.UserGuid ");
sqlCommand.Append("FROM mp_LetterSubscribeHx lsx ");
sqlCommand.Append("WHERE ((@ExcludeIfAnyUnsubscribeHx = 1) OR (lsx.LetterInfoGuid = @LetterInfoGuid)) ");
sqlCommand.Append(") ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new FbParameter("@LetterInfoGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = letterInfoGuid.ToString();
arParams[2] = new FbParameter("@ExcludeIfAnyUnsubscribeHx", FbDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = intExcludeIfAnyUnsubscribeHx;
int count = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
/// <summary>
/// Inserts a row in the mp_SystemLog table. Returns new integer id.
/// </summary>
/// <param name="logDate"> logDate </param>
/// <param name="ipAddress"> ipAddress </param>
/// <param name="culture"> culture </param>
/// <param name="url"> url </param>
/// <param name="shortUrl"> shortUrl </param>
/// <param name="thread"> thread </param>
/// <param name="logLevel"> logLevel </param>
/// <param name="logger"> logger </param>
/// <param name="message"> message </param>
/// <returns>int</returns>
public static int Create(
DateTime logDate,
string ipAddress,
string culture,
string url,
string shortUrl,
string thread,
string logLevel,
string logger,
string message)
{
FbParameter[] arParams = new FbParameter[9];
arParams[0] = new FbParameter(":LogDate", FbDbType.TimeStamp);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = logDate;
arParams[1] = new FbParameter(":IpAddress", FbDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = ipAddress;
arParams[2] = new FbParameter(":Culture", FbDbType.VarChar, 10);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = culture;
arParams[3] = new FbParameter(":Url", FbDbType.VarChar);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = url;
arParams[4] = new FbParameter(":ShortUrl", FbDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = shortUrl;
arParams[5] = new FbParameter(":Thread", FbDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = thread;
arParams[6] = new FbParameter(":LogLevel", FbDbType.VarChar, 20);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = logLevel;
arParams[7] = new FbParameter(":Logger", FbDbType.VarChar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = logger;
arParams[8] = new FbParameter(":Message", FbDbType.VarChar);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = message;
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetWriteConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_SYSTEMLOG_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}
public static int SchemaScriptHistoryAddSchemaScriptHistory(
Guid applicationId,
string scriptFile,
DateTime runTime,
bool errorOccurred,
string errorMessage,
string scriptBody)
{
#region Bit Conversion
int intErrorOccurred;
if (errorOccurred)
{
intErrorOccurred = 1;
}
else
{
intErrorOccurred = 0;
}
#endregion
FbParameter[] arParams = new FbParameter[6];
arParams[0] = new FbParameter(":ApplicationID", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = applicationId.ToString();
arParams[1] = new FbParameter(":ScriptFile", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = scriptFile;
arParams[2] = new FbParameter(":RunTime", FbDbType.TimeStamp);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = runTime;
arParams[3] = new FbParameter(":ErrorOccurred", FbDbType.SmallInt);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = intErrorOccurred;
arParams[4] = new FbParameter(":ErrorMessage", FbDbType.VarChar);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = errorMessage;
arParams[5] = new FbParameter(":ScriptBody", FbDbType.VarChar);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = scriptBody;
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_SCHEMASCRIPTHISTORY_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}
/// <summary>
/// Gets a count of rows in the mp_TaskQueue table.
/// </summary>
public static int GetCount()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_TaskQueue ;");
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
null)));
}
/// <summary>
/// Inserts a row in the mp_IndexingQueue table. Returns new integer id.
/// </summary>
/// <param name="indexPath"> indexPath </param>
/// <param name="serializedItem"> serializedItem </param>
/// <param name="itemKey"> itemKey </param>
/// <param name="removeOnly"> removeOnly </param>
/// <returns>int</returns>
public static Int64 Create(
int siteId,
string indexPath,
string serializedItem,
string itemKey,
bool removeOnly)
{
#region Bit Conversion
int intRemoveOnly;
if (removeOnly)
{
intRemoveOnly = 1;
}
else
{
intRemoveOnly = 0;
}
#endregion
FbParameter[] arParams = new FbParameter[5];
arParams[0] = new FbParameter(":SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter(":IndexPath", FbDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = indexPath;
arParams[2] = new FbParameter(":SerializedItem", FbDbType.VarChar);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = serializedItem;
arParams[3] = new FbParameter(":ItemKey", FbDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = itemKey;
arParams[4] = new FbParameter(":RemoveOnly", FbDbType.SmallInt);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intRemoveOnly;
Int64 newID = Convert.ToInt64(FBSqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_INDEXINGQUEUE_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return(newID);
}