public static int GetCountOfStateByUser()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(pu.*) ");
sqlCommand.Append("FROM cy_SitePersonalizationPerUser pu ;");
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString()));
return(count);
}
/// <summary>
/// Gets a count of rows in the cy_AuthorizeNetLog table.
/// </summary>
public static int GetCount()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_AuthorizeNetLog ");
sqlCommand.Append(";");
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
null)));
}
public static int RoleCreate(
Guid roleGuid,
Guid siteGuid,
int siteId,
string roleName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_Roles (");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("RoleName, ");
sqlCommand.Append("DisplayName, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("RoleGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":SiteID, ");
sqlCommand.Append(":RoleName, ");
sqlCommand.Append(":RoleName, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":RoleGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[4];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":RoleName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = roleName;
arParams[2] = new SqliteParameter(":SiteGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new SqliteParameter(":RoleGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = roleGuid.ToString();
int newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
/// <summary>
/// Gets a count of rows in the cy_TaskQueue table.
/// </summary>
public static int GetCountUnfinished()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_TaskQueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("CompleteUTC IS NULL ");
sqlCommand.Append(";");
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
null)));
}
/// <summary>
/// Inserts a row in the cy_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
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_BannedIPAddresses (");
sqlCommand.Append("BannedIP, ");
sqlCommand.Append("BannedUTC, ");
sqlCommand.Append("BannedReason )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":BannedIP, ");
sqlCommand.Append(":BannedUTC, ");
sqlCommand.Append(":BannedReason );");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[3];
arParams[0] = new SqliteParameter(":BannedIP", DbType.String, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = bannedIP;
arParams[1] = new SqliteParameter(":BannedUTC", DbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = bannedUtc;
arParams[2] = new SqliteParameter(":BannedReason", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = bannedReason;
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(GetConnectionString(), sqlCommand.ToString(), arParams).ToString());
return(newID);
}
/// <summary>
/// Gets a count of rows in the cy_ContactFormMessage table.
/// </summary>
public static int GetCount(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_ContactFormMessage ");
sqlCommand.Append("WHERE ModuleGuid = :ModuleGuid ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// gets the max sort rank or 1 if null
/// </summary>
/// <param name="contentGuid"></param>
/// <returns>int</returns>
public static int GetMaxSortRank(Guid contentGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COALESCE(MAX(SortRank),1) ");
sqlCommand.Append("FROM cy_ContentMeta ");
sqlCommand.Append("WHERE ContentGuid = :ContentGuid");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":ContentGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = contentGuid.ToString();
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the cy_LetterSendLog table.
/// </summary>
public static int GetCount(Guid letterGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_LetterSendLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LetterGuid = :LetterGuid ;");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":LetterGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterGuid.ToString();
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static int GetCountOfStateAllUsers(Guid pathId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_SitePersonalizationAllUsers ");
sqlCommand.Append("WHERE PathID = :PathID ; ");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":PathID", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pathId.ToString();
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
public static int GetCount(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_Links ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = :ModuleID ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static bool Exists(string folderName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_SiteFolders ");
sqlCommand.Append("WHERE FolderName = :FolderName ; ");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":FolderName", DbType.String, 255);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = folderName;
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count > 0);
}
/// <summary>
/// Gets a count of rows in the cy_GoogleCheckoutLog table.
/// </summary>
public static int GetCountByStore(Guid storeGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_GoogleCheckoutLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("StoreGuid = :StoreGuid ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":StoreGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = storeGuid.ToString();
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the cy_TaskQueue table.
/// </summary>
public static int GetCountUnfinished(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_TaskQueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = :SiteGuid ");
sqlCommand.Append("AND CompleteUTC IS NULL ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":SiteGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Returns true if the passed in address is banned
/// </summary>
/// <param name="rowID"> rowID </param>
/// <returns>bool</returns>
public static bool IsBanned(string ipAddress)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_BannedIPAddresses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("BannedIP = :BannedIP ;");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":BannedIP", DbType.String, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = ipAddress;
int foundRows = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(foundRows > 0);
}
public static int OnlyCount(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_WebParts m ");
sqlCommand.Append("WHERE m.SiteID = :SiteID ;");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
int count = 0;
count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
public static int GetCountOfStateByUserAllPaths(Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(pu.*) ");
sqlCommand.Append("FROM cy_SitePersonalizationPerUser pu ");
sqlCommand.Append("JOIN cy_Users u ");
sqlCommand.Append("ON pu.UserID = u.UserGuid ");
sqlCommand.Append("WHERE pu.UserID = :UserID ; ");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":UserID", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
public static int GetCountOfStateByUser(DateTime inactiveSinceTime)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(pu.*) ");
sqlCommand.Append("FROM cy_SitePersonalizationPerUser pu ");
sqlCommand.Append("JOIN cy_Users u ");
sqlCommand.Append("ON pu.UserID = u.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append(" u.LastActivityDate <= :LastActivityDate ; ");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":LastActivityDate", DbType.DateTime);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = inactiveSinceTime;
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
return(count);
}
/// <summary>
/// Gets a count of rows in the cy_EmailTemplate table.
/// </summary>
public static int GetCountByModuleSpecialAndName(Guid moduleGuid, Guid specialGuid1, Guid specialGuid2, string name)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_EmailTemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = :ModuleGuid ");
sqlCommand.Append("AND SpecialGuid1 = :SpecialGuid1 ");
sqlCommand.Append("AND SpecialGuid2 = :SpecialGuid2 ");
sqlCommand.Append("AND Name = :Name ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[4];
arParams[0] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new SqliteParameter(":SpecialGuid1", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = specialGuid1.ToString();
arParams[2] = new SqliteParameter(":SpecialGuid2", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = specialGuid2.ToString();
arParams[3] = new SqliteParameter(":Name", DbType.String, 2556);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = name;
return(Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Inserts a row in the cy_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)
{
#region Bit Conversion
int intErrorOccurred;
if (errorOccurred)
{
intErrorOccurred = 1;
}
else
{
intErrorOccurred = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_LetterSendLog (");
sqlCommand.Append("LetterGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("SubscribeGuid, ");
sqlCommand.Append("EmailAddress, ");
sqlCommand.Append("UTC, ");
sqlCommand.Append("ErrorOccurred, ");
sqlCommand.Append("ErrorMessage )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":LetterGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":SubscribeGuid, ");
sqlCommand.Append(":EmailAddress, ");
sqlCommand.Append(":UTC, ");
sqlCommand.Append(":ErrorOccurred, ");
sqlCommand.Append(":ErrorMessage );");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[7];
arParams[0] = new SqliteParameter(":LetterGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterGuid.ToString();
arParams[1] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
arParams[2] = new SqliteParameter(":EmailAddress", DbType.String, 100);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = emailAddress;
arParams[3] = new SqliteParameter(":UTC", DbType.DateTime);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = uTC;
arParams[4] = new SqliteParameter(":ErrorOccurred", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intErrorOccurred;
arParams[5] = new SqliteParameter(":ErrorMessage", DbType.Object);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = errorMessage;
arParams[6] = new SqliteParameter(":SubscribeGuid", DbType.String, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = subscribeGuid.ToString();
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(GetConnectionString(), sqlCommand.ToString(), arParams).ToString());
return(newID);
}
public static int AddModuleDefinition(
Guid featureGuid,
int siteId,
string featureName,
string controlSrc,
int sortOrder,
int defaultCacheTime,
String icon,
bool isAdmin,
string resourceFile,
bool isCacheable,
bool isSearchable,
string searchListName,
bool supportsPageReuse,
string deleteProvider)
{
int intIsAdmin = 0;
if (isAdmin)
{
intIsAdmin = 1;
}
int intIsCacheable = 0;
if (isCacheable)
{
intIsCacheable = 1;
}
int intIsSearchable = 0;
if (isSearchable)
{
intIsSearchable = 1;
}
int intSupportsPageReuse = 0;
if (supportsPageReuse)
{
intSupportsPageReuse = 1;
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_ModuleDefinitions (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("FeatureName, ");
sqlCommand.Append("ControlSrc, ");
sqlCommand.Append("SortOrder, ");
sqlCommand.Append("DefaultCacheTime, ");
sqlCommand.Append("Icon, ");
sqlCommand.Append("IsAdmin, ");
sqlCommand.Append("IsCacheable, ");
sqlCommand.Append("IsSearchable, ");
sqlCommand.Append("SearchListName, ");
sqlCommand.Append("SupportsPageReuse, ");
sqlCommand.Append("DeleteProvider, ");
sqlCommand.Append("ResourceFile ");
sqlCommand.Append(" )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":FeatureGuid, ");
sqlCommand.Append(":FeatureName, ");
sqlCommand.Append(":ControlSrc, ");
sqlCommand.Append(":SortOrder, ");
sqlCommand.Append(":DefaultCacheTime, ");
sqlCommand.Append(":Icon, ");
sqlCommand.Append(":IsAdmin, ");
sqlCommand.Append(":IsCacheable, ");
sqlCommand.Append(":IsSearchable, ");
sqlCommand.Append(":SearchListName, ");
sqlCommand.Append(":SupportsPageReuse, ");
sqlCommand.Append(":DeleteProvider, ");
sqlCommand.Append(":ResourceFile ");
sqlCommand.Append(" );");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[14];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":FeatureName", DbType.String, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = featureName;
arParams[2] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = controlSrc;
arParams[3] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = sortOrder;
arParams[4] = new SqliteParameter(":IsAdmin", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intIsAdmin;
arParams[5] = new SqliteParameter(":Icon", DbType.String, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = icon;
arParams[6] = new SqliteParameter(":DefaultCacheTime", DbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = defaultCacheTime;
arParams[7] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = featureGuid;
arParams[8] = new SqliteParameter(":ResourceFile", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = resourceFile;
arParams[9] = new SqliteParameter(":IsCacheable", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = intIsCacheable;
arParams[10] = new SqliteParameter(":IsSearchable", DbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = intIsSearchable;
arParams[11] = new SqliteParameter(":SearchListName", DbType.String, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = searchListName;
arParams[12] = new SqliteParameter(":SupportsPageReuse", DbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = intSupportsPageReuse;
arParams[13] = new SqliteParameter(":DeleteProvider", DbType.String, 255);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = deleteProvider;
int newID = Convert.ToInt32(
SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
if (siteId > -1)
{
// now add to cy_SiteModuleDefinitions
sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_SiteModuleDefinitions (");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("ModuleDefID ) ");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":SiteID, ");
sqlCommand.Append("(SELECT SiteGuid FROM cy_Sites WHERE SiteID = :SiteID LIMIT 1), ");
sqlCommand.Append("(SELECT Guid FROM cy_ModuleDefinitions WHERE ModuleDefID = :ModuleDefID LIMIT 1), ");
sqlCommand.Append(":ModuleDefID ) ; ");
arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = newID;
SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
}
return(newID);
}
public static bool AddHistory(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
int itemId,
int moduleId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int uploadUserId,
DateTime archiveDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_SharedFilesHistory (");
sqlCommand.Append("ItemID, ");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("FriendlyName, ");
sqlCommand.Append("OriginalFileName, ");
sqlCommand.Append("ServerFileName, ");
sqlCommand.Append("SizeInKB, ");
sqlCommand.Append("UploadDate, ");
sqlCommand.Append("UploadUserID, ");
sqlCommand.Append("ArchiveDate, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ItemID, ");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":FriendlyName, ");
sqlCommand.Append(":OriginalFileName, ");
sqlCommand.Append(":ServerFileName, ");
sqlCommand.Append(":SizeInKB, ");
sqlCommand.Append(":UploadDate, ");
sqlCommand.Append(":UploadUserID, ");
sqlCommand.Append(":ArchiveDate, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[12];
arParams[0] = new SqliteParameter(":ItemID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new SqliteParameter(":FriendlyName", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = friendlyName;
arParams[3] = new SqliteParameter(":OriginalFileName", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = originalFileName;
arParams[4] = new SqliteParameter(":ServerFileName", DbType.String, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = serverFileName;
arParams[5] = new SqliteParameter(":SizeInKB", DbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sizeInKB;
arParams[6] = new SqliteParameter(":UploadDate", DbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = uploadDate;
arParams[7] = new SqliteParameter(":UploadUserID", DbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = uploadUserId;
arParams[8] = new SqliteParameter(":ArchiveDate", DbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = archiveDate;
arParams[9] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = itemGuid.ToString();
arParams[10] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = moduleGuid.ToString();
arParams[11] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = userGuid.ToString();
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID > 0);
}
public static bool UpdateModuleDefinitionSetting(
Guid featureGuid,
int moduleDefId,
string resourceFile,
string settingName,
string settingValue,
string controlType,
string regexValidationExpression,
string controlSrc,
string helpKey,
int sortOrder)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT count(*)");
sqlCommand.Append("FROM cy_ModuleDefinitionSettings ");
sqlCommand.Append("WHERE (ModuleDefID = :ModuleDefID OR FeatureGuid = :FeatureGuid) ");
sqlCommand.Append("AND SettingName = :SettingName ;");
SqliteParameter[] arParams = new SqliteParameter[3];
arParams[0] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = featureGuid;
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
sqlCommand = new StringBuilder();
int rowsAffected = 0;
if (count > 0)
{
sqlCommand.Append("UPDATE cy_ModuleDefinitionSettings ");
sqlCommand.Append("SET SettingValue = :SettingValue ,");
sqlCommand.Append("FeatureGuid = :FeatureGuid, ");
sqlCommand.Append("ResourceFile = :ResourceFile, ");
sqlCommand.Append("ControlType = :ControlType ,");
sqlCommand.Append("ControlSrc = :ControlSrc ,");
sqlCommand.Append("HelpKey = :HelpKey ,");
sqlCommand.Append("SortOrder = :SortOrder ,");
sqlCommand.Append("RegexValidationExpression = :RegexValidationExpression ");
sqlCommand.Append("WHERE ModuleDefID = :ModuleDefID ");
sqlCommand.Append("AND SettingName = :SettingName ; ");
arParams = new SqliteParameter[10];
arParams[0] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new SqliteParameter(":ControlType", DbType.String, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new SqliteParameter(":RegexValidationExpression", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = featureGuid;
arParams[6] = new SqliteParameter(":ResourceFile", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = resourceFile;
arParams[7] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new SqliteParameter(":HelpKey", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
else
{
sqlCommand.Append("INSERT INTO cy_ModuleDefinitionSettings ");
sqlCommand.Append("( ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("ModuleDefID, ");
sqlCommand.Append("ResourceFile, ");
sqlCommand.Append("SettingName, ");
sqlCommand.Append("SettingValue, ");
sqlCommand.Append("ControlType, ");
sqlCommand.Append("ControlSrc, ");
sqlCommand.Append("HelpKey, ");
sqlCommand.Append("SortOrder, ");
sqlCommand.Append("RegexValidationExpression");
sqlCommand.Append(")");
sqlCommand.Append("VALUES ( ");
sqlCommand.Append(" :FeatureGuid , ");
sqlCommand.Append(" :ModuleDefID , ");
sqlCommand.Append(" :ResourceFile , ");
sqlCommand.Append(" :SettingName , ");
sqlCommand.Append(" :SettingValue ,");
sqlCommand.Append(" :ControlType ,");
sqlCommand.Append(" :ControlSrc, ");
sqlCommand.Append(" :HelpKey, ");
sqlCommand.Append(" :SortOrder, ");
sqlCommand.Append(" :RegexValidationExpression ");
sqlCommand.Append(");");
arParams = new SqliteParameter[10];
arParams[0] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new SqliteParameter(":ControlType", DbType.String, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new SqliteParameter(":RegexValidationExpression", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = featureGuid;
arParams[6] = new SqliteParameter(":ResourceFile", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = resourceFile;
arParams[7] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new SqliteParameter(":HelpKey", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
}
public static int AddLink(
Guid itemGuid,
Guid moduleGuid,
int moduleId,
string title,
string url,
int viewOrder,
string description,
DateTime createdDate,
int createdBy,
string target,
Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_Links (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Url, ");
sqlCommand.Append("ViewOrder, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("CreatedDate, ");
sqlCommand.Append("Target, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":Title, ");
sqlCommand.Append(":Url, ");
sqlCommand.Append(":ViewOrder, ");
sqlCommand.Append(":Description, ");
sqlCommand.Append(":CreatedDate, ");
sqlCommand.Append(":Target, ");
sqlCommand.Append(":CreatedBy, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[11];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":Title", DbType.String, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new SqliteParameter(":Url", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = url;
arParams[3] = new SqliteParameter(":ViewOrder", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = viewOrder;
arParams[4] = new SqliteParameter(":Description", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = description;
arParams[5] = new SqliteParameter(":CreatedDate", DbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdDate;
arParams[6] = new SqliteParameter(":Target", DbType.String, 20);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = target;
arParams[7] = new SqliteParameter(":CreatedBy", DbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = createdBy;
arParams[8] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = itemGuid.ToString();
arParams[9] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = moduleGuid.ToString();
arParams[10] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = userGuid.ToString();
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
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 cy_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_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[13];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":DisplayOrder", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = displayOrder;
arParams[2] = new SqliteParameter(":Caption", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = caption;
arParams[3] = new SqliteParameter(":Description", DbType.Object);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = description;
arParams[4] = new SqliteParameter(":MetaDataXml", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = metaDataXml;
arParams[5] = new SqliteParameter(":ImageFile", DbType.String, 100);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = imageFile;
arParams[6] = new SqliteParameter(":WebImageFile", DbType.String, 100);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = webImageFile;
arParams[7] = new SqliteParameter(":ThumbnailFile", DbType.String, 100);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = thumbnailFile;
arParams[8] = new SqliteParameter(":UploadDate", DbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = uploadDate;
arParams[9] = new SqliteParameter(":UploadUser", DbType.String, 100);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = uploadUser;
arParams[10] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = itemGuid.ToString();
arParams[11] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = moduleGuid.ToString();
arParams[12] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = userGuid.ToString();
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static DataTable GetWebImageByPage(int moduleId, int pageNumber)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM cy_GalleryImages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = :ModuleID ;");
SqliteParameter[] arParams = new SqliteParameter[1];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
sqlCommand = new StringBuilder();
int pageLowerBound = (1 * pageNumber) - 1;
int totalPages = (int)Math.Ceiling((double)count / (double)1);
sqlCommand.Append("SELECT i.ItemID As ItemID, ");
sqlCommand.Append("i.ModuleID As ModuleID, ");
sqlCommand.Append(":TotalPages As TotalPages ");
sqlCommand.Append("FROM cy_GalleryImages i ");
sqlCommand.Append("WHERE i.ModuleID = :ModuleID ");
sqlCommand.Append("ORDER BY DisplayOrder, ItemID ");
sqlCommand.Append("LIMIT 1 ");
sqlCommand.Append("OFFSET "+ pageLowerBound + " ; ");
arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":TotalPages", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = totalPages;
DataTable dt = new DataTable();
dt.Columns.Add("ItemID", typeof(int));
dt.Columns.Add("TotalPages", typeof(int));
using (IDataReader reader = SqliteHelper.ExecuteReader(
GetConnectionString(),
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);
}
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)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_HtmlContent (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Excerpt, ");
sqlCommand.Append("Body, ");
sqlCommand.Append("MoreLink, ");
sqlCommand.Append("SortOrder, ");
sqlCommand.Append("BeginDate, ");
sqlCommand.Append("EndDate, ");
sqlCommand.Append("CreatedDate, ");
sqlCommand.Append("UserID, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("LastModUserGuid, ");
sqlCommand.Append("LastModUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":Title, ");
sqlCommand.Append(":Excerpt, ");
sqlCommand.Append(":Body, ");
sqlCommand.Append(":MoreLink, ");
sqlCommand.Append(":SortOrder, ");
sqlCommand.Append(":BeginDate, ");
sqlCommand.Append(":EndDate, ");
sqlCommand.Append(":CreatedDate, ");
sqlCommand.Append(":UserID, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":CreatedDate )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[13];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":Title", DbType.String, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new SqliteParameter(":Excerpt", DbType.Object);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = excerpt;
arParams[3] = new SqliteParameter(":Body", DbType.Object);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = body;
arParams[4] = new SqliteParameter(":MoreLink", DbType.String, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = moreLink;
arParams[5] = new SqliteParameter(":BeginDate", DbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = beginDate;
arParams[6] = new SqliteParameter(":EndDate", DbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = endDate;
arParams[7] = new SqliteParameter(":CreatedDate", DbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = createdDate;
arParams[8] = new SqliteParameter(":UserID", DbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = userId;
arParams[9] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
arParams[10] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = itemGuid.ToString();
arParams[11] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = moduleGuid.ToString();
arParams[12] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = userGuid.ToString();
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static int AddRssFeed(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
int moduleId,
int userId,
string author,
string url,
string rssUrl,
DateTime createdUtc,
string imageUrl,
string feedType,
bool publishByDefault,
int sortRank)
{
#region Bit Conversion
int intPublishByDefault;
if (publishByDefault)
{
intPublishByDefault = 1;
}
else
{
intPublishByDefault = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_RssFeeds (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("CreatedDate, ");
sqlCommand.Append("UserID, ");
sqlCommand.Append("Author, ");
sqlCommand.Append("Url, ");
sqlCommand.Append("RssUrl, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("LastModUserGuid, ");
sqlCommand.Append("LastModUtc, ");
sqlCommand.Append("ImageUrl, ");
sqlCommand.Append("FeedType, ");
sqlCommand.Append("SortRank, ");
sqlCommand.Append("PublishByDefault )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":CreatedDate, ");
sqlCommand.Append(":UserID, ");
sqlCommand.Append(":Author, ");
sqlCommand.Append(":Url, ");
sqlCommand.Append(":RssUrl, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":CreatedDate, ");
sqlCommand.Append(":ImageUrl, ");
sqlCommand.Append(":FeedType, ");
sqlCommand.Append(":SortRank, ");
sqlCommand.Append(":PublishByDefault )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[13];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":UserID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userId;
arParams[2] = new SqliteParameter(":Author", DbType.String, 100);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = author;
arParams[3] = new SqliteParameter(":Url", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = url;
arParams[4] = new SqliteParameter(":RssUrl", DbType.String, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = rssUrl;
arParams[5] = new SqliteParameter(":CreatedDate", DbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdUtc;
arParams[6] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = itemGuid.ToString();
arParams[7] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = moduleGuid.ToString();
arParams[8] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = userGuid.ToString();
arParams[9] = new SqliteParameter(":ImageUrl", DbType.String, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = imageUrl;
arParams[10] = new SqliteParameter(":FeedType", DbType.String, 20);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = feedType;
arParams[11] = new SqliteParameter(":PublishByDefault", DbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = intPublishByDefault;
arParams[12] = new SqliteParameter(":SortRank", DbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = sortRank;
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static bool UpdateModuleSetting(
Guid moduleGuid,
int moduleId,
string settingName,
string settingValue)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT count(*) ");
sqlCommand.Append("FROM cy_ModuleSettings ");
sqlCommand.Append("WHERE ModuleID = :ModuleID ");
sqlCommand.Append("AND SettingName = :SettingName ;");
SqliteParameter[] arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
sqlCommand = new StringBuilder();
int rowsAffected = 0;
if (count > 0)
{
sqlCommand.Append("UPDATE cy_ModuleSettings ");
sqlCommand.Append("SET SettingValue = :SettingValue ");
sqlCommand.Append("WHERE ModuleID = :ModuleID ");
sqlCommand.Append("AND SettingName = :SettingName ");
arParams = new SqliteParameter[3];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
else
{
return(false);
//return CreateModuleSetting(
// Guid.NewGuid(),
// moduleGuid,
// moduleId,
// settingName,
// settingValue,
// "TextBox",
// string.Empty);
}
}
/// <summary>
/// Inserts a row in the cy_FriendlyUrls table. Returns new integer id.
/// </summary>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="siteID"> siteID </param>
/// <param name="friendlyUrl"> friendlyUrl </param>
/// <param name="realUrl"> realUrl </param>
/// <param name="isPattern"> isPattern </param>
/// <returns>int</returns>
public static int AddFriendlyUrl(
Guid itemGuid,
Guid siteGuid,
Guid pageGuid,
int siteId,
string friendlyUrl,
string realUrl,
bool isPattern)
{
int intIsPattern;
if (isPattern)
{
intIsPattern = 1;
}
else
{
intIsPattern = 0;
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_FriendlyUrls ( ");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("FriendlyUrl, ");
sqlCommand.Append("RealUrl, ");
sqlCommand.Append("IsPattern, ");
sqlCommand.Append("PageGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ItemGuid )");
sqlCommand.Append(" VALUES ( ");
sqlCommand.Append(":SiteID, ");
sqlCommand.Append(":FriendlyUrl, ");
sqlCommand.Append(":RealUrl, ");
sqlCommand.Append(":IsPattern, ");
sqlCommand.Append(":PageGuid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":ItemGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[7];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":FriendlyUrl", DbType.String, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = friendlyUrl;
arParams[2] = new SqliteParameter(":RealUrl", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = realUrl;
arParams[3] = new SqliteParameter(":IsPattern", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = intIsPattern;
arParams[4] = new SqliteParameter(":PageGuid", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pageGuid.ToString();
arParams[5] = new SqliteParameter(":SiteGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = siteGuid.ToString();
arParams[6] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = itemGuid.ToString();
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
/// <summary>
/// Inserts a row in the cy_CalendarEvents table. Returns new integer id.
/// </summary>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="moduleID"> moduleID </param>
/// <param name="title"> title </param>
/// <param name="description"> description </param>
/// <param name="imageName"> imageName </param>
/// <param name="eventDate"> eventDate </param>
/// <param name="startTime"> startTime </param>
/// <param name="endTime"> endTime </param>
/// <param name="userID"> userID </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="location"> location </param>
/// <param name="requiresTicket"> requiresTicket </param>
/// <param name="ticketPrice"> ticketPrice </param>
/// <param name="createdDate"> createdDate </param>
/// <returns>int</returns>
public static int AddCalendarEvent(
Guid itemGuid,
Guid moduleGuid,
int moduleId,
string title,
string description,
string imageName,
DateTime eventDate,
DateTime startTime,
DateTime endTime,
int userId,
Guid userGuid,
string location,
bool requiresTicket,
decimal ticketPrice,
DateTime createdDate)
{
#region Bit Conversion
int intRequiresTicket;
if (requiresTicket)
{
intRequiresTicket = 1;
}
else
{
intRequiresTicket = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_CalendarEvents (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("ImageName, ");
sqlCommand.Append("EventDate, ");
sqlCommand.Append("StartTime, ");
sqlCommand.Append("EndTime, ");
sqlCommand.Append("CreatedDate, ");
sqlCommand.Append("UserID, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("Location, ");
sqlCommand.Append("LastModUserGuid, ");
sqlCommand.Append("LastModUtc, ");
sqlCommand.Append("TicketPrice, ");
sqlCommand.Append("RequiresTicket )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":Title, ");
sqlCommand.Append(":Description, ");
sqlCommand.Append(":ImageName, ");
sqlCommand.Append(":EventDate, ");
sqlCommand.Append(":StartTime, ");
sqlCommand.Append(":EndTime, ");
sqlCommand.Append(":CreatedDate, ");
sqlCommand.Append(":UserID, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":Location, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":CreatedDate, ");
sqlCommand.Append(":TicketPrice, ");
sqlCommand.Append(":RequiresTicket )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[15];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":Title", DbType.String, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = title;
arParams[2] = new SqliteParameter(":Description", DbType.Object);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = description;
arParams[3] = new SqliteParameter(":ImageName", DbType.String, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = imageName;
arParams[4] = new SqliteParameter(":EventDate", DbType.DateTime);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = eventDate;
arParams[5] = new SqliteParameter(":StartTime", DbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = startTime;
arParams[6] = new SqliteParameter(":EndTime", DbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = endTime;
arParams[7] = new SqliteParameter(":CreatedDate", DbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = createdDate;
arParams[8] = new SqliteParameter(":UserID", DbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = userId;
arParams[9] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = itemGuid.ToString();
arParams[10] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = moduleGuid.ToString();
arParams[11] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = userGuid.ToString();
arParams[12] = new SqliteParameter(":Location", DbType.Object);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = location;
arParams[13] = new SqliteParameter(":TicketPrice", DbType.Decimal);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = ticketPrice;
arParams[14] = new SqliteParameter(":RequiresTicket", DbType.Int32);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = intRequiresTicket;
int newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
