Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

RSS Feed Aggregator and Blogging Smart Client

, 16 Aug 2005 CPOL
RSS Feed aggregator and blogging Smart Client which uses Enterprise Library, Updater Application Block, lots of XML hacks and desktop tricks. A comprehensive guide to real life hurdles of Smart Client development.
rssfeeder_src.zip
RSSFeeder
RSSBlogAPI
RSSBlogAPI.csproj.user
Web References
CommunityServer
blogservice.disco
blogservice.wsdl
Reference.map
DotText
Reference.map
simpleblogservice.disco
simpleblogservice.wsdl
RSSCommon
Helper
PropertyEditor
RSSCommon.csproj.user
RSSFeeder
Controls
docs
dropshadow.png
My Pic 7.jpg
WIndows98.jpg
Helpers
Resources
E-mail.ico
rss.ico
RSSFeeder.mdb
RSSFeeder.csproj.user
RSSStarter.exe
RSSFeederResources
PublicQueue.ico
RSSFeeder.mdb
RSSFeederResources.csproj.user
RSSFeederSetup
Messages.ico
RSSFeederSetup.vdproj
Test
RSSStarter
DummyForm.frm
DummyForm.frx
MainModule.bas
RSSStarter.exe
RSSStarter.vbp
RSSStarter.vbw
RSSTests
App.ico
RSSTests.csproj.user
Thirdparty
AxInterop.DHTMLEDLib.dll
AxInterop.SHDocVw.dll
Eyefinder.dll
GotDotNet.Exslt.dll
HttpDownloader.dll
Interop.DHTMLEDLib.dll
Interop.SHDocVw.dll
Interop.WindowsInstaller.dll
Microsoft.ApplicationBlocks.Updater.ActivationProcessors.dll
Microsoft.ApplicationBlocks.Updater.dll
Microsoft.ApplicationBlocks.Updater.Downloaders.dll
Microsoft.Practices.EnterpriseLibrary.Caching.dll
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Configuration.dll
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.dll
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.dll
Microsoft.Practices.EnterpriseLibrary.Logging.dll
Microsoft.Practices.EnterpriseLibrary.Security.Cache.CachingStore.dll
Microsoft.Practices.EnterpriseLibrary.Security.Cryptography.dll
Microsoft.Practices.EnterpriseLibrary.Security.dll
NotifyIconBalloon.dll
SandBar.dll
SandDock.dll
SgmlReaderDll.dll
// 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
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Omar Al Zabir
Architect BT, UK (ex British Telecom)
United Kingdom United Kingdom

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 16 Aug 2005
Article Copyright 2005 by Omar Al Zabir
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid