using System;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Text;

using DataStreams.ETL;
using DataStreams.Xml;

namespace LargeXMLReader
{
	public class LoadDiggRSS
	{
		static void Main(string[] args)
		{
			//<?xml version="1.0" encoding="UTF-8"?>
			//<feed xmlns="http://www.w3.org/2005/Atom" xmlns:digg="http://digg.com/docs/diggrss/" xmlns:media="http://search.yahoo.com/mrss/" xmlns:feedburner="http://rssnamespace.org/feedburner/ext/1.0" xml:lang="en-US">
			//    <title>Stories</title>
			//    <subtitle>Stories</subtitle>
			//    <updated>2011-10-05T11:30:29Z</updated>
			//    <id>http://services.digg.com/</id>
			//    <author>
			//        <name>Digg</name>
			//    </author>
			//    <atom10:link xmlns:atom10="http://www.w3.org/2005/Atom" rel="self" type="application/atom+xml" href="http://feeds.digg.com/digg/topic/programming/popular" /><feedburner:info uri="digg/topic/programming/popular" /><atom10:link xmlns:atom10="http://www.w3.org/2005/Atom" rel="hub" href="http://pubsubhubbub.appspot.com/" /><entry>
			//        <title>How Magazines Are Adapting to the Mobile Revolution</title>
			//        <link href="http://feeds.digg.com/~r/digg/topic/programming/popular/~3/1evIqO7R2kU/how_magazines_are_adapting_to_the_mobile_revolution" />
			//        <content type="html">Magazine editors discuss early lessons from tablet publishing, and the challenges of extending their content to a proliferating number of tablet sizes and platforms going forward.&lt;img src="http://feeds.feedburner.com/~r/digg/topic/programming/popular/~4/1evIqO7R2kU" height="1" width="1"/&gt;</content>
			//        <updated>2011-10-04T22:14:11Z</updated>
			//        <digg:diggCount>
			//            131
			//        </digg:diggCount>
			//        <digg:category>
			//            Technology
			//        </digg:category>
			//        <digg:commentCount>
			//            1
			//        </digg:commentCount>
			//        <media:thumbnail height="62" url="http://cdn3.diggstatic.com/story/how_magazines_are_adapting_to_the_mobile_revolution/t.png" width="62" />
			//        <media:group>
			//            <media:content height="160" url="http://cdn1.diggstatic.com/story/how_magazines_are_adapting_to_the_mobile_revolution/l.png" width="160" />
			//            <media:content height="48" url="http://cdn1.diggstatic.com/story/how_magazines_are_adapting_to_the_mobile_revolution/s.png" width="48" />
			//            <media:content height="120" url="http://cdn3.diggstatic.com/story/how_magazines_are_adapting_to_the_mobile_revolution/m.png" width="120" />
			//            <media:content height="62" url="http://cdn3.diggstatic.com/story/how_magazines_are_adapting_to_the_mobile_revolution/t.png" width="62" />
			//        </media:group>
			//        <id>http://digg.com/news/technology/how_magazines_are_adapting_to_the_mobile_revolution</id>
			//    <feedburner:origLink>http://digg.com/news/technology/how_magazines_are_adapting_to_the_mobile_revolution?utm_campaign=Feed%3A+http%3A%2F%2Fservices.digg.com%2F&amp;utm_medium=feed&amp;utm_source=diggapi</feedburner:origLink></entry>
			//    ...
			//</feed>

			using (SqlConnection dbConn = new SqlConnection(@"Data Source=.;Initial Catalog=Samples;Integrated Security=True"))
			{
				dbConn.Open();

				using (SqlTransaction tran = dbConn.BeginTransaction())
				{
					try
					{
						using (SqlCommand createTable = new SqlCommand(@"
							IF NOT EXISTS
							(
								SELECT
									*
								FROM
									sys.tables t
								WHERE
									t.[name] = 'DiggItem'
							)
							BEGIN
								CREATE TABLE dbo.DiggItem
								(
									digg_item_id INT IDENTITY(1,1) NOT NULL,
									title VARCHAR(MAX) NOT NULL,
									link VARCHAR(MAX) NOT NULL,
									description VARCHAR(MAX) NOT NULL,
									publish_date DATETIME NOT NULL,
									digg_count INT NOT NULL,
									category VARCHAR(MAX) NOT NULL,
									comment_count INT NOT NULL,
									CONSTRAINT PK_DiggItem PRIMARY KEY CLUSTERED
									(
										digg_item_id
									)
								);
							END
							",
							dbConn,
							tran))
						{
							createTable.ExecuteNonQuery();
						}

						HttpWebRequest request = HttpWebRequest.Create(
							"http://feeds.digg.com/digg/topic/programming/popular.rss") as HttpWebRequest;

						using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
						using (Stream responseStream = response.GetResponseStream())
						using (XmlDataReader reader = new XmlDataReader(
							responseStream,
							Encoding.UTF8,
							"atom:feed/atom:entry",
							LoadMethod.Streaming))
						using (SqlBulkCopy bcp = new SqlBulkCopy(
							dbConn,
							SqlBulkCopyOptions.Default,
							tran))
						{
							reader.AddNamespace("digg", "http://digg.com/docs/diggrss/");
							reader.AddNamespace("media", "http://search.yahoo.com/mrss/");
							reader.AddNamespace("feedburner", "http://rssnamespace.org/feedburner/ext/1.0");
							reader.AddNamespace("atom", "http://www.w3.org/2005/Atom");

							reader.Columns.Add("VARCHAR", "atom:title", "title");
							reader.Columns.Add("VARCHAR", "atom:link/@href", "link");
							reader.Columns.Add("VARCHAR", "atom:content", "description");
							reader.Columns.Add("DATETIME", "atom:updated", "pubDate");
							reader.Columns.Add("INT", "digg:diggCount", "diggCount");
							reader.Columns.Add("VARCHAR", "digg:category", "category");
							reader.Columns.Add("INT", "digg:commentCount", "commentCount");

							bcp.DestinationTableName = "dbo.DiggItem";
							bcp.BatchSize = 1000;
							bcp.BulkCopyTimeout = 300;

							bcp.ColumnMappings.Add("title", "title");
							bcp.ColumnMappings.Add("link", "link");
							bcp.ColumnMappings.Add("description", "description");
							bcp.ColumnMappings.Add("pubDate", "publish_date");
							bcp.ColumnMappings.Add("diggCount", "digg_count");
							bcp.ColumnMappings.Add("category", "category");
							bcp.ColumnMappings.Add("commentCount", "comment_count");

							using (ValidatingDataReader validator = new ValidatingDataReader(
								reader,
								dbConn,
								bcp,
								tran))
							{
								bcp.WriteToServer(reader);
							}
						}

						tran.Commit();
					}
					catch
					{
						try
						{
							tran.Rollback();
						}
						catch
						{

						}

						throw;
					}
				}
			}

			Console.WriteLine("Done.");
			Console.ReadLine();
		}
	}
}