// 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.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];
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 );
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;
}
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) VALUES (@Title, @FeedURL, @Frequency, " +
"@LastUpdated, @NextUpdate, @LastUpdatedInOutlook, @FolderPath, @XSLPath, " +
"@ShowInNewspaper, @ItemCount, @UnreadCount, @FolderType, @OutlookViewXmlPath, @OutlookXSL )",
_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 " +
" 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
{
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 );
return false;
}
else
{
throw x;
}
}
}
public static void MarkAsRead( bool isRead, string guid, int channelID, bool isRefreshChannelCounts )
{
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 )
{
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 )
{
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)
{
using( OleDbCommand cmd = new OleDbCommand( "UPDATE RssFeeds SET IsInOutlook=1 WHERE [ChannelID] = " + channelID.ToString(),
_Connection ) )
{
cmd.ExecuteNonQuery();
}
}
#endregion
}
}