// Copyright 2005 by Omar Al Zabir. All rights are reserved.
//
// If you like this code then feel free to go ahead and use it.
// The only thing I ask is that you don't remove or alter my copyright notice.
//
// Your use of this software is entirely at your own risk. I make no claims or
// warrantees about the reliability or fitness of this code for any particular purpose.
// If you make changes or additions to this code please mark your code as being yours.
//
// website http://www.oazabir.com, email OmarAlZabir@gmail.com, msn oazabir@hotmail.com
using System;
using System.Diagnostics;
using System.Collections;
using System.Data;
using System.Data.OleDb;
namespace RSSFeeder.Helpers{
using RSSCommon;
/// <summary>
/// Database storage helper
/// </summary>
public class DatabaseHelper
{
#region Constructor
#endregion
#region Connection Handling
private static OleDbConnection __Connection = null;
private static OleDbConnection _Connection
{
get
{
if( null == __Connection )
{
string connectionString = string.Format( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};",
ApplicationSettings.DatabaseFilePath );
__Connection = new OleDbConnection( connectionString );
__Connection.Open();
}
else
{
while( ConnectionState.Executing == __Connection.State || ConnectionState.Fetching == __Connection.State )
{
System.Threading.Thread.Sleep( 10 );
}
if( ConnectionState.Open != __Connection.State )
__Connection.Open();
}
return __Connection;
}
}
public static void Close()
{
if( null != __Connection )
if( ConnectionState.Closed != __Connection.State )
{
__Connection.Close();
__Connection.Dispose();
}
}
#endregion
#region Channel
public static void GetStatistics( out int channelCount, out int totalUnreadCount )
{
using( OleDbCommand cmd = new OleDbCommand( "SELECT Count(*) FROM Channels", _Connection ) )
{
channelCount = (int)cmd.ExecuteScalar();
cmd.CommandText = "SELECT Count(*) FROM RssFeeds WHERE IsRead = 0";
totalUnreadCount = (int)cmd.ExecuteScalar();
}
}
private static Channel MakeChannel( OleDbDataReader reader )
{
int id = (int)reader[Channel.Properties.Id];
string title = reader[Channel.Properties.Title] as string;
string feedUrl = reader[Channel.Properties.FeedURL] as string;
string folderPath = reader[Channel.Properties.FolderPath] as string;
string xslPath = reader[Channel.Properties.XSLPath] as string;
int itemCount = (int)reader[Channel.Properties.ItemCount];
int unreadCount = (int)reader[Channel.Properties.UnreadCount];
Channel.FolderCreationTypeEnum type = (Channel.FolderCreationTypeEnum)reader[Channel.Properties.FolderType];
string outlookViewXmlPath = (string)reader[Channel.Properties.OutlookViewXmlPath];
string outlookXSL = (string)reader[Channel.Properties.OutlookXSL];
string userName = (string)reader[Channel.Properties.UserName];
string userPassword = (string)reader[Channel.Properties.UserPassword];
TimeSpan frequency = TimeSpan.FromMinutes( (int)reader[ Channel.Properties.Frequency ] );
DateTime lastUpdated = (DateTime)reader[Channel.Properties.LastUpdated];
DateTime lastUpdatedInOutlook = (DateTime)reader[Channel.Properties.LastUpdatedInOutlook];
DateTime nextUpdate = (DateTime)reader[Channel.Properties.NextUpdate];
bool showInNewsPaper = (bool)reader[Channel.Properties.ShowInNewspaper];
Channel channel = new Channel( id, title, new Uri( feedUrl ), frequency, lastUpdated,
nextUpdate, lastUpdatedInOutlook, folderPath, xslPath,
showInNewsPaper, itemCount, unreadCount, type, outlookViewXmlPath, outlookXSL,
userName, userPassword );
return channel;
}
public static Channel GetChannel( int channelId )
{
using( OleDbCommand cmd = new OleDbCommand( "SELECT * FROM Channels ORDER BY Title", _Connection ) )
{
using( OleDbDataReader reader = cmd.ExecuteReader( ) )
{
if( reader.Read() )
{
return MakeChannel( reader );
}
else
{
return null;
}
}
}
}
public static IList GetChannels()
{
ArrayList channels = new ArrayList();
using( OleDbCommand cmd = new OleDbCommand( "SELECT * FROM Channels ORDER BY Title", _Connection ) )
{
using( OleDbDataReader reader = cmd.ExecuteReader( ) )
{
while( reader.Read() )
{
Channel channel = MakeChannel( reader );
channels.Add( channel );
}
reader.Close();
}
}
foreach( Channel channel in channels )
RefreshChannelCounts( channel.Id );
channels.Clear();
using( OleDbCommand cmd = new OleDbCommand( "SELECT * FROM Channels ORDER BY Title", _Connection ) )
{
using( OleDbDataReader reader = cmd.ExecuteReader( ) )
{
while( reader.Read() )
{
Channel channel = MakeChannel( reader );
channels.Add( channel );
}
reader.Close();
}
}
return channels;
}
public static void RefreshChannelCounts( int channelID )
{
using( OleDbCommand cmd = new OleDbCommand( "", _Connection ) )
{
// Get item count
string itemCountQuery = string.Format( "SELECT Count(*) FROM RssFeeds WHERE ChannelID = {0}", channelID );
cmd.CommandText = itemCountQuery;
int itemCount = (int)cmd.ExecuteScalar();
// Get unread count
string unreadCountQuery = string.Format( "SELECT Count(*) FROM RssFeeds WHERE ChannelID = {0} AND isRead = 0", channelID );
cmd.CommandText = unreadCountQuery;
int unreadCount = (int)cmd.ExecuteScalar();
string sql = string.Format(
"UPDATE Channels SET ItemCount = {0}" +
", UnreadCount = {1} " +
" WHERE Id = {2}", itemCount, unreadCount, channelID );
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
protected static void PrepareCommandFromChannel( Channel channel, OleDbCommand cmd )
{
cmd.Parameters.Add(Channel.Properties.Title, OleDbType.VarChar).Value = channel.Title;
cmd.Parameters.Add(Channel.Properties.FeedURL, OleDbType.LongVarWChar).Value = channel.FeedURL.ToString();
cmd.Parameters.Add(Channel.Properties.Frequency, OleDbType.Integer).Value = Convert.ToInt32( channel.Frequency.TotalMinutes );
cmd.Parameters.Add(Channel.Properties.LastUpdated, OleDbType.Date).Value = channel.LastUpdated;
cmd.Parameters.Add(Channel.Properties.NextUpdate, OleDbType.Date).Value = channel.NextUpdate;
cmd.Parameters.Add(Channel.Properties.LastUpdatedInOutlook, OleDbType.Date).Value = channel.LastUpdatedInOutlook;
cmd.Parameters.Add(Channel.Properties.FolderPath, OleDbType.LongVarWChar).Value = channel.FolderPath;
cmd.Parameters.Add(Channel.Properties.XSLPath, OleDbType.LongVarWChar).Value = channel.XSLPath;
cmd.Parameters.Add(Channel.Properties.ShowInNewspaper, OleDbType.Boolean).Value = channel.ShowInNewspaper;
cmd.Parameters.Add(Channel.Properties.ItemCount, OleDbType.Integer).Value = channel.ItemCount;
cmd.Parameters.Add(Channel.Properties.UnreadCount, OleDbType.Integer).Value = channel.UnreadCount;
cmd.Parameters.Add(Channel.Properties.FolderType, OleDbType.Integer).Value = (int)channel.FolderType;
cmd.Parameters.Add(Channel.Properties.OutlookViewXmlPath, OleDbType.LongVarWChar).Value = channel.OutlookViewXmlPath;
cmd.Parameters.Add(Channel.Properties.OutlookXSL, OleDbType.LongVarWChar).Value = channel.OutlookXSL;
cmd.Parameters.Add(Channel.Properties.UserName, OleDbType.LongVarChar).Value = channel.UserName;
cmd.Parameters.Add(Channel.Properties.UserPassword, OleDbType.LongVarWChar).Value = channel.UserPassword;
}
public static bool AddNewChannel( ref Channel channel )
{
using( OleDbCommand cmd = new OleDbCommand( "INSERT INTO Channels (Title, FeedURL, Frequency, " +
"LastUpdated, NextUpdate, LastUpdatedInOutlook, FolderPath, XSLPath, ShowInNewspaper, " +
"ItemCount, UnreadCount, FolderType, OutlookViewXmlPath, OutlookXSL, UserName, UserPassword) VALUES (@Title, @FeedURL, @Frequency, " +
"@LastUpdated, @NextUpdate, @LastUpdatedInOutlook, @FolderPath, @XSLPath, " +
"@ShowInNewspaper, @ItemCount, @UnreadCount, @FolderType, @OutlookViewXmlPath, @OutlookXSL , @UserName, @UserPassword )",
_Connection ) )
{
PrepareCommandFromChannel( channel, cmd );
try
{
// Perform insert
cmd.ExecuteNonQuery();
// Get the recent ID
cmd.CommandText = "SELECT MAX(ID) FROM Channels";
using( OleDbDataReader reader = cmd.ExecuteReader() )
{
if( reader.Read() )
channel.Id = Convert.ToInt32( reader[0].ToString() );
else
return false;
}
return true;
}
catch( OleDbException x )
{
throw x;
}
}
}
public static bool UpdateChannel( Channel channel )
{
using( OleDbCommand cmd = new OleDbCommand( "UPDATE Channels SET [Title] = @Title, " +
"[FeedURL] = @FeedURL, [Frequency] = @Frequency, [LastUpdated] = @LastUpdated, " +
"[NextUpdate] = @NextUpdate, [LastUpdatedInOutlook] = @LastUpdatedInOutlook, " +
"[FolderPath] = @FolderPath, [XSLPath] = @XSLPath, [ShowInNewspaper] = @ShowInNewspaper, " +
"[ItemCount] = @ItemCount, [UnreadCount] = @UnreadCount, [FolderType] = @FolderType, " +
"[OutlookViewXmlPath] = @OutlookViewXmlPath, [OutlookXSL] = @OutlookXSL, " +
"[UserName] = @UserName, [UserPassword] = @UserPassword " +
" WHERE [ID]="
+ channel.Id.ToString(), _Connection ) )
{
try
{
PrepareCommandFromChannel( channel, cmd );
return (1 == cmd.ExecuteNonQuery());
}
catch( OleDbException x )
{
throw x;
}
}
}
public static void DeleteAllChannels()
{
using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM Channels", _Connection ) )
{
cmd.ExecuteNonQuery();
}
}
public static void DeleteChannel( int channelID )
{
DeleteFeedsFromChannel( channelID );
using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM Channels WHERE ID = " + channelID.ToString(),
_Connection) )
{
cmd.ExecuteNonQuery();
}
}
#endregion
#region RSS
public static IList GetRssItems( int channelID, DateTime pubDateAfter )
{
ArrayList items = new ArrayList();
using( OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds WHERE [ChannelID]=@ChannelID AND [PubDate]>=@PubDate ORDER BY [PubDate] DESC", _Connection ) )
{
command.Parameters.Add("ChannelID", channelID );
command.Parameters.Add("PubDate", pubDateAfter );
using( OleDbDataReader reader = command.ExecuteReader( ) )
{
while( reader.Read() )
{
RssFeed feed = MakeFeed( reader );
items.Add( feed );
}
}
}
return items;
}
public static IList SearchFeed( string searchWords )
{
ArrayList items = new ArrayList();
searchWords = searchWords.Replace("'", "''");
string [] words = searchWords.Split(' ');
string likeExpr = "LIKE '%" + string.Join( "%' OR XML LIKE '%", words ) + "%'";
OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds WHERE XML " + likeExpr, _Connection );
using( OleDbDataReader reader = command.ExecuteReader( ) )
{
while( reader.Read() )
{
RssFeed feed = MakeFeed( reader );
items.Add( feed );
}
}
return items;
}
public static IList GetRssItemsPendingForOutlook( int channelID )
{
ArrayList items = new ArrayList();
OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds WHERE [ChannelID]=@ChannelID AND [IsInOutlook]=0 ORDER BY [PubDate] DESC", _Connection );
command.Parameters.Add("ChannelID", channelID );
using( OleDbDataReader reader = command.ExecuteReader( ) )
{
while( reader.Read() )
{
RssFeed feed = MakeFeed( reader );
items.Add( feed );
}
}
return items;
}
public static IList GetTopRssItems( int channelID, int top, bool isRead )
{
ArrayList items = new ArrayList();
using( OleDbCommand command = new OleDbCommand( "SELECT * FROM RssFeeds "
+ "WHERE [ChannelID]=@ChannelID "
+ "AND [IsRead] = " + (isRead ? "1" : "0")
+ " ORDER BY [PubDate] DESC", _Connection ) )
{
command.Parameters.Add("ChannelID", channelID );
using( OleDbDataReader reader = command.ExecuteReader( ) )
{
while( reader.Read() && top-- > 0 )
{
RssFeed feed = MakeFeed( reader );
items.Add( feed );
}
reader.Close();
}
}
return items;
}
protected static RssFeed MakeFeed( OleDbDataReader reader )
{
string guid = reader["Guid"] as string;
string title = reader["Title"] as string;
string xml = reader["XML"] as string;
DateTime pubDate = (DateTime)reader["PubDate"];
bool isRead = (bool)reader["IsRead"];
bool isInOutlook = (bool)reader["IsInOutlook"];
int channelID = (int)reader["ChannelID"];
RssFeed feed = new RssFeed( title, guid, channelID, xml, pubDate, isRead, isInOutlook );
return feed;
}
protected static void PrepareCommandForRssFeed( RssFeed item, OleDbCommand cmd )
{
cmd.Parameters.Add("Guid", OleDbType.WChar).Value = item.Guid;
cmd.Parameters.Add("Title", OleDbType.LongVarWChar).Value = item.Title;
cmd.Parameters.Add("XML", OleDbType.LongVarWChar).Value = item.XML;
cmd.Parameters.Add("PubDate", OleDbType.Date).Value = item.PublishDate;
cmd.Parameters.Add("IsRead", OleDbType.Boolean).Value = item.IsRead;
cmd.Parameters.Add("IsInOutlook", OleDbType.Boolean).Value = item.IsInOutlook;
}
public static bool AddNewFeed( RssFeed item, bool isRefreshChannelCount )
{
try
{
if( item.PublishDate < DateTime.Now.AddYears(-100) ) item.PublishDate = DateTime.Now;
string sql = string.Format( "INSERT INTO RssFeeds ([Guid], [ChannelID], [Title], [XML], [PubDate], [IsRead], [IsInOutlook]) VALUES (@Guid, {0}, @Title, @XML, @PubDate, @IsRead, @IsInOutlook)", item.ChannelID );
using( OleDbCommand cmd = new OleDbCommand( sql, _Connection ) )
{
PrepareCommandForRssFeed( item, cmd );
cmd.ExecuteNonQuery();
if( isRefreshChannelCount )
{
RefreshChannelCounts( item.ChannelID );
}
}
return true;
}
catch( OleDbException x )
{
if( x.ErrorCode == -2147467259 )
{
// duplicate insert
UpdateFeed( item, isRefreshChannelCount );
return false;
}
else
{
throw x;
}
}
}
public static RssFeed GetFeed( string guid )
{
string sql = "SELECT * FROM RssFeeds WHERE Guid = '" + guid + "'";
using( OleDbCommand cmd = new OleDbCommand( sql, _Connection ) )
{
using( OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow) )
{
try
{
if( reader.Read() )
{
RssFeed feed = MakeFeed( reader );
return feed;
}
}
finally
{
reader.Close();
}
}
}
return null;
}
public static void UpdateFeed( RssFeed item, bool isRefreshChannelCount )
{
try
{
RssFeed feed = GetFeed( item.Guid );
if( null == feed ) return;
// Same publish date, no need to update
if( feed.PublishDate == item.PublishDate ) return;
// More than one month old feed updated, who cares
if( item.PublishDate < DateTime.Now.AddMonths(-1) ) return;
// Same content, no need
if( feed.XML.Trim() == item.XML.Trim() ) return;
// The GUID is not added in the SQL as parameter because it does not work. For some reason,
// anything in WHERE clause cannot accept parameters instead need to be passed explicitely.
string sql = @"UPDATE RssFeeds SET [Title] = @Title, [XML] = @Xml, [PubDate] = @PubDate, [IsRead] = @IsRead, [IsInOutlook] = @IsInOutlook WHERE [Guid] = '" + item.Guid + "' AND NOT (PubDate = #" + item.PublishDate.ToString() + "#);";
using( OleDbCommand cmd = new OleDbCommand( sql, _Connection ) )
{
PrepareCommandForRssFeed( item, cmd );
cmd.Parameters.RemoveAt(0); // Remove the GUID parameter as it is not defined as parameter
int rowsAffected = cmd.ExecuteNonQuery();
//Debug.Assert( rowsAffected > 0, "Update was not successful" );
if( isRefreshChannelCount )
{
RefreshChannelCounts( item.ChannelID );
}
}
}
catch( Exception x )
{
Debug.WriteLine( x );
}
}
/// <summary>
/// Marks all items in the channel as read
/// </summary>
/// <param name="isRead"></param>
/// <param name="channelID"></param>
/// <param name="isRefreshChannelCounts"></param>
public static void MarkAsRead( bool isRead, int channelID, bool isRefreshChannelCounts )
{
// The ChannelID is not added in the SQL as parameter because it does not work. For some reason,
// anything in WHERE clause cannot accept parameters instead need to be passed explicitely.
using( OleDbCommand cmd = new OleDbCommand( "UPDATE RssFeeds SET [IsRead] = " + (isRead ? "1" : "0") + " WHERE [ChannelID]= " + channelID.ToString(),
_Connection ) )
{
cmd.ExecuteNonQuery();
}
if( isRefreshChannelCounts )
{
RefreshChannelCounts( channelID );
}
}
public static void MarkAsRead( bool isRead, string guid, int channelID, bool isRefreshChannelCounts )
{
// The GUID is not added in the SQL as parameter because it does not work. For some reason,
// anything in WHERE clause cannot accept parameters instead need to be passed explicitely.
using( OleDbCommand cmd = new OleDbCommand( "UPDATE RssFeeds SET [IsRead] = " + (isRead ? "1" : "0") + " WHERE [Guid]= '" + guid + "'",
_Connection ) )
{
cmd.ExecuteNonQuery();
}
if( isRefreshChannelCounts )
{
RefreshChannelCounts( channelID );
}
}
public static void DeleteFeedsFromChannel( int channelID )
{
// The ChannelID is not added in the SQL as parameter because it does not work. For some reason,
// anything in WHERE clause cannot accept parameters instead need to be passed explicitely.
using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM RssFeeds WHERE [ChannelID] = " + channelID.ToString(),
_Connection ) )
{
cmd.ExecuteNonQuery();
}
RefreshChannelCounts( channelID );
}
public static void DeleteFeed( string guid, int channelID, bool isRefreshChannelCount )
{
// The GUID is not added in the SQL as parameter because it does not work. For some reason,
// anything in WHERE clause cannot accept parameters instead need to be passed explicitely.
using( OleDbCommand cmd = new OleDbCommand( "DELETE FROM RssFeeds WHERE [Guid] = '" + guid + "'",
_Connection ) )
{
cmd.ExecuteNonQuery();
}
if( isRefreshChannelCount )
{
RefreshChannelCounts( channelID );
}
}
/// <summary>
/// Mark this channel as all items are in outlook now
/// </summary>
/// <param name="channelID"></param>
public static void RssInOutlook( int channelID)
{
// The ChannelID is not added in the SQL as parameter because it does not work. For some reason,
// anything in WHERE clause cannot accept parameters instead need to be passed explicitely.
using( OleDbCommand cmd = new OleDbCommand( "UPDATE RssFeeds SET IsInOutlook=1 WHERE [ChannelID] = " + channelID.ToString(),
_Connection ) )
{
cmd.ExecuteNonQuery();
}
}
#endregion
#region Manual Execution
public static IDataReader ExecuteReader( string sql )
{
using( OleDbCommand command = new OleDbCommand( sql, _Connection ) )
{
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
public static int ExecuteNonQuery( string sql )
{
using( OleDbCommand command = new OleDbCommand( sql, _Connection ) )
{
try
{
return command.ExecuteNonQuery();
}
catch( OleDbException x )
{
Debug.WriteLine( x );
throw x;
}
}
}
#endregion
}
}
|