In Part 1 of this series I went over the creation of a Windows Service and it's remote client. For this installment, I will be covering the use of "LINQ to XML" to gather the feed, and the saving and loading of the XML configuration files. I'll also show some code to download and unzip the videos using SharpZipLib from ic#code.

The first bit of code we are going to need is a class to hold our feed data. We will be using a List<dnrFeedList> to query against.  Here the code for the class. Nothing strange here, just a datetime field and four strings.

   1:    public class dnrFeedList

   2:      {

   3:          public string Title { get; set; }

   4:          public DateTime Published { get; set; }

   5:          public string Enclosure { get; set; }

   6:          public string Description { get; set; }

   7:          public string GUID { get; set; }

   8:      }

Let's make a few changes to our service. First we need to change our services' timer elapsed event. We want to download the feed list and loop through each show we have not already downloaded. After each download we want to update our XML file in case the program is shutdown. After all shows are downloaded for that day, we will update the last checked date.

   1:   void PollTimer_Elapsed(object sender, ElapsedEventArgs e)

   2:          {

   3:              if (Feed.LastCheckDate < DateTime.Now)

   4:              {

   5:                  foreach (dnrFeedList f in Feed.GetList())

   6:                  {

   7:                      Feed.DownloadFile(f);

   8:                      Feed.SaveXML();

   9:                  }

  10:                  // Add a day to our last check date. 

  11:                  Feed.LastCheckDate = Feed.LastCheckDate.AddDays(1);

  12:                  Feed.SaveXML();

  13:              }

  14:          }

Now we want to pull down the feed from DNRTV. The following LINQ to XML code has been posted online several times over on blogs such as Scott Guthrie. The only addition I made was to the where clause to filter out the videos that have already been downloaded. We will it in a generic called Episode List. This list is a List<String> that contains the shows GUID.  After each successful downloaded, we add that shows GUID to this list.

Also needed was a DateTimeZone class to parse in and correct the time zone issue ( ex. "-0500" instead of "EST" ).  I won't post that code here, but it will be included in the final download.

   1:  public List<dnrFeedList> GetList()

   2:          {

   3:   

   4:              XDocument x = XDocument.Load(URL);

   5:              var feeds = from feed in x.Descendants("item")

   6:                          orderby DateTimeZone.ParseDateTime(feed.Element("pubDate").Value.ToString()) descending

   7:                          where EpisodeList.Contains(feed.Element("guid").Value) == false

   8:                          select new dnrFeedList

   9:                          {

  10:                              Title = feed.Element("title").Value.ToString(),

  11:                              Published = DateTimeZone.ParseDateTime(feed.Element("pubDate").Value.ToString()),

  12:                              Enclosure = feed.Element("enclosure").Attribute("url").Value.ToString(),

  13:                              Description = feed.Element("description").Value.ToString(),

  14:                              GUID = feed.Element("guid").Value

  15:                          };

  16:   

  17:              return feeds.ToList();

  18:          }

Now that we have our feed list, we loop through each show and do our download using a generic TEMP.ZIP filename for each download. Afterward, we unzip the video into the video directory, add the GUID to our Episode List  and delete the TEMP.ZIP.

   1:  public void DownloadFile(dnrFeedList uri)

   2:          {

   3:              // Download ZIP file with TEMP.ZIP as the filename. 

   4:              // It will be deleted after it is unzipped. 

   5:              string filename = this.VideoDirectory + @"TEMP.ZIP";

   6:          

   7:              WebClient web = new WebClient();

   8:              web.DownloadFile(uri.Enclosure.ToString(), filename);

   9:   

  10:              const int bufferSize = 4096;

  11:              byte[] buffer = new byte[bufferSize];

  12:              int count = 0;

  13:   

  14:              // Here we are using the SharpZipLib from ic#code.

  15:              ZipInputStream s = new ZipInputStream(File.OpenRead(filename));

  16:              ZipEntry f;

  17:   

  18:              while ((f = s.GetNextEntry()) != null)

  19:              {

  20:                  string out_filename = this.VideoDirectory + string.Format("{0}", f.Name);

  21:                  if (!File.Exists(out_filename))

  22:                  {

  23:                      FileStream sw = new FileStream(out_filename, FileMode.Create, FileAccess.Write, FileShare.None);

  24:   

  25:                      while (true)

  26:                      {

  27:                          count = s.Read(buffer, 0, bufferSize);

  28:                          if (count > 0)

  29:                          {

  30:                              sw.Write(buffer, 0, bufferSize);

  31:                          }

  32:                          else break;

  33:                      }

  34:                      sw.Close();

  35:                  }

  36:              }

  37:   

  38:              // We need to the GUID for this show to our already downloaded

  39:              // episode list. Delete the Temp.zip afterward.

  40:              EpisodeList.Add(uri.GUID.ToString());

  41:              File.Delete(filename);

  42:          }

The final parts of the code I want to go over are the loading and saving of the configuration XML file. This file contains the last date checked and a list of already downloaded shows. It amazes me that this file can be created in two statements. It is longer than two lines, but it is still just two statements, a constructor and a save method. Notice the LINQ to Generic query for the show list section. Pretty cool.

   1:   public void SaveXML()

   2:          {

   3:   

   4:              XDocument doc = new XDocument(

   5:                  new XDeclaration("1.0", "utf-8", "yes"),

   6:                  new XComment("dnrTv Aggregator Configuration"),

   7:                  new XElement("Aggregator",

   8:                      new XElement("Configuration", new XElement("LastCheckDate", DateTime.Now.ToShortDateString())),

   9:                      new XElement("ShowList", from s in EpisodeList

  10:                                               select new XElement("Show", new XElement("GUID", s.ToString()))

  11:                              )));

  12:   

  13:              doc.Save(path);

  14:          }

Loading the file back in is just about as simple. In fact, there is likely a cooler way of doing this that I just have not discovered yet. Drop me a comment if you have one. Now we use LINQ to XML to pull the data and load it into our previous downloaded show list.

   1:    private void LoadXML()

   2:          {

   3:              if (File.Exists(path))

   4:              {

   5:                  XElement doc = XElement.Load(path);

   6:   

   7:                  LastCheckDate = DateTime.Parse(doc.Element("Configuration").Element("LastCheckDate").Value);

   8:                  var SavedList = from d in doc.Element("ShowList").Elements("Show")

   9:                                  select (string)d.Element("GUID");

  10:   

  11:                  foreach (string s in SavedList)

  12:                  {

  13:                      EpisodeList.Add(s);

  14:                  }

  15:   

  16:              }

  17:          }

I have this code up and running on my Homer Server. It is checking daily for new shows and then downloads them to a shared video directory. My next step will be to rewrite my client application to use WCF has it communication protocol and to fancy up it's features. At that point I will be posting the full code for download.

Code Updated 2-10-2008

Return of the Stored Procedure

We have seen a couple examples of how to use LINQ to SQL in the previous posts  LINQ to SQL Part 1, LINQ to SQL Part 2, and how easy it is to use. The next question now becomes, What if your company strictly uses stored procedures for all of its data accessing?  Companies today can have stored procedures number from the hundreds to the thousands. With LINQ to SQL, you can still call your stored procedures and even mix and match the type styles.

First off, if you are using SQLMETAL, you will have to add an extra parameter to your DataContext build. Here is the needed extra parameter hi-lighted in red. This will again build the needed class to enable you to access the database.

SQLMETAL /SERVER:ORCASBETA2_VSTS\SQLEXPRESS /DATABASE:NORTHWIND /CODE:NWIND.CS /SPROCS

Once we have the class built and added to our project, we can get down to work. Here is a some sample code using the Northwind database.

   1: static void TestWithAdo()

   2:        {

   3:            SqlConnection conn = new SqlConnection(Properties.Settings.Default.NorthwindConnectString);

   4:  

   5:            SqlCommand spCommand = new SqlCommand("SalesByCategory", conn);

   6:            spCommand.CommandType = CommandType.StoredProcedure;

   7:            spCommand.Parameters.Add("@CategoryName", "Seafood");

   8:            spCommand.Parameters.Add("@OrdYear", "2007");

   9:  

  10:            conn.Open();

  11:  

  12:            SqlDataReader reader = spCommand.ExecuteReader();

  13:            while (reader.Read())

  14:            {

  15:                Console.WriteLine("{0,-20}\t{1,10:c}",

  16:                            reader["ProductName"].ToString().PadRight(20, ' ').Substring(0, 20),

  17:                            reader["TotalPurchase"]);

  18:            };

  19:  

  20:            reader.Close();

  21:            conn.Close();

  22:        }

First, let's look at the standard ADO.NET way of accessing a stored procedure in .NET.  We create a SqlConnection object, followed by a SqlCommand Object. We than have to add parameters and open the connection. Finally, we need to create an SqlDataReader object before we can retrieve the first row of data from the database. Quite a few steps for each a every stored procedure we wish to call. 

Now lets take a look at LINQ to SQL 

   1: static void TestWithLinq()

   2:         {

   3:             Northwind db = new Northwind(Properties.Settings.Default.NorthwindConnectString);

   4:  

   5:             var SalesReport = db.SalesByCategory("Seafood", "2007");

   6:  

   7:             foreach (var SalesItem in SalesReport)

   8:             {

   9:                 Console.WriteLine("{0,-20}\t{1,10:c}",

  10:                         SalesItem.ProductName.PadRight(20, ' ').Substring(0, 20),

  11:                         SalesItem.TotalPurchase);

  12:             }

  13:         }

With LINQ we can really reduce most of the needed ADO.NET code down to just a line or two.  Once the Northwind object has been instantiated, we can simply call the stored procedure like any other method in the class as observed in the above code.  I don't know any programmer that uses SQL day in and out that won't be foaming at the mouth for this.

   1: select e.lastname, e.firstname, t.territoryDescription,r.regiondescription

   2: from employees e

   3: join employeeterritories et on e.employeeid = et.employeeid 

   4: join territories t on t.territoryid = et.territoryid

   5: join region r on r.regionid = t.regionid

   6: order by lastname,firstname

   1: var EmployeeList = from employees in nw.Employees

   2:                    join employeeTerritories in nw.EmployeeTerritories 

   3:                      on employees.EmployeeID equals employeeTerritories.EmployeeID

   4:                    join territories in nw.Territories 

   5:                      on employeeTerritories.TerritoryID equals territories.TerritoryID                               

   6:                    join region in nw.Region 

   7:                      on territories.RegionID equals region.RegionID  

   8:                    orderby employees.LastName,employees.FirstName 

   9:                    select new

  10:                    {

  11:                        employees.LastName,

  12:                        employees.FirstName,

  13:                        territories.TerritoryDescription,

  14:                        region.RegionDescription 

  15:                    };

The following code shows not only the query above, but how simple it is to loop through your resultset and display the information. Remember, when writing your code, you will get the full use of intellisense to help you.

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Text;

   5:  

   6: namespace ConsoleTest001

   7: {

   8:  

   9:     class Program

  10:     {

  11:         static void Main(string[] args)

  12:         {

  13:             Northwind nw = new Northwind(Properties.Settings.Default.NorthwindConnectString);

  14:  

  15:             var EmployeeList = from employees in nw.Employees

  16:                                join employeeTerritories in nw.EmployeeTerritories 

  17:                                  on employees.EmployeeID equals employeeTerritories.EmployeeID

  18:                                join territories in nw.Territories 

  19:                                  on employeeTerritories.TerritoryID equals territories.TerritoryID                               

  20:                                join region in nw.Region 

  21:                                  on territories.RegionID equals region.RegionID  

  22:                                orderby employees.LastName,employees.FirstName 

  23:                                select new

  24:                                {

  25:                                    employees.LastName,

  26:                                    employees.FirstName,

  27:                                    territories.TerritoryDescription,

  28:                                    region.RegionDescription 

  29:                                };

  30:                           

  31:             foreach(var e in EmployeeList)

  32:             {

  33:                 Console.WriteLine("{0,-10} {1,-10}\t{2}\t{3,20}", 

  34:                                     e.LastName, 

  35:                                     e.FirstName,

  36:                                     e.TerritoryDescription.PadRight(20,' ').Substring(0,20),

  37:                                     e.RegionDescription);

  38:             }

  39:  

  40:             Console.ReadLine();

  41:  

  42:         }

  43:     }

  44: }

Here is the result of of the LINQ to SQL query written out to the console.

I'm sure everyone so far has at least heard of LINQ. If you haven't, it is Microsoft's new technology for Language Integrated Querying.  LINQ is part of the upcoming 3.5 .Net Framework which will be part of Visual Studio 2008 ( codename Orcas ). LINQ will be able to query in memory objects such as Generics or datasets  in addition to external items like XML documents and SQL databases. As of this post, Visual Studio 2008 is currently in Beta 2 and available for download from Microsoft.

The flavors of LINQ include:

• LINQ to SQL
• LINQ to Objects
• LINQ to  Datasets
• LINQ  to XML

This will be a LINQ to SQL example so I will be using the standard Northwind database for this article, you will need it installed onto your machine. If you will be using the SQL Express Edition I would also recommend installing SQL Management Studio Express. It will make the management of your database much easier.

The first thing we need to do is to create a DataContext for the our Northwind database. There are two ways to do this: 1. Use SQLMETAL to create a entity class if you need to model a complete database automatically. This way is also useful if you need have a constantly changing scheme since you can have SQLMETAL run each time you build your application using the Pre-Build Event.  2. The second way is to create a DataContext via the LINQ to SQL template under 'Add new Item to Project', but I won't be going into that way in this article.

Open a command prompt and go to your project directory and type:

SQLMETAL /SERVER:ORCASBETA2_VSTS\SQLEXPRESS /DATABASE:NORTHWIND /CODE:NWIND.CS

This will generate a class file that you can add to you project by right clicking Project->Add->Existing Items and selecting the nwind.cs for the file list.  You will next need to add a reference to System.Data.Linq to your project and then your will be ready to add your code. The following code queries the customer table for all records and adds the CustomerName to a listbox on the screen.

   1: namespace Linq001

   2: {

   3:     /// <summary>

   4:     /// Interaction logic for Window1.xaml

   5:     /// </summary>

   6:     public partial class Window1 : Window

   7:     {

   8:         public Window1()

   9:         {

  10:             InitializeComponent();

  11:         }

  12:  

  13:         private void LoadButton_Click(object sender, RoutedEventArgs e)

  14:         {

  15:          

  16:             Northwind nw = new Northwind(@Properties.Settings.Default.NorthwindConnectionString );

  17:  

  18:             var CustomerList = from Customers in nw.Customers

  19:                                select Customers;   

  20:  

  21:             foreach(Customers c in CustomerList)

  22:             {

  23:                 CustomerListBox.Items.Add(c.CompanyName); 

  24:             }

  25:             

  26:         }

  27:     }

  28: }

As you can see, this process is pretty straight forward. Notice the reverse SQL statement order. The from clause comes before the select statement. The result is put into a anonymous type that can be iterated through with a for each loop and added to the listbox.

This a extremely simple first example. I will delve into this subject much deeper in the coming weeks.