public static Feed Load(FeedType type, string data)
{
Feed feed = new Feed(type, data);
string sql = "select Title, LogoUrl, ItemsData, ItemsDataDate, ItemsDataInvalidDate " +
"from Feeds " +
"where Type = @Type and Data = @Data and DataHash = @DataHash " +
"update Feeds " +
"set ReferencedDate = @ReferencedDate " +
"where Type = @Type and Data = @Data and DataHash = @DataHash ";
using (PhotoMixQuery query = new PhotoMixQuery(sql, CommandType.Text))
{
query.Parameters.Add("@Type", SqlDbType.Int).Value = (int)type;
query.Parameters.Add("@Data", SqlDbType.NVarChar).Value = data;
query.Parameters.Add("@DataHash", SqlDbType.Int).Value = data.GetHashCode();
query.Parameters.Add("@ReferencedDate", SqlDbType.DateTime).Value = DateTime.Now;
if (query.Reader.Read())
{
feed.title = query.Reader.GetString(0);
feed.logoUrl = query.Reader.IsDBNull(1) ? null : query.Reader.GetString(1);
feed.itemsInvalidDate = query.Reader.IsDBNull(4) ? DateTime.MinValue : query.Reader.GetDateTime(4);
if (feed.itemsInvalidDate < DateTime.Now)
{
feed.itemsDate = query.Reader.IsDBNull(3) ? DateTime.MinValue : query.Reader.GetDateTime(3);
feed.items = query.Reader.IsDBNull(2) ? null : ItemsFromString(query.Reader.GetString(2));
}
}
}
return(feed);
}
//
// Load
//
// Loads the object from the database, and if it doesn't exist, creates it
static public CompiledTextFeed Load(string textFeedUrl, string logoImageUrl, string title, bool renderAd, int compiledTextFeedHash, Guid compiledTextFeedGuid)
{
CompiledTextFeed compiledTextFeed = new CompiledTextFeed();
compiledTextFeed.textFeedUrl = textFeedUrl;
if (textFeedUrl != null)
{
compiledTextFeed.compiledTextFeedHash = ((string)(textFeedUrl.ToLower() + renderAd.ToString())).GetHashCode();
compiledTextFeed.compiledTextFeedGuid = Guid.NewGuid();
}
else
{
compiledTextFeed.compiledTextFeedHash = compiledTextFeedHash;
compiledTextFeed.compiledTextFeedGuid = compiledTextFeedGuid;
}
compiledTextFeed.compiledDate = DateTime.Now;
compiledTextFeed.imageGeneratedDate = DateTime.Now;
using (PhotoMixQuery query = new PhotoMixQuery("SelectCompiledTextFeed"))
{
query.Parameters.Add("@CompiledTextFeedHash", SqlDbType.Int).Value = compiledTextFeed.compiledTextFeedHash;
query.Parameters.Add("@CompiledTextFeedGuid", SqlDbType.UniqueIdentifier).Value = compiledTextFeed.compiledTextFeedGuid;
if (textFeedUrl != null)
{
query.Parameters.Add("@TextFeedUrl", SqlDbType.VarChar).Value = textFeedUrl;
query.Parameters.Add("@LogoImageUrl", SqlDbType.VarChar).Value = String.IsNullOrEmpty(logoImageUrl) ? (Object)DBNull.Value : (Object)logoImageUrl;
if (renderAd)
{
query.Parameters.Add("@Data", SqlDbType.Text).Value = "<RAD>true</RAD>";
}
query.Parameters.Add("@Title", SqlDbType.VarChar).Value = String.IsNullOrEmpty(title) ? (Object)DBNull.Value : (Object)title;
query.Parameters.Add("@CompiledDate", SqlDbType.DateTime).Value = compiledTextFeed.compiledDate;
}
else
{
query.Parameters.Add("@ImageGeneratedDate", SqlDbType.DateTime).Value = DateTime.Now;
}
if (query.Reader.Read())
{
compiledTextFeed.logoImageUrl = query.Reader.IsDBNull(0) ? null : query.Reader.GetString(0);
compiledTextFeed.title = query.Reader.IsDBNull(1) ? null : query.Reader.GetString(1);
if (!query.Reader.IsDBNull(2))
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(query.Reader.GetString(2));
try { compiledTextFeed.renderAd = FormUtil.GetBoolean(xmlDoc.SelectSingleNode("RAD").InnerText); }
catch { }
}
compiledTextFeed.compiledDate = query.Reader.IsDBNull(3) ? DateTime.MinValue : query.Reader.GetDateTime(3);
compiledTextFeed.imageGeneratedDate = query.Reader.IsDBNull(4) ? DateTime.MinValue : query.Reader.GetDateTime(4);
compiledTextFeed.compiledTextFeedGuid = query.Reader.GetGuid(5);
}
}
return(compiledTextFeed);
}
//
// Load
//
// Loads the object from the database, and if it doesn't exist, creates it
//
static private CompiledWebPage Load(string url, int compiledWebPageHash, Guid compiledWebPageGuid, bool forImage, DateTime dateContext, bool bypassCaches)
{
CompiledWebPage compiledWebPage = null;
using (PhotoMixQuery query = new PhotoMixQuery("SelectCompiledWebPage"))
{
if (forImage)
{
query.Parameters.Add("@ImageGeneratedDate", SqlDbType.DateTime).Value = dateContext;
query.Parameters.Add("@CompiledWebPageGuid", SqlDbType.UniqueIdentifier).Value = compiledWebPageGuid;
query.Parameters.Add("@CompiledWebPageHash", SqlDbType.Int).Value = compiledWebPageHash;
}
else
{
query.Parameters.Add("@CompiledDate", SqlDbType.DateTime).Value = dateContext;
query.Parameters.Add("@Url", SqlDbType.NVarChar).Value = url;
query.Parameters.Add("@CompiledWebPageGuid", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
query.Parameters.Add("@CompiledWebPageHash", SqlDbType.Int).Value = url.GetHashCode();
}
if (query.Reader.Read())
{
compiledWebPage = new CompiledWebPage();
compiledWebPage.compiledWebPageHash = query.Reader.GetInt32(0);
compiledWebPage.compiledWebPageGuid = query.Reader.GetGuid(1);
compiledWebPage.url = query.Reader.GetString(2);
compiledWebPage.compiledDate = query.Reader.GetDateTime(3);
compiledWebPage.imageGeneratedDate = query.Reader.IsDBNull(4) ? DateTime.MinValue : query.Reader.GetDateTime(4);
compiledWebPage.fetchDataDate = query.Reader.IsDBNull(5) ? DateTime.MinValue : query.Reader.GetDateTime(5);
}
}
if (compiledWebPage != null &&
(bypassCaches || compiledWebPage.fetchDataDate.AddMinutes(CompiledWebPage.webPageFetchTTL) < dateContext) || !CheckFetchWebPageData(compiledWebPage.compiledWebPageGuid)
)
{
FetchWebPageData(compiledWebPage.url, compiledWebPage.compiledWebPageGuid);
string sql = "update CompiledWebPages " +
"set FetchDataDate = @FetchDataDate " +
"where CompiledWebPageGuid = @CompiledWebPageGuid";
using (PhotoMixQuery query2 = new PhotoMixQuery(sql, CommandType.Text))
{
query2.Parameters.Add("@CompiledWebPageGuid", SqlDbType.UniqueIdentifier).Value = compiledWebPage.compiledWebPageGuid;
query2.Parameters.Add("@FetchDataDate", SqlDbType.DateTime).Value = dateContext;
query2.Execute();
}
compiledWebPage.fetchDataDate = dateContext;
}
return(compiledWebPage);
}
public void Save()
{
if (!this.isDirty)
{
return;
}
string sql = "if not exists (select Data from Feeds where Type = @Type and Data = @Data and DataHash = @DataHash) " +
" insert into Feeds (" +
" Type, Data, DataHash, Title, LogoUrl, ItemsData, ItemsDataDate, ItemsDataInvalidDate, ReferencedDate " +
" )" +
" values (" +
" @Type, @Data, @DataHash, @Title, @LogoUrl, @ItemsData, @ItemsDataDate, @ItemsDataInvalidDate, @ReferencedDate " +
" )" +
"else" +
" update Feeds" +
" set " +
" Title = @Title, LogoUrl = @LogoUrl, ItemsData = @ItemsData, ItemsDataDate = @ItemsDataDate, ItemsDataInvalidDate = @ItemsDataInvalidDate, ReferencedDate = @ReferencedDate" +
" where Type = @Type and Data = @Data and DataHash = @DataHash ";
using (PhotoMixQuery query = new PhotoMixQuery(sql, CommandType.Text))
{
query.Parameters.Add("@Type", SqlDbType.Int).Value = (int)this.feedType;
query.Parameters.Add("@Data", SqlDbType.NVarChar).Value = this.data;
query.Parameters.Add("@DataHash", SqlDbType.Int).Value = this.data.GetHashCode();
if (this.title != null)
{
query.Parameters.Add("@Title", SqlDbType.NVarChar).Value = this.title;
}
else
{
query.Parameters.Add("@Title", SqlDbType.NVarChar).Value = DBNull.Value;
}
if (this.logoUrl != null)
{
query.Parameters.Add("@LogoUrl", SqlDbType.NVarChar).Value = this.logoUrl;
}
else
{
query.Parameters.Add("@LogoUrl", SqlDbType.NVarChar).Value = DBNull.Value;
}
query.Parameters.Add("@ItemsDataDate", SqlDbType.DateTime).Value = this.itemsDate;
query.Parameters.Add("@ItemsDataInvalidDate", SqlDbType.DateTime).Value = this.itemsInvalidDate;
query.Parameters.Add("@ItemsData", SqlDbType.Text).Value = ItemsToString(this.items);
query.Parameters.Add("@ReferencedDate", SqlDbType.DateTime).Value = DateTime.Now;
query.Execute();
}
}
//
// DeleteFriendlyName
//
// Removes the friendly name for the passed in slideshow
//
static public void DeleteFriendlyName(string friendlyName, Guid slideShowGuid, int puidHash)
{
// First delete the friendly name from the lookup table
if (!String.IsNullOrEmpty(friendlyName))
{
string sql = "delete from FriendlyNames where FriendlyName = @FriendlyName and SlideShowGuid = @SlideShowGuid and PuidHash = @PuidHash and FriendlyNameHash = @FriendlyNameHash";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = friendlyName;
query.Parameters.Add("@SlideShowGuid", SqlDbType.UniqueIdentifier).Value = slideShowGuid;
query.Parameters.Add("@PuidHash", SqlDbType.Int).Value = puidHash;
query.Parameters.Add("@FriendlyNameHash", SqlDbType.Int).Value = friendlyName.GetHashCode();
query.Execute();
}
}
}
static public bool CheckFriendlyNameAvailable(string friendlyName)
{
string sql = "select FriendlyName from FriendlyNames where FriendlyName = @FriendlyName and FriendlyNameHash = @FriendlyNameHash";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = friendlyName;
query.Parameters.Add("@FriendlyNameHash", SqlDbType.Int).Value = friendlyName.GetHashCode();
if (query.Reader.Read())
{
return(false);
}
}
return(true);
}
//
// Load
//
// Loads the object from the database, and if it doesn't exist, creates it
static public CompiledTextFeedItem Load(int compiledTextFeedHash, Guid compiledTextFeedGuid, string title, string description, int compiledTextFeedItemHash)
{
CompiledTextFeedItem compiledTextFeedItem = new CompiledTextFeedItem();
compiledTextFeedItem.compiledTextFeedHash = compiledTextFeedHash;
compiledTextFeedItem.compiledTextFeedGuid = compiledTextFeedGuid;
if ((title != null) || (description != null))
{
compiledTextFeedItem.compiledTextFeedItemHash = ((string)(title + description)).GetHashCode();
}
else
{
compiledTextFeedItem.compiledTextFeedItemHash = compiledTextFeedItemHash;
}
compiledTextFeedItem.compiledDate = DateTime.Now;
compiledTextFeedItem.imageGeneratedDate = DateTime.Now;
using (PhotoMixQuery query = new PhotoMixQuery("SelectCompiledTextFeedItem"))
{
query.Parameters.Add("@CompiledTextFeedHash", SqlDbType.Int).Value = compiledTextFeedItem.compiledTextFeedHash;
query.Parameters.Add("@CompiledTextFeedGuid", SqlDbType.UniqueIdentifier).Value = compiledTextFeedItem.compiledTextFeedGuid;
query.Parameters.Add("@CompiledTextFeedItemHash", SqlDbType.Int).Value = compiledTextFeedItem.compiledTextFeedItemHash;
if ((title != null) || (description != null))
{
query.Parameters.Add("@Title", SqlDbType.VarChar).Value = String.IsNullOrEmpty(title) ? (Object)DBNull.Value : (Object)title;
query.Parameters.Add("@Description", SqlDbType.Text).Value = String.IsNullOrEmpty(description) ? (Object)DBNull.Value : (Object)description;
query.Parameters.Add("@CompiledDate", SqlDbType.DateTime).Value = compiledTextFeedItem.compiledDate;
}
else
{
query.Parameters.Add("@ImageGeneratedDate", SqlDbType.DateTime).Value = compiledTextFeedItem.imageGeneratedDate;
}
if (query.Reader.Read())
{
compiledTextFeedItem.title = query.Reader.IsDBNull(0) ? null : query.Reader.GetString(0);
compiledTextFeedItem.description = query.Reader.IsDBNull(1) ? null : query.Reader.GetString(1);
compiledTextFeedItem.compiledDate = query.Reader.IsDBNull(2) ? DateTime.MinValue : query.Reader.GetDateTime(2);
compiledTextFeedItem.imageGeneratedDate = query.Reader.IsDBNull(3) ? DateTime.MinValue : query.Reader.GetDateTime(3);
}
}
return(compiledTextFeedItem);
}
static public Guid LookupFriendlyName(string friendlyName, out int puidHash)
{
string sql = "select SlideShowGuid, PuidHash from FriendlyNames where FriendlyName = @FriendlyName and FriendlyNameHash = @FriendlyNameHash";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = friendlyName;
query.Parameters.Add("@FriendlyNameHash", SqlDbType.Int).Value = friendlyName.GetHashCode();
if (query.Reader.Read())
{
puidHash = query.Reader.GetInt32(1);
return(query.Reader.GetGuid(0));
}
}
puidHash = 0;
return(Guid.Empty);
}
//
// Load
//
// Loads the object from the database, and if it doesn't exist, creates it
static public CompiledWeather Load(string language, string location, bool forImage, DateTime dateContext, bool bypassCaches)
{
CompiledWeather compiledWeather = null;
using (PhotoMixQuery query = new PhotoMixQuery("SelectCompiledWeather"))
{
query.Parameters.Add("@Language", SqlDbType.Char).Value = language;
query.Parameters.Add("@Location", SqlDbType.VarChar).Value = location;
if (forImage)
{
query.Parameters.Add("@ImageGeneratedDate", SqlDbType.DateTime).Value = dateContext;
}
else
{
query.Parameters.Add("@CompiledDate", SqlDbType.DateTime).Value = dateContext;
}
if (query.Reader.Read())
{
compiledWeather = new CompiledWeather();
compiledWeather.language = language;
compiledWeather.location = location;
compiledWeather.compiledDate = query.Reader.IsDBNull(3) ? DateTime.MinValue : query.Reader.GetDateTime(3);
compiledWeather.imageGeneratedDate = query.Reader.IsDBNull(4) ? DateTime.MinValue : query.Reader.GetDateTime(4);
if (!query.Reader.IsDBNull(2))
{
XmlDocument xmlDoc = new XmlDocument();
compiledWeather.weatherData = xmlDoc.CreateNode(XmlNodeType.Element, "weatherdata", "");
compiledWeather.weatherData.InnerXml = query.Reader.IsDBNull(2) ? null : query.Reader.GetString(2);
compiledWeather.fetchDataDate = query.Reader.IsDBNull(5) ? DateTime.MinValue : query.Reader.GetDateTime(5);
}
else
{
compiledWeather.weatherData = null;
compiledWeather.fetchDataDate = DateTime.MinValue;
}
}
}
if (compiledWeather != null &&
(bypassCaches || compiledWeather.weatherData == null || compiledWeather.fetchDataDate.AddMinutes(CompiledWeather.weatherFetchTTL) < dateContext))
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("http://weather.msn.com/weatherdata.aspx?wealocations=" + location);
XmlNode weatherData = xmlDoc.SelectSingleNode("weatherdata");
string sql = "update CompiledWeather " +
"set WeatherData = @WeatherData, FetchDataDate = @FetchDataDate " +
"where Language = @Language and Location = @Location";
using (PhotoMixQuery query2 = new PhotoMixQuery(sql, CommandType.Text))
{
query2.Parameters.Add("@Language", SqlDbType.Char).Value = language;
query2.Parameters.Add("@Location", SqlDbType.VarChar).Value = location;
query2.Parameters.Add("@WeatherData", SqlDbType.Text).Value = String.IsNullOrEmpty(weatherData.InnerXml) ? (Object)DBNull.Value : (Object)weatherData.InnerXml;
query2.Parameters.Add("@FetchDataDate", SqlDbType.DateTime).Value = dateContext;
query2.Execute();
}
compiledWeather.weatherData = weatherData;
compiledWeather.fetchDataDate = dateContext;
}
return(compiledWeather);
}
//
// UpdateFriendlyName
//
// Updates the friendly name from the old friendly name to the new friendly name for the passed in slideshow. If the old slideshow is
// specified (optional) than the new friendly name is currently assigned to this slideshow and will be reasigned to the new slide show. It
// is assumed that this old slideshow is owned by the same PUID as the new slideshow
//
static public void UpdateFriendlyName(string newFriendlyName, string oldFriendlyName, Guid slideShowGuid, int puidHash, Guid oldSlideShowGuid)
{
string sql;
// We need to do this as distinct database transactions for future web store
// integration. This is because the friendly name partition is different than the slide show partition. As such
// updates to the friendly name table and the slideshow table would likely talk to different databases.
//
// To handle this we assume that the friendly name table is the definitive source of ownership of a friendly name.
// The slideshow table has the friendly name, but this is verfied before it is assumed to be valid.
//
// By doing so the right way we are never left in an inconsistent state. The Slideshow
// itself can deal with thinking it has a friendly name it doesn't own, but not the other way around
// So we order these transactions such if there is a failure of a subsequent write in this sequence things
// are not left in a bad state or with dangling data that won't be cleaned up
// First delete the old friendly name from the friendly name table
if (!String.IsNullOrEmpty(oldFriendlyName))
{
sql = "delete from FriendlyNames " +
"where FriendlyName = @FriendlyName and SlideShowGuid = @SlideShowGuid and PuidHash = @PuidHash and FriendlyNameHash = @FriendlyNameHash";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = oldFriendlyName;
query.Parameters.Add("@SlideShowGuid", SqlDbType.UniqueIdentifier).Value = slideShowGuid;
query.Parameters.Add("@PuidHash", SqlDbType.Int).Value = puidHash;
query.Parameters.Add("@FriendlyNameHash", SqlDbType.Int).Value = oldFriendlyName.GetHashCode();
query.Execute();
}
}
// Now update the slide show friendly name to the new value
sql = "" +
"update SlideShows " +
"set FriendlyName = @FriendlyName " +
"where SlideShowGuid = @SlideShowGuid and PuidHash = @PuidHash";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
if (String.IsNullOrEmpty(newFriendlyName))
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = DBNull.Value;
}
else
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = newFriendlyName;
}
query.Parameters.Add("@PuidHash", SqlDbType.Int).Value = puidHash;
query.Parameters.Add("@SlideShowGuid", SqlDbType.UniqueIdentifier).Value = slideShowGuid;
query.Execute();
}
// If we are taking the name from another
if (oldSlideShowGuid != Guid.Empty)
{
// Update the friendly name table for this friendly name
sql = "update FriendlyNames " +
"set SlideShowGuid = @SlideShowGuid, PuidHash = @PuidHash " +
"where FriendlyName = @FriendlyName and FriendlyNameHash = @FriendlyNameHash";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
query.Parameters.Add("@PuidHash", SqlDbType.Int).Value = puidHash;
query.Parameters.Add("@SlideShowGuid", SqlDbType.UniqueIdentifier).Value = slideShowGuid;
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = newFriendlyName;
query.Parameters.Add("@FriendlyNameHash", SqlDbType.Int).Value = newFriendlyName.GetHashCode();
query.Execute();
}
// Clear the friendly name from the old slideshow
sql = "" +
"update SlideShows " +
"set FriendlyName = @FriendlyName " +
"where SlideShowGuid = @SlideShowGuid and PuidHash = @PuidHash";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = DBNull.Value;
query.Parameters.Add("@PuidHash", SqlDbType.Int).Value = puidHash;
query.Parameters.Add("@SlideShowGuid", SqlDbType.UniqueIdentifier).Value = oldSlideShowGuid;
query.Execute();
}
}
// We are simply assigning a new friendly name
else
{
// Insert the new friendly name into the lookup table
if (!String.IsNullOrEmpty(newFriendlyName))
{
sql = "" +
"if not exists (select FriendlyName from FriendlyNames where FriendlyName = @FriendlyName and FriendlyNameHash = @FriendlyNameHash)" +
"insert into FriendlyNames (" +
" FriendlyName, SlideShowGuid, PuidHash, FriendlyNameHash" +
") values (" +
" @FriendlyName, @SlideShowGuid, @PuidHash, @FriendlyNameHash" +
")";
using (PhotoMixQuery query = new PhotoMixQuery(sql, System.Data.CommandType.Text))
{
query.Parameters.Add("@FriendlyName", SqlDbType.NVarChar).Value = newFriendlyName;
query.Parameters.Add("@PuidHash", SqlDbType.Int).Value = puidHash;
query.Parameters.Add("@SlideShowGuid", SqlDbType.UniqueIdentifier).Value = slideShowGuid;
query.Parameters.Add("@FriendlyNameHash", SqlDbType.Int).Value = newFriendlyName.GetHashCode();
query.Execute();
}
}
}
}
