public string LoadContent(Guid ID)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[LoadConnString()].ConnectionString))
{
conn.Open();
try
{
SqlCommand sqlcmd = new SqlCommand("SELECT @ContentText=ContentText FROM EditableContent WHERE ApplicationId=@ApplicationId AND ID=@ID;", conn);
sqlcmd.Parameters.Add("@ContentText", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters["@ContentText"].Direction = ParameterDirection.Output;
sqlcmd.Parameters["@ContentText"].Size = Int32.MaxValue;
sqlcmd.Parameters["@ID"].Value = ID;
sqlcmd.Parameters["@ApplicationId"].Value = new Guid(ConfigurationManager.AppSettings["AppId"]);
sqlcmd.ExecuteNonQuery();
return(HttpContext.Current.Server.HtmlDecode(sqlcmd.Parameters["@ContentText"].Value.ToString()));
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
return("Error loading content.");
}
}
}
public string SaveName(Guid ID, string Name)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[LoadConnString()].ConnectionString))
{
conn.Open();
try
{
string text = HttpUtility.UrlDecode(Name, Encoding.ASCII);
SqlCommand sqlcmd = new SqlCommand("UPDATE EditableContent SET Name=@Name WHERE ApplicationId=@ApplicationId AND ID=@ID;", conn);
sqlcmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar);
sqlcmd.Parameters["@ApplicationId"].Value = new Guid(ConfigurationManager.AppSettings["AppId"]);
sqlcmd.Parameters["@ID"].Value = ID;
sqlcmd.Parameters["@Name"].Value = HttpContext.Current.Server.HtmlEncode(text);
sqlcmd.ExecuteNonQuery();
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
return("Error saving name.");
}
}
return("Success");
}
private string SaveContent(Guid ID, string ContentID, string Content, bool Approved, bool IsNew)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[LoadConnString()].ConnectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
string text = HttpUtility.UrlDecode(Content, Encoding.ASCII);
try
{
SqlCommand sqlcmd = new SqlCommand("UPDATE EditableContent SET ContentText=@ContentText, LastModified=GetDate(), LastModifiedUserId=@UserId, IsApproved=@IsApproved WHERE ID=@ID AND ApplicationId=@ApplicationId;", conn, trans);
if (IsNew)
{
sqlcmd.CommandText = "INSERT INTO EditableContent (ApplicationId,Url,ContentId,ContentText,IsApproved,LastModifiedUserId) VALUES (@ApplicationId,@Url,@ContentId,@ContentText,@IsApproved,@UserId);";
}
sqlcmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ContentText", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@Url", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@ContentId", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@IsApproved", SqlDbType.Bit);
sqlcmd.Parameters["@ApplicationId"].Value = new Guid(ConfigurationManager.AppSettings["AppId"]);
sqlcmd.Parameters["@ID"].Value = ID;
sqlcmd.Parameters["@ContentText"].Value = HttpContext.Current.Server.HtmlEncode(text);
sqlcmd.Parameters["@UserId"].Value = Membership.GetUser().ProviderUserKey;
sqlcmd.Parameters["@Url"].Value = Url;
sqlcmd.Parameters["@ContentId"].Value = ContentID;
sqlcmd.Parameters["@IsApproved"].Value = (Approved && CanApprove());
sqlcmd.ExecuteNonQuery();
// Delete versions if there are more than 10 versions
sqlcmd.CommandText = "DELETE FROM EditableContent WHERE ApplicationId=@ApplicationId AND ContentId=(SELECT ContentId FROM EditableContent WHERE ApplicationId=@ApplicationId AND ID=@ID) AND Url=@Url AND IsPublished=0 AND ID NOT IN (SELECT TOP 10 ID FROM EditableContent WHERE ApplicationId=@ApplicationId AND Url=@Url AND ContentId=(SELECT ContentId FROM EditableContent WHERE ApplicationId=@ApplicationId AND ID=@ID) AND IsPublished=0 ORDER BY LastModified DESC);";
sqlcmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
trans.Rollback();
return("Error saving changes.");
}
}
if (Approved && Approve(ID, false, "", ContentID, null) != "Success")
{
return("Error approving content.");
}
return("Success");
}
public string CreateNew(string ContentID, string PageUrl)
{
if (!string.IsNullOrEmpty(PageUrl) && PageUrl != "undefined")
{
Url = PageUrl;
}
if (!CanEdit() && !CanApprove())
{
return("Invalid Permissions.");
}
if (CheckMaxVersions(ContentID))
{
return("Maximum versions exceeded.");
}
Guid VersionID = Guid.NewGuid();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[LoadConnString()].ConnectionString))
{
conn.Open();
try
{
SqlCommand sqlcmd = new SqlCommand("INSERT INTO EditableContent (ID,Url,ContentID,ApplicationId,LastModifiedUserId) VALUES (@ID,@Url,@ContentID,@ApplicationId,@UserId);", conn);
sqlcmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@Url", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@ContentID", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters["@ApplicationId"].Value = new Guid(ConfigurationManager.AppSettings["AppId"]);
sqlcmd.Parameters["@ID"].Value = VersionID;
sqlcmd.Parameters["@Url"].Value = Url;
sqlcmd.Parameters["@ContentID"].Value = ContentID;
sqlcmd.Parameters["@UserId"].Value = (Guid)Membership.GetUser().ProviderUserKey;
sqlcmd.ExecuteNonQuery();
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
return("Error creating new version.");
}
}
return(VersionID.ToString());
}
public String Publish(Guid ID, string PageUrl)
{
if (!string.IsNullOrEmpty(PageUrl) && PageUrl != "undefined")
{
Url = PageUrl;
}
if (!CanApprove())
{
return("Invalid Permissions.");
}
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[LoadConnString()].ConnectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction("Publish");
try
{
SqlCommand sqlcmd = new SqlCommand("DELETE FROM EditableContent WHERE ApplicationId=@ApplicationId AND" +
" Url=(SELECT Url FROM EditableContent WHERE ApplicationId=@ApplicationId AND ID=@ID) AND" +
" ContentId=(SELECT ContentId FROM EditableContent WHERE ApplicationId=@ApplicationId AND ID=@ID) AND IsPublished=1;", conn, trans);
sqlcmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters["@ApplicationId"].Value = new Guid(ConfigurationManager.AppSettings["AppId"]);
sqlcmd.Parameters["@ID"].Value = ID;
sqlcmd.ExecuteNonQuery();
sqlcmd.CommandText = "UPDATE EditableContent SET IsApproved=1 WHERE ApplicationId=@ApplicationId AND ID=@ID;" +
" INSERT INTO EditableContent (ApplicationId,Url,ContentId,ContentText,Name,LastModified,LastModifiedUserId,IsPublished,IsApproved,EditableContentId)" +
" SELECT ApplicationId,Url,ContentId,ContentText,Name,LastModified,LastModifiedUserId,1,1,ID FROM EditableContent" +
" WHERE ApplicationId=@ApplicationId AND ID=@ID;";
sqlcmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
trans.Rollback();
}
}
return("Success");
}
public String CopyContent(Guid ID, string ContentID, string PageUrl)
{
if (!string.IsNullOrEmpty(PageUrl) && PageUrl != "undefined")
{
Url = PageUrl;
}
if (!CanEdit())
{
return("Invalid Permissions.");
}
if (CheckMaxVersions(ContentID))
{
return("Maximum versions exceeded.");
}
Guid NewID = Guid.NewGuid();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[LoadConnString()].ConnectionString))
{
conn.Open();
try
{
SqlCommand sqlcmd = new SqlCommand("INSERT INTO EditableContent (ID,ApplicationId,Url,ContentId,ContentText,Name,LastModified,LastModifiedUserId)" +
" SELECT @NewID,ApplicationId,Url,ContentId,ContentText,Name,LastModified,LastModifiedUserId FROM EditableContent" +
" WHERE ApplicationId=@ApplicationId AND ID=@ID;", conn);
sqlcmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@NewID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters["@ApplicationId"].Value = new Guid(ConfigurationManager.AppSettings["AppId"]);
sqlcmd.Parameters["@ID"].Value = ID;
sqlcmd.Parameters["@NewID"].Value = NewID;
sqlcmd.ExecuteNonQuery();
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
return("Error copying version.");
}
}
return(NewID.ToString());
}
public string SaveApprove(Guid ID, string ContentID, string Content, bool Approve, bool IsNew, string PageUrl)
{
try
{
if (!string.IsNullOrEmpty(PageUrl) && PageUrl != "undefined")
{
Url = PageUrl;
}
if (!CanEdit() && !CanApprove())
{
return("Invalid Permssions.");
}
if (IsNew && CheckMaxVersions(ContentID))
{
return("Maximum versions exceeded.");
}
string status = SaveContent(ID, ContentID, Content, Approve, IsNew);
if (status != "Success")
{
return(status);
}
if (!Approve)
{
return(SendApproval(Content));
}
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
}
return("Success");
}
public String Approve(Guid ID, bool NewSave, string Content, string ContentID, string PageUrl)
{
if (!string.IsNullOrEmpty(PageUrl) && PageUrl != "undefined")
{
Url = PageUrl;
}
if (!CanApprove())
{
return("Invalid Permissions.");
}
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[LoadConnString()].ConnectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
SqlCommand sqlcmd = new SqlCommand("UPDATE EditableContent SET IsApproved=1 WHERE ApplicationId=@ApplicationId AND ID=@ID;", conn, trans);
sqlcmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters["@ApplicationId"].Value = new Guid(ConfigurationManager.AppSettings["AppId"]);
sqlcmd.Parameters["@ID"].Value = ID;
sqlcmd.ExecuteNonQuery();
// If content has changed then resave
if (NewSave && !string.IsNullOrEmpty(Content))
{
string text = HttpUtility.UrlDecode(Content, Encoding.ASCII);
sqlcmd.CommandText = "UPDATE EditableContent SET ContentText=@ContentText, LastModified=GetDate(), LastModifiedUserId=@UserId" +
" WHERE ApplicationId=@ApplicationId AND ID=@ID AND ContentText!=@ContentText;";
sqlcmd.Parameters.Add("@ContentText", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier);
sqlcmd.Parameters["@ContentText"].Value = HttpContext.Current.Server.HtmlEncode(text);
sqlcmd.Parameters["@UserId"].Value = (Guid)Membership.GetUser().ProviderUserKey;
sqlcmd.ExecuteNonQuery();
}
// If this is a published version, update the published version
sqlcmd.CommandText = "DELETE FROM EditableContent WHERE ApplicationId=@ApplicationId AND EditableContentId=@ID;";
sqlcmd.ExecuteNonQuery();
sqlcmd.CommandText = "IF NOT EXISTS (SELECT * FROM EditableContent WHERE ApplicationId=@ApplicationId" +
" AND Url=@Url AND ContentId=@ContentId AND IsPublished=1)" +
" BEGIN " +
" INSERT INTO EditableContent (ApplicationId,Url,ContentId,ContentText,IsPublished,IsApproved,EditableContentId,LastModified,LastModifiedUserId)" +
" SELECT ApplicationId,Url,ContentId,ContentText,1,1,ID,LastModified,LastModifiedUserId FROM EditableContent" +
" WHERE ApplicationId=@ApplicationId AND ID=@ID" +
" END";
sqlcmd.Parameters.Add("@Url", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@ContentId", SqlDbType.VarChar);
sqlcmd.Parameters["@Url"].Value = Url;
sqlcmd.Parameters["@ContentId"].Value = ContentID;
sqlcmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
SendErrorEmail.Send(new Uri(Url), e);
trans.Rollback();
return("Error approving content.");
}
}
return("Success");
}
public static void DoApplicationError()
{
SendErrorEmail.Send();
}
